DATABASE / SQL
SQL 窗口函数与聚合备忘
1. 分组取最新一条记录(Top-1 per Group)
WITH ranked AS (
SELECT id, user_id, amount, created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
)
SELECT id, user_id, amount, created_at
FROM ranked
WHERE rn = 1;
2. 环比与 7 日滚动均值
SELECT day,
revenue,
revenue - LAG(revenue) OVER (ORDER BY day) AS delta_prev_day,
AVG(revenue) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS ma_7d
FROM daily_metrics;
3. 排名函数差异
| 函数 | 并列行为示例(值:100, 100, 90) | 常见用途 |
ROW_NUMBER() | 1, 2, 3 | 分页去重、分组只取一条 |
RANK() | 1, 1, 3 | 传统竞赛名次(跳号) |
DENSE_RANK() | 1, 1, 2 | 取前 N 档分数(不跳号) |