课程 7.3 · 阅读时间：约 9 分钟

本课程重点介绍窗口框架，这是一种机制，用于定义哪些行参与相对于当前行的窗口函数计算。您将探索 ROWS、RANGE 和 GROUPS 模式，回顾常见边界，并查看 Sakila 数据的实际场景。课程结束时，您将能够有意识地选择框架边界并避免常见错误。

窗口框架和边界

在之前的课程中，我们使用了带有 PARTITION BY 和 ORDER BY 的窗口函数。但 OVER 子句提供了第三个同样强大的组件：窗口框架。窗口框架让您精确地定义哪些行围绕当前行被包含在计算中 — 使得运行总计、移动平均和许多其他时间序列模式成为可能。

什么是窗口框架？

当您编写 OVER (ORDER BY ...) 时，许多数据库会应用一个您可能不知道的默认框架。显式指定框架可以让您完全控制计算窗口。

OVER 子句的完整语法是：

function_name() OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_clause]
)

其中 frame_clause 是：

{ ROWS | RANGE | GROUPS }
BETWEEN frame_start AND frame_end

每个边界 (frame_start, frame_end) 是以下之一：

边界关键字	意义
`UNBOUNDED PRECEDING`	分区的第一行
`n PRECEDING`	当前行之前的 n 行（或范围单位）
`CURRENT ROW`	当前行本身
`n FOLLOWING`	当前行之后的 n 行（或范围单位）
`UNBOUNDED FOLLOWING`	分区的最后一行

框架模式：ROWS、RANGE 和 GROUPS

框架模式控制边界的测量方式。

ROWS 模式

ROWS 计算物理行。1 PRECEDING 总是意味着在排序中紧接着当前行之前的确切一行。

在需要固定宽度滑动窗口时使用最佳（例如，基于每日行的 7 天滚动平均）。

RANGE 模式

RANGE 计算逻辑值。1 PRECEDING 意味着所有 ORDER BY 值在当前行值的 1 个单位范围内的行 — 不一定仅仅是一个物理行。

在您希望聚合所有与当前行具有相同值的行或所有在值范围内的行时使用最佳。

重要： 当您指定 ORDER BY 但没有显式框架子句时，默认框架是：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

这意味着窗口包括从分区开始到并包括所有与当前行相同的 ORDER BY 值的行。

GROUPS 模式

GROUPS 计算同级组（具有相同 ORDER BY 值的行集）。1 PRECEDING 意味着具有下一个较低值的完整行组。此模式在 PostgreSQL 11+ 和其他一些数据库中受支持，但在 MySQL/MariaDB 中不支持。

常见框架模式

运行总计（累积和）

包括从分区开始到当前行的所有行：

SELECT
    customer_id,
    payment_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY payment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM
    payment
WHERE
    customer_id = 1
ORDER BY
    payment_date;

输出示例：

customer_id | payment_date | amount | running_total
1           | 2005-05-25   | 2.99   | 2.99
1           | 2005-06-15   | 4.99   | 7.98
1           | 2005-07-08   | 11.99  | 19.97
1           | 2005-08-01   | 11.99  | 31.96

关键点： 每行的 running_total 累积该客户的所有先前付款。框架 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 意味着：从该分区的第 1 行开始，到当前行结束。

移动平均（滑动窗口）

计算每个客户的 3 笔付款的移动平均：

SELECT
    customer_id,
    payment_date,
    amount,
    ROUND(
        AVG(amount) OVER (
            PARTITION BY customer_id
            ORDER BY payment_date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2
    ) AS moving_avg_3
FROM
    payment
WHERE
    customer_id = 1
ORDER BY
    payment_date;

输出示例：

customer_id | payment_date | amount | moving_avg_3
1           | 2005-05-25   | 2.99   | 2.99
1           | 2005-06-15   | 4.99   | 3.99
1           | 2005-07-08   | 11.99  | 6.66
1           | 2005-08-01   | 11.99  | 9.66
1           | 2005-08-23   | 5.99   | 9.99

关键点： ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 创建了一个恰好包含 3 行的窗口：当前行和之前的 2 行。当存在少于 3 行时（在分区开始时），窗口会相应缩小。

向前看（包括未来行）

计算当前行和接下来的 2 行的平均值：

SELECT
    customer_id,
    payment_date,
    amount,
    ROUND(
        AVG(amount) OVER (
            PARTITION BY customer_id
            ORDER BY payment_date
            ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
        ), 2
    ) AS forward_avg
FROM
    payment
WHERE
    customer_id = 1
ORDER BY
    payment_date;

关键点： CURRENT ROW AND 2 FOLLOWING 将窗口向前移动。分区中的最后两行将平均较少的值，因为它们后面没有行。

完整分区聚合（作为窗口）

将每笔付款与客户的整体平均值进行比较：

SELECT
    customer_id,
    payment_date,
    amount,
    ROUND(
        AVG(amount) OVER (
            PARTITION BY customer_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ), 2
    ) AS customer_avg,
    amount - ROUND(
        AVG(amount) OVER (
            PARTITION BY customer_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ), 2
    ) AS deviation
FROM
    payment
WHERE
    customer_id IN (1, 2)
ORDER BY
    customer_id, payment_date;

关键点： UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 跨越整个分区 — 相当于 GROUP BY 聚合，但不合并行。

ROWS 与 RANGE：直接比较

理解 ROWS 和 RANGE 之间的区别在于行共享相同的 ORDER BY 值时至关重要。

SELECT
    customer_id,
    amount,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    amount;

输出示例：

customer_id | amount | sum_rows | sum_range
3           | 9.99   | 9.99     | 9.99
2           | 10.99  | 20.98    | 20.98
1           | 11.99  | 32.97    | 55.94
2           | 11.99  | 44.96    | 55.94
1           | 11.99  | 55.94    | 55.94

观察：

使用 ROWS：每个物理行单独计数，无论是否存在平局。运行总和每次向前推进一行。
使用 RANGE：所有具有相同金额值的行一起包含。两个 11.99 的行被视为同一逻辑组的一部分，因此 sum_range 立即跳到总和。

命名窗口（WINDOW 子句）

如果您在查询中多次使用相同的框架定义，可以使用 WINDOW 子句为其命名，以避免重复：

SELECT
    customer_id,
    payment_date,
    amount,
    SUM(amount)   OVER w AS running_total,
    AVG(amount)   OVER w AS running_avg,
    COUNT(amount) OVER w AS payment_count
FROM
    payment
WHERE
    customer_id = 1
WINDOW w AS (
    PARTITION BY customer_id
    ORDER BY payment_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ORDER BY
    payment_date;

关键点： WINDOW w AS (...) 子句定义框架一次。所有三个窗口函数调用都通过 OVER w 引用它。这更简洁，减少了错误，并且更易于维护。

注意：WINDOW 子句在 PostgreSQL、MySQL 8.0+ 和 MariaDB 10.2+ 中受支持。

框架边界参考

框架定义	包含内容
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	从分区开始到当前行的所有行
`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	分区中的所有行（完整聚合）
`ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`	当前行加上两侧各一行（3 行窗口）
`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`	当前行和之前的 2 行（3 行滑动窗口）
`ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`	当前行到分区末尾
`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	当 `ORDER BY` 存在时的默认值；包括所有与当前相同的 ORDER BY 值的行

实际应用：每日销售与运行和移动指标

在单个查询中结合多个窗口框架以获得完整的视图：

SELECT
    DATE(payment_date)                            AS payment_day,
    SUM(amount)                                   AS daily_total,
    SUM(SUM(amount)) OVER (
        ORDER BY DATE(payment_date)
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                             AS cumulative_total,
    ROUND(AVG(SUM(amount)) OVER (
        ORDER BY DATE(payment_date)
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2)                                         AS rolling_7day_avg
FROM
    payment
GROUP BY
    DATE(payment_date)
ORDER BY
    payment_day;

关键点： 外部聚合 (SUM(SUM(amount))) 在分组结果上嵌套窗口函数 — 这是时间序列仪表板的强大模式。

何时使用每个框架选项

目标	推荐框架
运行/累积总计	`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
与行数据并行的完整分区聚合	`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`
N 期移动平均	`ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW`
对称平滑窗口	`ROWS BETWEEN N PRECEDING AND N FOLLOWING`
基于值的范围聚合（将平局作为一组处理）	`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
在多个函数中重用相同框架	命名的 `WINDOW` 子句

常见问题解答

默认使用哪个框架？

在许多 DBMS 中，如果存在 ORDER BY，默认值是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。因为这可能会令人惊讶，最好显式指定框架。

何时选择 ROWS，何时选择 RANGE？

在逐行控制时使用 ROWS。当您需要处理值范围并将相等的 ORDER BY 值一起处理时使用 RANGE。

为什么需要 UNBOUNDED FOLLOWING？

它将框架扩展到分区的末尾。当函数需要查看不仅仅是当前行的行，还需要查看所有后续行时，这很重要。

关键要点

窗口框架 定义了相对于当前行的行集，这些行包含在窗口函数计算中。
三种框架模式是 ROWS（物理行）、RANGE（逻辑值范围）和 GROUPS（相等值的同级组）。
当存在 ORDER BY 时，默认框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — 了解这个默认值可以防止与平局值相关的细微错误。
对于固定宽度的滑动窗口（例如 7 天滚动平均），使用 ROWS；当平局值应聚合在一起时，使用 RANGE。
边界关键字：UNBOUNDED PRECEDING、n PRECEDING、CURRENT ROW、n FOLLOWING、UNBOUNDED FOLLOWING。
WINDOW 子句允许您命名和重用框架定义，使复杂查询保持可读性。
窗口框架不影响 PARTITION BY — 它们仅在分区内缩小框架。

在下一课中，我们将探索偏移窗口函数 LAG、LEAD、FIRST_VALUE 和 LAST_VALUE，这些函数让您可以将一行的值与其他行的值进行比较，而无需自连接。

练习

课程

练习场

测试

窗口框架和边界

什么是窗口框架？

框架模式：ROWS、RANGE 和 GROUPS

ROWS 模式

RANGE 模式

GROUPS 模式

常见框架模式

运行总计（累积和）

移动平均（滑动窗口）

向前看（包括未来行）

完整分区聚合（作为窗口）

ROWS 与 RANGE：直接比较

命名窗口（WINDOW 子句）

框架边界参考

实际应用：每日销售与运行和移动指标

何时使用每个框架选项

常见问题解答

默认使用哪个框架？

何时选择 ROWS，何时选择 RANGE？

为什么需要 UNBOUNDED FOLLOWING？

关键要点

登录

注册

窗口框架和边界

什么是窗口框架？

框架模式：ROWS、RANGE 和 GROUPS

ROWS 模式

RANGE 模式

GROUPS 模式

常见框架模式

运行总计（累积和）

移动平均（滑动窗口）

向前看（包括未来行）

完整分区聚合（作为窗口）

ROWS 与 RANGE：直接比较

命名窗口（WINDOW 子句）

框架边界参考

实际应用：每日销售与运行和移动指标

何时使用每个框架选项

常见问题解答

默认使用哪个框架？

何时选择 ROWS，何时选择 RANGE？

为什么需要 UNBOUNDED FOLLOWING？

关键要点