跳到主要内容

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

  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 BY、ORDER BY和frame_clause，你可以灵活地定义窗口的范围和排序方式，从而实现各种复杂的分析需求。

提示

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

附加资源​

练习​

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

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