窗口函数与 CTE · 数据库

一句话定义

窗口函数（Window Function）在不折叠行的前提下，按「窗口」（OVER 子句定义的分组与排序范围）为每一行计算聚合或排名；CTE（Common Table Expression，WITH 子句）给一段查询命名以复用与分层，两者共同把复杂 SQL 从「嵌套面条」变成「清晰流水线」。

为什么重要

「组内排名、移动平均、环比同比、去重取最新一条」这些高频需求，用 GROUP BY 无法直接表达，用窗口函数一行搞定。
窗口函数避免了「先聚合再自连接」的复杂写法，减少一次扫描，通常显著更快。
递归 CTE 让 SQL 能处理图遍历与层级展开（组织架构树、物料清单），是与 NoSQL 图查询对垒的底气。

前置知识

kp-004（聚合与子查询）。

核心概念

窗口定义：OVER (PARTITION BY ... ORDER BY ...)，PARTITION 分「组」，ORDER 定「组内顺序」。
排名函数：ROW_NUMBER（连续唯一）、RANK（并列跳号）、DENSE_RANK（并列不跳号）。
值函数：LAG/LEAD（取前/后 N 行）、FIRST_VALUE/LAST_VALUE。
聚合作窗口：SUM(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 即移动合计。
帧（Frame）：ROWS/RANGE BETWEEN ... AND ... 显式限定当前行参与计算的行集。
CTE：WITH name AS (SELECT ...)；RECURSIVE 版本含定位成员与递归成员两段。

原理与机制

窗口函数在逻辑执行顺序中位于 GROUP BY 之后、ORDER BY 之前：它拿到的是「已过滤、未分组折叠」的行集，按 PARTITION 划分排序后逐行计算。排名三函数的差异来自并列处理：ROW_NUMBER 强行编号；RANK 并列同名次、下一名跳号；DENSE_RANK 跳号。帧默认为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，当排序列有并列值时 RANGE 会把并列行一并纳入——这就是「SUM 窗口结果与预期不符」的常见根因，显式写 ROWS 可消除歧义。递归 CTE 的机制是迭代物化：定位成员产出初始行集，递归成员反复引用 CTE 自身直至空集，深度过大需 LIMIT 防失控。

实例或案例

「每类商品中销量最高的前 2 条」（经典 Top-N per group）：

WITH ranked AS (
  SELECT product_id, category, sales,
         ROW_NUMBER() OVER (PARTITION BY category
                            ORDER BY sales DESC) AS rn
  FROM products
)
SELECT product_id, category, sales
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;

组织架构树展开（递归 CTE）：

WITH RECURSIVE sub (emp_id, mgr_id, depth) AS (
  SELECT emp_id, mgr_id, 0 FROM employees WHERE mgr_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.mgr_id, s.depth + 1
  FROM employees e JOIN sub s ON e.mgr_id = s.emp_id
)
SELECT * FROM sub ORDER BY depth;

常见误区

认为 ROW_NUMBER 稳定：排序键有并列时行号分配不确定，务必在 ORDER BY 中加入唯一列做决定性排序。
混淆窗口函数与 GROUP BY：窗口不折叠行，SUM() OVER 输出行数与输入相同；写成聚合再连表反而复杂。
递归 CTE 无终止保护：环状数据会导致无限递归，应加深度上限或已访问集合判断。

自测题

RANK 与 DENSE_RANK 在序列 1、2、2、3 上的输出？答：RANK 输出 1、2、2、4（跳号）；DENSE_RANK 输出 1、2、2、3（不跳号）。
为什么窗口 SUM 默认帧用 RANGE 时结果可能偏大？答：RANGE 按「值」把与当前行排序键并列的行都纳入帧，并列值被重复计入；需按行计数时应显式 ROWS BETWEEN ...。

公式或模型

移动平均（窗口宽 k）：MA_t = (1/k) × Σ_{i=t-k+1}^{t} x_i，用 AVG(x) OVER (ORDER BY t ROWS BETWEEN k-1 PRECEDING AND CURRENT ROW) 表达。

图示

本节不适用：窗口计算过程用上文「销售序列 + 三种排名函数」的问答已可自验，静态图示增益有限。

直观类比

GROUP BY 像把全班按小组收作业、每组只交一份汇总表；窗口函数则像给每个学生「在保留原座位的同时」发一张写有「本组排名、本组平均分」的便签——人还是那些人，信息更厚了。

与其他知识点的关系

kp-004 的 GROUP BY 是窗口的对照概念；递归 CTE 与 kp-027 的图数据库查询能力互为替代；复杂窗口查询的性能诊断入口是 kp-012。

前置知识点

相关知识点