带有 avg 和 group by 的 SQL 查询

2021-11-20 00:00:00 average sql group-by mysql

我在为 MySQL 编写 SQL 查询时遇到了一些问题.我有一个具有以下结构的表:

I have some problems with writing a SQL query for MySQL. I have a table with the following structure:

mysql> select id, pass, val from data_r1 limit 10;
+------------+--------------+----------------+
| id         | pass         | val            |
+------------+--------------+----------------+
| DA02959106 | 5.0000000000 |  44.4007000000 |
| 08A5969201 | 1.0000000000 | 182.4100000000 |
| 08A5969201 | 2.0000000000 | 138.7880000000 |
| DA02882103 | 5.0000000000 |  44.7265000000 |
| DA02959106 | 1.0000000000 | 186.1470000000 |
| DA02959106 | 2.0000000000 | 148.2660000000 |
| DA02959106 | 3.0000000000 | 111.9050000000 |
| DA02959106 | 4.0000000000 |  76.1485000000 |
| DA02959106 | 5.0000000000 |  44.4007000000 |
| DA02959106 | 4.0000000000 |  76.6485000000 |

我想创建一个从表中提取以下信息的查询:

I want to create a query that extracts the following information from the table:

id, AVG of 'val' for 'pass' = 1, AVG of 'val' for 'pass' = 2, etc

查询的结果应该是这样的:

The result of the query should look like this:

+------------+---------+---------+---------+---------+---------+---------+---------+
| id         | val_1   | val_2   | val_3   | val_4   | val_5   | val_6   | val_7   |
+------------+---------+---------+---------+---------+---------+---------+---------+
| DA02959106 | 186.147 | 148.266 | 111.905 | 76.3985 | 44.4007 | 0       | 0       |
+------------+---------+---------+---------+---------+---------+---------+---------+

当然，每个唯一的id"都有更多的行.

with more rows for each unique 'id', of course.

我已经尝试了一些查询，例如

I already tried some queries like

SELECT id, pass, AVG(val) AS val_1 FROM data_r1 WHERE pass = 1 GROUP BY id;

这将返回正确的结果，但我必须使用其他可能的pass"值(最多 7 个)的结果来扩展它

This returns the correct result, but I have to expand it with results for the other possible values of 'pass' (up to 7)

我尝试在 AVG 中使用嵌套的 SELECT 但这不起作用，因为我不知道如何正确地将其限制为当前的id".

I tried to use a nested SELECT within AVG but this didn't work because I didn't figure out how to correctly limit it to the current 'id'.

然后我创建了视图来表示pass"=1、pass"=2 等的每个查询的结果.但是对于大多数 id，pass"的最高值是 5.当使用 JOIN 查询来获取视图的最终结果我收到了一个空的结果集，因为一些视图是空的/没有特定id"的值.

I then created Views to represent the result of each query for 'pass' = 1, 'pass' = 2, etc. But for most ids the highest value for 'pass' is 5. When using JOIN queries to get the final result from the views I received an empty result set, because some of the Views are empty / don't have values for a specific 'id'.

有什么想法吗?

推荐答案

如果我明白你需要什么，试试这个:

If I understand what you need, try this:

SELECT id, pass, AVG(val) AS val_1 
FROM data_r1 
GROUP BY id, pass;

或者，如果您只想为每个 id 分配一行，则:

Or, if you want just one row for every id, this:

SELECT d1.id,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 1) as val_1,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 2) as val_2,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 3) as val_3,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 4) as val_4,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 5) as val_5,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 6) as val_6,
    (SELECT IFNULL(ROUND(AVG(d2.val), 4) ,0) FROM data_r1 d2 
     WHERE d2.id = d1.id AND pass = 7) as val_7
from data_r1 d1
GROUP BY d1.id

相关文章