MySQL 窗口函数
MySQL窗口函数是一种强大的工具,允许你在查询结果中执行复杂的计算,而无需使用子查询或复杂的连接操作。窗口函数特别适用于需要在结果集的每一行上执行聚合操作(如排名、累计和移动平均)的场景。
什么是窗口函数?
窗口函数是一种特殊的SQL函数,它可以在查询结果的每一行上执行计算,同时保留原始行的上下文。与普通聚合函数(如SUM
、AVG
)不同,窗口函数不会将多行合并为一行,而是为每一行生成一个独立的结果。
窗口函数的基本语法如下:
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
:定义窗口的范围,通常用于计算移动平均或累计和。
常见的窗口函数
MySQL支持多种窗口函数,以下是一些常见的窗口函数及其用途:
- 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 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_sales
FROM
sales;
输出:
salesperson | sale_date | amount | cumulative_sales |
---|---|---|---|
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:计算每个销售员的月度排名
假设我们需要为每个销售员在每个月的销售额进行排名,可以使用RANK()
函数:
sql
SELECT
salesperson,
sale_date,
amount,
RANK() OVER (PARTITION BY salesperson, DATE_FORMAT(sale_date, '%Y-%m') ORDER BY amount DESC) AS monthly_rank
FROM
sales;
场景2:计算移动平均
假设我们需要计算每个销售员的3天移动平均销售额,可以使用AVG()
函数结合frame_clause
:
sql
SELECT
salesperson,
sale_date,
amount,
AVG(amount) OVER (PARTITION BY salesperson ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM
sales;
总结
MySQL窗口函数为数据分析提供了强大的工具,允许你在不改变原始数据的情况下执行复杂的计算。通过PARTITION BY
、ORDER BY
和frame_clause
,你可以灵活地定义窗口的范围和排序方式,从而实现各种复杂的分析需求。
提示
建议初学者通过实际案例练习窗口函数的使用,逐步掌握其语法和应用场景。
附加资源
练习
- 使用
DENSE_RANK()
函数为sales
表中的每个销售员计算销售额的密集排名。 - 使用
LEAD()
函数获取每个销售员的下一次销售日期。 - 尝试使用
LAG()
函数计算每个销售员的销售额与前一天的差异。
通过练习,你将更好地理解窗口函数的强大功能及其在实际应用中的价值。