利用MySQL窗口函数实现复杂OLAP操作：移动平均与排名
大家好，今天我们来深入探讨如何利用MySQL的窗口函数进行复杂的OLAP（Online Analytical Processing）操作，重点讲解移动平均和排名这两个非常实用的场景。窗口函数是MySQL 8.0引入的一项强大功能，它允许我们在查询结果集的“窗口”范围内执行聚合、排名和其他计算，而无需使用子查询或临时表，大大简化了复杂分析SQL的编写。
一、窗口函数的基本概念
窗口函数的核心在于OVER()子句。OVER()子句定义了窗口的范围和排序方式，它决定了函数如何对结果集进行分组和计算。
基本语法如下：
function_name(arguments) OVER (
[PARTITION BY column1, column2, ...]
[ORDER BY column3 [ASC | DESC], ...]
[frame_clause]
)function_name(arguments): 这是你要使用的窗口函数，例如AVG(),SUM(),ROW_NUMBER(),RANK()等。OVER(...): 这是窗口函数的关键子句。PARTITION BY column1, column2, ...: 将结果集按照指定的列进行分区。每个分区都会独立计算窗口函数。类似于GROUP BY，但PARTITION BY不会折叠行。ORDER BY column3 [ASC | DESC], ...: 在每个分区内，按照指定的列进行排序。窗口函数会根据排序的顺序进行计算。frame_clause: 定义窗口的框架，即窗口的大小。如果没有frame_clause，默认的框架是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
二、移动平均
移动平均是一种常用的时间序列分析方法，用于平滑数据并突出趋势。利用窗口函数，我们可以轻松计算移动平均。
2.1. 简单移动平均 (SMA)
简单移动平均是指在指定窗口内，计算值的平均值。
假设我们有一个销售数据表 sales，包含 sale_date (日期) 和 amount (销售额) 两个字段。
CREATE TABLE sales (
sale_date DATE NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO sales (sale_date, amount) VALUES
('2023-01-01', 100.00),
('2023-01-02', 120.00),
('2023-01-03', 150.00),
('2023-01-04', 130.00),
('2023-01-05', 140.00),
('2023-01-06', 160.00),
('2023-01-07', 180.00),
('2023-01-08', 170.00),
('2023-01-09', 190.00),
('2023-01-10', 200.00);要计算一个3天的简单移动平均，我们可以使用以下SQL：
SELECT
sale_date,
amount,
AVG(amount) OVER (ORDER BY sale_date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM
sales;AVG(amount) OVER(...): 计算amount的平均值。ORDER BY sale_date ASC: 按照销售日期升序排序。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: 定义窗口框架。2 PRECEDING表示当前行之前的两行，CURRENT ROW表示当前行。因此，窗口包含当前行和之前两行的总共三行。ROWS关键字表示基于行数定义窗口。
查询结果如下：
| sale_date | amount | moving_average |
|---|---|---|
| 2023-01-01 | 100.00 | 100.00 |
| 2023-01-02 | 120.00 | 110.00 |
| 2023-01-03 | 150.00 | 123.33 |
| 2023-01-04 | 130.00 | 133.33 |
| 2023-01-05 | 140.00 | 140.00 |
| 2023-01-06 | 160.00 | 143.33 |
| 2023-01-07 | 180.00 | 160.00 |
| 2023-01-08 | 170.00 | 170.00 |
| 2023-01-09 | 190.00 | 180.00 |
| 2023-01-10 | 200.00 | 186.67 |
2.2. 指定未来窗口的移动平均
我们可以使用 FOLLOWING 关键字来指定未来窗口。例如，计算当前行和未来两行的平均值：
SELECT
sale_date,
amount,
AVG(amount) OVER (ORDER BY sale_date ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS moving_average
FROM
sales;2.3. 对特定范围的移动平均
可以使用 RANGE 关键字来基于值的范围定义窗口。例如，计算日期范围内（例如前后两天）的平均值。需要注意的是，RANGE 只能与具有数值或日期/时间类型的 ORDER BY 列一起使用。
SELECT
sale_date,
amount,
AVG(amount) OVER (ORDER BY sale_date ASC RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND INTERVAL 2 DAY FOLLOWING) AS moving_average
FROM
sales;2.4. 考虑缺失值的移动平均
在实际数据中，可能存在缺失值。如果窗口内存在缺失值，AVG() 函数会自动忽略它们。 如果希望更精细地处理缺失值，可能需要结合 CASE 语句进行判断和处理。
三、排名
窗口函数提供了多种排名函数，可以根据指定的排序规则对结果集进行排名。
3.1. ROW_NUMBER()
ROW_NUMBER() 函数为结果集中的每一行分配一个唯一的序号，从1开始。
SELECT
sale_date,
amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num
FROM
sales;这条SQL会按照销售额降序排列，并为每一行分配一个序号。 即使销售额相同，ROW_NUMBER() 也会分配不同的序号。
查询结果如下：
| sale_date | amount | row_num |
|---|---|---|
| 2023-01-10 | 200.00 | 1 |
| 2023-01-09 | 190.00 | 2 |
| 2023-01-07 | 180.00 | 3 |
| 2023-01-08 | 170.00 | 4 |
| 2023-01-06 | 160.00 | 5 |
| 2023-01-03 | 150.00 | 6 |
| 2023-01-05 | 140.00 | 7 |
| 2023-01-04 | 130.00 | 8 |
| 2023-01-02 | 120.00 | 9 |
| 2023-01-01 | 100.00 | 10 |
3.2. RANK()
RANK() 函数为结果集中的每一行分配一个排名，排名基于指定的排序规则。如果存在相同的值，则它们会获得相同的排名，并且下一个排名会被跳过。
-- 插入一些重复数据
INSERT INTO sales (sale_date, amount) VALUES
('2023-01-11', 200.00),
('2023-01-12', 190.00);
SELECT
sale_date,
amount,
RANK() OVER (ORDER BY amount DESC) AS ranking
FROM
sales;查询结果如下：
| sale_date | amount | ranking |
|---|---|---|
| 2023-01-10 | 200.00 | 1 |
| 2023-01-11 | 200.00 | 1 |
| 2023-01-09 | 190.00 | 3 |
| 2023-01-12 | 190.00 | 3 |
| 2023-01-07 | 180.00 | 5 |
| 2023-01-08 | 170.00 | 6 |
| 2023-01-06 | 160.00 | 7 |
| 2023-01-03 | 150.00 | 8 |
| 2023-01-05 | 140.00 | 9 |
| 2023-01-04 | 130.00 | 10 |
| 2023-01-02 | 120.00 | 11 |
| 2023-01-01 | 100.00 | 12 |
注意，由于有两个销售额为200.00的行，它们都获得了排名1，因此下一个排名是3，跳过了2。
3.3. DENSE_RANK()
DENSE_RANK() 函数与 RANK() 类似，但它不会跳过排名。如果存在相同的值，它们会获得相同的排名，并且下一个排名是紧随其后的。
SELECT
sale_date,
amount,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_ranking
FROM
sales;查询结果如下：
| sale_date | amount | dense_ranking |
|---|---|---|
| 2023-01-10 | 200.00 | 1 |
| 2023-01-11 | 200.00 | 1 |
| 2023-01-09 | 190.00 | 2 |
| 2023-01-12 | 190.00 | 2 |
| 2023-01-07 | 180.00 | 3 |
| 2023-01-08 | 170.00 | 4 |
| 2023-01-06 | 160.00 | 5 |
| 2023-01-03 | 150.00 | 6 |
| 2023-01-05 | 140.00 | 7 |
| 2023-01-04 | 130.00 | 8 |
| 2023-01-02 | 120.00 | 9 |
| 2023-01-01 | 100.00 | 10 |
由于有两个销售额为200.00的行，它们都获得了排名1，因此下一个排名是2，没有跳过。
3.4. NTILE(n)
NTILE(n) 函数将结果集划分为 n 个桶（bucket），并为每一行分配一个桶号。
SELECT
sale_date,
amount,
NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM
sales;这条SQL会将销售数据按照销售额降序排列，并将其划分为4个四分位数，每一行都会被分配到1到4之间的一个桶号。
查询结果如下：
| sale_date | amount | quartile |
|---|---|---|
| 2023-01-10 | 200.00 | 1 |
| 2023-01-11 | 200.00 | 1 |
| 2023-01-09 | 190.00 | 1 |
| 2023-01-12 | 190.00 | 1 |
| 2023-01-07 | 180.00 | 2 |
| 2023-01-08 | 170.00 | 2 |
| 2023-01-06 | 160.00 | 3 |
| 2023-01-03 | 150.00 | 3 |
| 2023-01-05 | 140.00 | 3 |
| 2023-01-04 | 130.00 | 4 |
| 2023-01-02 | 120.00 | 4 |
| 2023-01-01 | 100.00 | 4 |
四、结合PARTITION BY子句
可以将 PARTITION BY 子句与排名函数结合使用，在每个分区内独立进行排名。例如，假设我们有一个包含 category (产品类别) 和 amount (销售额) 的表 product_sales：
CREATE TABLE product_sales (
category VARCHAR(50) NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO product_sales (category, sale_date, amount) VALUES
('A', '2023-01-01', 100.00),
('A', '2023-01-02', 120.00),
('A', '2023-01-03', 150.00),
('B', '2023-01-01', 80.00),
('B', '2023-01-02', 90.00),
('B', '2023-01-03', 110.00);要计算每个类别中销售额的排名，可以使用以下SQL：
SELECT
category,
sale_date,
amount,
RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS category_ranking
FROM
product_sales;PARTITION BY category: 按照产品类别进行分区。ORDER BY amount DESC: 在每个类别内，按照销售额降序排列。
查询结果如下：
| category | sale_date | amount | category_ranking |
|---|---|---|---|
| A | 2023-01-03 | 150.00 | 1 |
| A | 2023-01-02 | 120.00 | 2 |
| A | 2023-01-01 | 100.00 | 3 |
| B | 2023-01-03 | 110.00 | 1 |
| B | 2023-01-02 | 90.00 | 2 |
| B | 2023-01-01 | 80.00 | 3 |
五、更复杂的OLAP场景
窗口函数不仅可以用于简单的移动平均和排名，还可以用于更复杂的OLAP场景，例如：
- 累计总和 (Cumulative Sum)：计算从第一行到当前行的总和。
- 百分比排名 (Percentile Rank)：计算每一行在结果集中的百分比位置。
- 滞后/超前 (Lag/Lead)：访问结果集中前一行或后一行的值。
- 首行/末行 (First_Value/Last_Value)：访问窗口中的第一行或最后一行的值。
5.1. 累计总和
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date ASC) AS cumulative_sum
FROM
sales;5.2. 百分比排名
SELECT
sale_date,
amount,
PERCENT_RANK() OVER (ORDER BY amount ASC) AS percentile_rank
FROM
sales;5.3. 滞后/超前
SELECT
sale_date,
amount,
LAG(amount, 1, 0) OVER (ORDER BY sale_date ASC) AS previous_amount, -- 获取前一天的销售额，如果不存在则返回0
LEAD(amount, 1, 0) OVER (ORDER BY sale_date ASC) AS next_amount -- 获取后一天的销售额，如果不存在则返回0
FROM
sales;5.4. 首行/末行
SELECT
category,
sale_date,
amount,
FIRST_VALUE(amount) OVER (PARTITION BY category ORDER BY sale_date ASC) AS first_sale_amount, -- 获取每个类别第一次销售的金额
LAST_VALUE(amount) OVER (PARTITION BY category ORDER BY sale_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_amount -- 获取每个类别最后一次销售的金额
FROM
product_sales;六、窗口函数性能考量
虽然窗口函数功能强大，但在处理大型数据集时，需要注意性能问题。
- 索引: 确保
ORDER BY和PARTITION BY子句中使用的列有适当的索引，可以显著提高查询性能。 - 避免不必要的排序: 如果不需要排序，尽量避免使用
ORDER BY子句，因为排序会消耗大量资源。 - 窗口大小: 大的窗口大小会增加计算量，影响性能。 尽量选择合适的窗口大小。
- 物化视图: 对于频繁使用的窗口函数查询，可以考虑创建物化视图来预先计算结果，提高查询速度。
七、实际应用案例
- 电商: 计算用户购买频率、复购率、客单价等指标。
- 金融: 计算股票的移动平均、波动率、收益率等指标。
- 电信: 分析用户通话时长、流量使用情况等。
- 广告: 计算广告点击率、转化率等。
八、总结
掌握窗口函数是进行复杂数据分析的关键。利用窗口函数，我们可以轻松实现移动平均、排名、累计总和等各种OLAP操作，而无需编写复杂的子查询或存储过程。 通过PARTITION BY实现分组计算，并通过ORDER BY和frame_clause来定义计算的窗口范围，最终辅助我们更高效的进行数据分析。
希望今天的讲解能够帮助大家更好地理解和使用MySQL的窗口函数。 谢谢大家！