SQL window functions：排名、运行总计与分析

Published: 2026-03-24

window functions会在与当前行相关的一组行上执行计算——但不会像 GROUP BY 那样把结果折叠成单行输出。它们是现代 SQL 中最强大的特性之一，SQLite（3.25+）、PostgreSQL、MySQL（8.0+）、SQL Server 和 Oracle 都支持。

示例数据

下面两张表会贯穿所有示例：

employees 表：

id	name	department	hire_date	salary
1	Alice	Engineering	2023-01-15	95000
2	Bob	Engineering	2023-06-01	85000
3	Carol	Engineering	2024-03-10	85000
4	Dave	Sales	2023-02-20	72000
5	Eve	Sales	2023-09-05	68000
6	Frank	Sales	2024-01-15	65000
7	Grace	Marketing	2023-04-01	82000
8	Henry	Marketing	2024-06-01	70000

monthly_sales 表：

id	employee_id	month	revenue
1	1	2026-01	22000
2	1	2026-02	25000
3	1	2026-03	28000
4	2	2026-01	19000
5	2	2026-02	21000
6	2	2026-03	20000
7	3	2026-01	15000
8	3	2026-02	17000
9	3	2026-03	19000
10	4	2026-01	18000
11	4	2026-02	22000
12	4	2026-03	17000
13	5	2026-01	14000
14	5	2026-02	16000
15	5	2026-03	19000
16	6	2026-01	11000
17	6	2026-02	13000
18	6	2026-03	15000
19	7	2026-01	9000
20	7	2026-02	12000
21	7	2026-03	14000
22	8	2026-01	7000
23	8	2026-02	9000
24	8	2026-03	11000

在 DbGate Lite 中探索数据集

window functions语法

每个window functions都使用 OVER 子句来定义要操作的行集：

function_name(...) OVER (
  [PARTITION BY column]
  [ORDER BY column]
  [frame_clause]
)

PARTITION BY —— 将行划分为多个分区（类似 GROUP BY，但保留所有行）
ORDER BY —— 定义每个分区内的行顺序
Frame 子句 —— 将窗口限制为相对于当前行的一段行范围

ROW_NUMBER()

ROW_NUMBER() 会在每个分区内为每一行分配一个唯一的连续编号。编号总是从 1 开始，没有空缺也没有并列。

示例：按薪资为所有员工编号

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

INSERT INTO employees (id, name, department, hire_date, salary) VALUES
  (1, &#39;Alice&#39;, &#39;Engineering&#39;, &#39;2023-01-15&#39;, 95000),
  (2, &#39;Bob&#39;, &#39;Engineering&#39;, &#39;2023-06-01&#39;, 85000),
  (3, &#39;Carol&#39;, &#39;Engineering&#39;, &#39;2024-03-10&#39;, 85000),
  (4, &#39;Dave&#39;, &#39;Sales&#39;, &#39;2023-02-20&#39;, 72000),
  (5, &#39;Eve&#39;, &#39;Sales&#39;, &#39;2023-09-05&#39;, 68000),
  (6, &#39;Frank&#39;, &#39;Sales&#39;, &#39;2024-01-15&#39;, 65000),
  (7, &#39;Grace&#39;, &#39;Marketing&#39;, &#39;2023-04-01&#39;, 82000),
  (8, &#39;Henry&#39;, &#39;Marketing&#39;, &#39;2024-06-01&#39;, 70000);

INSERT INTO monthly_sales (id, employee_id, month, revenue) VALUES
  (1, 1, &#39;2026-01&#39;, 22000),
  (2, 1, &#39;2026-02&#39;, 25000),
  (3, 1, &#39;2026-03&#39;, 28000),
  (4, 2, &#39;2026-01&#39;, 19000),
  (5, 2, &#39;2026-02&#39;, 21000),
  (6, 2, &#39;2026-03&#39;, 20000),
  (7, 3, &#39;2026-01&#39;, 15000),
  (8, 3, &#39;2026-02&#39;, 17000),
  (9, 3, &#39;2026-03&#39;, 19000),
  (10, 4, &#39;2026-01&#39;, 18000),
  (11, 4, &#39;2026-02&#39;, 22000),
  (12, 4, &#39;2026-03&#39;, 17000),
  (13, 5, &#39;2026-01&#39;, 14000),
  (14, 5, &#39;2026-02&#39;, 16000),
  (15, 5, &#39;2026-03&#39;, 19000),
  (16, 6, &#39;2026-01&#39;, 11000),
  (17, 6, &#39;2026-02&#39;, 13000),
  (18, 6, &#39;2026-03&#39;, 15000),
  (19, 7, &#39;2026-01&#39;, 9000),
  (20, 7, &#39;2026-02&#39;, 12000),
  (21, 7, &#39;2026-03&#39;, 14000),
  (22, 8, &#39;2026-01&#39;, 7000),
  (23, 8, &#39;2026-02&#39;, 9000),
  (24, 8, &#39;2026-03&#39;, 11000);

示例：在每个部门内为员工编号

使用 PARTITION BY 后，每个部门的编号都会从 1 重新开始：

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

RANK() 和 DENSE_RANK()

RANK() 和 DENSE_RANK() 在处理并列时与 ROW_NUMBER() 不同：

ROW_NUMBER() —— 始终唯一，并列时按某种任意顺序打破
RANK() —— 相同值获得相同名次，名次会跳号（1, 2, 2, 4）
DENSE_RANK() —— 相同值获得相同名次，不跳号（1, 2, 2, 3）

示例：比较三种排名函数

Bob 和 Carol 的薪资都是 85000 —— 注意每个函数如何处理这次并列：

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

示例：在部门内为员工排名

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

NTILE()

NTILE(n) 会把行分配到 n 个尽量均匀的组（桶）中，编号从 1 到 n。

示例：按薪资将员工划分为四分位

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

示例：在每个部门内划分为两个层级

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

LAG() 和 LEAD()

LAG(column, offset) 访问前面某一行的值；LEAD(column, offset) 访问后面某一行的值。二者的默认 offset 为 1，当不存在对应行时返回 NULL。

示例：逐月营收对比

将每个月的营收与该员工上个月的营收进行比较：

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

示例：预览下个月营收

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

聚合window functions

标准聚合函数（SUM、AVG、COUNT、MIN、MAX）配合 OVER 使用时就成了window functions。与 GROUP BY 不同，输出中会保留每一行。

示例：员工薪资与部门平均值对比

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

示例：占总薪资的百分比

计算每位员工在总薪资支出中所占的比例：

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

示例：运行总计与累计计数

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

按分区计算运行总计

结合 PARTITION BY 和 ORDER BY，可以在分组内计算运行总计。

示例：每位员工的累计营收

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

示例：每月全员营收排名

找出每个月每位员工的营收排名：

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

窗口帧（Window Frames）

窗口帧精确定义了相对于当前行，哪些行会被包含在计算中。帧子句写在 OVER 内部的 ORDER BY 之后。

语法

ROWS BETWEEN <start> AND <end>

常见边界：

UNBOUNDED PRECEDING —— 从分区的第一行开始
N PRECEDING —— 当前行之前的第 N 行
CURRENT ROW —— 当前行
N FOLLOWING —— 当前行之后的第 N 行
UNBOUNDED FOLLOWING —— 到分区的最后一行

示例：2 个月移动平均

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

示例：运行最小值与最大值营收

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

FIRST_VALUE 和 LAST_VALUE

FIRST_VALUE(column) 返回窗口帧中的第一个值；LAST_VALUE(column) 返回窗口帧中的最后一个值。需要注意：在默认帧（ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）下，LAST_VALUE 只能看到当前行及之前的行。

示例：将每个月与第一个月对比

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

示例：将每个月与最佳月份对比

使用 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 来覆盖整个分区：

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

实战示例：每个部门的最高收入者

一个常见模式：在子查询中使用window functions，然后按排名过滤。

示例：每个部门薪资最高的员工

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

示例：每位员工营收最高的月份

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

window functions vs. GROUP BY

关键区别在于：GROUP BY 会把多行折叠成汇总行，而window functions会保留每一行，只是额外添加计算列。

示例：并排对比

GROUP BY 每个部门只返回一行：

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

window functions则保留所有行：

SQL Query

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  hire_date TEXT,
  salary INTEGER
);

CREATE TABLE monthly_sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  month TEXT,
  revenue INTEGER
);

window functions的常见用例

排名与排行榜 —— 使用 ROW_NUMBER、RANK、DENSE_RANK 做 Top-N 查询
运行总计 —— 使用累计 SUM 做财务或库存跟踪
期间对比分析 —— 使用 LAG/LEAD 做环比、同比分析
移动平均 —— 使用带帧的 AVG 平滑数据
分位分析 —— 使用 NTILE 将数据划分为等份组
去重 —— 使用 ROW_NUMBER 为每组选择一行
缺口分析 —— 使用 LAG 检测缺失序列或异常变化

性能提示

为 ORDER BY 列建索引 —— window functions内部需要排序，索引可以加速
减少分区数量 —— 分区过多会增加计算开销
复用窗口定义 —— 许多数据库支持 WINDOW 子句，避免重复书写相同的 OVER 定义
在窗口计算之后再过滤 —— window functions在 WHERE 之前执行，因此按排名过滤时要用子查询或 CTE
避免不必要的帧 —— 只有在需要非默认行为时才指定帧子句

总结

window functions在相关行之间计算值，但不会折叠结果集
ROW_NUMBER、RANK、DENSE_RANK 为行分配序号或名次
LAG 和 LEAD 访问前一行或后一行的值
NTILE 将行划分为大小尽量相等的桶
FIRST_VALUE 和 LAST_VALUE 返回窗口中的边界值
带 OVER 的聚合函数（SUM、AVG、COUNT、MIN、MAX）在计算组级指标的同时保留所有行
PARTITION BY 创建独立分组；ORDER BY 定义行顺序
窗口帧（ROWS BETWEEN ... AND ...）可以精细控制参与计算的行
使用子查询按window functions结果过滤（例如每组 Top-N）
SQLite 3.25+、PostgreSQL、MySQL 8.0+、SQL Server 和 Oracle 均已支持

可以尝试修改这些示例中的分区、排序和帧子句，观察window functions如何简化你的分析型查询。