HQL打卡第2题

需求

1. 表格数据

user_id	event_ts
A	1566300034
A	1566300044
A	1566300050
A	1566300150
A	1566300180
A	1566300300
B	1566300050
B	1566300060
B	1566300110
B	1566300210
B	1566300270
B	1566300295

2. 最终结果

user_id	event_ts	diff_ts	group_id
A	1566300034	0	1
A	1566300044	10	1
A	1566300050	6	1
A	1566300150	100	2
A	1566300180	30	2
A	1566300300	120	3
B	1566300050	0	1
B	1566300060	10	1
B	1566300110	50	1
B	1566300210	100	2
B	1566300270	60	3
B	1566300295	25	3

3. 最终结果字段解释

字段名	解释
DIFF_TS	同一用户 id 之间相邻 EVENT_TS 时间间隔
GROUP_ID	同一用户 id ，相邻时间间隔 > 50 的时候，分成一个小组

HQL 书写

1. 计算出每一行的 DIFF_TS

一、需要采用 Hive 的开窗函数，重点关注分区字段和排序字段:
1、分区字段: user_id
2、排序字段: event_ts
二、DIFF_TS 的计算
DIFF_TS 是用当前的时间戳减去上一行的时间戳，在 Hive 语法中可以使用 lag 函数。

lag(column, n, default_value)
表示取当前 column 的前 n 行的值，如果不存在则赋值为 default_value。

HQL语句如下所示:

select 
	user_id, 
	event_ts, 
	event_ts - (lag(event_ts, 1, event_ts) 
	over(partition by user_id order by event_ts)) as diff_ts
from 
	11_t_user_event;

上述表格的结果记为 subtable1

2. 当 DIFF_TS > 0 的时候，赋值为 1，否则赋值为 0

select 
	user_id, 
	event_ts, 
	diff_ts, 
	if(diff_ts > 50, 1, 0) as group_id_flag
from
	subtable1;

上述表格的结果记为 subtable2

3. 对于每个用户，GROUP_ID 的编号是从第一行到该行所有标号为 1 的个数加 1

select 
	user_id, 
	event_ts, 
	diff_ts, 
	sum(group_id_flag) 
	over(partition by user_id) + 1 as group_id
from
	subtable2;

最终 HQL 总结

select 
	user_id, 
	event_ts, 
	diff_ts, 
	sum(group_id_flag) 
	over(partition by user_id) + 1 as group_id
from
	(
	select 
		user_id, 
		event_ts, 
		diff_ts, 
		if(diff_ts > 50, 1, 0) as group_id_flag
	from
		(
		select 
	        user_id, 
	        event_ts, 
	        event_ts - (lag(event_ts, 1, event_ts) 
	        over(partition by user_id order by event_ts)) as diff_ts
        from 
	        user_event
        ) 
    as subtable1
    )
as subtable2;