mysql 窗口函数

weixin_43784390

864人浏览 · 2026-01-09 11:01:03

weixin_43784390 · 2026-01-09 11:01:03 发布

1）定义

在一组查询行上执行类似聚合的操作，但是不会将查询结果折叠为单行输出，而是为每个查询行生成一个结果

2）功能

处理复杂的报表统计分析场景，例如计算移动平均值、累计和、排名等

3）语法

窗口函数 over(partition by 分组字段 order by 排序字段 rows between 窗口范围)

rows between 2 preceding and current row # 取当前行和前面两行
rows between unbounded preceding and current row # 包括本行和之前所有的行
rows between current row and unbounded following # 包括本行和之后所有的行
rows between 3 preceding and current row # 包括本行和前面三行
rows between 3 preceding and 1 following # 从前面三行和下面一行，总共五行
# 当order by后面缺少rows语句时，窗口规范默认范围如下：
rows between unbounded preceding and current row.
# 当order by和rows语句都缺失, 窗口规范默认是
rows between unbounded preceding and unbounded following

分类	函数	作用
序号函数	ROW_NUMBER()	添加序号给结果集每行分配一个唯一的连续整数序号，序号不会重复当有相同排序值时，每行都会有不同的序号。
	RANK()	排名给结果集中的每一行分配一个排名，排序值相同，跳过相同的排名下一个排名会按照跳过的数量递增
	DENSE_RANK()	排名给结果集中的每一行分配一个排名，但不会跳过相同的排名相同的排序值会有相同的排名，排名是连续的
分布函数	PERCENT_RANK()	计算某一行在结果集中的相对排名百分比返回一个介于0和1之间的值，表示当前行在整个结果集中的相对位置 (rank-1)/(rows-1)
分布函数	CUME_DIST()	计算某一行在结果集中的累积分布值返回一个介于0和1之间的值，表示当前行在整个结果集中的累积分布比例 <=当前rank值的行数/总行数
前后函数	LAG(expr,n)	返回当前行的前n行的满足expr的值
前后函数	LEAD(expr,n)	返回当前行的后n行的满足expr的值
头尾函数	FIRST_VALUE(expr)	返回第一个满足expr的值
头尾函数	LAST_VALUE(expr)	返回最后一个满足expr的值
其他函数	NTH_VALUE(expr,n)	返回第n个满足expr的值
其他函数	NTILE (n)	将有序数据分为n个桶，记录等级数

将 employees 表数据按照工资（salary）降序进行排序，并使用 ROW_NUMBER | RANK | DENSE_RANK 函数为每个员工分配序号、排名、稠密排名

SELECT 
    employee_id, 
    last_name, 
    first_name, 
    department_id, 
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY salary DESC)  AS dense_rank_num
FROM employees;

案例：对每个部门的员工按照薪资排序，给部门名、序号、排名、稠密排名

SELECT
	departments.department_name,
	last_name,
	first_name,
	salary,
	ROW_NUMBER ( ) OVER (PARTITION BY employees.department_id ORDER BY salary DESC) AS 序号,
	RANK ( ) OVER ( PARTITION BY employees.department_id ORDER BY salary DESC) AS 排名,
	DENSE_RANK ( ) OVER (PARTITION BY employees.department_id ORDER BY salary DESC) AS 稠密排名 
FROM
	employees
	INNER JOIN departments ON employees.department_id = departments.department_id

案例：计算员工工资的排名百分比、累积分布比例

SELECT
    department_id,
    last_name,
    first_name,
    salary,
    RANK() OVER (ORDER BY salary) AS ranking,
    PERCENT_RANK() OVER (ORDER BY salary) AS 相对排名百分比,
    CUME_DIST() OVER(ORDER BY salary) AS 累积分布值
FROM employees