SQL查询中的窗口函数（主要以 PostgreSQL 为例）

窗口函数 (Window Functions) 在与查询结果集相关的“窗口”上执行计算，并为结果集的每一行返回一个值。

啥是“窗口”？为什么我查询几条数据还有窗口啊。带着这个问题读完看看能明白吗

为什么需要窗口函数？

在传统 SQL 中，我们使用 GROUP BY 和聚合函数（如 SUM, AVG）进行数据汇总。但这种方式存在一个核心局限：聚合后行数会减少。比如按部门计算平均销售额，原始的员工个体销售记录就“消失”了。

想象以下场景：你想查看每位员工的销售额，并在旁边同时显示该员工所在部门的平均销售额。传统的 GROUP BY 无法一次完成这个任务，您可能需要复杂的自连接或子查询。

窗口函数允许在不减少原始行数的情况下，进行分组、排序和计算聚合，实现个体数据和聚合数据的并行观察。
它的核心特性就是不会折叠行，它将聚合计算的结果“投影”回每一行，而不是将多行合并成一行。

下面的书写上关键字都用的大写，但小写也是可以的

语法

窗口函数通用形式：

function(args) OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [frame_clause]
)

组成部分：

function：SUM, AVG, MAX, ROW_NUMBER, LAG 等
OVER()：定义窗口范围
PARTITION BY：按分组计算（不会减少行）
ORDER BY：定义分组内排序
frame_clause：帧，用于指定计算所覆盖的行（后续详讲）

示例：

SELECT
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY create_time)
        AS running_total
FROM orders;

SUM(amount) OVER (PARTITION BY user_id ORDER BY create_time) 含义：

每个用户内部独立计算
按时间排序
累计求和
结果同时包含每条订单和累计结果。

OVER()

如果 OVER() 为空：

AVG(score) OVER ()

表示全表为一个分区，全局平均值会出现在每一行。

PARTITION BY

分组但不减少行。示例：

SUM(amount) OVER (PARTITION BY user_id)

与 GROUP BY 不同：

GROUP BY	PARTITION BY
压缩行，只返回一行	不压缩行，保留所有明细
聚合后无法看到明细	明细和统计值可共存
不支持排名/序列分析	能跨行分析、排名、累计

ORDER BY

窗口函数中的排序。用于：

取上一行/下一行：LAG() / LEAD()
排名：ROW_NUMBER() / RANK()
计算累计：SUM() OVER(ORDER BY ...)
计算差值、连续行为分析等

如果 ORDER BY 为空：

对排名函数基本无意义（顺序不可控）
对 SUM/AVG 等聚合窗口会让结果不稳定

窗口定义复用（WINDOW）

SELECT
    SUM(amount) OVER w,
    AVG(amount) OVER w
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY create_time);

减少重复代码，还更清晰，建议总是使用这种写法。

分类

接下来是窗口函数的重点。
窗口函数不是特别多，熟悉了语法以后也不会再嫌写得长。

排名类

1. ROW_NUMBER()

行号这个用得最多

ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC)

2.RANK()

排名，这个用得也不少。排名字段如果值一样，排名也会一样，后面的排名会顺延（跳号）

 RANK() OVER (ORDER BY sales DESC)

3. DENSE_RANK()

也是排名，但是如果前面出现了一样的排名，不会跳号，而是紧挨着往下排。

4. NTILE(n)

分位，就是（近似）平均分组。比如分4组

NTILE(4) OVER (ORDER BY score)

分析类

1. LAG()

排序后取上一行的字段值

SELECT 
    sale_date,
    amount,
    LAG(amount) OVER (ORDER BY sale_date) AS previous_day_amount
FROM sales
ORDER BY sale_date;

它最多接受三个参数，第二个参数表示往前取第几个，默认往前一个；第三个参数是取不到的时候的默认值。

2. LEAD()

同前，取后面一行的字段值。

3. FIRST_VALUE()

返回窗口分区中的第一行的值

FIRST_VALUE(salary) OVER (
        PARTITION BY department 
        ORDER BY hire_date
    ) AS first_salary_in_dept

4. LAST_VALUE

不说你也知道了吧。

聚合类

将传统的聚合函数（SUM, AVG, COUNT, MAX, MIN）与 OVER 子句结合使用

-- 计算每个员工销售额，并同时显示部门平均值
SELECT
    employee_name,
    department,
    sale_amount,
    -- 在每个部门 (PARTITION BY department) 的窗口内计算平均销售额
    AVG(sale_amount) OVER (PARTITION BY department) AS dept_avg_sale,
    -- 计算该员工销售额占部门总销售额的百分比
    sale_amount / SUM(sale_amount) OVER (PARTITION BY department) * 100 AS sales_percentage
FROM sales;

又或者简单点的

MAX(amount) OVER (PARTITION BY user_id)

分布类

用于计算当前行值在整个窗口或分区中的相对分布

1. PERCENT_RANK()

百分比排名，计算当前行在分区中的百分比排名。结果范围 \(0 \le X < 1\)。

PERCENT_RANK() OVER (ORDER BY amount)

2. CUME_DIST()

累积分布，计算当前行值小于或等于分区中所有值的比例。结果范围 \(0 < X \le 1\)。

窗口帧 (Window Frame)

这是入门窗口函数的最后一块关键拼图了。

窗口帧是 OVER 子句的第三部分，用来定义当前行计算时需要考虑的物理行或逻辑值范围。

ROWS (基于行数)：关注物理行数。例如，“当前行之前的 2 行”。
RANGE (基于值域)：关注排序键的值域。例如，“当前行之前 \(N\) 个值”或“与当前行值相同的所有行”。当需要处理并列值时，RANGE 更有用。

前面的使用上我们好像没接触到窗口帧，是因为它有默认值：当 OVER 子句中包含 ORDER BY 但没有显式定义窗口帧时，PgSQL 使用的默认帧是： RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

窗口帧的边界

CURRENT ROW,当前行
UNBOUNDED PRECEDING,分区内的第一行
UNBOUNDED FOLLOWING,分区内的最后一行
N PRECEDING,当前行之前 N 行/值
N FOLLOWING,当前行之后 N 行/值

跟分析类的4个函数很像是吧

通过这些边界我们看看默认值的含义：基于值的，从分区第一行开始到当前行。

你能看到这里已经非常不容易，还要让你发现这里面的问题简直难于上青天！
这里有什么问题呢？

1. 同值同行帧问题

默认值的范围按排序字段的“值”划定的，不是按行！
如果当前行值 = 100，那之前所有 value <= 100 的都是帧内。

那咋了

SUM(amount) OVER (ORDER BY price)

你这样写可能是想一行一行的累加，但是由于是基于值的，如果数据如下

id	price
1	100
2	100
3	101

在计算第一行的累积值时就已经包含了第二行的值，导致第一行和第二行都是200。
所以需要改成基于行的：

SUM(amount)
OVER (ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

做为最佳实践：在 PostgreSQL 中尽量使用 ROWS，而非 RANGE

2. LAST_VALUE()恒等于CURRENT ROW

由于默认窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，如果不显式定义帧，LAST_VALUE() 看到的窗口截止到当前行，因此它通常只会返回当前行的值。
正确获取分区内最后一个值的方法：

SELECT
    employee_name,
    sale_amount,
    FIRST_VALUE(sale_amount) OVER w AS first_sale,
    LAST_VALUE(sale_amount)  OVER (
        PARTITION BY department 
        ORDER BY sale_date 
        -- 强制指定帧为整个分区
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS final_sale_in_dept
FROM sales
WINDOW w AS (PARTITION BY department ORDER BY sale_date);

做为最佳实践：在使用LAST_VALUE时总是强制指定

常用帧写法

（1）运行累计（running sum）

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

（2）滚动窗口（移动平均）

过去 2 行 + 当前行：

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

（3）只看上一行（环比/差值）

ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING

（4）整组统计（等同于不写）

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

last but not least

最后说一些性能考量方面的东西。

PG 对窗口函数优化很强：

排序一次，多窗口复用
窄帧（如 ROWS x PRECEDING）性能最好
宽帧（UNBOUNDED FOLLOWING 等）可能更重
RANGE 在重复值多时计算量更大

一般建议：

能用 ROWS 不用 RANGE
尽量只使用需要的帧，不要一律写 "UNBOUNDED"
仅在必要时排序：如果不需要排名或偏移函数，可以省略 ORDER BY 子句，这会避免昂贵的排序操作。
使用 FILTER 子句减少聚合窗口函数的计算量： COUNT(sale_amount) FILTER (WHERE sale_amount > 10000) OVER (PARTITION BY department)
使用 WINDOW 子句避免重复定义。当多个窗口函数使用相同的分区和排序定义时，应使用 WINDOW 子句。这不仅提高了可读性和维护性，还允许 PgSQL 优化器更好地重用分区和排序结果，避免多次重复的昂贵操作。

为啥叫“窗口”

回到开头的问题。
我觉得这个叫法并不清晰，当然也可能是翻译不够好。但是英语就叫"window"。
简单点说，窗口就是一个个的分组，或者叫分区。但是分组group，分区partition都有含义了，叫分段函数吧，歧义也大。所以只能使用“窗口”来统一语言了。

posted @ 2025-12-08 10:06 大卫小东（Sheldon）阅读(190) 评论(0) 收藏举报

刷新页面返回顶部