Window Function

Window Function

窗口函数（Window Function）是 SQL 中一种在不合并原始行的前提下，对与当前行相关的一组数据（即“窗口”）执行计算的函数；而“开窗”则是使用 OVER() 子句为每一行动态划定这个计算范围的操作过程。

核心概念解析

• 什么是“开窗”（OVER 子句）：在传统 SQL 中，使用 GROUP BY 聚合后，多行数据会被压缩成一行。而“开窗”相当于在全表数据上，为当前正在处理的这一行打开一个局部透明的“观察窗口”（比如“和它同部门的所有人”，或者“它前3天的记录”）。窗口内的行用来参与计算，但原表的所有行依然完整保留。
• 什么是窗口函数：指运行在这个“窗口”之上的具体计算逻辑。它把窗口内的多行数据算出一个结果，然后直接附加到当前行的后面作为新的一列。

开窗的三要素（OVER 内部结构）

一个完整的开窗操作通常由 OVER (…) 括号里的三个可选部分决定窗口的大小和形状：
– 分区 (PARTITION BY)：决定窗口的边界。例如按 部门 分区，意味着计算只会发生在同一部门内部，跨部门的数据互不干扰（类似 GROUP BY 的分组，但不折叠行）。
– 排序 (ORDER BY)：决定窗口内数据的排列顺序。这对于排名类计算，以及“累计求和”等依赖先后顺序的计算至关重要。
– 框架 (ROWS / RANGE)：进一步精细控制窗口的滑动范围。例如指定 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW，代表窗口只包含“当前行及它前面的 2 行”，常用于计算 3 日移动平均值。

常见窗口函数分类

• 排名函数：ROW_NUMBER()（连续不重复排名：1, 2, 3）、RANK()（跳跃并列排名：1, 1, 3）、DENSE_RANK()（连续并列排名：1, 1, 2）。
• 聚合窗口函数：把普通的 SUM()、AVG()、COUNT()、MAX() 加上 OVER()，即可实现累计求和或移动平均。
• 取值/偏移函数：LAG()（获取当前行前面第 N 行的数据，常用于计算环比/同比）和 LEAD()（获取当前行后面第 N 行的数据）。

对比：普通聚合 (GROUP BY) vs 窗口函数 (OVER)

对比维度	普通聚合 (GROUP BY)	窗口函数 (OVER)
输出行数	减少（每组只输出一行总结数据）	不变（保留原始明细表的每一行）
明细保留	丢失非分组字段的明细信息	完整保留所有原始字段，直接新增计算列
典型场景	统计每个部门的平均薪资	列出每个员工的薪资，并在旁边附上其所在部门的平均薪资及部门内排名

完整 SQL 代码示例

下面是一段包含部门内销售额排名、部门累计销售额以及部门总销售额的完整 SQL 代码示例（兼容 MySQL 8.0+、PostgreSQL、Oracle、SQL Server 等主流数据库）。

1. 示例 SQL 代码

假设有一张销售记录表 sales_records，记录了员工姓名、所属部门、成交日期和销售额：

SELECT 
    department AS 部门,
    emp_name AS 员工姓名,
    sale_date AS 成交日期,
    amount AS 单笔销售额,

    -- 1. 部门内排名：按部门分区，按销售额从高到低排名（并列时跳过后续名次）
    RANK() OVER (
        PARTITION BY department 
        ORDER BY amount DESC
    ) AS 部门销售额排名,

    -- 2. 部门累计销售额：按部门分区，按日期先后顺序逐行累加
    SUM(amount) OVER (
        PARTITION BY department 
        ORDER BY sale_date ASC
    ) AS 部门累计销售额,

    -- 3. 部门总销售额：只按部门分区，不排序，窗口包含整个部门的所有行
    SUM(amount) OVER (
        PARTITION BY department
    ) AS 部门总销售额

FROM sales_records
ORDER BY department, sale_date;

2. 预期输出效果演示

假设原表有 4 条数据，运行上述 SQL 后，原始明细行完全保留，右侧直接附带计算结果：

部门  员工姓名    成交日期    单笔销售额   部门销售额排名 部门累计销售额 部门总销售额
华东区 张三  2026-11-01  1000    2   1000    4000
华东区 李四  2026-11-02  3000    1   4000 (1000+3000)    4000
华南区 王五  2026-11-01  2000    1   2000    3500
华南区 赵六  2026-11-03  1500    2   3500 (2000+1500)    3500

3. 代码关键点拆解

• PARTITION BY department：划定了“开窗”的边界。华东区和华南区的数据完全隔离，排名和累计都在各自部门内部独立重新开始。
• 为什么同样是 SUM(amount)，结果却不同？：
  • 加了 ORDER BY sale_date：数据库会默认将窗口范围限定为“从本部门第一行到当前行”，从而实现逐日滚动累加。
  • 没加 ORDER BY：窗口默认包含“当前部门的所有行”，因此每一行显示的都是整个部门的总和。

解决同一天有多笔订单时的“累计偏差”

问题原因：
当只写 ORDER BY sale_date 时，SQL 默认的窗口框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这里的 RANGE 是按数值（日期）划分的。如果同一天有 2 笔订单，数据库会把这两行视为“并列当前行”，导致这两行算出来的累计销售额一模一样（直接加上了两笔的总和），而不是逐笔递增。
解决方法：
显式指定物理行框架 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（并最好在 ORDER BY 中加入订单 ID 保证排序稳定）。

SELECT 
    department AS 部门,
    sale_date AS 成交日期,
    order_id AS 订单号,
    amount AS 单笔销售额,

    -- 默认行为 (RANGE)：同一天的多笔订单会显示相同的累计值（按天批量累加）
    SUM(amount) OVER (
        PARTITION BY department 
        ORDER BY sale_date ASC
    ) AS 按天并列累计,

    -- 精准逐行累计 (ROWS)：严格按照物理行顺序，一笔一笔往下累加
    SUM(amount) OVER (
        PARTITION BY department 
        ORDER BY sale_date ASC, order_id ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS 逐笔精准累计

FROM sales_records;

使用 LAG() 函数计算环比增长率

核心逻辑：
计算环比需要先按时间粒度（如按月）汇总销售额，再利用 LAG(字段, 偏移量) 把上一期的销售额“拉”到当前行，最后套用增长率公式：(本期 – 上期) / 上期。

-- 第一步：先按部门和月份汇总基础销售额（使用 CTE 公共表表达式）
WITH monthly_sales AS (
    SELECT 
        department,
        DATE_FORMAT(sale_date, '%Y-%m') AS sale_month, -- 注：PostgreSQL 可用 TO_CHAR(sale_date, 'YYYY-MM')
        SUM(amount) AS current_amount
    FROM sales_records
    GROUP BY department, DATE_FORMAT(sale_date, '%Y-%m')
)

-- 第二步：使用 LAG() 获取上月数据并计算环比
SELECT 
    department AS 部门,
    sale_month AS 月份,
    current_amount AS 本月销售额,

    -- 获取同一部门上一行的销售额（即上月销售额）
    LAG(current_amount, 1) OVER (
        PARTITION BY department 
        ORDER BY sale_month ASC
    ) AS 上月销售额,

    -- 计算环比增长率（使用 NULLIF 防止上月销售额为 0 时触发除零报错）
    ROUND(
        (current_amount - LAG(current_amount, 1) OVER (PARTITION BY department ORDER BY sale_month ASC)) 
        / NULLIF(LAG(current_amount, 1) OVER (PARTITION BY department ORDER BY sale_month ASC), 0) * 100, 
        2
    ) AS 环比增长率_百分比

FROM monthly_sales
ORDER BY department, sale_month;

关键细节提示

• 首行空值处理：每个部门的第一条记录因为没有“上一期”，LAG() 返回的结果为 NULL，对应的环比增长率也会自然显示为 NULL。如果想让上期默认显示为 0，可以写成 LAG(current_amount, 1, 0)（第三个参数为默认值）。
• 防除零保护：NULLIF(分母, 0) 是生产环境中的常用技巧，当分母为 0 时它会返回 NULL，从而让整个除法安全地返回 NULL 而不会导致 SQL 报错中断。

OVER

在刚才的代码中，OVER 就像是一个“范围划定器”（或者叫“窗口定义器”）。
前面的函数（如 SUM()、RANK()、LAG()）只负责定义“怎么算”，而紧跟在后面的 OVER (…) 则专门负责告诉数据库“拿哪些数据来算”。
我们可以把 OVER 的作用拆解成以下三个核心步骤来理解：

1. 为什么必须有 OVER？

在标准 SQL 中，如果你直接写 SUM(amount)，数据库会认为你想把全表所有行压缩成1 个总和。
一旦你在后面加上 OVER (…)，你就改变了对数据库的指令：
– 不压缩行数：保留原表的每一行数据。
– 逐行独立计算：数据库会一行一行地往下走，每走到一行，就根据 OVER 括号里的规则，临时圈出一批数据（即“窗口”）算一个结果，贴在这一行的后面。

2. 结合刚才的例子拆解 OVER 的内部工作

以刚才“逐笔精准累计”的这句代码为例：

SUM(amount) OVER (
    PARTITION BY department 
    ORDER BY sale_date ASC, order_id ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

当数据库处理到华东区的第 3 笔订单时，OVER 括号里的三句话会依次生效，精准圈出计算范围：
– 第一步：PARTITION BY department（画大圈）
OVER 先把全表按部门拆开。既然当前行是“华东区”，那就把华南区、华北区的数据全部屏蔽掉，只看华东区的数据。
– 第二步：ORDER BY sale_date ASC, order_id ASC（排好队）
在华东区内部，OVER 把所有订单按成交日期和订单号从小到大排好队，确定谁在前、谁在后。
– 第三步：ROWS BETWEEN …（框出局部范围）
在排好队的华东区数据中，OVER 最终拉出一个框：从第一行（UNBOUNDED PRECEDING）开始，一直框到当前第 3 行（CURRENT ROW）为止。
最后，前面的 SUM(amount) 只需要对这个框里的 3 行数据求和，就得到了当前行的累计销售额。

3. OVER 括号里的三种常见“偷懒”写法

在实际写 SQL 时，OVER() 括号里的三个部分都是可选的，省略不同部分会形成完全不同的计算范围：

OVER 写法示例	划定的窗口范围	最终计算效果
SUM(amount) OVER ()	全表所有行（括号完全留空）	计算全公司的总销售额，并贴在每一行后面
SUM(amount) OVER (PARTITION BY department)	当前行所在部门的所有行	计算该部门的总销售额，并贴在部门每行后面
LAG(amount, 1) OVER (PARTITION BY department ORDER BY sale_month)	当前部门内按月排好序的序列	专供 LAG() 往前找 1 行，获取上月销售额

简单总结：前面的函数决定“做什么动作”（求和、排名、找上一行），而 OVER 决定了这个动作的“势力范围”。

窗口含义

1. 为什么叫“窗口（Window）”而不是“关门”？

• 核心物理意象：透过窗户看局部
  想象你站在一堵巨大的数据墙面前（整张数据库表）。如果你用传统的 GROUP BY，相当于把整面墙推倒重组，压缩成几块砖头（汇总行）。
  而窗口（Window）的意象是：数据墙本身原封不动，你只是在墙上开了一扇透明的玻璃窗。你透过这扇窗户，只能看到框在窗框里的那几行数据，并对它们进行计算；随着你逐行往下看，这扇“窗户”也在跟着你向下滑动（这就是计算机科学中经典的 Sliding Window 滑动窗口 概念）。

2. OVER 和“窗口”是什么关联？

在英文 SQL 语法 SUM(amount) OVER (…) 中，OVER 是一个介词，意思是“在……之上”或“跨越……范围”。
把英文直译过来，整句话的逻辑就是：
– SUM(amount)：计算金额的总和。
– OVER (…)：在（后面括号里定义的这扇窗口）之上进行计算。
换句话说，OVER 后面跟着的那对括号 (…)，在语法定义上就是“窗口（Window）”本身。OVER 是连接“计算动作”与“窗口范围”的桥梁。

3. 语法演进的铁证：WINDOW 关键字

为了印证 OVER 后面的括号就是“窗口”，SQL 标准（SQL:2003）后来甚至专门引入了 WINDOW 关键字，允许你先画好一扇“窗户”并给它起个名字，然后再用 OVER 套用它：

SELECT 
    emp_name,
    amount,
    -- 这里直接写 OVER w，意思就是“在名叫 w 的窗口之上计算”
    SUM(amount) OVER w AS 部门累计,
    AVG(amount) OVER w AS 部门平均
FROM sales_records
-- 这里用 WINDOW 关键字正式定义这扇窗户长什么样
WINDOW w AS (PARTITION BY department ORDER BY sale_date);

通过这个写法可以清晰地看到：WINDOW 定义了窗口的形状，而 OVER 负责把计算函数架设到这个窗口之上。

Sliding Window（滑动窗口）

Sliding Window（滑动窗口）是计算机科学中一种极其经典且高效的思想。它的本质是：在连续的数据序列（如数组、字符串、时间序列数据流）上，维护一个固定或动态长度的“局部区间”（窗口），通过让窗口的左右边界同向滑动来逐步覆盖整个数据集，从而避免大量重复计算。

1. 核心原理：为什么滑动窗口快？

假设你有一个包含 100 万个数字的数组，需要计算每连续 3 个数字的和。
– 暴力做法（双重循环）：每次框住 3 个数重新相加（a+b+c，然后 b+c+d）。窗口每次移动，中间重叠的 b+c 都要被重复算一遍，时间复杂度为 (O(N \times K))（K 为窗口大小）。
– 滑动窗口做法：窗口向右滑动一格时，只做一加一减——加上新进入窗口右侧的那个数，减去刚离开窗口左侧的那个数。无论窗口多大，每次滑动的计算量都是常数级的 O(1)，整体时间复杂度直接降为线性的 O(N)。

2. 滑动窗口的两大类型

根据窗口大小是否变化，滑动窗口主要分为两类：
– 固定大小窗口（Fixed-size Window）
– 特征：窗口的左右边界同步向右移动，窗口长度 K 始终保持不变。
– 例子：计算股票价格的“5 日移动平均线”（永远只看最近 5 天）、网络传输中一次最多发送 10 个数据包。
– 可变大小窗口（Dynamic / Variable-size Window）
– 特征：通常用“左右双指针（left 和 right）”来控制。右指针主动向右扩张以寻找满足条件的数据；一旦窗口内的状态超标或不再满足条件，左指针就向右收缩，直到窗口重新恢复合法。
– 例子：寻找字符串中“不包含重复字符的最长子串”、寻找数组中“和大于等于目标值的最短连续子数组”。

3. 三大核心应用场景

滑动窗口不仅是个概念，它在多个计算机领域都有具体的工程落地：

应用领域	具体作用	典型例子
算法与数据结构	解决连续子数组、子串的最值或计数问题，将嵌套循环优化为单层循环	LeetCode 经典高频题（如“无重复字符的最长子串”、“滑动窗口最大值”）
SQL 与大数据流计算	对时间序列数据进行滚动统计，平滑短期波动	SQL 中的 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW（计算过去 7 天滚动总销量）；Flink / Spark Streaming 中的时间窗口统计
计算机网络与系统架构	控制流量速率，防止接收方被海量请求压垮	TCP 协议流量控制（接收方通告窗口大小，限制发送方未确认的数据量）；API 网关限流（如“任意连续 1 分钟内最多允许 100 次请求”）

4. 算法代码直观演示（固定窗口）

下面用一段简短的 Python 代码展示如何用滑动窗口在 O(N) 时间内求出“长度为 k 的最大子数组和”：

def max_sum_subarray(nums, k):
    # 1. 先计算第一个窗口（前 k 个元素）的和
    window_sum = sum(nums[:k])
    max_sum = window_sum

    # 2. 窗口开始向右滑动：i 代表新进入窗口的右边界元素索引
    for i in range(k, len(nums)):
        # 加上新进来的元素 nums[i]，减去滑出去的旧元素 nums[i - k]
        window_sum = window_sum + nums[i] - nums[i - k]
        max_sum = max(max_sum, window_sum)

    return max_sum

# 示例：在 [2, 1, 5, 1, 3, 2] 中找连续 3 个数的最大和 -> 结果为 9 (5+1+3)
print(max_sum_subarray([2, 1, 5, 1, 3, 2], 3))

滚动窗口（Tumbling Window）和滑动窗口（Sliding Window）

在大数据流计算（如 Apache Flink、Spark Streaming）以及时序 SQL 分析中，滚动窗口（Tumbling Window）和滑动窗口（Sliding Window）是最核心的两种时间切分方式。
两者的根本区别在于窗口之间是否存在重叠，以及数据是否会被重复计算。

1. 核心机制与参数对比

要理解这两种窗口，只需关注两个核心参数：窗口大小（Window Size）和滑动步长（Slide Interval）。
– 滚动窗口（Tumbling Window）
– 参数关系：滑动步长 = 窗口大小。
– 切分方式：时间轴被切分成一个个首尾相接、互不重叠的固定区间。
– 数据归属：每一条数据严格只属于一个窗口，算完即止，绝不重复。
– 直观例子：设定窗口大小为 1 小时（如 10:00-11:00、11:00-12:00）。10:15 产生的一笔订单，只会进入 10:00-11:00 这个窗口参与统计。
– 滑动窗口（Sliding Window）
– 参数关系：滑动步长 < 窗口大小。
– 切分方式：窗口按照设定的较小步长向前滑动，导致前后多个窗口在时间上存在重叠区域。
– 数据归属：由于窗口重叠，同一条数据会同时落入多个相邻的窗口中被重复计算。
– 直观例子：设定窗口大小为 1 小时，滑动步长为 15 分钟。系统会依次生成 10:00-11:00、10:15-11:15、10:30-11:30 的窗口。此时 10:20 产生的订单，会同时出现在上述前两个窗口中。

2. 多维度全面对比

对比维度	滚动窗口 (Tumbling Window)	滑动窗口 (Sliding Window)
步长与大小关系	步长 == 窗口大小	步长 < 窗口大小（步长越短，重叠度越高）
窗口重叠性	无重叠（首尾严格相接）	有重叠（相邻窗口共享部分数据）
单条数据计算次数	仅 1 次	多次（次数 = 窗口大小 / 滑动步长）
输出频率与平滑度	输出频率较低，相邻结果波动可能较大	输出频率高（按步长更新），趋势曲线更平滑
内存与计算开销	低（状态只需维护一份）	高（需同时维护多个活跃窗口的状态）
典型业务场景	按自然小时/天统计总销售额、每分钟独立 PV/UV	监控大屏（每 5 秒刷新一次“过去 10 分钟”的异常报警率）、股票移动平均线

3. 在 SQL 中的写法体现

在现代流式 SQL（如 Flink SQL）中，这两者的语法差异非常直观：
– 滚动窗口 SQL（只需传 1 个时间参数：窗口大小）：

-- 每 1 小时统计一次各部门总销售额（10:00-11:00, 11:00-12:00...）
SELECT 
    department,
    TUMBLE_START(sale_time, INTERVAL '1' HOUR) AS window_start,
    SUM(amount) AS total_sales
FROM sales_stream
GROUP BY department, TUMBLE(sale_time, INTERVAL '1' HOUR);

• 滑动窗口 SQL（必须传 2 个时间参数：滑动步长、窗口大小）：

-- 每隔 15 分钟，统计一次过去 1 小时内的各部门总销售额
SELECT 
    department,
    HOP_START(sale_time, INTERVAL '15' MINUTE, INTERVAL '1' HOUR) AS window_start,
    SUM(amount) AS total_sales
FROM sales_stream
GROUP BY department, HOP(sale_time, INTERVAL '15' MINUTE, INTERVAL '1' HOUR);

(注：在 Flink SQL 和部分流计算引擎中，滑动窗口通常被称为 Hopping Window，关键字为 HOP)。

4. 顺带一提：第三种常见窗口（会话窗口 Session Window）

除了固定节拍的滚动和滑动窗口，流计算中还有一种会话窗口。它没有固定的大小或步长，而是根据数据活跃度（超时时间 Gap）动态划定——只要用户持续操作，窗口就一直向后延伸；一旦超过设定时间（例如 30 分钟）没有新数据进来，当前窗口就关闭结算。常用于统计用户的单次 App 访问时长或点击轨迹。

PARTITION BY 和 GROUP BY

PARTITION BY 和 GROUP BY 的核心区别在于：是否合并原始数据行。 GROUP BY 会将多行数据压缩折叠成一行汇总结果；而 PARTITION BY 只是在逻辑上划分计算边界，完整保留每一行原始明细。

1. 核心差异对比

对比维度	GROUP BY（分组聚合）	PARTITION BY（窗口分区）
输出行数	减少（每个分组只输出一行汇总结果）	不变（原表有多少行，结果就输出多少行）
明细数据	丢失（无法直接查询未参与分组的明细列）	保留（可以同时展示个体明细和组内汇总指标）
使用位置	独立作为 SQL 查询的一个子句	必须嵌套在窗口函数的 OVER(...) 内部使用
支持的函数	仅支持聚合函数（SUM, COUNT, AVG, MAX 等）	支持聚合函数，还支持专属的排名函数（RANK）和偏移函数（LAG）
SQL 执行顺序	较早执行（在 WHERE 之后、SELECT 之前执行）	较晚执行（在 GROUP BY 和 HAVING 之后执行）

2. 直观代码与结果对比

假设有一张包含 4 条员工工资记录的表 employees：
– 使用 GROUP BY（做减法，看宏观汇总）：

SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department;

• 结果：只剩 2 行（“技术部：15000”、“市场部：10000”）。具体每个员工叫什么、拿多少工资，全部被折叠隐藏了。
• 使用 PARTITION BY（做加法，看微观对比）：

SELECT emp_name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS avg_sal
FROM employees;

• 结果：依然是 4 行。每个员工的姓名和个人薪资都在，同时最后一列还附带了他们所在部门的平均薪资，方便直接计算“个人薪资与部门平均水平的差值”。

3. 什么时候该用哪一个？

• 选 GROUP BY：当你只需要看报表级汇总数据时（例如：“统计每个部门有多少人”、“计算每个月的总销售额”）。
• 选 PARTITION BY：当你需要拿个体和群体做对比，或者需要在组内排序/累计时（例如：“找出每个部门工资最高的前 3 名员工”、“计算每个员工的销售额占所在部门总销售额的百分比”）。

4. 进阶冷知识：两者可以同时出现在同一条 SQL 中

因为 GROUP BY 的执行顺序在 PARTITION BY 之前，你可以先用 GROUP BY 把明细表按“部门 + 月份”汇总成月度报表，再在外部或同级用 PARTITION BY 计算每个部门的累计月销售额。