MySQL 8.0 窗口函数
介绍
MySQL 8.0 引入了窗口函数(Window Functions),这是一组强大的工具,允许你在查询结果集中执行复杂的计算。窗口函数与聚合函数类似,但它们不会将多行合并为一行,而是保留每一行的独立性,同时基于一组相关的行进行计算。
窗口函数特别适用于需要计算排名、累积和、移动平均等场景。它们可以帮助你在不改变原始数据的情况下,生成更丰富的分析结果。
窗口函数的基本语法
窗口函数的基本语法如下:
sql
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
:定义窗口的框架,即计算的范围。
常见的窗口函数
以下是一些常见的窗口函数及其用途:
- ROW_NUMBER():为每一行分配一个唯一的序号。
- RANK():为每一行分配一个排名,相同值的行会得到相同的排名,后续排名会跳过。
- DENSE_RANK():与
RANK()
类似,但不会跳过后续排名。 - SUM():计算分区内的累积和。
- AVG():计算分区内的移动平均。
- LEAD() 和 LAG():访问当前行之前或之后的行的值。
代码示例
假设我们有一个 sales
表,记录了每个销售员的销售额:
sql
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()
分配序号
sql
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()
计算累积和
sql
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:计算销售排名
假设你想知道每个销售员在每天的销售额排名:
sql
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 天移动平均:
sql
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 的窗口函数为数据分析提供了强大的工具。通过使用窗口函数,你可以在不改变原始数据的情况下,生成复杂的分析结果。无论是计算排名、累积和,还是移动平均,窗口函数都能帮助你轻松实现。
提示
练习:尝试在自己的数据库中创建一个类似的表,并使用不同的窗口函数进行实验,以加深理解。