MySQL 8.0 窗口函数

介绍

MySQL 8.0 引入了窗口函数（Window Functions），这是一组强大的工具，允许你在查询结果集中执行复杂的计算。窗口函数与聚合函数类似，但它们不会将多行合并为一行，而是保留每一行的独立性，同时基于一组相关的行进行计算。

窗口函数特别适用于需要计算排名、累积和、移动平均等场景。它们可以帮助你在不改变原始数据的情况下，生成更丰富的分析结果。

窗口函数的基本语法

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

function_name (expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_clause]
)

• function_name：窗口函数的名称，例如 ROW_NUMBER()、RANK()、SUM() 等。
• expression：要计算的表达式。
• PARTITION BY：将数据分成多个分区，每个分区独立计算。
• ORDER BY：指定分区内的排序方式。
• frame_clause：定义窗口的框架，即计算的范围。

常见的窗口函数

以下是一些常见的窗口函数及其用途：

1. ROW_NUMBER()：为每一行分配一个唯一的序号。
2. RANK()：为每一行分配一个排名，相同值的行会得到相同的排名，后续排名会跳过。
3. DENSE_RANK()：与 RANK() 类似，但不会跳过后续排名。
4. SUM()：计算分区内的累积和。
5. AVG()：计算分区内的移动平均。
6. LEAD() 和 LAG()：访问当前行之前或之后的行的值。

代码示例

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

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

INSERT INTO sales (salesperson, sale_date, amount) VALUES
('Alice', '2023-01-01', 100.00),
('Bob', '2023-01-01', 200.00),
('Alice', '2023-01-02', 150.00),
('Bob', '2023-01-02', 250.00),
('Alice', '2023-01-03', 200.00),
('Bob', '2023-01-03', 300.00);

示例 1：使用 ROW_NUMBER() 分配序号

SELECT 
    salesperson, 
    sale_date, 
    amount,
    ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY sale_date) AS row_num
FROM 
    sales;

输出：

salesperson	sale_date	amount	row_num
Alice	2023-01-01	100.00	1
Alice	2023-01-02	150.00	2
Alice	2023-01-03	200.00	3
Bob	2023-01-01	200.00	1
Bob	2023-01-02	250.00	2
Bob	2023-01-03	300.00	3

示例 2：使用 SUM() 计算累积和

SELECT 
    salesperson, 
    sale_date, 
    amount,
    SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS cumulative_sum
FROM 
    sales;

输出：

salesperson	sale_date	amount	cumulative_sum
Alice	2023-01-01	100.00	100.00
Alice	2023-01-02	150.00	250.00
Alice	2023-01-03	200.00	450.00
Bob	2023-01-01	200.00	200.00
Bob	2023-01-02	250.00	450.00
Bob	2023-01-03	300.00	750.00

实际应用场景

场景 1：计算销售排名

假设你想知道每个销售员在每天的销售额排名：

SELECT 
    salesperson, 
    sale_date, 
    amount,
    RANK() OVER (PARTITION BY sale_date ORDER BY amount DESC) AS sales_rank
FROM 
    sales;

输出：

salesperson	sale_date	amount	sales_rank
Bob	2023-01-01	200.00	1
Alice	2023-01-01	100.00	2
Bob	2023-01-02	250.00	1
Alice	2023-01-02	150.00	2
Bob	2023-01-03	300.00	1
Alice	2023-01-03	200.00	2

场景 2：计算移动平均

假设你想计算每个销售员的销售额的 2 天移动平均：

SELECT 
    salesperson, 
    sale_date, 
    amount,
    AVG(amount) OVER (PARTITION BY salesperson ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales;

输出：

salesperson	sale_date	amount	moving_avg
Alice	2023-01-01	100.00	100.00
Alice	2023-01-02	150.00	125.00
Alice	2023-01-03	200.00	175.00
Bob	2023-01-01	200.00	200.00
Bob	2023-01-02	250.00	225.00
Bob	2023-01-03	300.00	275.00

总结

MySQL 8.0 的窗口函数为数据分析提供了强大的工具。通过使用窗口函数，你可以在不改变原始数据的情况下，生成复杂的分析结果。无论是计算排名、累积和，还是移动平均，窗口函数都能帮助你轻松实现。

提示

练习：尝试在自己的数据库中创建一个类似的表，并使用不同的窗口函数进行实验，以加深理解。

附加资源

• MySQL 8.0 官方文档 - 窗口函数
• SQL 窗口函数教程
• MySQL 窗口函数实战