Dev AtlasDev Atlas
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 档分数(不跳号)