HIVE - 窗口/分析函数 Window Function

窗口函数（Window Function）：

• 每一行数据生成一个结果，即返回多个结果。
• 聚合函数（sum,avg,max…）可以将多行数据按照规定聚合为一行，一般来说聚合后的行数少于聚合前的函数。
• 但是，如果既想要展示聚合前的数据，又要展示聚合后的数据，则需要使用窗口函数。
• 窗口函数是在select时执行，执行顺序位于order by之前。

窗口函数over( )

over (partition by column_name order by column_name rows between start_pos and end_pos)
包含三个分析子句：

• partition by： 可以理解为group by，以哪些字段分区/分组
• order by：以那些字段排序，顺序是升序还是降序
• rows between…and…： 指定聚合计算移动的行范围（M行 -> N行），用来灵活控制窗口的子集
  ——包括本行和之前的所有行
  rows between unbounded preceding and current row
  ——包括本行和之后的所有行
  rows between current row and unbounded following
  ——包括本行在内的和前三行
  rows between 3 preceding and current row
  ——包括本行在内的前三行和后一行
  rows between 3 preceding and 1 following

窗口函数结合分析函数使用及其场景案例

聚合计算：sum/avg/min/max/count

A: 需要被加工的字段名称
B: 分组的字段名称
C: 排序的字段名称
D: 计算的行数范围

• sum(…A…) over(partition by …B… order by …C… rows between…D1… and …D2…)

场景：对2017和2018年公司的支付总额 按月度累计进行分析，按年度进行汇总。

• count(…A…) over(partition by …B… order by …C… rows between …D1… and …D2…)

场景：对2018年每个月的近三个月进行移动地求平均支付金额。

【拓展】

• max(…A…) over(partition by …B… order by …C… rows between…D1… and …D2…)

• min(…A…) over(partition by …B… order by …C… rows between …D1… and …D2…)

分区排序窗口函数

A: 分区的字段名称
B: 排序的字段名称

1. 为查询出来的每一行生成一个序号，依次排序且不会重复 1,2,3,4,5,6,7
   row_number() over(partition by …A… order by …B…)
2. 中式排名：出现相同排名，将不跳过相同的排名号 1,1,2,3,4,5,5,5…
   dense_rank() over(partition by …A… order by …B…)
3. 紧接上一次的rank值，跳跃排序 1,1,3,4,5,5,5,8
   rank() over(partition by …A… order by …B…)

场景：对2019年1月份用户的购买爱好（购买商品品类的数量）进行分析。
场景：提取Top N 的用户 -> 通过排序函数添加过滤条件即可：where rank <= N

【拓展】占比：
前N行占比 = （小于等于当前值的行数/分组内总行数）%
cume_dist() over(partition by … order by …)

前N行占比 = （分组内当前行的RANK值-1/分组内总行数-1）%
当前行rank值采用dense_rank中式排名计算，即会出现多个排名相同的值
percent_rank() over(partition by … order by…)

平均分组排序窗口函数

n: 切片的片数
A: 分组的字段名称
B: 排序的字段名称

1. ntile(n) over(partition by …A… order by …B…)
2. 将分组的数据按照顺序切分成n片，返回当前切片值；如果切片不均匀，默认依次增加前M个切片内数据的个数，已达到后续整除的目的
3. 不支持rows between…

场景：选出2019年退款金额排名前10%的用户。

【思考？】：相比于拆分成10组，拆分成100组会不会有更高的精度或更快的运算速度？

偏移分析窗口函数

exp_str: 字段名称
offset：偏移量，即上1个或上N个的值，默认值为1
defval：当向前或向后取N行超出表的范围时，返回该默认值，否则返回NULL

• lag(exp_str, offset, defval) over(partition by …order by …)
• lead(exp_str, offset, defval) over(