窗口函数

窗口函数也称为OLAP函数，意思是对数据库数据进行实时分析处理。能进行排序并生成序列号。

窗口函数分为：1. sum()，avg()，count()，max()，min()等一般聚合函数

                          2.rank()，dense_rank()，row_number()，ntile()，first_value()，last_value()，lag()，lead()，cume_dist()，    percent_rank()

窗口函数使用语法：fun() OVER([PARTITION BY] col [ORDER BY col])

窗口函数的应用场景：1. 分区排名  2.动态groupby  3.Top N  4.累计查询   5.层次查询

WINDOW子句

• 用于从分区中选择指定的多条记录，供窗口函数处理。Hive 提供了两种定义窗口帧的形式：ROWS 和 RANGE。
• 对于SELECT *, SUM() OVER (PARTITION BY col1 ORDER BY col2) from t: 1.没指定window子句，指定order by，返回起始行到当前行的累加值；2. 没指定window字句，没指定order by字句，返回所有行的累加值；3）指定ROWS BETWEEN window字句，没指定order by，按ROWS BETWEEN执行
• PRECEDING：往前
  FOLLOWING：往后
  CURRENT ROW：当前行
  UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点
• 例如：ROWS BETWEEN 3 PERCEDING AND 1 FOLLOWING表示当前行+往前3行+往后1行                                                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从起点到当前行

RANK()，DENSE_RANK()，ROW_NUMBER()

• RANK()得到数据项在分组中的排名，排名相等的时候会留下空位：1,2,2,4，...
• DENSE_RANK()得到数据项在分组中的排名，排名相等的时候不会留下空位：1,2,2,3，...
• ROW_NUMBER()按照分组中的顺序生成序列，不存在重复的序列：1,2,3,4...

NTILE()

• NTILE(N)用于将分组数据按照顺序切分成N片，返回当前切片值，不支持ROWS_BETWEE

FIRST_VALUE()，LAST_VALUE()

• FIRST_VALUE(col)取分组排序后，截止到当前行，分组内第一个值
• LAST_VALUE(col)取分组排序后，截止到当前行，分组内的最后一个值
• 不支持WINDOW字句

LAG()、LEAD()

• LAG(col,n,DEFAULT) 统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
• LEAD(col,n,DEFAULT) 统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
• 不支持WINDOW字句

CUME_DIST()，PERCENT_RANK()

• CUME_DIST()返回（小于等于当前行值的行数）/（当前分组内的总行数）
• 应用场景：比如，统计小于等于当前薪水的人数，所占总人数的比例
• PERCENT_RANK()返回（组内当前行的rank值-1）/（分组内做总行数-1）
• 不支持WINDOW字句

 

注：https://blog.csdn.net/huangyinzhao/article/details/80507967                                                                                                             https://blog.csdn.net/weixin_38750084/article/details/82779910