窗口函数 — 不折起来也能往旁边看

在 LabHub 中继续学习

一句话总结

窗口函数无需折叠行，就能让当前行引用周围其他行；排名、累计、与前一值比较等仅靠聚合难以回答的问题，可以在一次扫描中解决。

对比图: 不会折叠行。 · 不能在同一个 SELECT 的 WHERE 中使用窗口函数结果。 · 如果某个月缺失，它会与跳过该月后的上一条记录比较。 · 必须唯一识别每一行，就用 rownumber()

为什么需要它

如果只用 GROUP BY 求“每个类别价格最高的 3 件商品”，就需要自连接或相关子查询，代码更长，性能也更差。如果每一行都能知道自己在组内排第几，问题就会简单许多。

它如何工作

窗口函数采用 함수() OVER (PARTITION BY ... ORDER BY ...) 的形式。

PARTITION BY——划分窗口的标准。它与 GROUP BY 相似，但不会折叠行。
ORDER BY——窗口内的顺序，是排名与累计的依据。

常用函数可以这样区分。

函数	作用	并列处理
`row_number()`	从 1 开始编号	并列值也使用不同编号
`rank()`	排名	并列值排名相同，后续名次跳号（1,1,3）
`dense_rank()`	排名	并列值排名相同，后续名次连续（1,1,2）
`sum() OVER (ORDER BY ...)`	累计和	默认窗口帧从开头到当前行
`lag()` / `lead()`	上一行／下一行的值	不存在时返回 NULL
`ntile(n)`	划分为 n 组后的组号	各组大小尽量均匀

有一个重要限制：不能在同一个 SELECT 的 WHERE 中使用窗口函数结果。 因为按照处理顺序，WHERE 会先执行。要计算“每个类别的前 3 名”，必须多包一层子查询或 CTE，再在外层过滤。

SELECT category, id, price
FROM (
  SELECT category, id, price,
         row_number() OVER (PARTITION BY category ORDER BY price DESC, id) AS rn
  FROM products
) t
WHERE rn <= 3;

像 sum(...) OVER () 这样把括号留空，表示把全部行视为一个窗口。计算每行占总和的比例时，就不必执行两次查询。

在实际项目中

计算环比变化时，lag() 是标准做法。使用自连接会让首月处理与缺失月份处理变得繁琐，而 lag() 会自然地为不存在的值返回 NULL。不过要注意：如果某个月缺失，它会与跳过该月后的上一条记录比较。 如果要把空缺月份也包含在内，应先生成日期序列再进行 join。

选择排名函数也需要明确标准。分页或去重时，如果必须唯一识别每一行，就用 row_number()；展示给人的名次则适合用 rank() 或 dense_rank()，让并列值保持同名次。如果排序标准存在并列，结果还可能在每次执行时变化，因此最好加入 tie-break 字段。

下一项实验要做什么

依次实现编号、排名、累计和、环比、占比、各组前 N 名、四分位数，以及每位客户的首笔订单。