数据窗：解锁 PostgreSQL 窗口函数的强大能力

原创

已于 2025-01-02 17:22:35 修改 · 1.1k 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库 #数据库开发

于 2025-01-02 15:29:25 首次发布

窗口函数（Window Function）是 PostgreSQL 强大的 SQL 功能之一，它允许对结果集中的行执行计算，同时保留行的原始结构。它在分析和统计场景中非常有用，比如排名、聚合、移动平均等。

窗口函数的语法

function_name(expression) OVER (
    [PARTITION BY column_list]
    [ORDER BY column_list]
    [frame_clause]
)

function_name：窗口函数名（如 ROW_NUMBER(), RANK(), SUM() 等）。
PARTITION BY：可选，定义分组（类似于 GROUP BY）。
ORDER BY：定义窗口的排序顺序。
frame_clause：可选，定义计算范围（如 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING）。

常见的窗口函数

排序相关窗口函数
- ROW_NUMBER()：返回当前行的行号。
- RANK()：返回当前行在分区中的排名，排名相同的行号相同，后续行号会跳过。
- DENSE_RANK()：类似于 RANK()，但不跳过排名。
- NTILE(n)：将分区中的行分成 n 等份，并返回行所属的组号。
聚合窗口函数
- SUM(), AVG(), MAX(), MIN(), COUNT() 等支持窗口计算。
值偏移窗口函数
- LAG(column, offset, default)：获取当前行的某列相对于前几行的值。
- LEAD(column, offset, default)：获取当前行的某列相对于后几行的值。
- FIRST_VALUE(column)：获取窗口中第一个值。
- LAST_VALUE(column)：获取窗口中最后一个值.
- NTH_VALUE(column, n)：获取窗口中第 n 个值。
统计相关窗口函数
- CUME_DIST()：计算相对于分区的累积分布值。
- PERCENT_RANK()：计算相对于分区的百分比排名。

窗口函数与普通聚合函数的区别

聚合函数：将结果合并为一行（例如，SUM()、AVG()）。
窗口函数：不改变结果集的行数，计算结果附加到每行。

示例

1. 基础示例：行号与排名

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;