跳到主要内容

MySQL 窗口函数

MySQL窗口函数是一种强大的工具,允许你在查询结果中执行复杂的计算,而无需使用子查询或复杂的连接操作。窗口函数特别适用于需要在结果集的每一行上执行聚合操作(如排名、累计和移动平均)的场景。

什么是窗口函数?

窗口函数是一种特殊的SQL函数,它可以在查询结果的每一行上执行计算,同时保留原始行的上下文。与普通聚合函数(如SUMAVG)不同,窗口函数不会将多行合并为一行,而是为每一行生成一个独立的结果。

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

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支持多种窗口函数,以下是一些常见的窗口函数及其用途:

  1. ROW_NUMBER():为每一行分配一个唯一的序号。
  2. RANK():为每一行分配一个排名,相同值的行会获得相同的排名,后续排名会跳过。
  3. DENSE_RANK():与RANK()类似,但不会跳过后续排名。
  4. SUM():计算分区内的累计和。
  5. AVG():计算分区内的移动平均。
  6. 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;

输出:

salespersonsale_dateamountrow_num
Alice2023-01-01100.001
Alice2023-01-02150.002
Alice2023-01-03200.003
Bob2023-01-01200.001
Bob2023-01-02250.002
Bob2023-01-03300.003

示例2:使用SUM()计算每个销售员的累计销售额

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

输出:

salespersonsale_dateamountcumulative_sales
Alice2023-01-01100.00100.00
Alice2023-01-02150.00250.00
Alice2023-01-03200.00450.00
Bob2023-01-01200.00200.00
Bob2023-01-02250.00450.00
Bob2023-01-03300.00750.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 BYORDER BYframe_clause,你可以灵活地定义窗口的范围和排序方式,从而实现各种复杂的分析需求。

提示

建议初学者通过实际案例练习窗口函数的使用,逐步掌握其语法和应用场景。

附加资源

练习

  1. 使用DENSE_RANK()函数为sales表中的每个销售员计算销售额的密集排名。
  2. 使用LEAD()函数获取每个销售员的下一次销售日期。
  3. 尝试使用LAG()函数计算每个销售员的销售额与前一天的差异。

通过练习,你将更好地理解窗口函数的强大功能及其在实际应用中的价值。