按平均值分组的SQL查询

Question

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

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 |

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

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

查询的结果应该像这样：

+------------+---------+---------+---------+---------+---------+---------+---------+
| 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"都会有更多行。

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

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

这个代码可以返回正确的结果，但我需要对其他可能的 'pass' 值进行扩展(最多到7)。

我尝试使用AVG内嵌SELECT，但这样做不起作用，因为我没有弄清楚如何正确地将其限制在当前的'id'上。

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

有什么想法吗？

- theFen

---

您有3条记录，其pass为5，所有记录的ID不同。您想要哪个ID呢？请发布您期望的数据输出结果。 - Quassnoi

你的平均值必须在列中吗？还是你想要的查询可以返回与不同的通过值一样多的行？ - Grzegorz W

我编辑了我的问题并添加了一个表格，展示了我期望的结果。我必须将结果提供给另一个程序，因此每行应该有一个ID。 - theFen

我的修改后的查询提供了你所要求的确切结果... - Marco

Answer 1

如果我理解你的需求，可以尝试这个：

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

或者，如果你只想要每个 id 的一行记录，可以使用以下方法：

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

- Marco

---

Marco，我刚刚尝试了你的解决方案，看起来它确实做到了我想要的。我不知道你可以从“内部”SELECT引用到外部的那个。非常感谢！ - theFen

Answer 2

据我了解，您希望在每次通过时计算每个 ID 的平均值。解决方案如下：

SELECT id, pass, avg(value) FROM data_r1
GROUP BY id, pass;

- Cristian Iordache

Answer 3

我假设您有两个类似以下数据的表：

id	sensor_id	amount
1	1	100
2	1	200
3	2	300

传感器

ID	名称
1	传感器1
2	传感器2

您想要以下结果：

ID	AVG_rows_sensor
1	150
2	300

---

用于获取上述结果的MySQL查询语句：

SELECT AVG(amount) AS AVG_rows_sensor FROM DW GROUP BY sensor_id;

- sadiya khan