窗口函数和普通聚合函数的区别:
- 聚合函数是将多条记录聚合为一条;窗口函数是每条记录都会执行,有几条记录执行完还是几条。
- 聚合函数也可以用于窗口函数。
窗口函数用法
函数名 OVER 子句
over关键字用来指定函数执行的窗口范围,若后面括号中什么都不写,则意味着窗口包含满足WHERE条件的所有行,窗口函数基于所有行进行计算;如果不为空,则支持以下4中语法来设置窗口。
- window_name:给窗口指定一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读
- PARTITION BY 子句:窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行
- ORDER BY子句:按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号
- FRAME子句:FRAME是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用
窗口函数分类
序号函数
ROW_NUMBER(),RANK(),DENSE_RANK()
用途:显示当前行号
区别:
ROW_NUMBER():顺序排序——1、2、3
RANK():并列排序,跳过重复序号——1、1、3
DENSE_RANK():并列排序,不跳过重复序号——1、1、2
分布函数
PERCENT_RANK()
用途:每行按照公式(rank-1) / (rows-1)进行计算。其中,rank为RANK()函数产生的序号,rows为当前窗口的记录总行数
CUME_DIST()
用途:分组内小于、等于当前rank值的行数 / 分组内总行数
前后函数
LAG(expr,n)、LEAD(expr,n)
用途:返回位于当前行的前n行(LAG(expr,n))或后n行(LEAD(expr,n))的expr的值
头尾函数
FIRST_VALUE(expr)、LAST_VALUE(expr)
用途:返回第一个(FIRST_VALUE(expr))或最后一个(LAST_VALUE(expr))expr的值
其他函数
NTH_VALUE(expr, n)
用途:返回窗口中第n个expr的值。expr可以是表达式,也可以是列名
NTILE(n)
用途:将分区中的有序数据分为n个等级,记录等级数
upbmvtphzl
博主真是太厉害了!!!