按 Enter 键跳转到正文

Mysql窗口函数

发布于 2025年2月20日，作者 Leanku

Mysql窗口函数

一、什么是窗口函数？

窗口函数是一种在查询结果的"窗口"上执行计算的函数，它不会像常规聚合函数那样将多行合并为一行，而是为每一行返回一个值，同时保持原始行数不变。

基本语法

1<窗口函数>(<参数>) OVER (
2[PARTITION BY <分区表达式>]
3[ORDER BY <排序表达式> [ASC | DESC]]
4[ROWS/RANGE <窗口范围>]
5)
  • ‌<窗口函数>‌：可以是聚合函数（如SUM、AVG）或专用函数（如ROW_NUMBER、RANK）。‌‌
  • ‌OVER()‌：必需子句，定义窗口框架。‌‌
  • ‌PARTITION BY‌：可选，用于分组数据；若省略，窗口覆盖整个结果集。
  • ‌ORDER BY‌：可选，指定窗口内行的排序顺序。‌‌
  • ‌窗口范围‌：可选，默认ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。‌‌

二、 准备测试数据

 1-- 创建销售数据表
 2CREATE TABLE sales (
 3    sale_id INT PRIMARY KEY,
 4    salesperson VARCHAR(50),
 5    region VARCHAR(50),
 6    sale_date DATE,
 7    amount DECIMAL(10,2)
 8);
 9
10INSERT INTO sales VALUES
11(1, '张三', '北京', '2023-01-15', 5000),
12(2, '李四', '上海', '2023-01-16', 8000),
13(3, '王五', '北京', '2023-01-17', 6000),
14(4, '张三', '北京', '2023-02-01', 7000),
15(5, '李四', '上海', '2023-02-02', 9000),
16(6, '赵六', '广州', '2023-02-03', 4000),
17(7, '王五', '北京', '2023-02-04', 5500),
18(8, '张三', '北京', '2023-03-01', 8500);
19
20-- 创建员工薪资表
21CREATE TABLE employee_salary (
22    emp_id INT,
23    name VARCHAR(50),
24    department VARCHAR(50),
25    salary DECIMAL(10,2),
26    hire_date DATE
27);
28
29INSERT INTO employee_salary VALUES
30(1, '张三', '技术部', 15000, '2020-01-15'),
31(2, '李四', '技术部', 12000, '2021-03-20'),
32(3, '王五', '销售部', 8000, '2022-06-10'),
33(4, '赵六', '销售部', 7500, '2022-08-05'),
34(5, '钱七', '技术部', 13000, '2020-11-30'),
35(6, '孙八', '人事部', 9000, '2021-09-15'),
36(7, '周九', '技术部', 16000, '2019-05-20');

1. 排名函数

ROW_NUMBER()

1SELECT 
2    salesperson,
3    region,
4    amount,
5    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) as row_num
6FROM sales;

结果：为每个地区的销售按金额降序分配唯一序号

RANK() 和 DENSE_RANK()

1SELECT 
2    name,
3    department,
4    salary,
5    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank_pos,
6    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank_pos
7FROM employee_salary;

区别：

  • RANK(): 相同值相同排名，后续排名跳过 (1,2,2,4)

  • DENSE_RANK(): 相同值相同排名，后续排名不跳过 (1,2,2,3)

NTILE() - 数据分桶

1SELECT 
2    name,
3    salary,
4    NTILE(4) OVER (ORDER BY salary DESC) as quartile
5FROM employee_salary;

结果：将员工按薪资分为4个分组，用于四分位分析

2. 分布函数

PERCENT_RANK()

1SELECT 
2    name,
3    salary,
4    ROUND(PERCENT_RANK() OVER (ORDER BY salary) * 100, 2) as percentile
5FROM employee_salary;

结果：计算每个薪资在总体中的百分比排名 (0-1之间)

CUME_DIST()

1SELECT 
2    name,
3    salary,
4    ROUND(CUME_DIST() OVER (ORDER BY salary) * 100, 2) as cumulative_dist
5FROM employee_salary;

结果：计算累计分布（小于等于当前值的行数比例）

3. 前后行函数

LAG() 和 LEAD()

1SELECT 
2    salesperson,
3    sale_date,
4    amount,
5    LAG(amount, 1) OVER (PARTITION BY salesperson ORDER BY sale_date) as prev_amount,
6    LEAD(amount, 1) OVER (PARTITION BY salesperson ORDER BY sale_date) as next_amount,
7    amount - LAG(amount, 1) OVER (PARTITION BY salesperson ORDER BY sale_date) as growth
8FROM sales;

应用：计算环比增长、访问前后期数据

FIRST_VALUE() 和 LAST_VALUE()

1SELECT 
2    salesperson,
3    sale_date,
4    amount,
5    FIRST_VALUE(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) as first_sale,
6    LAST_VALUE(amount) OVER (PARTITION BY salesperson ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_sale
7FROM sales;

注意：LAST_VALUE需要指定正确的窗口框架

4. 聚合窗口函数

累计计算

1SELECT 
2    salesperson,
3    sale_date,
4    amount,
5    SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) as running_total,
6    AVG(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) as running_avg
7FROM sales;

移动平均

1SELECT 
2    sale_date,
3    amount,
4    AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_3,
5    AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7
6FROM sales;

5. 窗口框架详解

框架语法

1ROWS BETWEEN frame_start AND frame_end

常用框架范围

 1SELECT 
 2    salesperson,
 3    sale_date,
 4    amount,
 5    -- 从开始到当前行
 6    SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date ROWS UNBOUNDED PRECEDING) as total_to_date,
 7    
 8    -- 前1行到当前行
 9    AVG(amount) OVER (PARTITION BY salesperson ORDER BY sale_date ROWS 1 PRECEDING) as avg_last_2,
10    
11    -- 前1行到后1行
12    SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sum_3_rows
13FROM sales;

6. 高级窗口函数应用

复杂业务分析

 1-- 销售员绩效综合分析
 2WITH sales_stats AS (
 3    SELECT 
 4        salesperson,
 5        region,
 6        sale_date,
 7        amount,
 8        -- 排名分析
 9        ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) as region_rank,
10        -- 累计分析
11        SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) as ytd_sales,
12        -- 趋势分析
13        LAG(amount, 1) OVER (PARTITION BY salesperson ORDER BY sale_date) as prev_month,
14        -- 占比分析
15        amount * 100.0 / SUM(amount) OVER (PARTITION BY salesperson) as pct_of_total
16    FROM sales
17)
18SELECT *,
19    CASE 
20        WHEN region_rank = 1 THEN '地区冠军'
21        WHEN region_rank <= 3 THEN '地区前三'
22        ELSE '其他'
23    END as performance_level
24FROM sales_stats;

员工薪资深度分析

 1SELECT 
 2    name,
 3    department,
 4    salary,
 5    -- 部门内分析
 6    AVG(salary) OVER (PARTITION BY department) as dept_avg,
 7    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_dept_avg,
 8    
 9    -- 公司整体分析
10    AVG(salary) OVER () as company_avg,
11    salary - AVG(salary) OVER () as diff_from_company_avg,
12    
13    -- 排名分析
14    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
15    RANK() OVER (ORDER BY salary DESC) as company_rank,
16    
17    -- 分布分析
18    PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary) as dept_percentile,
19    CUME_DIST() OVER (ORDER BY salary) as company_cume_dist
20FROM employee_salary
21ORDER BY department, salary DESC;

7. 性能优化技巧

使用窗口函数替代复杂子查询

不推荐：

1SELECT 
2    s1.salesperson,
3    s1.amount,
4    (SELECT COUNT(*) 
5     FROM sales s2 
6     WHERE s2.region = s1.region AND s2.amount > s1.amount) + 1 as rank
7FROM sales s1;

推荐：

1SELECT 
2    salesperson,
3    amount,
4    RANK() OVER (PARTITION BY region ORDER BY amount DESC) as rank
5FROM sales;

合理使用PARTITION BY

 1-- 只在需要时使用PARTITION BY
 2SELECT 
 3    salesperson,
 4    region,
 5    amount,
 6    -- 需要分区：按销售员计算累计
 7    SUM(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) as personal_running_total,
 8    -- 不需要分区：总体排名
 9    RANK() OVER (ORDER BY amount DESC) as overall_rank
10FROM sales;

8. 实际业务场景

场景1：销售排行榜

 1SELECT 
 2    salesperson,
 3    region,
 4    total_sales,
 5    region_rank,
 6    company_rank
 7FROM (
 8    SELECT 
 9        salesperson,
10        region,
11        SUM(amount) as total_sales,
12        RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) as region_rank,
13        RANK() OVER (ORDER BY SUM(amount) DESC) as company_rank
14    FROM sales
15    GROUP BY salesperson, region
16) ranked_sales
17WHERE region_rank <= 3 OR company_rank <= 10;

场景2：员工薪资调整分析

 1SELECT 
 2    name,
 3    department,
 4    salary,
 5    dept_avg,
 6    CASE 
 7        WHEN salary < dept_avg * 0.9 THEN '低于平均-建议调整'
 8        WHEN salary > dept_avg * 1.2 THEN '高于平均-表现优秀'
 9        ELSE '在正常范围内'
10    END as adjustment_recommendation
11FROM (
12    SELECT 
13        name,
14        department,
15        salary,
16        AVG(salary) OVER (PARTITION BY department) as dept_avg
17    FROM employee_salary
18) with_avg;

三、总结

窗口函数是MySQL中极其强大的功能，它能够：

  • 简化复杂查询，避免多重子查询

  • 提高查询性能

  • 实现高级分析功能

  • 保持数据的原始粒度

掌握窗口函数能够显著提升数据分析和报表开发的效率

发布在 Database

发表评论