SQL 窗口框架：ROWS 与 RANGE

第 7.3 课 · 阅读时间：约 8 分钟 · 练习题 ↓

窗口框架决定聚合型窗口函数实际使用当前行周围的哪些行：从开头起的所有行、最近三行，还是最近七天。在本课中，你将学习 ROWS 和 RANGE 框架的语法，构建累计总和与移动平均，在真实数据上看到为什么“最近 3 行”和“最近 3 天”会得出不同的答案，并避开默认框架的陷阱。

PARTITION BY 把行分组，ORDER BY 对行排序。框架是 OVER 的第三部分：在当前分区内，它相对于当前行选出一个滑动的行范围。聚合函数（SUM、AVG、COUNT、MIN、MAX）和取值函数（FIRST_VALUE、LAST_VALUE）会遵循框架；排名函数和 LAG/LEAD 会忽略框架。

示例在 Sakila 示例数据库（MySQL 8.0）上运行。

框架语法

function_name(expression) OVER (
    PARTITION BY partition_columns
    ORDER BY sort_column
    { ROWS | RANGE } BETWEEN frame_start AND frame_end
)

• ROWS 按物理行计数：2 PRECEDING 就是当前行上面的两行，不管它们的值是多少。
• RANGE 按 ORDER BY 列的值计算：INTERVAL 2 DAY PRECEDING 指日期最多早两天的所有行。带偏移量的 RANGE 框架要求 ORDER BY 中恰好有一个数值列或日期列。

数据：每日收入

所有示例都使用 2005 年 7 月 26 日到 8 月 23 日的每日收入。注意其中的空缺：8 月 2 日之后，下一个有付款的日子是 8 月 16 日。

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT pay_day, revenue
FROM daily
ORDER BY pay_day;

结果：16 天：7 月 26 日至 8 月 2 日，以及 8 月 16 日至 23 日。

累计总和与剩余总和

UNBOUNDED PRECEDING AND CURRENT ROW 从第一行开始累计；CURRENT ROW AND UNBOUNDED FOLLOWING 对后面还没到的部分求和：

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    SUM(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    SUM(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining_total
FROM daily
ORDER BY pay_day
LIMIT 3;

结果：7 月 26 日的累计总和是 160.67，剩余总和是整个时期的收入 37971.56；7 月 28 日分别是 5464.98 和 35084.38。

累计总和的更多内容，包括按组累计以及不用窗口函数的解法，见第 12.7 课。

移动平均：3 行不等于 3 天

移动平均可以平滑每日波动。通常的第一反应是“当前行及其前两行”，也就是 ROWS 框架。而带 INTERVAL 的 RANGE 框架取的是“当天及之前的两个日历日”：

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    ROUND(AVG(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg_3_rows,
    ROUND(AVG(revenue) OVER (ORDER BY pay_day
        RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW), 2) AS avg_3_days
FROM daily
ORDER BY pay_day;

结果：16 行；除了紧接空缺之后的日子，两种平均值都相同：8 月 16 日按 3 行计算的平均值是 1885.21，按 3 天计算的是 111.77。

8 月 16 日的“最近 3 行”是 8 月 1 日、8 月 2 日和 8 月 16 日，其中两天是两周以前的数据。“最近 3 天”只包含 8 月 16 日当天，因为 8 月 14 日和 15 日没有付款。当日期存在空缺时，请用带 INTERVAL 的 RANGE 来实现日历窗口，或者先把数据连接到完整的日历上（第 6.6 课），让每一天都有一行。

MariaDB 不接受框架中的 INTERVAL。在 MariaDB 中，可以按天数排序并使用数值偏移量，结果相同：AVG(revenue) OVER (ORDER BY TO_DAYS(pay_day) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)。

在数据开头，两种框架只是包含的行更少：第一天按一个值求平均，第二天按两个值求平均。

默认框架

如果 OVER 中有 ORDER BY 但没有框架，数据库会使用 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这是一种累计计算，而且由于是 RANGE，排序值相同的行会作为一组同时加入。没有 ORDER BY 时，默认框架就是整个分区。

这带来两个实际影响：

• SUM(x) OVER (ORDER BY day) 是累计总和，而不是总计。要得到总计，请去掉 ORDER BY。
• 当排序列有重复值时，累计总和会一次性跳过整组相等的行。第 12.7 课用同一秒内发生的两笔付款演示了这一点。只要框架很重要，就显式写出 ROWS BETWEEN ...。

用 WINDOW 为窗口命名

当多个函数使用同一个窗口时，可以用 WINDOW 子句定义一次，再按名称引用：

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    MIN(revenue) OVER last_3_days AS min_3_days,
    MAX(revenue) OVER last_3_days AS max_3_days,
    COUNT(*) OVER last_3_days AS days_in_window
FROM daily
WINDOW last_3_days AS (
    ORDER BY pay_day
    RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW
)
ORDER BY pay_day;

结果：16 行；8 月 16 日的 days_in_window 为 1，8 月 17 日为 2，从 8 月 18 日起又恢复为 3。

WINDOW 子句写在 HAVING 之后、ORDER BY 之前。MySQL 8.0、MariaDB 10.2+、PostgreSQL 和 SQLite 都支持它，SQL Server 从 2022 版开始支持。

MySQL 不支持的功能

SQL 标准还定义了按相等值分组计数的 GROUPS 框架和 EXCLUDE 选项。PostgreSQL 和 SQLite 支持它们，MySQL 和 MariaDB 不支持。

要点：

• 框架选出聚合型窗口函数所使用的当前行周围的行：ROWS 或 RANGE BETWEEN start AND end。
• UNBOUNDED PRECEDING AND CURRENT ROW 得到累计总和；n PRECEDING AND CURRENT ROW 得到滑动窗口。
• ROWS 按行计数，RANGE 比较值：当日期有空缺时，“3 行”和“3 天”的平均值不同。
• 有 ORDER BY 而没有框架时，默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW；当框架很重要时请显式写出。
• WINDOW 子句为窗口命名，让多个函数可以复用它。

常见问题

SQL 中 ROWS 和 RANGE 有什么区别？

ROWS 按物理行计数：2 PRECEDING 指当前行上面的两行。RANGE 比较 ORDER BY 列的值：INTERVAL 2 DAY PRECEDING 指日期在当前行之前两天以内的所有行，不管有多少行。

如何在 SQL 中计算移动平均？

使用带框架的 AVG：AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 计算当前行及前六行的平均值。如果有些日子没有数据行，请使用 RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW，或者先用日历填补空缺。

默认的窗口框架是什么？

OVER 中有 ORDER BY 且没有框架子句时，默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即一种累计计算，排序值相同的行会一起计入。没有 ORDER BY 时，框架就是整个分区。

窗口框架对 ROW_NUMBER 和 RANK 有效吗？

无效。排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）以及 LAG/LEAD 始终在整个分区上工作，会忽略框架。框架只影响聚合函数以及 FIRST_VALUE、LAST_VALUE 和 NTH_VALUE。