发布于 由admin

MySQL高级函数之：`Window Frame`：`ROWS`、`RANGE`和`GROUPS`的区别。

MySQL Window Frame：ROWS, RANGE, GROUPS 详解

大家好，今天我们来深入探讨MySQL窗口函数中的Window Frame，特别是ROWS、RANGE和GROUPS这三个关键字的区别。理解这些关键字对于有效地使用窗口函数至关重要，可以帮助我们实现更复杂的分析需求。

1. Window Frame 的作用

首先，我们需要明确什么是Window Frame。简单来说，Window Frame定义了窗口函数计算时所使用的行集合，这个行集合是当前行的“窗口”。它相对于当前行而言，决定了哪些行会参与到窗口函数的计算中。如果没有显式指定Window Frame，MySQL会使用默认的Frame定义，这取决于具体的窗口函数。

2. Window Frame 的基本语法

Window Frame通常与OVER子句结合使用，其基本语法如下：

OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column3, column4, ...
    [frame_unit { BETWEEN frame_start AND frame_end }]
)

其中：

  • PARTITION BY (可选): 将结果集分成多个分区。每个分区独立计算窗口函数。
  • ORDER BY (可选): 在每个分区内，按照指定的列进行排序。ORDER BY子句对于ROWS, RANGE, 和 GROUPS 尤其重要，因为它决定了行的顺序，进而影响Frame的定义。
  • frame_unit: 指定Frame的单位，可以是ROWS, RANGE, 或 GROUPS。
  • frame_start: 定义Frame的起始位置。
  • frame_end: 定义Frame的结束位置。

3. Frame 的起始和结束位置

frame_start 和 frame_end 可以使用以下关键字：

  • UNBOUNDED PRECEDING: Frame从分区的起始位置开始。
  • UNBOUNDED FOLLOWING: Frame到分区的结束位置结束。
  • CURRENT ROW: Frame从当前行开始/结束。
  • N PRECEDING: Frame从当前行之前的第N行开始。
  • N FOLLOWING: Frame从当前行之后的第N行结束。

4. ROWS 关键字

ROWS 关键字根据物理行数定义Frame。它指定Frame包含当前行之前或之后特定数量的行。

示例：

假设我们有一个sales表，包含以下数据：

CREATE TABLE sales (
    sale_date DATE,
    product VARCHAR(50),
    sale_amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, product, sale_amount) VALUES
('2023-01-01', 'Product A', 100.00),
('2023-01-02', 'Product A', 120.00),
('2023-01-03', 'Product A', 150.00),
('2023-01-04', 'Product A', 110.00),
('2023-01-05', 'Product A', 130.00),
('2023-01-01', 'Product B', 80.00),
('2023-01-02', 'Product B', 90.00),
('2023-01-03', 'Product B', 100.00),
('2023-01-04', 'Product B', 110.00),
('2023-01-05', 'Product B', 120.00);

现在，我们想计算每个产品每天的销售额，以及过去3天的销售额总和（包括当天）。我们可以使用ROWS BETWEEN 2 PRECEDING AND CURRENT ROW来实现：

SELECT
    sale_date,
    product,
    sale_amount,
    SUM(sale_amount) OVER (PARTITION BY product ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3day_sum
FROM
    sales;

这个查询的结果会是：

sale_date product sale_amount rolling_3day_sum
2023-01-01 Product A 100.00 100.00
2023-01-02 Product A 120.00 220.00
2023-01-03 Product A 150.00 370.00
2023-01-04 Product A 110.00 380.00
2023-01-05 Product A 130.00 390.00
2023-01-01 Product B 80.00 80.00
2023-01-02 Product B 90.00 170.00
2023-01-03 Product B 100.00 270.00
2023-01-04 Product B 110.00 300.00
2023-01-05 Product B 120.00 330.00

解释：

  • PARTITION BY product：将数据按产品分组。
  • ORDER BY sale_date：在每个产品组内，按销售日期排序。
  • ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：定义Frame为：从当前行之前的2行，到当前行。这意味着对于每一行，都会计算包括当前行和前两行的销售额总和。

5. RANGE 关键字

RANGE 关键字根据排序键的值的范围定义Frame。它指定Frame包含所有排序键值在当前行排序键值的一定范围内的行。RANGE 需要 ORDER BY 子句，并且ORDER BY子句中只能有一个表达式。

示例：

假设我们想计算每个产品每天的销售额，以及销售日期在前后2天内的销售额总和（包括当天）。 这里我们假设销售日期是排序键。

SELECT
    sale_date,
    product,
    sale_amount,
    SUM(sale_amount) OVER (PARTITION BY product ORDER BY sale_date RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND INTERVAL 2 DAY FOLLOWING) AS range_sum
FROM
    sales;

这个查询的结果会是：

sale_date product sale_amount range_sum
2023-01-01 Product A 100.00 370.00
2023-01-02 Product A 120.00 510.00
2023-01-03 Product A 150.00 510.00
2023-01-04 Product A 110.00 510.00
2023-01-05 Product A 130.00 390.00
2023-01-01 Product B 80.00 270.00
2023-01-02 Product B 90.00 370.00
2023-01-03 Product B 100.00 400.00
2023-01-04 Product B 110.00 420.00
2023-01-05 Product B 120.00 340.00

解释：

  • PARTITION BY product：将数据按产品分组。
  • ORDER BY sale_date：在每个产品组内，按销售日期排序。
  • RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND INTERVAL 2 DAY FOLLOWING：定义Frame为：所有销售日期在当前行销售日期前后2天内的行。 例如，对于2023-01-01，Frame包含2022-12-30到2023-01-03的所有行。

重要注意事项：

  • 如果 ORDER BY 表达式是数字类型，可以直接使用数字来指定范围，例如 RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING。
  • 如果 ORDER BY 表达式是日期类型，需要使用 INTERVAL 来指定范围，例如 RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND INTERVAL 2 DAY FOLLOWING。
  • 当 ORDER BY 子句中的排序表达式存在重复值时，RANGE 的行为与 ROWS 明显不同。RANGE 会将所有具有相同排序键值的行都包含在Frame内。
  • RANGE 只能用于数值类型和时间类型。

6. GROUPS 关键字 (MySQL 8.0.18 及更高版本)

GROUPS 关键字根据排序后的行组定义Frame。它将具有相同排序键值的行视为一个组，并指定Frame包含当前行组之前或之后的特定数量的组。与ROWS基于物理行计数不同，GROUPS基于逻辑组计数。GROUPS 需要 ORDER BY 子句。

示例：

为了演示GROUPS，我们需要一些重复的排序键值。 修改sales表，添加一些具有相同日期的销售记录：

INSERT INTO sales (sale_date, product, sale_amount) VALUES
('2023-01-03', 'Product A', 160.00),
('2023-01-04', 'Product A', 120.00);

现在 sales 表中，Product A 在 2023-01-03 和 2023-01-04 有多条记录。

我们想计算每个产品每天的销售额，以及当前行组之前和之后的 1 个行组的销售额总和。

SELECT
    sale_date,
    product,
    sale_amount,
    SUM(sale_amount) OVER (PARTITION BY product ORDER BY sale_date GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS groups_sum
FROM
    sales;

这个查询的结果会是：

sale_date product sale_amount groups_sum
2023-01-01 Product A 100.00 380.00
2023-01-02 Product A 120.00 630.00
2023-01-03 Product A 150.00 760.00
2023-01-03 Product A 160.00 760.00
2023-01-04 Product A 110.00 650.00
2023-01-04 Product A 120.00 650.00
2023-01-05 Product A 130.00 360.00
2023-01-01 Product B 80.00 270.00
2023-01-02 Product B 90.00 370.00
2023-01-03 Product B 100.00 400.00
2023-01-04 Product B 110.00 420.00
2023-01-05 Product B 120.00 340.00

解释：

  • PARTITION BY product：将数据按产品分组。
  • ORDER BY sale_date：在每个产品组内，按销售日期排序。注意相同 sale_date的行被视为一个组。
  • GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING：定义Frame为：当前行组之前的一个行组，到当前行组之后的一个行组。

详细分析Product A的结果:

  • 2023-01-01: Frame包含 2023-01-01 和 2023-01-02 的行。 总和为 100 + 120 + 160 = 380。
  • 2023-01-02: Frame包含 2023-01-01, 2023-01-02 和 2023-01-03 的行。 总和为 100 + 120 + 150 + 160 = 630。
  • 2023-01-03 (两行): Frame包含 2023-01-02, 2023-01-03 和 2023-01-04 的行。总和为 120 + 150 + 160 + 110 + 120 = 660。
  • 2023-01-04 (两行): Frame包含 2023-01-03, 2023-01-04 和 2023-01-05 的行。总和为 150 + 160 + 110 + 120 + 130 = 670。
  • 2023-01-05: Frame包含 2023-01-04 和 2023-01-05 的行。总和为 110 + 120 + 130 = 360。

7. 三者对比总结

为了更清晰地理解ROWS、RANGE和GROUPS的区别，我们用表格总结如下：

特性 ROWS RANGE GROUPS (MySQL 8.0.18+)
定义Frame依据 物理行数 排序键值的范围 排序后的行组 (具有相同排序键值的行被视为一个组)
ORDER BY 可选，但通常需要 必须，且只能有一个表达式 必须
排序键重复值 按照物理行计数 将所有具有相同排序键值的行都包含在Frame内 将具有相同排序键值的行视为一个组
适用数据类型 所有数据类型 数值类型和时间类型 所有数据类型
适用场景 需要基于物理行数进行计算的场景 需要基于排序键值的范围进行计算的场景，例如计算一段时间内的累计值 需要基于逻辑组进行计算的场景，例如计算相邻几个组的统计值
示例 过去3天的销售额总和，包括当天 计算销售日期在前后2天内的销售额总和（包括当天） 计算当前行组之前和之后的 1 个行组的销售额总和

8. 实际应用场景

  • ROWS: 计算移动平均值、滚动总和，或者需要精确控制参与计算的行数的场景。
  • RANGE: 计算一段时间内的累计值，例如计算过去一周的销售额总和，或者分析特定数值范围内的用户行为。
  • GROUPS: 分析具有相同属性的组，例如计算相邻几个城市的人口总和，或者分析相邻几个时间段内的用户活跃度。

9. 注意事项

  • 选择合适的frame_unit取决于具体的分析需求。错误的选择可能导致计算结果不准确。
  • RANGE 关键字的排序键必须是数值类型或时间类型。
  • GROUPS 关键字是MySQL 8.0.18及更高版本才支持的。
  • 理解默认的Frame定义，避免不必要的错误。默认情况下，如果使用了ORDER BY，但没有显式指定Window Frame，则Frame为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。如果既没有ORDER BY也没有显式指定Window Frame，则Frame为整个分区。

10. 总结：选择正确的 Frame 定义

掌握ROWS、RANGE和GROUPS的区别能够灵活解决问题。在实际使用中，需要根据具体的业务需求，认真分析数据特点，选择最合适的frame_unit和frame_start/frame_end，才能编写出高效准确的SQL查询。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注