可分为两大类:专用窗口函数、常用聚合函数
专用窗口函数
ROW_NUMBER() OVER(PARTITION BY …ORDER BY …)
当前行在组内的序号,不管是否出现重复项,不间断连续排序号,如1,2,3,4,5......
DENSE_RANK() OVER(PARTITION BY …ORDER BY …)
不间断组内排序,重复项序号会相同,如1,1,2,2,3......
RANK() OVER(PARTITION BY …ORDER BY …)
间断组内排序,重复项序号会相同,如1,1,3,3,5......
LAG(expr[,N[,default]]) OVER(PARTITION BY …ORDER BY …)
LEAD(expr[,N[,default]]) OVER(PARTITION BY …ORDER BY …)
参数说明:
expr:字段名
N:偏移量,即要取在当前行往上(lag())或往下(lead())N行的那个字段值,比如当前行为10,N=3,就是取组内第7行的字段值
default:为空时设定的返回值,若没有指定默认值,则返回NULL
应用场景:环比、取组内最大最小、路径分析或流程分析
如,常用环比,使用窗口函数的方法会比表自联的方法效率高
使用频率较低的其他专用窗口函数(个人经历)
PERCENT_RANK() OVER(PARTITION BY …ORDER BY …)
每行按照公式(rank-1) / (rows-1)进行计算。rank为RANK()函数产生的序号,rows为当前窗口的记录总行数
CUME_DIST() OVER(PARTITION BY …ORDER BY …)
小于当前值的累计分布比例(处理一下可以用作帕累托图)
FIRST_VALUE(expr) OVER(PARTITION BY …ORDER BY …)
返回组内expr字段第一个值(若expr为简单表达式,返回1,0)
LAST_VALUE(expr) OVER(PARTITION BY …ORDER BY …)
返回组内expr字段最后的那个值(若expr为简单表达式,返回1,0)
NTH_VALUE(expr, n) OVER(PARTITION BY …ORDER BY …)
n是指这个函数取排名第几的记录,返回窗口中第n个expr的值(若expr为简单表达式,返回1,0,null)
常用聚合函数
如sum, avg, count, max, min等,这块大家用的比较熟就不展开了
若有不足之处,请大家指正。