lag()函数 lead()函数

大家好，欢迎来到IT知识分享网。

前言

1.last_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.66666 cume_dist2=大于等于80的人数为4/总人数9=0.44444 cume_dist3=分区内小于等于80的人数为3/分区内总人数5=0.6