mysql8窗口函数

一、mysql窗口函数简介

MySQL从8.0开始支持窗口函数，这个功能在大多商业数据库和部分开源数据库中早已支持，有的也叫分析函数。

窗口函数：在满足某些条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数。有的函数随着记录的不同，窗口大小都是固定的，称为静态窗口；有的函数则相反，不同的记录对应着不同的窗口，称为滑动窗口。

1.窗口函数和普通聚合函数的区别：

①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。
②聚合函数也可以用于窗口函数。

2.窗口函数的基本用法：

函数名 OVER 子句

over 关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下4中语法来设置窗口。

①window_name：给窗口指定一个别名。如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读；

②PARTITION BY 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行；

③ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号；

④FRAME子句：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。

3.按功能划分，可将MySQL支持的窗口函数分为如下几类：

窗口函数	window_name
序号函数	row_number() / rank() / dense_rank()
分布函数	percent_rank() / cume_dist()
前后函数	lag() / lead()
头尾函数	first_val() / last_val()
其他函数	nth_value() / nfile()

二、窗口函数使用示例

演示数据：

# 序号，学号，课程号，分数
create table score (
	id int(10) NOT NULL,
	sid int(10) NOT NULL,
	lid varchar(10) NOT NULL,
	score int(10) NOT NULL,
	PRIMARY KEY(id)
	);

INSERT INTO score(id,sid,lid,score) VALUES 
('1','1001','L01','100'),('2','1001','L02','98'),('3','1001','L03','99'),
('4','1001','L04','100'),('5','1001','L05','89'),('6','1002','L01','99'),
('7','1002','L02','90'),('8','1002','L03','89'),('9','1002','L04','87'),
('10','1002','L05','89'),('11','1003','L01','90'),('12','1003','L02','96'),
('13','1003','L03','79'),('14','1003','L04','89'),('15','1003','L05','96'),
('16','1004','L01','100'),('17','1004','L02','89'),('18','1004','L03','97'),
('19','1004','L04','79'),('20','1004','L05','85');

1.序号函数： row_number、rank、dense_rank

• 用途：显示分区中的当前行号
• 应用场景：查询每个学生的分数最高的前N门课程 （分组排序），如 1、2、3 、4

row_number : 排序序号连续，不重复（相同分数的人序号递增）

SELECT
	sid,
	lid,
	score,
	ROW_NUMBER () OVER (PARTITION BY sid ORDER BY score desc) AS srank
FROM
	score;

rank : 排序序号不连续，相同分数的人序号相同，但会占用序号位置，如 1、1、3 、4

SELECT
	sid,
	lid,
	score,
	RANK () OVER (
		PARTITION BY sid
		ORDER BY
			score DESC
	) AS srank
FROM
	score;

dense_rank : 序号连续，且分数相同的人序号相同，如1、1 、2 、3

SELECT
	sid,
	lid,
	score,
	dense_rank () OVER (
		PARTITION BY sid
		ORDER BY
			score DESC
	) AS srank
FROM
	score;

2.分布函数：percent_rank()、cume_dist()

percent_rank()：

• 每行按照公式(rank-1) / (rows-1)进行计算。
• 其中，rank为rank()函数产生的序号，rows为当前窗口的记录总行数

SELECT
	sid,
	lid,
	score,
	rank() over w as row_num,
	percent_rank() over w as percent
	from score 
	window w as (partition by sid order by score desc);

cume_dist()：

• 用途：分组内小于、等于当前rank值的行数 / 分组内总行数
• 应用场景：查询大于等于当前成绩（score）的比例 （大于主要取决排序是降序，若为升序，则就是小于）

SELECT
	sid,
	lid,
	score,
	rank () over w AS row_num,
	cume_dist () over w AS percent
	FROM score 
	window w AS ( PARTITION BY sid ORDER BY score DESC );

3.前后函数： lag(expr,n)、lead(expr,n)

• 用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
• 应用场景：查询当前同学和前1名同学的成绩的差值

SELECT
	sid,
	lid,
	score,
  srank,
	prescore,
	(prescore - score) AS diffscore
FROM
	(
		SELECT
			sid,
			lid,
			score,
			dense_rank() over w as srank,
			lag (score, 1) over w AS prescore
		FROM score 
      window w AS (	PARTITION BY lid ORDER BY score DESC )
	) a

4.头尾函数： FIRST_VALUE(expr)、LAST_VALUE(expr)

• 用途：返回分组中第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）指定参数的值
• 应用场景：查询截止当前行，各科成绩最大和最小的分数

SELECT
	sid,
	lid,
	score,
	first_value(score) over w AS max_score,
	last_value(score) over w AS min_score
FROM score 
	window w AS (	PARTITION BY lid ORDER BY score DESC );

5.其它函数：NTH_VALUE(expr, n)、NTILE(n)

NTH_VALUE(expr,n)

• 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
• 应用场景：截止到当前成绩，显示每个同学的成绩中排名第2和第3的成绩的分数

SELECT
	sid,
	lid,
	score,
	row_number() over w as srank,
	nth_value(score,2) over w AS second_score,
	nth_value(score,3) over w AS third_score
FROM score 
	window w AS (	PARTITION BY sid ORDER BY score desc )

NTILE(n)

• 用途：将分区中的有序数据分为n个等级，记录等级数
• 应用场景：将每门课程按照成绩分成3组

SELECT
	sid,
	lid,
	score,
	row_number() over w as srank,
	ntile(3) over w AS nf
FROM score 
	window w AS (	PARTITION BY lid ORDER BY score desc );

NTILE(n)函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到n个并行的进程分别计算，此时就可以用NTILE(n)对数据进行分组（由于记录数不一定被n整除，所以数据不一定完全平均），然后将不同桶号的数据再分配。

三、聚合函数作为窗口函数

• 用途：在窗口中每条记录动态地应用聚合函数（SUM()、AVG()、MAX()、MIN()、COUNT()），可以动态计算在指定的窗口内的各种聚合函数值
• 应用场景：截止到当前时间，查询每个学生的累计分数、平均分数、分数最高的科目、分数最低的科目

SELECT
	sid,
	lid,
	score,
	sum(score) over w as sumscore,
	avg(score) over w as avgscore,
	max(score) over w as maxscore,
	min(score) over w as minscore
FROM score 
	window w AS (	PARTITION BY sid ORDER BY score desc );