如何在 MySQL 中使用'AVG”和'GROUP BY”來計算表中不同組的列的多個平均值？

使用AVG 和Group By 的SQL 查詢

在MySQL 中，提取表中不同組的列的多個平均值的查詢可以使用AVG() 和GROUP BY 子句的組合來實現。考慮具有以下結構的表data_r1：

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 |

要從此表中提取所需信息，請使用以下查詢：

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

此查詢對data_r1 表中的行進行分組透過id 和pass 列，然後計算每組val 列的平均值。結果是一張表，其中包含 id 和 pass 的每個唯一組合的一行，以及 val 的相應平均值。

另一個查詢，它實現相同的結果，但為每個唯一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

此查詢使用巢狀子查詢來計算特定id組內每個傳遞值的平均值。 IFNULL() 函數用於確保空值被 0 替換，防止除零錯誤。

以上是如何在 MySQL 中使用'AVG”和'GROUP BY”來計算表中不同組的列的多個平均值？的詳細內容。更多資訊請關注PHP中文網其他相關文章！