5 分钟搞懂窗口函数
ROW_NUMBER / RANK / LAG / LEAD / 滑动窗口聚合
窗口函数是 MariaDB 10.2+ 的标准 SQL 特性。让你不打散行就能做聚合和排名。
第一个例子
SELECT user_id, amount,
SUM(amount) OVER (PARTITION BY user_id) AS user_total
FROM orders;| user_id | amount | user_total |
|---|---|---|
| 1 | 100 | 300 |
| 1 | 200 | 300 |
| 2 | 50 | 50 |
每行都保留原貌，user_total 是"这个 user 的全部订单总和"。
常用窗口函数
ROW_NUMBER / RANK / DENSE_RANK
SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
RANK() OVER (ORDER BY score DESC) AS rk,
DENSE_RANK() OVER (ORDER BY score DESC) AS dr
FROM students;| name | score | rn | rk | dr |
|---|---|---|---|---|
| alice | 95 | 1 | 1 | 1 |
| bob | 95 | 2 | 1 | 1 |
| carol | 90 | 3 | 3 | 2 |
| dan | 85 | 4 | 4 | 3 |
ROW_NUMBER：唯一序号RANK：并列后跳号（1,1,3）DENSE_RANK：并列后不跳号（1,1,2）
每组 TOP N
-- 每个用户最近 3 笔订单
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
) t
WHERE rn <= 3;LAG / LEAD（看相邻行）
SELECT day, revenue,
LAG(revenue, 1) OVER (ORDER BY day) AS prev_day,
revenue - LAG(revenue, 1) OVER (ORDER BY day) AS diff
FROM daily_sales;| day | revenue | prev_day | diff |
|---|---|---|---|
| 2026-01-01 | 1000 | NULL | NULL |
| 2026-01-02 | 1200 | 1000 | 200 |
滑动窗口聚合（7 日均线）
SELECT day, revenue,
AVG(revenue) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS ma7
FROM daily_sales;ROWS BETWEEN N PRECEDING AND CURRENT ROW 定义"前 N 行 + 当前行"的窗口。
累计求和
SELECT day, revenue,
SUM(revenue) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING) AS cum
FROM daily_sales;性能提示
- 窗口函数本身很高效，比子查询通常快
- 需要
ORDER BY时，如果有合适的索引能极大加速 PARTITION BY列建索引