1、开窗函数/分析函数:over()
参考链接:SQL开窗函数(窗口函数)详解
调用格式:函数名(列名) OVER(partition by 列名 order by列名) 。
2、几个排序函数的差异
(1)row_number() over():对相等的值不进行区分,其实就是行号,相等的值对应的排名不同,序号从1到n连续。1,2,3,4,5……
(2)rank() over():跳跃排序,如果有两个第一级时,接下来就是第三级。1,2,3,3,5……
(3)dense_rank() over():连续排序,如果有两个第一级时,接下来仍然是第二级。1,1,2,3,4……
(4)ntile( n ) over():可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行,排序对应的数字为桶号,序号从1到n连续。如果不能平均分配,则较小桶号的桶分配额外的行,并且各个桶中能放的数据条数最多相差1。
3、主要开窗函数
first_value() over(partition by … order by …)
last_value() over(partition by … order by …)
lag() over(partition by … order by …)
lead() over(partition by … order by …)
lag 和lead 可以 获取结果集中,按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联);
lag ,lead 分别是向前,向后;
lag 和lead 有三个参数,第一个参数是列名,第二个参数是偏移的offset,第三个参数是 超出记录窗口时的默认值)
4、rollup,cube
rollup是cube的一种特殊情况,和rollup一样,cube也是根据维度在分组的结果集中进行聚合操作。但是rollup只在层次上对数据进行聚合,而cube对所有的维度进行聚合。具有N个维度的列,cube需要2的N次方次分组操作,而rollup只需要N次分组操作。
group by后面使用 grouping sets(…) 语句。通过该语句可以实现rollup、cube同样的功能。