发布于 由admin

MySQL高级函数之：`ROWS BETWEEN`：`UNBOUNDED PRECEDING`和`UNBOUNDED FOLLOWING`的应用。

MySQL窗口函数：ROWS BETWEEN深入解析 UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING

大家好！今天我们来深入探讨MySQL窗口函数中的ROWS BETWEEN子句，重点关注UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING这两个关键的选项。 窗口函数是MySQL 8.0引入的强大功能，它允许我们在查询结果的“窗口”内执行计算，而ROWS BETWEEN子句则用于定义这个“窗口”的大小和范围。理解并熟练运用ROWS BETWEEN能极大地提升数据分析和报表生成的效率。

窗口函数基础回顾

在深入ROWS BETWEEN之前，我们先快速回顾一下窗口函数的基本概念。窗口函数与聚合函数类似，都对一组行进行计算，但窗口函数不会将这些行折叠成单个结果行，而是为每一行返回一个结果。

一个典型的窗口函数语法如下：

function_name(arguments) OVER (
  [PARTITION BY column1, column2, ...]
  [ORDER BY column3, column4, ...]
  [ROWS BETWEEN frame_start AND frame_end]
)
  • function_name: 要执行的窗口函数，例如SUM(), AVG(), RANK(), LAG(), LEAD()等。
  • PARTITION BY: 将结果集划分为多个分区，窗口函数将分别在每个分区内计算。
  • ORDER BY: 定义每个分区内行的顺序，这对于某些窗口函数（如RANK()）至关重要。
  • ROWS BETWEEN: 定义窗口的起始和结束位置。 这是我们今天要重点讨论的部分。

ROWS BETWEEN子句详解

ROWS BETWEEN子句用于精确控制窗口的大小和范围，它定义了当前行（current row）的“窗口”包含哪些行。 ROWS BETWEEN子句总是与ORDER BY子句一起使用，因为窗口的定义依赖于行的顺序。

ROWS BETWEEN子句的基本语法是：

ROWS BETWEEN frame_start AND frame_end

其中 frame_start 和 frame_end 可以是以下几种选项：

  • UNBOUNDED PRECEDING: 窗口从分区的第一行开始，直到当前行。
  • UNBOUNDED FOLLOWING: 窗口从当前行开始，直到分区的最后一行。
  • CURRENT ROW: 窗口仅包含当前行。
  • n PRECEDING: 窗口从当前行之前第 n 行开始，到当前行结束。
  • n FOLLOWING: 窗口从当前行之后第 n 行开始，到当前行结束。

今天我们重点讲解UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING。

UNBOUNDED PRECEDING的应用

UNBOUNDED PRECEDING表示窗口从分区的最开始一行到当前行。 这意味着每一行的窗口都包含分区中从第一行到该行的所有行。

示例：计算累计销售额

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

CREATE TABLE sales (
  sale_date DATE,
  region VARCHAR(50),
  amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, region, amount) VALUES
('2023-01-01', 'East', 100.00),
('2023-01-02', 'East', 150.00),
('2023-01-03', 'East', 200.00),
('2023-01-01', 'West', 80.00),
('2023-01-02', 'West', 120.00),
('2023-01-03', 'West', 180.00);

我们想计算每个区域的累计销售额，可以使用以下SQL查询：

SELECT
  sale_date,
  region,
  amount,
  SUM(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM
  sales;

这个查询的执行结果如下：

sale_date region amount cumulative_amount
2023-01-01 East 100.00 100.00
2023-01-02 East 150.00 250.00
2023-01-03 East 200.00 450.00
2023-01-01 West 80.00 80.00
2023-01-02 West 120.00 200.00
2023-01-03 West 180.00 380.00

可以看到，cumulative_amount列显示了每个区域每天的累计销售额。 对于每个区域的第一天，累计销售额等于当天的销售额。 对于每个区域的第二天，累计销售额等于第一天和第二天的销售额之和，以此类推。

逻辑解析：

  • PARTITION BY region: 将数据按照region列分成不同的分区，East和West分别是一个分区。
  • ORDER BY sale_date: 在每个分区内，按照sale_date对行进行排序。
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 对于每一行，窗口包含从该分区的第一行到当前行的所有行。 SUM(amount)计算该窗口内所有行的amount之和，得到累计销售额。

其他应用场景：

  • 计算累计用户数： 追踪网站或应用的注册用户数量随时间的变化。
  • 计算累计利润： 分析公司利润随时间的变化趋势。
  • 计算移动平均： 虽然UNBOUNDED PRECEDING不能直接计算移动平均，但可以作为计算移动平均的基础，例如，先用UNBOUNDED PRECEDING计算累计值，再用累计值计算移动平均。

UNBOUNDED FOLLOWING的应用

UNBOUNDED FOLLOWING表示窗口从当前行到分区的最后一行。 这意味着每一行的窗口都包含分区中从该行到最后一行的所有行。

示例：计算未来销售额

继续使用上面的sales表，我们想计算每个区域的未来销售额，即从当前日期到该区域最后一次销售日期的销售额之和。

SELECT
  sale_date,
  region,
  amount,
  SUM(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS future_amount
FROM
  sales;

这个查询的执行结果如下：

sale_date region amount future_amount
2023-01-01 East 100.00 450.00
2023-01-02 East 150.00 350.00
2023-01-03 East 200.00 200.00
2023-01-01 West 80.00 380.00
2023-01-02 West 120.00 300.00
2023-01-03 West 180.00 180.00

可以看到，future_amount列显示了每个区域从当天开始到最后一天总的销售额。 对于每个区域的第一天，未来销售额等于该区域所有天的销售额之和。 对于每个区域的第二天，未来销售额等于第二天和第三天的销售额之和，以此类推。

逻辑解析：

  • PARTITION BY region: 将数据按照region列分成不同的分区。
  • ORDER BY sale_date: 在每个分区内，按照sale_date对行进行排序。
  • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 对于每一行，窗口包含从该行到该分区最后一行的所有行。 SUM(amount)计算该窗口内所有行的amount之和，得到未来销售额。

其他应用场景：

  • 计算剩余库存： 根据每天的销售记录，计算剩余库存量。
  • 计算剩余任务数量： 根据每天完成的任务数量，计算剩余未完成任务的数量。
  • 计算平均剩余寿命： 在人口统计分析中，根据年龄段数据，计算平均剩余寿命。

UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING的组合应用

UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING可以组合使用，定义包含整个分区的窗口。 这种情况下，ROWS BETWEEN子句通常可以省略，因为它是默认行为。

示例：计算每个区域的销售额占比

我们想计算每个区域每天的销售额占该区域总销售额的百分比。 可以使用以下SQL查询：

SELECT
  sale_date,
  region,
  amount,
  amount / SUM(amount) OVER (PARTITION BY region) AS sales_percentage
FROM
  sales;

或者，显式地使用 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING:

SELECT
  sale_date,
  region,
  amount,
  amount / SUM(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS sales_percentage
FROM
  sales;

两种查询方式的结果相同：

sale_date region amount sales_percentage
2023-01-01 East 100.00 0.2222
2023-01-02 East 150.00 0.3333
2023-01-03 East 200.00 0.4444
2023-01-01 West 80.00 0.2105
2023-01-02 West 120.00 0.3158
2023-01-03 West 180.00 0.4737

逻辑解析：

  • PARTITION BY region: 将数据按照region列分成不同的分区。
  • SUM(amount) OVER (PARTITION BY region): 计算每个区域的总销售额。 由于没有显式指定ROWS BETWEEN， 默认行为是 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING， 即窗口包含整个分区。
  • amount / SUM(amount) OVER (PARTITION BY region): 计算每天的销售额占该区域总销售额的比例。

更复杂的示例：计算移动平均

虽然我们前面提到UNBOUNDED PRECEDING不能直接计算移动平均，但我们可以结合子查询或Common Table Expression (CTE)来实现。

首先，我们使用UNBOUNDED PRECEDING计算累计销售额：

WITH CumulativeSales AS (
  SELECT
    sale_date,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_date) AS row_num
  FROM
    sales
)
SELECT
  sale_date,
  region,
  amount,
  cumulative_amount,
  (cumulative_amount - COALESCE((SELECT cumulative_amount FROM CumulativeSales cs2 WHERE cs2.region = CumulativeSales.region AND cs2.row_num = CumulativeSales.row_num - 3), 0)) / 3 AS moving_average
FROM
  CumulativeSales;

这个查询计算了每个区域的3日移动平均销售额。 这里COALESCE函数用于处理前3天数据不足的情况。

注意事项和最佳实践

  • 性能影响： 窗口函数通常比传统的聚合函数性能更好，但过度使用复杂的窗口函数仍然可能影响查询性能。 优化查询和适当的索引可以提高性能。
  • 理解ORDER BY的重要性： ROWS BETWEEN子句必须与ORDER BY子句一起使用，因为窗口的定义依赖于行的顺序。 ORDER BY子句的正确选择是保证窗口函数计算结果正确的关键。
  • 数据类型一致性： 确保窗口函数中使用的列具有兼容的数据类型，避免数据类型转换错误。
  • 空值处理： 考虑如何处理空值。 某些窗口函数（例如AVG()）会自动忽略空值，而其他函数（例如SUM()）则会将空值视为0。 可以使用COALESCE()函数显式地处理空值。
  • 理解默认行为： 如果省略ROWS BETWEEN子句，则默认行为取决于ORDER BY子句是否存在。 如果存在ORDER BY子句，则默认行为是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。 如果不存在ORDER BY子句，则默认行为是ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

常见问题解答

  1. ROWS BETWEEN必须和ORDER BY一起使用吗？

    是的，ROWS BETWEEN子句必须与ORDER BY子句一起使用，因为窗口的定义依赖于行的顺序。

  2. UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING可以单独使用吗？

    是的，UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING可以单独使用，分别表示窗口从分区的第一行到当前行，以及从当前行到分区的最后一行。

  3. 省略ROWS BETWEEN子句有什么影响？

    如果省略ROWS BETWEEN子句，则默认行为取决于ORDER BY子句是否存在。 如果存在ORDER BY子句，则默认行为是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。 如果不存在ORDER BY子句，则默认行为是ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

  4. 窗口函数可以嵌套使用吗？

    MySQL 8.0 不直接支持窗口函数的嵌套使用。 但是，可以通过子查询或 CTE 来实现类似的效果。

总结

希望通过今天的讲解，大家对MySQL窗口函数中的ROWS BETWEEN子句有了更深入的理解，特别是UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING的应用。 掌握这些技巧能够帮助大家更好地进行数据分析和报表生成，提升工作效率。 实践是最好的老师，希望大家多多练习，熟练掌握这些强大的工具。

发表回复

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