mysql 窗口函数(Window Functions)详解
mysql 窗口函数(Window Functions)详解
窗口函数是 SQL 中的一种强大工具,它允许你在不改变原始表行数的情况下对数据进行聚合计算。与传统的 GROUP BY
不同,窗口函数不会将多行合并为一行,而是可以在每一行上应用聚合操作,同时保留原始的行结构。这使得窗口函数非常适合用于复杂的分析任务,例如:
- 移动平均:计算某个时间段内的平均值。
- 排名:为每行数据分配一个排名。
- 累计和:计算到当前行为止的累计总和。
- 前后行比较:比较当前行与前几行或后几行的数据。
1. 窗口函数的基本语法
-- 完整示例
SELECT
time, subject, val,
SUM(val) OVER (PARTITION BY subject ORDER BY time ROWS UNBOUNDED PRECEDING) AS running_total,
AVG(val) OVER (PARTITION BY subject ORDER BY time ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS running_average
FROM observations;
+----------+---------+------+---------------+-----------------+
| time | subject | val | running_total | running_average |
+----------+---------+------+---------------+-----------------+
| 07:00:00 | st113 | 10 | 10 | 9.5000 |
| 07:15:00 | st113 | 9 | 19 | 14.6667 |
| 07:30:00 | st113 | 25 | 44 | 18.0000 |
| 07:45:00 | st113 | 20 | 64 | 22.5000 |
| 07:00:00 | xh458 | 0 | 0 | 5.0000 |
| 07:15:00 | xh458 | 10 | 10 | 5.0000 |
| 07:30:00 | xh458 | 5