深入简出 SQL窗口函数

前言

大家好，我是楚生辉，在未来的日子里我们一起来学习SQL窗口函数相关的技术，一起努力奋斗，遇见更好的自己！

本文详细的介绍了窗口函数的各种类型，并提供了案例的材料，有需要的小伙伴可以自行获取与学习~

1.窗口函数

与聚合函数类似，聚合函数会把多行数据聚合成一行，而窗口函数会完整的保留每一行

• 语法结构：

ㅤㅤㅤ

1.1常见的窗口函数

1.1.1 序号函数

ROW_NUMBER()：并列第一名的时候，还是按照顺序往下排

RANK()：并列第一名的时候，第二名就是序号3

DENSE_RANK()：并列第一的时候，第二名还是序号2

使用场景

• 对每个部门的员工按照工资进行排序，并给出排名

SELECT
	dname,
	ename,
	salary,
	ROW_NUMBER() over ( PARTITION BY dname ORDER BY salary DESC ) AS rn 
FROM
	employee

ㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤ

1.1.2 分布函数

• CUME_DIST：分组内小于，等于当前rank值的行数/分组内总行数

使用场景：查询小于等于当前薪资(salary)的比例

SELECT
	dname,
	ename,
	salary,
	CUME_DIST() OVER ( ORDER BY salary ) AS rn1,
	CUME_DIST() OVER ( PARTITION BY dname ORDER BY salary ) AS rn2 
FROM
	employee

ㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤ

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

1.1.3 前后函数

LAG LEAD：返回位于当前行的前n行(LAG(expr,n)) 或者后n行(LEAD(expr,n))的expr的值

使用场景：查询前一名同学的成绩和当前同学成绩从差值

SELECT
	dname,
	ename,
	hiredate,
	LAG( hiredate, 1, '2000-01-01' ) OVER ( PARTITION BY dname BY hiredate ) AS last_1_name,
	LAG( hiredate, 2 ) OVER ( PARTITION BY dname BY hiredate ) AS last_2_name,
FROM
	employee

ㅤㅤㅤㅤㅤㅤ

1.1.4 头尾函数

FIRST_VALUE(expr)或LAST_VALUE(expr) ： 返回到目前位置的第一个或者最后一个expr的值

使用场景：截至到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

注意：如果不指定ORDER BY，则进行排序混乱，会出现错误的结果

SELECT
	dname,
	ename,
	salary hiredate,
	FIRST_VALUE( salary ) OVER ( PARTITION BY dname ORDER BY hiredate ) AS FIRST,
	LAST_VALUE( salary ) OVER ( PARTITION BY dname ORDER BY hiredate ) AS last,	
FROM
	employee

ㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤ

1.1.5 其他函数

NTH_VALUE(expr,n) : 截止到当前行，返回窗口中第n个expr的值，expr可以是表达式也可以是列明名字

使用场景：截止到当前薪资，显示每个员工的薪资中排名第二和第三的薪资

SELECT
	dname,
	ename,
	hiredate,
	salary,
	NTH_VALUE( salary, 2 ) OVER ( PARTITION BY dname, ORDER BY hiredate ) AS second_score,
	NTH_VALUE( salary, 3 ) OVER ( PARTITION BY dname, ORDER BY hiredate ) AS third_score,
FROM
	employee

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

使用场景：将每个部门员工按照入职日期分成3组

SELECT
	dname,
	ename,
	salary,
	hiredate,
	NTILE( 4 ) OVER ( PARTITION BY dname ORDER BY hiredate ) AS nt 
FROM
	employee

ㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤㅤ

1.2 窗口聚合函数

SUM:表示从第一行开始加到当前行，还可以自定义相加哪些行，以便于实现统计，例如从当前行加到最后，从头到当先行，从哪一行到哪一行

函数	用法
max()	最大值
min()	最小值
avg()	平均值
sum()	求和
median()	中位数

SELECT
	dname,
	ename,
	salary,
	sum( salary ) over ( PARTITION BY dname ORDER BY salary DESC ) AS c1 
FROM
	employee

ㅤㅤ ㅤㅤ ㅤㅤ ㅤㅤ ㅤㅤ ㅤㅤ ㅤㅤ ㅤㅤ