【MySQL】窗口函数

窗口函数

窗口函数类似于可以返回聚合值的函数，例如sum()、count()、max()，但又与普通的窗口函数不同，它不会对结果进行分组。因此输出中的行数与输入中的行数相同。

窗口函数语法：
SELECT SUM() OVER(PARTITION BY ____ ORDER BY ____) FROM Table


OVER	用来指定函数执行的窗口范围	如果后面的括号中什么都不写，则表示窗口包含满足where条件的所有数据行，窗口函数基于所有数据行进行计算；如果括号中不为空，则支持partition by子句和order by子句。
PARTITION BY	窗口按照哪些字段进行分组	窗口函数在不同的分组上分别执行
ORDER BY	窗口按照哪些字段进行排序	窗口函数将按照排序后的记录顺序进行编号

窗口函数包括排名函数、分析函数、头尾函数、聚合统计函数和其他函数。


排名函数	对查询结果中的每一行进行排名	row_number()、rank()、dense_rank()
分析函数	在查询结果中对数据进行分析	lag()、lead()
头尾函数	获取分组数据中每个分组的第一个或者最后一个元素	first_value()、last_value()
聚合统计函数	对查询结果进行聚合统计	count()、sum()、avg()、max()、min()
其他函数	--	nth_value()、ntile()

- select * from student_score;
- stu_no	course_no	score
	1		L0001		90
	1		L0002		98
	1		L0003		98
	1		L0004		95
	2		L0001		90
	2		L0002		98
	2		L0003		98
	2		L0004		95
	3		L0001		98
	3		L0002		96
	3		L0003		90
	3		L0004		98
	1		L0005		95
	2		L0005		96
	3		L0005		97

排名函数

row_number(): 顺序排名函数

根据提供的字段的值依次进行排序，如果出现相同的值，排名号仍会依次增加。

- 对学生1的各科目成绩进行排名
- select * from (
	select stu_no,row_numer() over (partition by stu_no order by score desc) as score_order,course_no,score
	from student_score
) t where stu_no = 1;

- stu_no	score_order		course_no	score
	1			1			L0002		98
	1			2			L0003		98
	1			3			L0004		95
	1			4			L0005		95
    1			5			L0001		90
  
- 尽管L0002与L0003、L0004与L0005的成绩一样，但排名还是依次增加。

- 查询每个学生最高分数的科目
- select * from (
  stu_no,row_number() over(partition by stu_no order by score desc) as score_order,course_no,score
  from student_score ) t where score_order <= 1;
  
- stu_no	score_order		course_no	score
	1			1			L0002		98
	2			1			L0002		98
	3			1			L0001		98

rank(): 跳级排名函数

根据提供的字段的值进行排序（跳过重复序号），出现相同值时，排名号会一致，但下一个排名号是上一个排名号加上重复的排名号。

- 对学生1的各科成绩进行排名
- select * from(
  select stu_no,rank() over(partition by stu_no order by score desc) as score_order,course_no,score
  from student_score
) t where stu_no = 1;

- stu_no	score_order		course_no	score
	1			1			L0002		98
	1			1			L0003		98
	1			3			L0004		95
	1			3			L0005		95
	1			5			L0001		90

- 查询每个学生分数最高的科目
- select * from(
  select stu_no,rank() over(partition by stu_no order by score desc) as score_order,course_no,score
  from student_score
) t where score_order <= 1;

- stu_no	score_order		course_no	score
	1			1			L0002		98
	1			1			L0003		98
	2			1			L0002		98
	2			1			L0003		98
	3			1			L0001		98
	3			1			L0004		98
	
- 一名学生的最高分数科目存在多个，因为rank()排名会导致出现重复排名

dense_rank(): 不跳级排名函数

根据提供的字段的值进行排序（不跳过序号），出现相同值时，排名号会一致，但下一个排名号是上一个排名号+1

- 对学生2的各科成绩进行排名
- select * from (
  select stu_no,dense_rank() over(partition by stu_no order by score desc) as score_order,course_no,score
  from student_score
) t where stu_no = 2;

- stu_no	score_order		course_no	score
	2			1			L0002		98
	2			1			L0003		98
	2			2			L0005		96
	2			3			L0004		95
    2			4			L0001		90

分析函数

lag(): 前分析函数

lag(expr,n)函数用于返回当前行往前数第n行的expr的值

- 查询科目L0001的成绩，根据分数排名，查看与前一名的成绩之差
- select stu_no,course_no,score,pre_score,
  score-pre_score as diff from (
  select stu_no,course_no,score,
  lag(score,1) over w as pre_score
  from student_score
  where course_no in ('10001')
  window w as (partition by course_no order by score)
  ) t;
  
- stu_no	course_no	score	pre_score	diff
	1		L0001		90		null		null
	2		L0001		90		90			0
	3		L0001		98		90			8

[!NOTE]

window关键字：如果在一条语句中多次使用相同的窗口定义，用window关键字取个别名，避免重复书写。
select name,SUM(salary) over w, AVG(salary) as w
from employees
window w as (partition by department order by salary desc);

lead(): 后分析函数

lead(expr,n)函数用于返回当前行往后数第n行的expr的值

- 查询科目L0001的成绩，根据分数排名，查看前一名和后一名的成绩之差
- select stu_no,course_no,score,pre_score,score-pre_score as diff
  from (
    select stu_no,course_no,score,
    lead(score,1) over w as pre_score
    where course_no in ('L0001')
    window w as (partition by course_no order by score)
  )t;
  
- stu_no	course_no	score	pre_score	diff
	1		L0001		90			90		0
	2		L0001		90			98		-8
	3		L0001		98			null	null

头尾函数

first_value(): 返回第一个值

first_value(expr)返回分区的第一个值

- 根据科目分组对成绩升序排序，查询每科的成绩最低分
- select stu_no,course_no,score,
  first_value(score) over (partition by course_no order by score) as first_score
  from student_score;
  
- stu_no	course_no	score	first_score
	1		L0001		90			90
    2		L0001		90			90
    3		L0001		98			90
    3		L0002		96			96
	1		L0002		98			96
    2		L0002		98			96
    3		L0003		90			90
	1		L0003		98			90
    2		L0003		98			90
	1		L0004		95			95
	2		L0004		95			95
	3		L0004		98			95
	1		L0005		95			95
	2		L0005		96			95
	3		L0005		97			95

last_value(): 返回最后一个值

last_value()返回分区的最后一个值

- 根据科目分组对成绩升序排序，查询每科的最高分
- select stu_no,course_no,score,
  last_value(score) over (partition by course_no order by score range between unbounded preceding and unbounded following) as last_score
  from student_score;
  
- stu_no	course_no	score	last_score
	1		L0001		90			98
    2		L0001		90			98
    3		L0001		98			98
    3		L0002		96			98
	1		L0002		98			98
    2		L0002		98			98
    3		L0003		90			98
	1		L0003		98			98
    2		L0003		98			98
	1		L0004		95			98
	2		L0004		95			98
	3		L0004		98			98
	1		L0005		95			97
	2		L0005		96			97
	3		L0005		97			97

[!NOTE]

在 SQL 窗口函数中，RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 是用来定义窗口帧范围的，

RANGE: 表示基于“值”的范围（与之相对的是 ROWS，基于“行数”）。

BETWEEN ... AND ...: 定义范围的开始和结束。

UNBOUNDED PRECEDING: 意为“无限的前方”，即分区中的第一行。

UNBOUNDED FOLLOWING: 意为“无限的后方”，即分区中的最后一行。

意思是：窗口范围从第一行（起点）一直覆盖到最后一行（终点）。

不写范围（默认 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从开头到当前行））：计算的是累计值 。

写了 UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：计算的是全部分区的总值，即便进行了排序。

聚合统计函数

sum(): 统计总和

avg(): 统计平均值

max(): 统计最大值

min(): 统计最小值

count(): 统计总条数

- 根据学生编号分组，查询每个学生科目的累加分、平均分、最高分数
- select stu_no,course_no,score,
  sum(score) over (partition by stu_no order by course_no) as score_sum,
  avg(score) over (partition by stu_no order by course_no range between unbounded preceding and unbounded following) as score_avg,
  max(score) over (partition by stu_no order by course_no range between unbounded preceding and unbounded following) as score_max
  from student_score;

- stu_no	course_no	score	score_sum	score_avg	score_max
	1		L0001		90			90		95.2000			98
	1		L0002		98			188		95.2000			98
	1		L0003		98			286		95.2000			98
	1		L0004		95			381		95.2000			98
    1		L0005		95			476		95.2000			98
	2		L0001		90			90		95.4000			98
	2		L0002		98			188		95.4000			98
	2		L0003		98			286		95.4000			98
	2		L0004		95			381		95.4000			98
    2		L0005		96			477		95.4000			98
	3		L0001		98			98		95.8000			98
	3		L0002		96			194		95.8000			98
	3		L0003		90			284		95.8000			98
	3		L0004		98			382		95.8000			98
	3		L0005		97			479		95.8000			98

其他函数

nth_value(): 从结果集中的第n行获取值

nth_value(expr,n)返回窗口中第n个expr值

- 显示编号为1和2学生的成绩中排名第1和第2的成绩分数
- select stu_no,course_no,score,
  nth_value(score,1) over w as score1,
  nth_value(score,2) over w as score2
  from student_score
  where stu_no in (1,2)
  window w as (partition by stu_no order by score desc range between unbounded proceding and unbounded following);
  
- stu_no	course_no	score	score1	score2
	1		L0002		98		98		98
	1		L0003		98		98		98
	1		L0004		95		98		98
    1		L0005		95		98		98
    1		L0001		90		98		98
	2		L0002		98		98		98
	2		L0003		98		98		98
	2		L0004		95		98		98
    2		L0005		96		98		98
    2		L0001		90		98		98

ntile(): 数据集分桶

ntile(n)将分区中的有序数据分为n个等级，记录等级数。

由于记录数不一定被n整除，因此数据不一定完全平均分配。

- 假设有一组销售员的业绩数据，把他们分成 3 个等级
- SELECT 姓名,销售额,
  NTILE(3) OVER (ORDER BY 销售额 DESC) as 级别
  FROM sales;
  
- 根据科目目录将数据分为2组
- select ntile(2) over w as nf,
  stu_no,course_no,score
  from student_score
  window w as (partition by course_no order by score); 
- nf	stu_no	course_no	score
	1	1		L0001		90
    1	2		L0001		90
    2	3		L0001		98
    1	3		L0002		96
	1	1		L0002		98
    2	2		L0002		98
    1	3		L0003		90
	1	1		L0003		98
    2	2		L0003		98
	1	1		L0004		95
	1	2		L0004		95
	2	3		L0004		98
	1	1		L0005		95
	1	2		L0005		96
	2	3		L0005		97

[!NOTE]

n: 桶的数量（必须是正整数）。

ORDER BY: 决定了数据是按什么顺序进行分配的（非常关键）。

PARTITION BY: 可选。如果使用了，则在每个分组内部独立进行切分。

规则 1：桶与桶之间的行数差异最多为 1。

规则 2：较大的桶会排在前面。

例子： 如果你有 7 行 数据，要分进 3 个桶：

7 / 3 = 2 余 1

分配结果是：第一个桶 3 行，第二个桶 2 行，第三个桶 2 行。（3, 2, 2）

posted @ 2026-01-09 15:43 wasline 阅读(13) 评论(0) 收藏举报

刷新页面返回顶部