MySQL高级函数之：`AVG()`：其作为窗口函数在滚动平均值计算中的应用。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL高级函数之：`AVG()` 窗口函数在滚动平均值计算中的应用

大家好，今天我们来深入探讨MySQL中AVG()函数作为窗口函数在滚动平均值计算中的应用。AVG()函数大家都很熟悉，用于计算平均值。但是，当它与窗口函数结合使用时，就能实现更强大的功能，例如计算滚动平均值。滚动平均值在数据分析中非常有用，可以平滑数据波动，突出趋势，应用于股票价格分析、销售额分析等多种场景。

1. 理解窗口函数

在深入AVG()窗口函数之前，我们需要先理解什么是窗口函数。简单来说，窗口函数允许你对数据集的特定“窗口”进行计算，而这个窗口是与当前行相关的行的集合。窗口函数不会像GROUP BY那样合并行，而是为每一行返回一个计算结果，基于该行所属的窗口。

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

function_name() OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3, column4, ...]
    [ROWS | RANGE BETWEEN frame_start AND frame_end]
)

function_name(): 这是你要使用的窗口函数，例如AVG(), SUM(), RANK()等。
OVER(): 这个关键字表示这是一个窗口函数。
PARTITION BY: 将数据集分成多个分区。窗口函数将在每个分区内独立计算。如果省略，则整个数据集被视为一个分区。
ORDER BY: 定义每个分区内行的顺序。这对于计算滚动平均值至关重要。
ROWS | RANGE BETWEEN frame_start AND frame_end: 定义窗口的范围，即哪些行包含在当前行的窗口中。这被称为窗口帧（window frame）。

2. `AVG()` 作为窗口函数的基本应用

首先，我们创建一个示例表 sales_data，用于存储每日的销售额：

CREATE TABLE sales_data (
    sale_date DATE,
    sales_amount DECIMAL(10, 2)
);

INSERT INTO sales_data (sale_date, sales_amount) VALUES
('2023-01-01', 100.00),
('2023-01-02', 120.00),
('2023-01-03', 110.00),
('2023-01-04', 130.00),
('2023-01-05', 140.00),
('2023-01-06', 150.00),
('2023-01-07', 160.00),
('2023-01-08', 170.00),
('2023-01-09', 180.00),
('2023-01-10', 190.00);

现在，我们可以使用AVG()窗口函数计算每个销售日的平均销售额，但不使用任何PARTITION BY或窗口帧：

SELECT
    sale_date,
    sales_amount,
    AVG(sales_amount) OVER () AS overall_average
FROM
    sales_data;

在这个例子中，AVG(sales_amount) OVER () 计算的是整个数据集的平均销售额，并为每一行返回相同的结果。这并不是我们想要的滚动平均值，但它展示了AVG()窗口函数的基本用法。

3. 计算滚动平均值：窗口帧的定义

要计算滚动平均值，我们需要定义一个窗口帧，指定哪些行包含在当前行的窗口中。我们使用ROWS BETWEEN子句来定义窗口帧。常用的窗口帧定义包括：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 窗口从分区的第一行开始，直到当前行。
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 窗口从当前行开始，直到分区的最后一行。
ROWS BETWEEN n PRECEDING AND CURRENT ROW: 窗口包含当前行和之前的n行。
ROWS BETWEEN CURRENT ROW AND n FOLLOWING: 窗口包含当前行和之后的n行。
ROWS BETWEEN n PRECEDING AND n FOLLOWING: 窗口包含当前行以及之前和之后的n行。

例如，要计算3天的滚动平均值，我们可以使用以下查询：

SELECT
    sale_date,
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_average_3_days
FROM
    sales_data;

在这个查询中，ORDER BY sale_date 指定了行的顺序，ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 定义了窗口帧，包含当前行和之前的两行。因此，对于每一行，AVG()函数将计算该行及其之前两行的平均销售额。

sale_date	sales_amount	rolling_average_3_days
2023-01-01	100.00	100.00
2023-01-02	120.00	110.00
2023-01-03	110.00	110.00
2023-01-04	130.00	120.00
2023-01-05	140.00	126.67
2023-01-06	150.00	140.00
2023-01-07	160.00	150.00
2023-01-08	170.00	160.00
2023-01-09	180.00	170.00
2023-01-10	190.00	180.00

可以看到，前两行的滚动平均值由于数据不足3天，所以计算的是现有数据的平均值。

4. `RANGE` 窗口帧

除了ROWS，我们还可以使用RANGE来定义窗口帧。RANGE是基于ORDER BY列的值的差异来定义窗口的。例如：

SELECT
    sale_date,
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW) AS rolling_average_range
FROM
    sales_data;

在这个例子中，RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW 定义了一个窗口，包含了sale_date在当前行之前2天内的所有行。需要注意的是，RANGE 只能用于数值类型或日期类型的ORDER BY 列。

RANGE 和 ROWS 的区别在于：

ROWS 是基于行数来定义窗口的，无论行之间值的差异如何。
RANGE 是基于值的差异来定义窗口的，它会将所有在指定范围内的行都包含在窗口中。

在sales_data表中，由于每天都有数据，RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW 的结果与 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 的结果基本相同。但是，如果数据中有缺失的日期，RANGE 和 ROWS 的结果就会有所不同。

5. `PARTITION BY` 的应用

PARTITION BY 子句可以将数据集分成多个分区，并在每个分区内独立计算滚动平均值。例如，如果我们有一个包含多个产品销售数据的表，我们可以使用PARTITION BY 来计算每个产品的滚动平均销售额。

首先，我们创建一个示例表 product_sales：

CREATE TABLE product_sales (
    product_id INT,
    sale_date DATE,
    sales_amount DECIMAL(10, 2)
);

INSERT INTO product_sales (product_id, sale_date, sales_amount) VALUES
(1, '2023-01-01', 100.00),
(1, '2023-01-02', 120.00),
(1, '2023-01-03', 110.00),
(2, '2023-01-01', 80.00),
(2, '2023-01-02', 90.00),
(2, '2023-01-03', 100.00),
(1, '2023-01-04', 130.00),
(1, '2023-01-05', 140.00),
(2, '2023-01-04', 110.00),
(2, '2023-01-05', 120.00);

现在，我们可以使用以下查询来计算每个产品的3天滚动平均销售额：

SELECT
    product_id,
    sale_date,
    sales_amount,
    AVG(sales_amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_average_3_days
FROM
    product_sales;

在这个查询中，PARTITION BY product_id 将数据分成两个分区，每个分区对应一个产品。ORDER BY sale_date 指定了每个分区内行的顺序，ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 定义了窗口帧。因此，对于每一行，AVG()函数将计算该产品在该行及其之前两行的平均销售额。

product_id	sale_date	sales_amount	rolling_average_3_days
1	2023-01-01	100.00	100.00
1	2023-01-02	120.00	110.00
1	2023-01-03	110.00	110.00
1	2023-01-04	130.00	120.00
1	2023-01-05	140.00	126.67
2	2023-01-01	80.00	80.00
2	2023-01-02	90.00	85.00
2	2023-01-03	100.00	90.00
2	2023-01-04	110.00	100.00
2	2023-01-05	120.00	110.00

6. 处理缺失数据

在实际应用中，数据可能存在缺失。例如，sales_data 表中可能缺少某些日期的销售数据。在这种情况下，滚动平均值的计算可能会受到影响。

一种处理缺失数据的方法是使用IFNULL()函数或COALESCE()函数将缺失值替换为0。但是，这种方法可能会导致滚动平均值失真，尤其是在缺失数据较多的情况下。

另一种更精确的方法是使用子查询或连接来填充缺失的数据，然后再计算滚动平均值。这种方法可以确保滚动平均值的准确性，但实现起来可能更复杂。

例如，我们可以创建一个包含所有日期的表 date_series，然后使用 LEFT JOIN 将 sales_data 表与 date_series 表连接起来，并使用 IFNULL() 函数将缺失的销售额替换为0：

-- 创建一个包含所有日期的表
CREATE TABLE date_series (
    sale_date DATE PRIMARY KEY
);

-- 填充日期数据 (示例，实际应用中需要根据实际情况生成日期序列)
INSERT INTO date_series (sale_date) VALUES
('2023-01-01'), ('2023-01-02'), ('2023-01-03'), ('2023-01-04'), ('2023-01-05'),
('2023-01-06'), ('2023-01-07'), ('2023-01-08'), ('2023-01-09'), ('2023-01-10');

-- 使用 LEFT JOIN 和 IFNULL() 填充缺失数据
SELECT
    ds.sale_date,
    IFNULL(sd.sales_amount, 0) AS sales_amount,
    AVG(IFNULL(sd.sales_amount, 0)) OVER (ORDER BY ds.sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_average_3_days
FROM
    date_series ds
LEFT JOIN
    sales_data sd ON ds.sale_date = sd.sale_date;

这个查询首先将 sales_data 表与 date_series 表连接起来，确保所有日期都包含在结果中。然后，使用 IFNULL(sd.sales_amount, 0) 将缺失的销售额替换为0。最后，使用 AVG() 窗口函数计算滚动平均值。

注意：实际应用中，生成 date_series 表需要根据实际情况使用更灵活的方法，例如使用存储过程或函数。

7. 性能考虑

当处理大量数据时，窗口函数的性能可能会成为一个问题。以下是一些可以提高窗口函数性能的建议：

索引: 确保 ORDER BY 列上有索引，这可以加快排序的速度。
数据类型: 选择合适的数据类型，避免使用过大的数据类型。
避免不必要的计算: 只计算需要的滚动平均值，避免计算不必要的窗口帧。
分区: 合理使用 PARTITION BY 子句，可以将数据分成更小的分区，从而提高计算效率。
查询优化器: MySQL 查询优化器会自动优化查询，但在某些情况下，可以手动优化查询，例如使用 FORCE INDEX 提示。

8. 总结

我们学习了如何在MySQL中使用AVG()函数作为窗口函数来计算滚动平均值。我们探讨了窗口函数的基本语法，窗口帧的定义（ROWS和RANGE），PARTITION BY 的应用，以及处理缺失数据和性能优化的一些方法。这些技术可以帮助你更好地分析数据，发现趋势，并做出更明智的决策。掌握这些技巧，可以灵活地在各种数据分析场景中使用窗口函数。

9. 其他窗口函数

除了 AVG()，MySQL 还提供了许多其他的窗口函数，例如：

SUM()：计算窗口内的总和。
MIN()：计算窗口内的最小值。
MAX()：计算窗口内的最大值。
COUNT()：计算窗口内的行数。
RANK()：计算窗口内行的排名。
DENSE_RANK()：计算窗口内行的密集排名。
ROW_NUMBER()：为窗口内的每一行分配一个唯一的行号。
LAG()：访问窗口内前一行的值。
LEAD()：访问窗口内后一行的值。

这些窗口函数可以与 AVG() 函数结合使用，实现更复杂的数据分析功能。

10. 实际应用场景

滚动平均值在实际应用中有很多用途，以下是一些常见的场景：

股票价格分析: 计算股票价格的滚动平均值可以平滑价格波动，帮助投资者识别趋势。
销售额分析: 计算销售额的滚动平均值可以识别销售趋势，帮助企业制定营销策略。
网站流量分析: 计算网站流量的滚动平均值可以识别流量趋势，帮助网站优化内容和推广策略。
传感器数据分析: 计算传感器数据的滚动平均值可以平滑噪声，识别异常值。
财务数据分析: 计算财务数据的滚动平均值可以识别财务趋势，帮助企业制定财务决策。

11. 灵活运用窗口函数

窗口函数是一个强大的工具，可以帮助你更好地分析数据。掌握窗口函数的基本语法和常用函数，可以灵活地在各种数据分析场景中使用窗口函数。通过不同的窗口帧和分区方式，可以实现各种复杂的数据分析需求。

MySQL高级函数之：AVG() 窗口函数在滚动平均值计算中的应用