基础-窗口函数

1.取值函数

1.1.FIRST_VALUE


SELECT 
	score-FIRST_VALUE(score) OVER(PARTITION BY name)
	# 窗口函数：按姓名分组，取出姓名等于当前姓名的分组中的第一行
FROM
	student;
# 等价于
SELECT
	score-(SELECT score FROM student AS S WHERE S.name=P.name LIMIT 1)
FROM
	student AS P;

SELECT 
	score-FIRST_VALUE(score) OVER(PARTITION BY name ORDER BY date ASC)
	# 窗口函数：按姓名分组，取出姓名等于当前姓名的按日期排序后的分组中的第一行
FROM
	student;
# 等价于
SELECT
	score-(SELECT score FROM student AS S WHERE S.name=P.name ORDER BY date ASC LIMIT 1)
FROM
	student AS P;

# PARTITION BY类似GROUP BY
# 不难看出，窗口函数在某种程度上与SELECT的相关子查询类似

1.2.LAST_VALUE


SELECT 
	score-LAST_VALUE(score) OVER(PARTITION BY name)
	# 窗口函数：按姓名分组，取出姓名等于当前姓名的分组中的最后一行
FROM
	student;
# 等价于（由于LIMIT不支持子查询也不支持变量，因此该实现较复杂，可待有一定基础后再看）
WITH T AS
(SELECT 
	S.*,
	@seq:=@seq+1 AS seq # 每一行都令变量+1
FROM student AS S,(SELECT @seq:=0) AS VT)# 定义一个变量用于标记行号，初始值为1
SELECT
	score-(SELECT score FROM T WHERE name=P.name ORDER BY seq DESC LIMIT 1)
	# 利用行号逆序取首行，即取最后一行
FROM
	T AS P;
# 可以看出该实现已经很麻烦，这也正是窗口函数的优势所在

SELECT 
	score-LAST_VALUE(score) 
				OVER(PARTITION BY name 
					 ORDER BY date ASC 
					 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	# 窗口函数：按姓名分组，取出姓名等于当前姓名的按日期排序后的分组中的最后一行
	# 注意此处使用ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 用于将范围定为整个分区
FROM
	student;
# 等价于
SELECT
	score-(SELECT score FROM student AS S WHERE S.name=P.name ORDER BY date DESC LIMIT 1)
FROM
	student AS P;

# 现在已引出窗口的全部三要素：分区PARTITION BY，排序ORDER BY和范围 ROWS或RANGE
# 事实上，三要素高度依赖上下文（即当前行），这与相关子查询不谋而合
# 分区与排序通过相关子查询的演示其本质已经显而易见
# 范围是分区的子集，且根据上下文改变而改变
# UNBOUNDED PRECEDING与UNBOUNDED FOLLOWING分别表示分区的上界和下界，当范围设置为两者时则范围等价于分区
# n PRECEDING与n FOLLOWING（n为数字）分别表示从当前行向上偏移的行数和向下偏移的行数
# CURRENT ROW为当前行
# 为什么第二个查询要使用范围？因为ORDER BY的使用会导致范围隐式指定为分区上界到当前行，这有时不符合需求
# 对于排序函数和LAG,LEAD，指定范围无意义，即使指定MySQL也会忽略
# 可以仅指定范围的起始，ROWS 3 PRECEDING等价于ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
# 也可以同时指定起始和结束,ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING

1.3.NTH_VALUE


SELECT 
	score-NTH_VALUE(score,3) 
			OVER(PARTITION BY name 
				 ORDER BY date ASC 
				 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	# 窗口函数：按姓名分组，取出姓名等于当前姓名的按日期排序后的分组中的第三行
FROM
	student;

1.4.LEAD和LAG


SELECT 
	score-LEAD(score,1) 
			OVER(PARTITION BY name 
				 ORDER BY date ASC 
				 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	# 窗口函数：按姓名分组，取出姓名等于当前姓名的按日期排序后的分组中'对应的下一行'（没有下一行则返回NULL）
	# 注意'对应的下一行'的实际意义，此处指当前行在排序后的下一行
FROM
	student;
# 等价于
WITH T AS
(SELECT 
	S.*,
	@seq:=@seq+1 AS seq # 每一行都令变量+1
FROM student AS S,(SELECT @seq:=0) AS VT
ORDER BY name ASC,date ASC)# 定义一个变量用于标记行号，初始值为1
SELECT
	score-(SELECT score FROM T WHERE name=P.name AND seq=P.seq+1)
	# 利用排序后的临时表
FROM
	T AS P;

SELECT 
	score-LEAD(score,1,score) 
			OVER(PARTITION BY name 
				 ORDER BY date ASC 
				 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
	# 窗口函数：按姓名分组，取出姓名等于当前姓名的按日期排序后的分组中的对应下一行（没有下一行则返回当前行）
FROM
	student;

# 窗口函数就好像时复制一份原表后进行一系列操作后再返回所需值给查询，也就意味着窗口函数内的操作不影响查询主体
# LAG用法等同于LEAD，只是它返回的是当前行的上一行

2.排名函数


SELECT 
	*,
	DENSE_RANK() OVER(PARTITION BY name ORDER BY score DESC)
	# 根据ORDER BY的列进行排名
FROM
	student;

# 排名函数的语法皆如上所示

# 排名函数的区别：
# 对于数值列：10，20，20，30
# DENSE_RANK：密集排名（也称中国式排名），返回1，2，2，3
# RANK：国际排名，返回1，2，2，4
# ROW_NUMBER：行编号，返回1，2，3，4
# PERCENT_RANK：国际排名的百分比形式
# 另外NTILE和CUME_DIST请参考官方文档

3.聚合函数


SELECT 
	*,
	AVG(score) 
		OVER(PARTITION BY name 
			 ORDER BY date ASC 
			 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
	# 移动平均值，返回每个学生每次考试的（本次得分加前两行得分）的三次得分的平均值
FROM
	student;

# 聚合函数语法皆如上所示
# 聚合函数有AVG,SUM,COUNT,MAX,MIN

4.ROWS和RANGE的区别

SELECT 
	*,
	AVG(score) 
		OVER(PARTITION BY name ROWS 2 PRECEDING)
FROM
	student;
# 偏移指定行数，不依赖排序函数

SELECT 
	*,
	AVG(score) 
		OVER(PARTITION BY name 
			 ORDER BY date ASC 
			 RANGE INTERVAL 2 DAY PRECEDING)
FROM
	student;
# 表示（当前行得分+当前行对应的日期的前两日的得分）的平均值
# 假定当前行对应日期为2023-03-06，前两日为2023-03-04和2023-03-05
# 日期为此三天的所有得分（不一定是三个）均参与平均值计算
SELECT 
	*,
	AVG(score) 
		OVER(PARTITION BY name 
			 ORDER BY score ASC 
			 RANGE 2 PRECEDING)
FROM
	student;
# 同理，但此处为数值
# RANGE必须依赖ORDER BY且排序的必须是数值或日期

# 文字描述可能不够清晰，此处差异最好自行尝试对比

5.窗口函数的再探索

5.1.越界

LEAD,LAG,NTH_VALUE或聚合函数超出分区或范围会返回NULL

其中LEAD,LAG可以显式指定超出范围后返回的内容

5.2命名窗口


SELECT 
	*,
	AVG(score) OVER w,
	SUM(score) OVER w
FROM
	student
WINDOW w AS (PARTITION BY name ORDER BY date ASC RANGE INTERVAL 2 DAY PRECEDING);
# 如果同一窗口定义多次使用则可以使用命名窗口，简化代码量同时提高阅读性

5.3.窗口函数的执行顺序


SELECT 
	name,
	AVG(score),
	DENSE_RANK() 
		OVER(PARTITION BY name ORDER BY AVG(score) DESC)
FROM
	student
GROUP BY name;
# 实际上，窗口函数执行在SELECT之后
# 因此，窗口函数基于WHERE,GROUP BY,HAVING之后的表，而不是FROM后的表
# 也因此，窗口函数只能在SELECT中使用

# 如果对查询主体使用GROUP BY，应非常了解窗口函数的执行顺序，否则将造成错误