使用AVG 和GROUP BY 的SQL 查詢
在SQL 中，您可能會遇到需要從表中提取組平均值的情況。這可以使用 AVG() 和 GROUP BY 子句來實現。
考慮範例表：
| id | pass | val |
|---|---|---|
| DA02959106 | 5.00 | 44.40 |
| 08A5969201 | 1.00 | 182.41 |
| 08A5969201 | 2.00 | 138.78 |
| DA02882103 | 5.00 | 44.73 |
| DA02959106 | 1.00 | 186.15 |
| DA02959106 | 2.00 | 148.27 |
| DA02959106 | 3.00 | 111.91 |
| DA02959106 | 4.00 | 76.15 |
| DA02959106 | 5.00 | 44.40 |
| DA02959106 | 4.00 | 76.65 |
計算每個通道的平均值
要計算同一「id」的每個「pass」的平均值，請使用以下查詢：
<code class="sql">SELECT id, pass, AVG(val) AS val_1 FROM data_r1 GROUP BY id, pass;</code>
此查詢按“id”和“pass”對行進行分組，併計算每組的平均“val”。結果將是：
| id | pass | val_1 |
|---|---|---|
| DA02959106 | 1.00 | 186.15 |
| 08A5969201 | 1.00 | 182.41 |
| DA02882103 | 5.00 | 44.73 |
| 08A5969201 | 2.00 | 138.78 |
| DA02959106 | 2.00 | 148.27 |
| DA02959106 | 3.00 | 111.91 |
| DA02959106 | 4.00 | 76.40 |
| DA02959106 | 5.00 | 44.40 |
旋轉每個ID 一行的資料
如果您希望每個「id」有一行，並有多個欄位代表要取得每個「pass」的平均值，請使用以下查詢：
<code class="sql">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;</code>此查詢使用子查詢來擷取每個「pass」的平均值並將它們指派給對應的欄位。結果表將是：
| id | val_1 | val_2 | val_3 | val_4 | val_5 | val_6 | val_7 |
|---|---|---|---|---|---|---|---|
| DA02959106 | 186.15 | 148.27 | 111.91 | 76.40 | 44.40 | 0 | 0 |
以上是如何計算同一「id」的每個「pass」的平均值，並在 SQL 中為每個「id」轉置一行資料？的詳細內容。更多資訊請關注PHP中文網其他相關文章！
