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(partition by …order by …)

场景：支付时间间隔超过100天的用户数。【跨时间潜在VIP用户流失分析】

【拓展】

• 分组内排序后，截止到当前行的第一个值
  first_value(column) over(partition by … order by… rows …)

• 分组内排序后，截止到当前行的最后一个值
  last_value(column) over(partition by … order by… rows …)

练习题

1. 计算出每12个月的用户累计支付金额 -> 以年作为分区，进行每12个月的金额累计

-- 存在跨年份，但是没有对年份进行要求，故代码如下
select 
	a.month
	, a.pay_amount
	, sum(a.pay_amount) over(order by a.month rows between 11 preceding and current row) as sum_amount
from
(
	select substr(dt,1,7) month,sum(pay_amount) pay_amount
	from user_trade
	where dt > '0'
	group by substr(dt,1,7)
)a
;

2. 计算出每4个月的最大退款金额 -> 先得到每个月的最大退款额度，再从每四个月最大额度中选取最大值

--第一种理解：单笔最大
select 
	a.dt
	, a.refund_amount
	, max(a.refund_amount) over(order by a.dt rows between 3 preceding and current row) max_refund
from
(
	--每个月退款最大额
	select substr(dt,1,7) dt, max(refund_amount) refund_amount
	from user_refund
	where dt > '0' 
	group by substr(dt,1,7)
)a
;

-- 第二种理解：退款金额之和最大
select 
	a.dt
	, a.refund_amount
	, max(a.refund_amount) over(order by a.dt rows between 3 preceding and current row) max_refund
from
(
	--每个月退款最大额
	select substr(dt,1,7) dt, sum(refund_amount) refund_amount
	from user_refund
	where dt > '0' 
	group by substr(dt,1,7)
)a
;

3. 退款时间间隔最长的用户

--第一种理解（面试常规）：同一个用户，两次相邻退款间间隔
select b.user_name, b.refund_days
from (--where a.lag_dt is not null 因为所有用户第一次退款的时候，之前是没有数据的，所以会出现lag_dt为空，因此过滤了这部分数据
	 select a.user_name, datediff(a.dt,a.lag_dt) refund_days,
	 		rank() over(order by datediff(a.dt,a.lag_dt) desc) as rank
	 from
		(select user_name, dt, lag(dt) over(partition by user_name order by dt) lag_dt
		from user_refund
		where dt>'0' 
		)a
	where  a.lag_dt is not null
	)b  
where b.rank=1
;


select a.user_name, datediff(a.lead_dt,dt) as TimeInterval
from (--最后一次退款后，后面没有数据，因此lead_dt字段会出现空值，过滤这部分数据
	select user_name,dt,lead(dt) over (partition by user_name order by dt) lead_dt
	from user_refund
	where dt>"0"
	) a
where a.lead_dt is not null 
group by a.user_name, datediff(a.lead_dt,dt)
order by 2 desc limit 1
;


--第二种理解：退款时间 - 购买时间 (因按天算颗粒度过小，故改为计算小时数）
select d.user_name, d.timediff
from
(--时间间隔可能存在最大重复值，故采用dense_rank() 或rank()均可，只要同一编号可返回多个值即可
	select c.user_name, c.timediff, dense_rank() over(order by c.timediff desc) rank
	from
		(--两表中，user_name = user_name, piece = refund_piece, pay_amount = refund_amount
			select a.user_name, b.refund_time, a.pay_time, (hour(b.refund_time) - hour(a.pay_time) + (datediff(b.refund_time, a.pay_time)*24)) timediff
			from
			(
			select user_name, piece, pay_amount, from_unixtime(pay_time, 'yyyy-MM-dd HH:mm:ss') pay_time
			from user_trade
			where dt > '0'
			)a
		join
			(
			select user_name, refund_piece, refund_amount, refund_time
			from user_refund
			where dt > '0'
			)b
		on a.user_name = b.user_name
		and a.piece = b.refund_piece
		and a.pay_amount = b.refund_amount
	)c
)d
where d.rank = 1
;