PostgreSQL Window Functions 实用教程（常用函数 + 窗口框架 + 易错点）

窗口函数是什么？

窗口函数对一组相关行进行计算，但不会像 GROUP BY 那样把多行合并成一行。因此可以同时保留每条明细和它的排名、分组统计或累计值。

函数名(参数) OVER (
  PARTITION BY 分组列
  ORDER BY 排序列
  窗口框架
)

-- 每行订单都保留，同时显示该用户的订单总金额
SELECT user_id, order_id, amount, SUM(amount) OVER (PARTITION BY user_id) AS user_total FROM orders;

信息

OVER 定义窗口；PARTITION BY 将行分组但不合并；ORDER BY 定义窗口内顺序；窗口框架定义当前计算实际覆盖哪些行。

窗口函数与 GROUP BY 的区别

方式	结果行	适合场景
GROUP BY	每个分组通常只返回一行	统计汇总，不需要保留原始明细
窗口函数	保留原始行，并附加计算结果	明细 + 汇总、排名、累计值、前后行对比

-- GROUP BY：每个用户一行
SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id;
-- 窗口函数：每条订单一行，同时带用户汇总金额
SELECT order_id, user_id, amount, SUM(amount) OVER (PARTITION BY user_id) AS total_amount FROM orders;

排名函数：ROW_NUMBER / RANK / DENSE_RANK

函数	并列时结果	示例排名
ROW_NUMBER()	每行序号唯一，不保留并列	1, 2, 3, 4
RANK()	并列后跳过后续名次	1, 2, 2, 4
DENSE_RANK()	并列后不跳过名次	1, 2, 2, 3

-- 每个部门按薪资排名
SELECT department_id, employee_id, salary, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY salary DESC, employee_id ) AS row_num, RANK() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS salary_rank, DENSE_RANK() OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS dense_salary_rank FROM employees;

注意

如果使用 ROW_NUMBER() 选出“第一条”，排序应包含唯一的决胜字段（例如主键），否则相同排序值之间的行顺序不确定。

每组 Top N：先排名，再过滤

窗口函数不能直接写在同一层查询的 WHERE 中；先用 CTE 或子查询计算排名，再在外层过滤。

-- 每个部门薪资最高的前 3 名员工（并列也最多取 3 行）
WITH ranked AS ( SELECT department_id, employee_id, salary, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY salary DESC, employee_id ) AS rn FROM employees ) SELECT department_id, employee_id, salary FROM ranked WHERE rn <= 3 ORDER BY department_id, rn;

信息

若需求是“包含所有并列前三名”，可用 RANK()；若要求前三个不同薪资档位，可用 DENSE_RANK()。选哪种函数由业务定义决定。

窗口聚合：累计和、移动平均

-- 按用户和下单时间计算累计消费金额
SELECT user_id, order_id, created_at, amount, SUM(amount) OVER ( PARTITION BY user_id ORDER BY created_at, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM orders;
-- 当前行及前两行的 3 行移动平均
SELECT sensor_id, measured_at, temperature, AVG(temperature) OVER ( PARTITION BY sensor_id ORDER BY measured_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avg_last_3_rows FROM sensor_readings;

注意

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示当前行加前两行，不是前两天。要定义时间范围，需要基于日期/时间值选择合适的 RANGE 框架。

LAG / LEAD：读取前一行或后一行

LAG 读取当前行之前的值，LEAD 读取之后的值。常用于环比、相邻记录比较和时间序列分析。

-- 对比每位用户本次与上次订单金额
SELECT user_id, order_id, created_at, amount, LAG(amount) OVER ( PARTITION BY user_id ORDER BY created_at, order_id ) AS previous_amount, LEAD(amount) OVER ( PARTITION BY user_id ORDER BY created_at, order_id ) AS next_amount, amount - LAG(amount) OVER ( PARTITION BY user_id ORDER BY created_at, order_id ) AS amount_change FROM orders;

常见形式：LAG(列, 偏移量, 默认值)。没有前一行时默认返回 NULL；可指定默认值，例如 LAG(amount, 1, 0)。

注意

排序必须能稳定确定相邻行；如果 created_at 可能重复，应追加唯一列（如 order_id）。

FIRST_VALUE / LAST_VALUE / NTILE

-- 每个部门最高薪员工的薪资
SELECT department_id, employee_id, salary, FIRST_VALUE(salary) OVER ( PARTITION BY department_id ORDER BY salary DESC, employee_id ) AS top_salary FROM employees;
-- 按成绩分成 4 个桶（每桶行数尽量接近）
SELECT student_id, score, NTILE(4) OVER (ORDER BY score DESC, student_id) AS score_quartile FROM exam_results;

注意

LAST_VALUE 容易受窗口框架影响：带排序时默认框架通常只到当前行及其同级行，所以它不一定是整个分区的最后一行。要取分区最后值，明确写出：

LAST_VALUE(salary) OVER ( PARTITION BY department_id ORDER BY salary, employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )

窗口框架默认值的关键坑

指定 ORDER BY 而未明确写框架时，PostgreSQL 默认框架是从分区开始到当前行的 RANGE 框架，并包含与当前行排序键相同的“同级行”。

-- 若 created_at 有重复值，同一时间的多行可能得到相同累计结果 SUM(amount) OVER ( PARTITION BY user_id ORDER BY created_at )
-- 逐行累计：明确使用 ROWS，并加入稳定排序字段 SUM(amount) OVER ( PARTITION BY user_id ORDER BY created_at, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )

信息

常见框架边界：UNBOUNDED PRECEDING 表示分区起点；CURRENT ROW 表示当前行（或当前行的同级组，取决于框架类型）；n PRECEDING 表示前 n 行/值。

执行位置与常见限制

• 窗口函数在同一查询层的 WHERE、GROUP BY、HAVING 之后计算，因此不能直接用在这些子句中。
• 可在 SELECT 列表和 ORDER BY 中使用窗口函数。
• 要过滤排名结果，请使用 CTE 或子查询。
• 窗口函数不能嵌套调用；如需基于窗口结果再次计算，拆成多层查询。
• 窗口的 ORDER BY 只控制计算顺序，不保证最终结果集的显示顺序；外层仍需写 ORDER BY。

-- 外层 ORDER BY 控制最终显示顺序
SELECT user_id, order_id, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY created_at, order_id ) AS order_seq FROM orders ORDER BY user_id, order_seq;

总结：Window Functions 高频易错清单

• 1. 窗口函数保留明细行；GROUP BY 会将行聚合成分组结果。
• 2. PARTITION BY 是逻辑分组，不会像 GROUP BY 那样减少行数。
• 3. 窗口结果不能直接在同层 WHERE 过滤，使用 CTE/子查询。
• 4. ROW_NUMBER、RANK、DENSE_RANK 对并列排名的处理不同。
• 5. 排序可能重复时追加唯一字段，避免行序不确定。
• 6. 累计或移动计算要理解窗口框架；ROWS 的 n PRECEDING 是行数，不是时间长度。
• 7. LAST_VALUE 注意默认框架，常需明确写到 UNBOUNDED FOLLOWING。
• 8. OVER 内部排序不保证最终输出顺序，外层 SELECT 仍需 ORDER BY。