Kodokon kodokon.com

窗口函数：ROW_NUMBER、RANK、SUM OVER

借助 OVER 和 PARTITION BY，计算排名、累计值以及组内比较，同时又不把行合并掉。

10 分钟 · 3 题

在 Kodokon 中打开本课

GROUP BY 会把行合并成一个个分组；而窗口函数在计算聚合值或排名的同时保留每一行。它是实现“每组前 N 名”、累计值以及“与自己团队平均值作比较”的利器。SQLite 从 3.25 版（2018 年）起支持。载入数据集。

SQL
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (name, dept, salary)
VALUES
  ('alice', 'tech', 5200),
  ('bruno', 'tech', 4800),
  ('chloe', 'tech', 4800),
  ('david', 'tech', 4100),
  ('emma', 'sales', 3900),
  ('fanny', 'sales', 3600),
  ('gilles', 'sales', 3600),
  ('hugo', 'sales', 3200);
本节课的数据集。

三个排名函数，在面对平局时有三种不同表现：ROW_NUMBER 编号时不会重复（平局时任意打破），RANK 给平局相同的排名，然后跳号（1、2、2、4），DENSE_RANK 则不跳号（1、2、2、3）。WINDOW 子句可以避免重复书写窗口定义（SQLite 和 PostgreSQL 支持，SQL Server 不支持）。

SQL
SELECT name, salary,
  ROW_NUMBER() OVER w AS row_num,
  RANK() OVER w AS rnk,
  DENSE_RANK() OVER w AS dense
FROM employees
WHERE dept = 'tech'
WINDOW w AS (ORDER BY salary DESC);
bruno 和 chloe（4800）：排名都是 2，然后 RANK 跳到 4。

PARTITION BY 会为每个子集重新开始计算：每个部门一个窗口，且不合并任何行。与 CTE 结合，就得到了那个经典的面试题模式：每个部门里薪资最高的 N 个人。这里 CTE 是必需的，因为窗口函数是在 WHERE 之后才求值的：你没法直接对 rn 进行筛选。

SQL
WITH ranked AS (
  SELECT name, dept, salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 2;
每个部门前 2 名：要烂熟于心的模式。

经典的聚合函数加上 OVER 就变成了窗口函数：SUM(salary) OVER (ORDER BY ...) 会逐行产生一个累计值。用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 指定窗口帧，就能得到严格逐行的累计值。

SQL
SELECT name, dept, salary,
  SUM(salary) OVER (
    PARTITION BY dept
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW
  ) AS running_total
FROM employees;
薪资累计值，在每个部门处重新归零。

知识检测

确认你已牢记本课的重点内容。

  1. 当薪资为 5200、4800、4800、4100 时，RANK() 会给 4100 分配哪个排名？
    • 3
    • 4
    • 2
  2. 为什么要对 ROW_NUMBER() 进行筛选就需要用到 CTE？
    • OVER 在 WHERE 之后才求值，所以不能写在 WHERE 里面
    • CTE 总是更高效
    • ROW_NUMBER 只能在 WITH 内部使用
  3. 在 OVER 子句中，PARTITION BY dept 起什么作用？
    • 它按部门对最终结果排序
    • 它为每个部门重新开始窗口计算
    • 它把每个部门的行合并成一行