MySQL函数：`SUM()`与`AVG()`在窗口函数中的累积求和与平均值计算。

MySQL 窗口函数：SUM() 与 AVG() 的累积求和与平均值计算

大家好，今天我们来深入探讨 MySQL 中窗口函数的功能，特别是 SUM() 和 AVG() 这两个函数在窗口函数上下文中的应用，着重讲解如何使用它们进行累积求和与平均值计算。窗口函数是 SQL 查询中一个非常强大的工具，能够让我们在不使用 GROUP BY 的情况下，对结果集中的数据进行聚合计算，并能访问与当前行相关的其他行的值。这对于处理需要进行比较、排名、累积计算等任务的数据分析场景非常有用。

1. 窗口函数的基本概念

在深入 SUM() 和 AVG() 之前，我们先回顾一下窗口函数的基本概念。

• 窗口函数与聚合函数的区别： 聚合函数如 SUM(), AVG(), COUNT(), MAX(), MIN() 等，会将多行数据聚合成单行结果。而窗口函数则不会减少结果集的行数，它会为结果集中的每一行都计算出一个值。

• OVER() 子句： 窗口函数的核心在于 OVER() 子句，它定义了窗口的范围和计算方式。OVER() 子句可以包含以下几个部分：

  • PARTITION BY： 将结果集划分为多个分区，窗口函数会在每个分区内独立计算。类似于 GROUP BY，但不会减少行数。
  • ORDER BY： 定义分区内数据的排序方式，这对于累积计算非常重要。
  • ROWS 或 RANGE： 定义窗口的范围，即当前行周围哪些行参与计算。

• 窗口框架（Window Frame）： ROWS 和 RANGE 子句用于定义窗口框架，它指定了哪些行包含在当前行的窗口中。

  • ROWS BETWEEN ... AND ...: 基于行数定义窗口。
  • RANGE BETWEEN ... AND ...: 基于排序字段的值定义窗口。

2. SUM() 函数在窗口函数中的应用：累积求和

SUM() 函数用于计算指定列的总和。在窗口函数中，它可以用于计算累积和，即每一行的值都是当前行及其之前所有行的总和。

示例数据：

假设我们有一个 sales 表，记录了每天的销售额。

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

INSERT INTO sales (sale_date, sale_amount) VALUES
('2023-01-01', 100.00),
('2023-01-02', 150.00),
('2023-01-03', 200.00),
('2023-01-04', 120.00),
('2023-01-05', 180.00);

累积求和的 SQL 查询：

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

结果：

sale_date	sale_amount	cumulative_sales
2023-01-01	100.00	100.00
2023-01-02	150.00	250.00
2023-01-03	200.00	450.00
2023-01-04	120.00	570.00
2023-01-05	180.00	750.00

解释：

• SUM(sale_amount) OVER (ORDER BY sale_date)：对 sale_amount 列进行求和，OVER() 子句指定了窗口的范围。
• ORDER BY sale_date：指定了窗口内数据的排序方式，即按 sale_date 升序排列。 由于没有使用ROWS 或 RANGE 关键字指定窗口框架，默认的窗口框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是从分区的第一行到当前行。

带 PARTITION BY 的累积求和：

如果我们希望按不同的类别进行累积求和，可以使用 PARTITION BY 子句。 假设我们有一个 orders 表，记录了不同客户的订单金额。

CREATE TABLE orders (
    customer_id INT,
    order_date DATE,
    order_amount DECIMAL(10, 2)
);

INSERT INTO orders (customer_id, order_date, order_amount) VALUES
(1, '2023-01-01', 50.00),
(1, '2023-01-02', 75.00),
(2, '2023-01-03', 100.00),
(2, '2023-01-04', 125.00),
(1, '2023-01-05', 60.00),
(2, '2023-01-06', 80.00);

SELECT
    customer_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_order_amount
FROM
    orders;

结果：

customer_id	order_date	order_amount	cumulative_order_amount
1	2023-01-01	50.00	50.00
1	2023-01-02	75.00	125.00
1	2023-01-05	60.00	185.00
2	2023-01-03	100.00	100.00
2	2023-01-04	125.00	225.00
2	2023-01-06	80.00	305.00

解释：

• PARTITION BY customer_id：将结果集按 customer_id 分区，每个客户的数据独立计算累积和。

使用 ROWS 定义窗口框架:

现在让我们使用 ROWS 关键字来更明确地定义窗口框架。 例如，我们可以计算过去三天（包括当天）的销售额总和。

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

结果：

sale_date	sale_amount	rolling_3_day_sales
2023-01-01	100.00	100.00
2023-01-02	150.00	250.00
2023-01-03	200.00	450.00
2023-01-04	120.00	470.00
2023-01-05	180.00	500.00

解释：

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：定义了窗口框架，包括当前行以及当前行之前的两行。 如果当前行之前的行数不足两行，则窗口会相应缩小。

使用 RANGE 定义窗口框架:

RANGE 关键字允许我们基于排序字段的值来定义窗口框架。 例如，如果我们想计算与当前日期相差不超过两天的销售额总和，可以使用 RANGE。

SELECT
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW) AS range_sales
FROM
    sales;

结果：

sale_date	sale_amount	range_sales
2023-01-01	100.00	100.00
2023-01-02	150.00	250.00
2023-01-03	200.00	450.00
2023-01-04	120.00	470.00
2023-01-05	180.00	500.00

在本例中，结果与使用 ROWS 的示例相同，因为日期是连续的。但如果日期不连续，RANGE 会根据日期值之间的差值来确定窗口范围。

3. AVG() 函数在窗口函数中的应用：累积平均值

AVG() 函数用于计算指定列的平均值。 与 SUM() 类似，在窗口函数中，它可以用于计算累积平均值，即每一行的值都是当前行及其之前所有行的平均值。

使用之前的 sales 表。

累积平均值的 SQL 查询：

SELECT
    sale_date,
    sale_amount,
    AVG(sale_amount) OVER (ORDER BY sale_date) AS cumulative_average_sales
FROM
    sales;

结果：

sale_date	sale_amount	cumulative_average_sales
2023-01-01	100.00	100.0000
2023-01-02	150.00	125.0000
2023-01-03	200.00	150.0000
2023-01-04	120.00	142.5000
2023-01-05	180.00	150.0000

解释：

• AVG(sale_amount) OVER (ORDER BY sale_date)：对 sale_amount 列进行求平均值，OVER() 子句指定了窗口的范围。
• ORDER BY sale_date：指定了窗口内数据的排序方式，即按 sale_date 升序排列。 窗口框架默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

带 PARTITION BY 的累积平均值：

使用之前的 orders 表。

SELECT
    customer_id,
    order_date,
    order_amount,
    AVG(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_average_order_amount
FROM
    orders;

结果：

customer_id	order_date	order_amount	cumulative_average_order_amount
1	2023-01-01	50.00	50.0000
1	2023-01-02	75.00	62.5000
1	2023-01-05	60.00	61.6667
2	2023-01-03	100.00	100.0000
2	2023-01-04	125.00	112.5000
2	2023-01-06	80.00	101.6667

解释：

• PARTITION BY customer_id：将结果集按 customer_id 分区，每个客户的数据独立计算累积平均值。

使用 ROWS 定义窗口框架:

计算过去三天（包括当天）的销售额平均值。

SELECT
    sale_date,
    sale_amount,
    AVG(sale_amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3_day_average_sales
FROM
    sales;

结果：

sale_date	sale_amount	rolling_3_day_average_sales
2023-01-01	100.00	100.0000
2023-01-02	150.00	125.0000
2023-01-03	200.00	150.0000
2023-01-04	120.00	156.6667
2023-01-05	180.00	166.6667

解释：

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：定义了窗口框架，包括当前行以及当前行之前的两行。

使用 RANGE 定义窗口框架:

计算与当前日期相差不超过两天的销售额平均值。

SELECT
    sale_date,
    sale_amount,
    AVG(sale_amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW) AS range_average_sales
FROM
    sales;

结果：

sale_date	sale_amount	range_average_sales
2023-01-01	100.00	100.0000
2023-01-02	150.00	125.0000
2023-01-03	200.00	150.0000
2023-01-04	120.00	156.6667
2023-01-05	180.00	166.6667

4. 实际应用场景

• 金融分析： 计算股票的移动平均线，评估风险和趋势。
• 销售分析： 计算产品的累积销售额，了解产品的销售增长情况。
• 网站分析： 计算用户的滚动平均访问时长，评估用户参与度。
• 性能监控： 计算服务器的 CPU 使用率的滚动平均值，检测性能瓶颈。

5. 注意事项

• ORDER BY 子句在窗口函数中非常重要，因为它决定了累积计算的顺序。
• PARTITION BY 子句可以将数据划分为多个分区，每个分区独立计算。
• ROWS 和 RANGE 子句用于定义窗口框架，可以根据行数或排序字段的值来指定窗口范围。
• 窗口函数的性能可能受到数据量和窗口大小的影响，需要根据实际情况进行优化。
• 并非所有数据库系统都支持所有窗口函数的功能，需要查阅相应的数据库文档。

6. 窗口函数让数据分析更高效

通过今天的讲解，我们了解了 SUM() 和 AVG() 函数在窗口函数中的应用，特别是如何进行累积求和与平均值计算。 掌握窗口函数能够帮助我们更有效地进行数据分析，解决各种实际问题。 灵活运用窗口函数能够极大地提升 SQL 查询的表达能力和分析效率。