发布于 由admin

MySQL高级函数之：如何使用窗口函数进行`时间序列`分析。

MySQL 窗口函数进行时间序列分析

大家好，今天我们来聊聊如何使用 MySQL 的窗口函数进行时间序列分析。时间序列数据无处不在，例如股票价格、销售额、网站流量等等。而窗口函数在处理这类数据时，能够简化很多复杂的查询，提高效率。

1. 什么是窗口函数？

窗口函数，也称为OLAP (Online Analytical Processing) 函数，它允许你在与当前行相关的行的集合（即窗口）上执行计算。 与聚合函数不同，窗口函数不会将结果集分组，而是为结果集中的每一行返回一个值。这意味着你可以在不丢失原始行的详细信息的情况下执行诸如计算移动平均值、排名等操作。

1.1 窗口函数的基本语法

窗口函数的基本语法如下：

function_name(argument1, argument2, ...) OVER (
  [PARTITION BY column1, column2, ...]
  [ORDER BY column1, column2, ...]
  [frame_clause]
)
  • function_name(): 窗口函数的名称，例如 ROW_NUMBER(), RANK(), SUM(), AVG() 等。
  • argument1, argument2, ...: 窗口函数的参数，取决于具体的函数。
  • OVER(): 定义窗口的子句。
  • PARTITION BY column1, column2, ...: 将结果集划分为多个分区。窗口函数将在每个分区内独立计算。类似于 GROUP BY，但不会将结果集分组。
  • ORDER BY column1, column2, ...: 定义每个分区内数据的排序方式。这对于诸如计算累积总和、移动平均值等操作至关重要。
  • frame_clause: 定义窗口的范围。它指定了当前行周围哪些行将被包含在窗口中。如果没有指定 frame_clause，默认的窗口是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

1.2 常用的窗口函数

MySQL 8.0 及以上版本提供了丰富的窗口函数，常用的包括：

  • 排名函数: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
  • 聚合函数: SUM(), AVG(), MIN(), MAX(), COUNT()
  • 值函数: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()

2. 时间序列分析中的窗口函数应用

接下来，我们将通过几个实际案例，演示如何在时间序列分析中使用窗口函数。假设我们有一个名为 sales 的表，包含以下字段：

  • sale_date: 销售日期 (DATE)
  • product_id: 产品ID (INT)
  • sales_amount: 销售额 (DECIMAL)
CREATE TABLE sales (
  sale_date DATE NOT NULL,
  product_id INT NOT NULL,
  sales_amount DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (sale_date, product_id)
);

INSERT INTO sales (sale_date, product_id, sales_amount) VALUES
('2023-01-01', 1, 100.00),
('2023-01-01', 2, 150.00),
('2023-01-02', 1, 120.00),
('2023-01-02', 2, 180.00),
('2023-01-03', 1, 110.00),
('2023-01-03', 2, 200.00),
('2023-01-04', 1, 130.00),
('2023-01-04', 2, 170.00),
('2023-01-05', 1, 140.00),
('2023-01-05', 2, 190.00),
('2023-01-06', 1, 150.00),
('2023-01-06', 2, 210.00),
('2023-01-07', 1, 160.00),
('2023-01-07', 2, 220.00);

2.1 计算每日总销售额

我们可以使用 SUM() 窗口函数计算每日总销售额，而无需使用 GROUP BY 子句。

SELECT
  sale_date,
  product_id,
  sales_amount,
  SUM(sales_amount) OVER (PARTITION BY sale_date) AS daily_total_sales
FROM sales
ORDER BY sale_date, product_id;

这个查询会返回每一行的销售额以及当天的总销售额。 PARTITION BY sale_date 将数据按照销售日期进行分区，SUM(sales_amount) 计算每个分区内的销售额总和。

sale_date product_id sales_amount daily_total_sales
2023-01-01 1 100.00 250.00
2023-01-01 2 150.00 250.00
2023-01-02 1 120.00 300.00
2023-01-02 2 180.00 300.00
2023-01-03 1 110.00 310.00
2023-01-03 2 200.00 310.00
2023-01-04 1 130.00 300.00
2023-01-04 2 170.00 300.00
2023-01-05 1 140.00 330.00
2023-01-05 2 190.00 330.00
2023-01-06 1 150.00 360.00
2023-01-06 2 210.00 360.00
2023-01-07 1 160.00 380.00
2023-01-07 2 220.00 380.00

2.2 计算累积销售额

我们可以使用 SUM() 窗口函数结合 ORDER BY 子句计算累积销售额。

SELECT
  sale_date,
  product_id,
  sales_amount,
  SUM(sales_amount) OVER (ORDER BY sale_date) AS cumulative_sales
FROM sales
ORDER BY sale_date, product_id;

这个查询会返回每一行的销售额以及截止到当天的累积销售额。ORDER BY sale_date 指定了计算累积总和的顺序。 默认的frame子句是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这意味着窗口包括从分区的第一行到当前行的所有行。

sale_date product_id sales_amount cumulative_sales
2023-01-01 1 100.00 100.00
2023-01-01 2 150.00 250.00
2023-01-02 1 120.00 370.00
2023-01-02 2 180.00 550.00
2023-01-03 1 110.00 660.00
2023-01-03 2 200.00 860.00
2023-01-04 1 130.00 990.00
2023-01-04 2 170.00 1160.00
2023-01-05 1 140.00 1300.00
2023-01-05 2 190.00 1490.00
2023-01-06 1 150.00 1640.00
2023-01-06 2 210.00 1850.00
2023-01-07 1 160.00 2010.00
2023-01-07 2 220.00 2230.00

如果我们想要按照产品计算累计销售额，可以加上 PARTITION BY 子句：

SELECT
  sale_date,
  product_id,
  sales_amount,
  SUM(sales_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS cumulative_sales_by_product
FROM sales
ORDER BY product_id, sale_date;
sale_date product_id sales_amount cumulative_sales_by_product
2023-01-01 1 100.00 100.00
2023-01-02 1 120.00 220.00
2023-01-03 1 110.00 330.00
2023-01-04 1 130.00 460.00
2023-01-05 1 140.00 600.00
2023-01-06 1 150.00 750.00
2023-01-07 1 160.00 910.00
2023-01-01 2 150.00 150.00
2023-01-02 2 180.00 330.00
2023-01-03 2 200.00 530.00
2023-01-04 2 170.00 700.00
2023-01-05 2 190.00 890.00
2023-01-06 2 210.00 1100.00
2023-01-07 2 220.00 1320.00

2.3 计算移动平均值

移动平均值可以帮助我们平滑时间序列数据，识别趋势。我们可以使用 AVG() 窗口函数结合 frame_clause 计算移动平均值。

SELECT
  sale_date,
  product_id,
  sales_amount,
  AVG(sales_amount) OVER (ORDER BY sale_date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average_3_days
FROM sales
ORDER BY sale_date, product_id;

在这个查询中，ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 定义了窗口的范围。 它包括当前行以及前两行。 AVG(sales_amount) 计算这个窗口内的销售额平均值，即3日移动平均值。 ORDER BY sale_date ASC 指定了计算移动平均值的顺序。

sale_date product_id sales_amount moving_average_3_days
2023-01-01 1 100.00 100.00
2023-01-01 2 150.00 125.00
2023-01-02 1 120.00 123.33
2023-01-02 2 180.00 150.00
2023-01-03 1 110.00 136.67
2023-01-03 2 200.00 163.33
2023-01-04 1 130.00 146.67
2023-01-04 2 170.00 166.67
2023-01-05 1 140.00 146.67
2023-01-05 2 190.00 166.67
2023-01-06 1 150.00 160.00
2023-01-06 2 210.00 183.33
2023-01-07 1 160.00 173.33
2023-01-07 2 220.00 196.67

我们也可以使用 RANGE 关键字代替 ROWS。 RANGE 基于 ORDER BY 列的值来定义窗口。 例如，如果我们想要计算过去3天（包括当天）的移动平均值，可以使用以下查询：

SELECT
  sale_date,
  product_id,
  sales_amount,
  AVG(sales_amount) OVER (ORDER BY sale_date ASC RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW) AS moving_average_3_days
FROM sales
ORDER BY sale_date, product_id;

注意，如果 ORDER BY 列不是唯一的， ROWS 和 RANGE 的行为可能会有所不同。 ROWS 基于物理行数，而 RANGE 基于 ORDER BY 列的值范围。

2.4 计算销售额的变化百分比

我们可以使用 LAG() 或 LEAD() 窗口函数计算销售额的变化百分比。 LAG() 函数可以访问分区中前一行的值，而 LEAD() 函数可以访问分区中后一行的值。

SELECT
  sale_date,
  product_id,
  sales_amount,
  LAG(sales_amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) AS previous_sales_amount,
  (sales_amount - LAG(sales_amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date)) / LAG(sales_amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) * 100 AS sales_change_percentage
FROM sales
ORDER BY product_id, sale_date;

在这个查询中，LAG(sales_amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) 返回每个产品前一天的销售额。 1 表示偏移量，即前一行。 0 表示默认值，如果前一行不存在（例如，第一行），则返回 0。 然后，我们使用这个值计算销售额的变化百分比。

sale_date product_id sales_amount previous_sales_amount sales_change_percentage
2023-01-01 1 100.00 0.00 NULL
2023-01-02 1 120.00 100.00 20.00
2023-01-03 1 110.00 120.00 -8.33
2023-01-04 1 130.00 110.00 18.18
2023-01-05 1 140.00 130.00 7.69
2023-01-06 1 150.00 140.00 7.14
2023-01-07 1 160.00 150.00 6.67
2023-01-01 2 150.00 0.00 NULL
2023-01-02 2 180.00 150.00 20.00
2023-01-03 2 200.00 180.00 11.11
2023-01-04 2 170.00 200.00 -15.00
2023-01-05 2 190.00 170.00 11.76
2023-01-06 2 210.00 190.00 10.53
2023-01-07 2 220.00 210.00 4.76

2.5 识别销售额最高的日期

我们可以使用 RANK() 或 DENSE_RANK() 窗口函数识别销售额最高的日期。

SELECT
  sale_date,
  daily_total_sales,
  RANK() OVER (ORDER BY daily_total_sales DESC) AS sales_rank
FROM (
  SELECT
    sale_date,
    SUM(sales_amount) AS daily_total_sales
  FROM sales
  GROUP BY sale_date
) AS daily_sales
ORDER BY sales_rank;

这个查询首先计算每日总销售额，然后使用 RANK() 函数根据销售额对日期进行排名。 RANK() 函数会为具有相同销售额的日期分配相同的排名，并跳过后续排名。 例如，如果有两个日期的销售额相同且排名第一，则下一个日期的排名将是第三名。 DENSE_RANK() 函数类似，但它不会跳过排名。 在这个例子中，如果使用 DENSE_RANK()，下一个日期的排名将是第二名。

sale_date daily_total_sales sales_rank
2023-01-07 380.00 1
2023-01-06 360.00 2
2023-01-05 330.00 3
2023-01-03 310.00 4
2023-01-02 300.00 5
2023-01-04 300.00 5
2023-01-01 250.00 7

3. 窗口函数的性能考虑

虽然窗口函数非常强大，但在处理大型数据集时需要注意性能。

  • 索引: 确保 PARTITION BY 和 ORDER BY 子句中使用的列上有索引。 这可以显著提高查询性能。
  • 避免不必要的计算: 尽量避免在窗口函数中执行复杂的计算。 如果可能，先计算出结果，然后再在窗口函数中使用。
  • 数据类型: 确保窗口函数中使用的数据类型是正确的。 例如，如果要计算平均值，请确保使用数值类型。
  • frame_clause: 合理选择 frame_clause。 过大的窗口范围可能会导致性能下降。
  • MySQL 版本: 确保使用 MySQL 8.0 及以上版本，因为早期的版本可能不支持某些窗口函数或性能较差。

4. 一些高级技巧

  • 结合子查询: 可以将窗口函数与子查询结合使用，以执行更复杂的分析。 例如，可以先使用子查询计算每日总销售额，然后再使用窗口函数计算移动平均值。
  • 多个窗口函数: 可以在一个查询中使用多个窗口函数。 这可以让你同时执行多个分析操作。
  • 动态窗口: 虽然 frame_clause 通常是静态的，但可以使用用户定义的变量或存储过程来实现动态窗口。 这允许你根据不同的条件动态调整窗口范围。

5. 练习题

  1. 计算每个产品的7日移动平均销售额。
  2. 找到每个产品销售额增长最快的3天。
  3. 计算每个产品每个月的总销售额，并找到每个月销售额最高的产品的ID。

总结：窗口函数简化了时间序列分析

总而言之，MySQL 的窗口函数为时间序列分析提供了强大的工具。 它们可以简化复杂的查询，提高效率，并让你在不丢失原始数据的详细信息的情况下执行各种分析操作。 通过掌握窗口函数，你可以更好地理解和利用时间序列数据。

希望今天的讲解对你有所帮助，谢谢大家！

发表回复

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