1、连续求和函数:sum(..) over(..)
原数据:
示例:
select sum(nums) over(), sum(nums) over(order by id), sum(nums) over(partition by depart_id order by id) from tmp_all
2、排序值分析函数:rank() 和 dense_rank()
原数据:
示例:rank()函数,相同科目(subject)根据分数(fraction)进行排序,并生成等级列。
select rank() over(partition by t.subject order by t.fraction desc) dj,t.* from tmp_all t
获取各科前两名成绩的数据
select mt.* from (select rank() over(partition by t.subject order by t.fraction desc) dj,t.* from tmp_all t) mt where mt.dj <= 2
dense_rank与rank()用法其实差不多,看如下示例就知道两者的区别了
3、排序后顺序号分析函数:row_number()
原数据:
示例:根据科目(subject)分组,根据成绩排序(fraction),组内生成连续且唯一的顺序号
select t.*,row_number() over(partition by t.subject order by t.fraction) nums from tmp_all t
4、 取上下行数据函数:lag()和lead()
原数据:
示例:根据科目(subject)分组,根据成绩排序(fraction),组内生成连续且唯一的顺序号。
lag() 获取上一行的值,lead() 获取下一行的值
select t.*,
lag(t.fraction) over (partition by t.subject order by t.fraction) lags,
lead(t.fraction) over (partition by t.subject order by t.fraction) leads
from tmp_all t