使用 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中文网其他相关文章！
