MySQL8-窗口函数

为什么要使用窗口函数

MySQL 窗口函数是 8.0版本之后才推出的新特性，它相比于聚合函数，会有更好的性能（可以避免写一些子查询的sql语句，改为使用窗口函数、减少了扫描数据库的次数）。且语法简单，适合更复杂的查询。

从聚合函数说起

假设有一个销售额表（sales），他有字段（id，name，year，sale）。

-- 创建销售额表
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    year INT,
    sale DECIMAL(10,2)
);

-- 插入示例数据
INSERT INTO sales (name, year, sale) VALUES
('Alice', 2021, 5000.00),
('Bob',   2021, 7000.00),
('Cindy', 2021, 8000.00),
('Alice', 2022, 9000.00),
('Bob',   2022, 6000.00),
('Cindy', 2022, 12000.00),
('Alice', 2023, 11000.00),
('Bob',   2023, 9500.00),
('Cindy', 2023, 10500.00);

现有一个需求——查询每年的总销售额

-- 查询每年的总销售额
SELECT year, SUM(sale) AS total_sale
FROM sales
GROUP BY year;

可以发现，使用sum()函数和group by语句后，查询到的数据行数变少了。

查询前：

查询后：

那么使用聚合函数，查询结果会变成什么样呢？

SELECT 
    id,
    name,
    year,
    sale,
    SUM(sale) OVER (PARTITION BY year) AS total_sale
FROM sales;

会发现，不仅select中能查询到其余的所有字段，还会在查询结果多出一列（即SUM(sale) OVER (PARTITION BY year)），我们给他命名为total_sale

直观上来看，窗口函数能在不压缩列的情况下，为每行都增加该年的销售总额。

窗口函数具体语法

调用窗口函数语法如下：

window_function_name ( [expression] ) 
    OVER (
        [PARTITION BY expr_list]
        [ORDER BY order_list]
        [frame_clause]
    )

参数说明：

• window_function_name：窗口函数名称，如 SUM, AVG, ROW_NUMBER, RANK 等。

• expression：可选，用于计算的列或表达式。

• PARTITION BY：按指定字段对数据进行分区，窗口函数在每个分区内独立计算。

• ORDER BY：定义分区内行的计算顺序。部分函数（如排名类）必须使用。

• frame_clause：定义计算窗口的行范围。语法如下：

  ROWS | RANGE BETWEEN frame_start AND frame_end

  其中：

  • UNBOUNDED PRECEDING：从分区第一行开始
  • N PRECEDING：当前行前 N 行
  • CURRENT ROW：当前行
  • N FOLLOWING：当前行后 N 行
  • UNBOUNDED FOLLOWING：到分区最后一行

默认帧（当使用 ORDER BY 且未显式指定时）：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

示例：

SELECT 
    name,
    year,
    sale,
    SUM(sale) OVER (PARTITION BY year ORDER BY name) AS total_sale
FROM sales;

MySQL窗口函数列表

名称	描述
CUME_DIST	计算一组值中值的累积分布。
DENSE_RANK	根据 ORDER BY 子句为其分区中的每一行分配一个排名。 它为具有相同值的行分配相同的排名。 如果两行或更多行具有相同的等级，则排序值序列中将没有间隙。
FIRST_VALUE	返回指定表达式相对于窗口框架中第一行的值。
LAG	返回分区中当前行之前的第N行的值。 如果不存在前一行，则返回NULL。
LAST_VALUE	返回指定表达式相对于窗口框架中最后一行的值。
LEAD	返回分区中当前行之后的第N行的值。 如果不存在后续行，则返回NULL。
NTH_VALUE	返回窗口框架第N行的参数值
NTILE	将每个窗口分区的行分配到指定数量的已排名组中。
PERCENT_RANK	计算分区或结果集中行的百分位数
RANK	与 DENSE_RANK() 函数类似，只是当两行或更多行具有相同的排名时，排序值序列中存在间隙。
ROW_NUMBER	为其分区中的每一行分配一个连续整数

排序类：ROW_NUMBER, RANK, DENSE_RANK, NTILE

取值类：FIRST_VALUE, LAST_VALUE, NTH_VALUE, LAG, LEAD

统计类：SUM, AVG, COUNT, MAX, MIN, CUME_DIST, PERCENT_RANK

窗口函数实践

适合的场景有：Top N查询、累计百分比（贡献度分析）、计算移动平均（时间序列分析）、增长率计算、多维排序与全局排名、动态帧区间分析

用以下表和数据作为展示

CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    year INT,
    sale DECIMAL(10,2)
);

INSERT INTO sales (name, year, sale) VALUES
-- 2021
('Alice', 2021, 5000.00),
('Bob',   2021, 7000.00),
('Cindy', 2021, 8000.00),
('David', 2021, 6000.00),
('Ella',  2021, 9000.00),

-- 2022
('Alice', 2022, 9000.00),
('Bob',   2022, 6000.00),
('Cindy', 2022,12000.00),
('David', 2022, 9500.00),
('Ella',  2022,11000.00),

-- 2023
('Alice', 2023,11000.00),
('Bob',   2023, 9500.00),
('Cindy', 2023,10500.00),
('David', 2023,12500.00),
('Ella',  2023, 8000.00);

1. Top N查询

   需求：取出每年销售额前 2 名的销售员。

   SELECT *
   FROM (
       SELECT
           name,
           year,
           sale,
           DENSE_RANK() OVER (PARTITION BY year ORDER BY sale DESC) AS rnk
       FROM sales
   ) t
   WHERE t.rnk <= 2;

   窗口排名在每个年份分区内重新计算，通过外层筛选得到每年 Top N。

   

2. 累计百分比（贡献度分析）

   需求：统计每年各销售员销售额占比及其累计百分比。

   SELECT
       year,
       name,
       sale,
       ROUND(sale / SUM(sale) OVER (PARTITION BY year) * 100, 2) AS pct,
       ROUND(SUM(sale) OVER (PARTITION BY year ORDER BY sale DESC
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
             / SUM(sale) OVER (PARTITION BY year) * 100, 2) AS cum_pct
   FROM sales
   ORDER BY year, sale DESC;

   pct 表示每人销售额占当年比例。

   cum_pct 表示累计占比，可用于找出“贡献前 80% 的销售员”。如果加上where cum_pct < 80，就能知道。

   

3. 计算移动平均（时间序列分析）

   需求：计算每个销售员最近 2 年的移动平均销售额。

   也就是计算出销售员这两年平均的销售额，相比于只计算一年，可以剔除单年异常波动。

   （同理也可以改为近3年的、近3个月的）

   SELECT
       name,
       year,
       sale,
       ROUND(AVG(sale) OVER (
           PARTITION BY name ORDER BY year
           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg
   FROM sales
   ORDER BY name, year;

   ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 表示窗口为当前行及前一行，形成滑动平均。

   

4. 增长率计算

   需求：比较每个销售员当年销售额与上一年增长率。

   SELECT
       name,
       year,
       sale,
       LAG(sale) OVER (PARTITION BY name ORDER BY year) AS prev_sale,
       ROUND((sale - LAG(sale) OVER (PARTITION BY name ORDER BY year))
             / LAG(sale) OVER (PARTITION BY name ORDER BY year) * 100, 2) AS growth_rate
   FROM sales
   ORDER BY name, year;

   使用 LAG() 获取上一年销售额，计算百分比增长率。

   

5. 多维排序与全局排名

   需求：为所有销售数据按年份、销售额综合排序，显示全局名次与分区内名次。

   SELECT
       name,
       year,
       sale,
       RANK() OVER (ORDER BY sale DESC) AS global_rank,
       RANK() OVER (PARTITION BY year ORDER BY sale DESC) AS year_rank
   FROM sales
   ORDER BY global_rank;

   global_rank 给出全表排名。

   year_rank 给出每年分区内的排名。

   

6. 动态帧区间分析

   需求：查看每个销售员从当前年份开始到未来两年内的销售总额。

   SELECT
       name,
       year,
       sale,
       SUM(sale) OVER (
           PARTITION BY name ORDER BY year
           ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
       ) AS next_3_year_sales
   FROM sales
   ORDER BY name, year;

   窗口定义为当前行 + 后两行，适合做预测期累计或滚动规划。

   下面展示的数据虽然看上去next_3_year_sales数值越来越小、实际上只是因为示例数据没有2024、2025年的数据而已……