窗口函数

INFO

要点概述窗口函数是 PostgreSQL 中的强大特性，允许你对行集合执行计算，同时保留每行的独立身份。它们在数据分析、排名、累计计算等场景中极为有用。

什么是窗口函数？

窗口函数（Window Functions）是一类特殊的函数，它们能够在保留每行独立标识的同时，对与当前行相关的一组表行执行计算。与普通聚合函数不同，窗口函数不会将多行合并为一行，而是为每一行生成一个结果。

窗口函数为数据分析提供了强大的功能，使我们能够在同一个查询中既显示详细数据又显示汇总数据，极大简化了复杂报表的编写。

窗口函数的基本语法

sql

SELECT 列名,
       窗口函数() OVER (
           [PARTITION BY 分组列]
           [ORDER BY 排序列]
           [窗口帧定义]
       )
FROM 表名;

核心组件解析：

窗口函数()：可以是专用窗口函数（如rank()、dense_rank()等）或聚合函数（如sum()、avg()等）
OVER子句：将普通函数转变为窗口函数的关键
PARTITION BY：定义行分组方式，类似于GROUP BY但不合并行
ORDER BY：定义窗口内行的排序方式
窗口帧定义：指定计算范围（默认由系统设定）

窗口函数的分类

PostgreSQL 中的窗口函数可以分为两大类：

类型	描述	常见函数
排名函数	根据指定条件对数据行进行排名	`rank()`, `dense_rank()`, `row_number()`, `ntile()`
值函数	访问前后行的值或计算聚合值	`lag()`, `lead()`, `first_value()`, `last_value()`, `nth_value()`
聚合窗口函数	在窗口内执行聚合计算	`sum()`, `avg()`, `count()`, `max()`, `min()`

PARTITION BY：数据分组

PARTITION BY 子句将数据集划分为多个分区（组），窗口函数会在每个分区内单独计算。这类似于 GROUP BY，但不会减少结果集中的行数。

示例：按部门分组计算平均工资

sql

SELECT
    depname,
    empno,
    salary,
    avg(salary) OVER (PARTITION BY depname) AS dept_avg_salary
FROM
    empsalary;

输出结果：

  depname  | empno | salary |      dept_avg_salary
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667

理解分区操作在上面的例子中：

数据按 depname 列分为三个分区：develop、personnel 和 sales
对每个分区分别计算平均工资
每行都保留原始数据，并附加了所在分区的平均值

ORDER BY：窗口内排序

在窗口函数中，ORDER BY 不仅决定结果的排序，更重要的是它定义了窗口函数计算时的行顺序和窗口帧范围。

示例：部门内工资排名

sql

SELECT
    depname,
    empno,
    salary,
    rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS salary_rank
FROM
    empsalary;

输出结果：

  depname  | empno | salary | salary_rank
-----------+-------+--------+-------------
 develop   |     8 |   6000 |           1
 develop   |    10 |   5200 |           2
 develop   |    11 |   5200 |           2
 develop   |     9 |   4500 |           4
 develop   |     7 |   4200 |           5
 personnel |     2 |   3900 |           1
 personnel |     5 |   3500 |           2
 sales     |     1 |   5000 |           1
 sales     |     3 |   4800 |           2
 sales     |     4 |   4800 |           2

分析：

数据首先按 depname 分组
在每个部门内，按 salary 降序排列
rank() 函数为每个部门内的员工分配排名
注意相同工资的员工获得相同排名，下一个排名会跳过（如开发部门的排名是 1,2,2,4,5）

窗口帧：计算的行范围

窗口帧（Window Frame）定义了窗口函数计算时考虑的行范围。默认情况下：

如果有 ORDER BY：帧包括从分区开始到当前行的所有行，以及与当前行相等的任何后续行
如果没有 ORDER BY：帧包括分区中的所有行

示例 1：无 ORDER BY 的窗口帧（整个分区）

sql

SELECT
    salary,
    sum(salary) OVER () AS total_sum
FROM
    empsalary;

输出结果：

 salary | total_sum
--------+-----------
   5200 |     47100
   5000 |     47100
   3500 |     47100
   4800 |     47100
   3900 |     47100
   4200 |     47100
   4500 |     47100
   4800 |     47100
   6000 |     47100
   5200 |     47100

分析：

无 PARTITION BY 和 ORDER BY，整个表作为一个分区
窗口帧是整个表的所有行
每行显示相同的总和（47100）

示例 2：有 ORDER BY 的窗口帧（累计计算）

sql

SELECT
    salary,
    sum(salary) OVER (ORDER BY salary) AS running_total
FROM
    empsalary;

输出结果：

 salary | running_total
--------+--------------
   3500 |          3500
   3900 |          7400
   4200 |         11600
   4500 |         16100
   4800 |         25700
   4800 |         25700
   5000 |         30700
   5200 |         41100
   5200 |         41100
   6000 |         47100

分析：

有 ORDER BY 但无 PARTITION BY
窗口帧是从表的开始到当前行（包括与当前行 salary 相等的行）
结果显示累计总和（running total）

自定义窗口帧

可以使用以下语法明确定义窗口帧：

sql

OVER (... ROWS|RANGE BETWEEN frame_start AND frame_end)

常用的窗口帧定义：

窗口帧定义	描述
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	从分区开始到当前行（默认，当有 ORDER BY 时）
`ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`	从当前行到分区结束
`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	整个分区（默认，当无 ORDER BY 时）
`ROWS BETWEEN n PRECEDING AND CURRENT ROW`	当前行及其前 n 行
`ROWS BETWEEN CURRENT ROW AND n FOLLOWING`	当前行及其后 n 行
`ROWS BETWEEN n PRECEDING AND n FOLLOWING`	当前行及其前后各 n 行

示例：移动平均值计算

移动平均值计算是窗口函数在数据分析中的经典应用，它展示了如何使用窗口帧来定义计算范围，从而实现复杂的统计分析功能。

INFO

什么是移动平均值？移动平均值（Moving Average）是通过计算一个数据点及其相邻数据点的平均值来平滑数据波动的方法。它可以帮助识别数据的整体趋势，减少随机波动的影响。

sql

SELECT
    salary,
    avg(salary) OVER (
        ORDER BY salary
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING   ---当前行前后各1行
    ) AS moving_avg
FROM
    empsalary;

实际计算过程模拟：假设我们有以下工资数据（按工资排序）：

行号	salary	计算窗口	moving_avg 计算过程
1	3500	[3500, 3900]	(3500 + 3900) ÷ 2 = 3700
2	3900	[3500, 3900, 4200]	(3500 + 3900 + 4200) ÷ 3 = 3866.67
3	4200	[3900, 4200, 4500]	(3900 + 4200 + 4500) ÷ 3 = 4200
4	4500	[4200, 4500, 4800]	(4200 + 4500 + 4800) ÷ 3 = 4500
5	4800	[4500, 4800, 4800]	(4500 + 4800 + 4800) ÷ 3 = 4700
6	4800	[4800, 4800, 5000]	(4800 + 4800 + 5000) ÷ 3 = 4866.67
7	5000	[4800, 5000, 5200]	(4800 + 5000 + 5200) ÷ 3 = 5000
8	5200	[5000, 5200, 5200]	(5000 + 5200 + 5200) ÷ 3 = 5133.33
9	5200	[5200, 5200, 6000]	(5200 + 5200 + 6000) ÷ 3 = 5466.67
10	6000	[5200, 6000]	(5200 + 6000) ÷ 2 = 5600

移动平均值的优势

平滑数据：减少数据中的随机波动和噪音
趋势识别：更容易看出数据的整体趋势方向
异常值处理：极端值对结果的影响被周围值稀释

向前移动平均值

sql

-- 计算股票的5日移动平均价格
SELECT
    trade_date,
    close_price,
    avg(close_price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS ma_5_days
FROM
    stock_prices;

常用窗口函数案例

排名函数比较

sql

SELECT
    salary,
    row_number() OVER (ORDER BY salary DESC) AS row_num,
    rank() OVER (ORDER BY salary DESC) AS rank_val,
    dense_rank() OVER (ORDER BY salary DESC) AS dense_rank_val,
    percent_rank() OVER (ORDER BY salary DESC) AS percent_rank_val
FROM
    empsalary;

各排名函数的区别：

排名函数对比

row_number()：分配唯一序号（1,2,3,4...）
rank()：相同值获得相同排名，排名有间隔（1,2,2,4...）
dense_rank()：相同值获得相同排名，排名无间隔（1,2,2,3...）
percent_rank()：计算百分比排名（0 到 1 之间）

获取前后行的值

sql

SELECT
    empno,
    salary,
    lag(salary) OVER (ORDER BY salary) AS prev_salary,
    lead(salary) OVER (ORDER BY salary) AS next_salary
FROM
    empsalary;

计算同比增长率

sql

SELECT
    date_trunc('month', date) AS month,
    sales,
    lag(sales) OVER (ORDER BY date_trunc('month', date)) AS prev_month_sales,
    (sales - lag(sales) OVER (ORDER BY date_trunc('month', date))) /
        lag(sales) OVER (ORDER BY date_trunc('month', date)) * 100 AS growth_rate
FROM
    monthly_sales;

窗口函数的使用限制

使用限制窗口函数只能出现在以下位置：

SELECT 列表（投影列）
ORDER BY 子句

不允许在以下位置使用：

WHERE 子句
GROUP BY 子句
HAVING 子句
其他位置

这是因为窗口函数在逻辑上是在这些操作之后执行的。窗口函数也总是在非窗口聚合函数之后执行。

使用子查询处理窗口函数结果

如果需要基于窗口函数的结果进行过滤，可以使用子查询：

sql

-- 获取每个部门薪资排名前2的员工
SELECT
    depname,
    empno,
    salary,
    enroll_date
FROM (
    SELECT
        depname,
        empno,
        salary,
        enroll_date,
        rank() OVER (PARTITION BY depname ORDER BY salary DESC) AS pos
    FROM
        empsalary
) AS ss
WHERE
    pos <= 2;

使用命名窗口

当查询中有多个窗口函数使用相同的窗口定义时，可以使用WINDOW子句为窗口定义命名，然后在OVER中引用：

sql

SELECT
    depname,
    empno,
    salary,
    sum(salary) OVER w AS dept_total,
    avg(salary) OVER w AS dept_avg,
    rank() OVER w AS dept_rank
FROM
    empsalary
WINDOW
    w AS (PARTITION BY depname ORDER BY salary DESC);

这样可以减少代码重复，提高可读性和可维护性。

窗口函数的实际应用场景

1. 销售数据分析

sql

-- 计算每个产品在各个地区的销售百分比
SELECT
    product_name,
    region,
    sales,
    sales / sum(sales) OVER (PARTITION BY product_name) * 100 AS region_percent
FROM
    sales_data;

2. 时间序列分析

sql

-- 计算月度销售额及同比增长
SELECT
    date_trunc('month', order_date) AS month,
    sum(amount) AS monthly_sales,
    sum(amount) - lag(sum(amount)) OVER (ORDER BY date_trunc('month', order_date)) AS sales_growth,
    (sum(amount) - lag(sum(amount)) OVER (ORDER BY date_trunc('month', order_date))) /
        lag(sum(amount)) OVER (ORDER BY date_trunc('month', order_date)) * 100 AS growth_percent
FROM
    orders
GROUP BY
    date_trunc('month', order_date)
ORDER BY
    month;

3. 数据分组和分布分析

sql

-- 将客户按消费金额分为4个等级组
SELECT
    customer_id,
    customer_name,
    total_spent,
    ntile(4) OVER (ORDER BY total_spent DESC) AS spending_quartile
FROM
    customer_spending;

窗口函数 vs GROUP BY

对比 窗口函数:

保留所有行
每行可以访问相关行的信息
可以在同一查询中显示详细数据和聚合数据

GROUP BY:

将多行合并为一行
每个分组只返回一行
无法同时显示详细数据和聚合数据

窗口函数执行顺序

在 SQL 查询处理中，窗口函数的执行顺序如下：

总结

核心要点

窗口函数允许在保留行独立性的同时进行计算，是数据分析的强大工具
OVER 子句是窗口函数的核心，可包含 PARTITION BY 和 ORDER BY
PARTITION BY 定义分组方式，ORDER BY 定义排序和窗口帧范围
窗口帧定义了计算涉及的行范围，可以自定义
窗口函数只能在 SELECT 列表和 ORDER BY 子句中使用
使用子查询可以基于窗口函数结果进行过滤
WINDOW 子句可以为重复使用的窗口定义命名

窗口函数是进行高级数据分析的强大工具。掌握它们可以帮助你编写更简洁、更高效的 SQL 查询，特别是在需要同时展示详细数据和汇总数据的场景下。

通过深入理解窗口函数，你可以极大地提升数据分析能力，简化复杂报表的编写，更加优雅地解决各种数据分析问题。

窗口函数 ​

什么是窗口函数？ ​

窗口函数的基本语法 ​

窗口函数的分类 ​

PARTITION BY：数据分组 ​

示例：按部门分组计算平均工资 ​

ORDER BY：窗口内排序 ​

示例：部门内工资排名 ​

窗口帧：计算的行范围 ​

示例 1：无 ORDER BY 的窗口帧（整个分区） ​

示例 2：有 ORDER BY 的窗口帧（累计计算） ​

自定义窗口帧 ​

示例：移动平均值计算 ​

常用窗口函数案例 ​

排名函数比较 ​

获取前后行的值 ​

计算同比增长率 ​

窗口函数的使用限制 ​

使用子查询处理窗口函数结果 ​

使用命名窗口 ​

窗口函数的实际应用场景 ​

1. 销售数据分析 ​

2. 时间序列分析 ​

3. 数据分组和分布分析 ​

窗口函数 vs GROUP BY ​

窗口函数执行顺序 ​

总结 ​

窗口函数

什么是窗口函数？

窗口函数的基本语法

窗口函数的分类

PARTITION BY：数据分组

示例：按部门分组计算平均工资

ORDER BY：窗口内排序

示例：部门内工资排名

窗口帧：计算的行范围

示例 1：无 ORDER BY 的窗口帧（整个分区）

示例 2：有 ORDER BY 的窗口帧（累计计算）

自定义窗口帧

示例：移动平均值计算

常用窗口函数案例

排名函数比较

获取前后行的值

计算同比增长率

窗口函数的使用限制

使用子查询处理窗口函数结果

使用命名窗口

窗口函数的实际应用场景

1. 销售数据分析

2. 时间序列分析

3. 数据分组和分布分析

窗口函数 vs GROUP BY

窗口函数执行顺序

总结