34. 窗口函数

普通聚合函数（如 SUM、AVG）会把多行压成一行（配合 GROUP BY）。窗口函数（window function）不一样：它也跨多行算，但每一行都保留，还会在每行旁边附上计算结果。这是它最大的特点——「算跨行指标，但不丢行」。

先建一张部门工资表来演示：

CREATE TABLE empsalary (
    depname   VARCHAR(20),
    empno     INT,
    salary    NUMERIC(10,2)
);

INSERT INTO empsalary VALUES
    ('研发', 11, 5200), ('研发', 7, 4200), ('研发', 9, 4500),
    ('研发', 8, 6000), ('研发', 10, 5200),
    ('人事', 5, 3500), ('人事', 2, 3900),
    ('销售', 3, 4800), ('销售', 1, 5000), ('销售', 4, 4800);

OVER()：窗口的开关

窗口函数一定跟着一个 OVER(...) 子句，这就是它和普通函数的分界线。OVER() 里可以写分区和排序，也可以留空表示「全表是一个窗口」。

-- 每一行都带上「全表平均工资」，行本身不合并
SELECT depname, empno, salary,
       AVG(salary) OVER () AS 全表平均
FROM empsalary;

这里 AVG(salary) OVER () 把 AVG 当窗口函数用：它算的是全表均值，但因为是窗口函数，研发、人事、销售的每一行都各自带上了这个值。如果用普通 GROUP BY，这 10 行会被压成 1 行，信息就丢了。

PARTITION BY：给窗口分区

PARTITION BY 把数据按某列分成若干「小组」，窗口函数在每个小组内分别计算，但行仍然不合并。

-- 每个部门内的平均工资
SELECT depname, empno, salary,
       AVG(salary) OVER (PARTITION BY depname) AS 部门平均
FROM empsalary;

研发的每行显示研发组均值（5020），人事的每行显示人事组均值（3700），销售的每行显示销售组均值（4866.67）。行还是 10 行，没有合并。你可以把 PARTITION BY 理解成「先在内部按部门 GROUP BY 一次，再把这个值贴回每一行」。

ORDER BY：窗口内排序

OVER 里的 ORDER BY 决定窗口内按什么顺序处理。它和结果输出的排序无关，只影响函数计算（尤其是累计类和排名类函数）。

-- 每个部门内，按工资从高到低给行编号
SELECT depname, empno, salary,
       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) AS 排名
FROM empsalary;

注意：这个 ORDER BY 只决定「编号怎么排」，不会让结果集按工资排序输出。要让结果也按工资排，得在语句最后再写一个顶层 ORDER BY。

排名三兄弟：ROW_NUMBER / RANK / DENSE_RANK

这三个是常用的排序窗口函数，区别在「怎么处理并列」：

• ROW_NUMBER()：不管是否并列，一律给 1、2、3…… 连续不重复。
• RANK()：并列占同名次，但会留空位（1、1、3）。
• DENSE_RANK()：并列占同名次，且不留空位（1、1、2）。

SELECT depname, empno, salary,
       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY depname ORDER BY salary DESC) AS rk,
       DENSE_RANK() OVER (PARTITION BY depname ORDER BY salary DESC) AS drk
FROM empsalary;

研发的两条 5200 会拿到相同的 rk/drk（比如都是第 1），但 rn 仍会给它们不同的序号。并列行的 rn 先后顺序不确定，所以若想要稳定的次序，可在 ORDER BY 后加第二排序键，如 ORDER BY salary DESC, empno。

聚合函数当窗口函数：累计求和

带 ORDER BY 的聚合窗口，默认从分区开头累计到当前行，做出「累计/跑批」效果：

-- 全表按工资升序，算到当前行的累计和
SELECT salary,
       SUM(salary) OVER (ORDER BY salary) AS 累计
FROM empsalary;

第一行的累计就是它自己，第二行是前两行之和，依次累加。若想对整个分区求和（不去累计），就省略 ORDER BY，或显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

还能精确控制窗口「框」的范围，比如「当前行及前一行」：

SELECT salary,
       AVG(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS 近两行均值
FROM empsalary;

ROWS BETWEEN ... AND ... 让你定义滑动窗口，做移动平均很方便。

几个注意点

• 窗口函数只能出现在 SELECT 列表和顶层 ORDER BY 里，不能写在 WHERE、GROUP BY、HAVING 中。原因是它逻辑上在那些子句之后才执行。
• 一条查询可以有多个窗口函数，各自用不同的 OVER，但都基于同一批经过 WHERE/GROUP BY/HAVING 过滤后的行。
• 想在窗口计算之后再过滤（比如只要排名前 3），用子查询或 CTE 包一层：

SELECT * FROM (
    SELECT depname, empno, salary,
           ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) AS rn
    FROM empsalary
) t
WHERE rn <= 3;

常见错误

1. 把窗口函数写进 WHERE，报错——必须先算出来再在外层过滤。
2. 以为 OVER() 里的 ORDER BY 会排序结果，其实它只影响窗口内的计算顺序。
3. 用 RANK() 想取「前 3 名」却得到并列的 4、5 行——要严格控制行数就用 ROW_NUMBER()。
4. 忘记 PARTITION BY，导致排名是在全表范围内算的，而非分组内。

NTILE：把数据分桶

NTILE(n) 把分区内的行平均分成 n 个桶，给每行标上桶号。适合做「按比例分组」，比如把员工按工资分成高、中、低三档：

SELECT depname, empno, salary,
       NTILE(3) OVER (ORDER BY salary DESC) AS 档位
FROM empsalary;

第 1 桶是工资最高的三分之一，第 3 桶最低。行数不能整除时，前面的桶会多分一行。

取值类窗口函数

除了排名，还有直接取「窗口内某一行的值」的函数：

• FIRST_VALUE(列)：分区（到当前行）第一行的值。
• LAST_VALUE(列)：分区（到当前行）最后一行的值。
• NTH_VALUE(列, n)：第 n 行的值。

SELECT depname, empno, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY depname ORDER BY salary) AS 组内最低,
       LAST_VALUE(salary)  OVER (PARTITION BY depname ORDER BY salary
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 组内最高
FROM empsalary;

窗口帧（frame）到底是什么

带 ORDER BY 的窗口默认帧是「从分区开头到当前行」（RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）。累计求和之所以逐行变大，正是因为帧随当前行往后延伸。想改范围，用 ROWS BETWEEN ... AND ... 精确框定，比如 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 就是「当前行加前两行」的滑动窗口。理解帧，是写对累计、移动平均的关键。

没有 ORDER BY 时窗口什么样

如果 OVER() 里只写 PARTITION BY 而不写 ORDER BY，窗口默认覆盖整个分区，此时没有「逐行延伸」的帧概念，适合算「分组总和 / 分组均值」这类不依赖顺序的指标。换句话说，要不要 ORDER BY，取决于你要的是「静态分组指标」还是「随行累计的动态指标」。

小结

窗口函数 = 函数 + OVER()。PARTITION BY 划小组，ORDER BY 定顺序，ROW_NUMBER/RANK/DENSE_RANK 做排名，聚合函数也能当窗口函数做累计。它最大的价值是「算跨行指标却不丢行」，分组排名、累计求和、移动平均都靠它。记住它只能出现在 SELECT 和 ORDER BY，过滤要靠外层子查询。