Over函数
基本语法:
-- 根据c1分区,然后按照c2排序
over(partition by c1 order by c2)
该函数不能单独使用,需要与row_number()
、rank()
、dense_rank()
、lag()
、lead()
和sum()
配合使用
partition by
和group by
的区别:
group by
:会对结果按照指定字段进行聚合,结果集会缩减。例如:统计部门人数,总工资等。
partition by
:会对结果集按照指定字段分层排列,结果集不会缩减。例如:按部门分区。
row_number(), rank(), dense_rank() 与 over连用时,order by 不能省略,省略partition by 将结果集默认为一个分区
--按照c1分区,按照c2排序,排序从1开始,遇到相同数据,排名照样增加
row_number() over(partition by c1 order by c2)
--按照c1分区,按照c2排序,排序从1开始,遇到相同数据,排名相同,如连续的3,3,则下一个为5,排名可能不连续
rank() over(partition by c1 order by c2)
--按照c1分区,按照c2排序,排序从1开始,遇到相同数据,排名相同,如连续的3,3,则下一个为4,排名连续
dense_rank() over(partition by c1 order by c2)
lag(), lead() 与 over连用时,order by 不能省略,省略partition by 将结果集默认为一个分区
--按照c1分区,按照c2排序,在分区内获取前一行数据的c3字段的值,第一行为空
lag(c3) over(partition by c1 order by c2)
--按照c1分区,按照c2排序,在分区内获取后一行数据的c3字段的值,最后一行为空
lead(c3) over(partition by c1 order by c2)
sum() 与 over连用时,order by 不能省略,省略partition by 将结果集默认为一个分区,此处sum不是对整个分区进行求和,而是根据ordey by 后面的字段分区排序之后,求出分区中小于等于自身所在分层等级的和,即连
续求和。
--按照c1分区,按照c2排序,在分区内求出小于等于自身所在分层等级的和,如遇到等级相同的,求和不会增加
sum(c3) over(partition by c1 order by c2)