前言
大家好,我是楚生辉,在未来的日子里我们一起来学习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
ㅤㅤ ㅤㅤ ㅤㅤ ㅤㅤ ㅤㅤ ㅤㅤ ㅤㅤ ㅤㅤ