lag()函数 lead()函数

前言

书接上回，上回重点讲了聚合函数之count开窗函数，first_value开窗函数；
言归正传，这次我们重点讲解lag开窗函数和cume_dist开窗函数；

1.last_value开窗函数

语义：返回分区中最后一个值（某一列属性的最后一个值）
同first-value开窗函数；

2.lag开窗函数

语义：lag(col,n,default) 用于统计窗口内往上第n个值。
col:列名
n:往上第n行
default:往上第n行为NULL时候，取默认值,不指定则取NULL

-- lag 开窗函数

select studentId,math,departmentId,classId,
 --窗口内 往上取第二个 取不到时赋默认值60
lag(math,2,60) over(partition by classId order by math) as lag1,
 --窗口内 往上取第二个 取不到时赋默认值NULL
lag(math,2) over(partition by classId order by math) as lag2
from student_scores where departmentId='department1';

结果
studentid   math    departmentid    classid lag1    lag2
111         69      department1     class1  60      NULL
113         74      department1     class1  60      NULL
112         80      department1     class1  69      69
115         93      department1     class1  74      74
114         94      department1     class1  80      80
124         70      department1     class2  60      NULL
121         74      department1     class2  60      NULL
123         78      department1     class2  70      70
122         86      department1     class2  74      74

3.lead开窗函数

语义：lead(col,n,default) 用于统计窗口内往下第n个值。
col:列名
n:往下第n行
default:往下第n行为NULL时候，取默认值,不指定则取NULL
同lag开窗函数

--lead 开窗函数

select studentId,math,departmentId,classId,
 --窗口内 往下取第二个 取不到时赋默认值60
lead(math,2,60) over(partition by classId order by math) as lag1,
 --窗口内 往下取第二个 取不到时赋默认值NULL
lead(math,2) over(partition by classId order by math) as lag2
from student_scores where departmentId='department1';

结果
studentid   math    departmentid    classid lag1    lag2
111         69      department1     class1  80      80
113         74      department1     class1  93      93
112         80      department1     class1  94      94
115         93      department1     class1  60      NULL
114         94      department1     class1  60      NULL
124         70      department1     class2  78      78
121         74      department1     class2  86      86
123         78      department1     class2  60      NULL
122         86      department1     class2  60      NULL

4.cume_dist开窗函数

语义：计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：
小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。
应用场景：统计小于等于当前分数的人数占总人数的比例

-- cume_dist 开窗函数

select studentId,math,departmentId,classId,
-- 统计小于等于当前分数的人数占总人数的比例
round(cume_dist() over(order by math),2) cume_dist1
-- 统计大于等于当前分数的人数占总人数的比例
round(cume_dist() over(order by math desc),2) cume_dist2,
-- 统计分区内小于等于当前分数的人数占总人数的比例
round(cume_dist() over(partition by classId order by math),2)  cume_dist3
from student_scores where departmentId='department1';

结果
studentid   math    departmentid    classid cume_dist1              cume_dist2          cume_dist3
111         69      department1     class1  0.11     				1.0 					 0.2
113         74      department1     class1  0.44					0.78 			 		 0.4
112         80      department1     class1  0.67     				0.44  					 0.6
115         93      department1     class1  0.89     				0.22					 0.8
114         94      department1     class1  1.0      				0.11					 1.0
124         70      department1     class2  0.22     				0.89  	 				 0.25
121         74      department1     class2  0.44     				0.78					 0.5
123         78      department1     class2  0.56     				0.56					 0.75
122         86      department1     class2  0.78     				0.33					 1.0

结果解释:
    第三行:
        cume_dist1=小于等于80的人数为6/总人数9=0.6666666666666666
        cume_dist2=大于等于80的人数为4/总人数9=0.4444444444444444
        cume_dist3=分区内小于等于80的人数为3/分区内总人数5=0.6