HQL练习进阶

第1题 同时在线人数问题

1.1 题目需求

现有各直播间的用户访问记录表（live_events）如下，表中每行数据表达的信息为，一个用户何时进入了一个直播间，又在何时离开了该直播间。

user_id (用户id)	live_id (直播间id)	in_datetime (进入直播间的时间)	out_datetime (离开直播间的时间)
100	1	2021-12-1 19:30:00	2021-12-1 19:53:00
100	2	2021-12-1 21:01:00	2021-12-1 22:00:00
101	1	2021-12-1 19:05:00	2021-12-1 20:55:00

现要求统计各直播间最大同时在线人数，期望结果如下：

live_id	max_user_count
1	4
2	3
3	2

1.2 数据准备

1)建表语句

drop table if exists live_events;

create table if not exists live_events

(

user_id int comment '用户id',

live_id int comment '直播id',

in_datetime string comment '进入直播间时间',

out_datetime string comment '离开直播间时间'

)

comment '直播间访问记录';

2）数据装载

INSERT overwrite table live_events

VALUES (100, 1, '2021-12-01 19:00:00', '2021-12-01 19:28:00'),

(100, 1, '2021-12-01 19:30:00', '2021-12-01 19:53:00'),

(100, 2, '2021-12-01 21:01:00', '2021-12-01 22:00:00'),

(101, 1, '2021-12-01 19:05:00', '2021-12-01 20:55:00'),

(101, 2, '2021-12-01 21:05:00', '2021-12-01 21:58:00'),

(102, 1, '2021-12-01 19:10:00', '2021-12-01 19:25:00'),

(102, 2, '2021-12-01 19:55:00', '2021-12-01 21:00:00'),

(102, 3, '2021-12-01 21:05:00', '2021-12-01 22:05:00'),

(104, 1, '2021-12-01 19:00:00', '2021-12-01 20:59:00'),

(104, 2, '2021-12-01 21:57:00', '2021-12-01 22:56:00'),

(105, 2, '2021-12-01 19:10:00', '2021-12-01 19:18:00'),

(106, 3, '2021-12-01 19:01:00', '2021-12-01 21:10:00');

1.3 思路分析

每有一名用户进入，直播间人数加1，每有一名用户离开，直播间人数减1。根据这一规律，我们可以将直播间的登入登出操作分离，用额外的字段记录人数变化，然后借助开窗函数按照时间升序排列，计算截止当前行（当前时间）的直播间人数，最后取最大值即可。

第一步：分离登入登出操作（t1子查询）

① 取每条数据的user_id，live_id，in_datetime字段，补充user_change字段，取值为1。

② 取每条数据的user_id，live_id，out_datetime字段，补充user_change字段，取值-1。

③ 将①和②的结果通过union联合在一起，结果作为子查询t1。同一用户不可能在同一时间多次登入或登出同一直播间，因而数据不会重复，union all和union结果相同。此处使用union all。

第二步：计算截至当前行的直播间人数（t2子查询）。

使用开窗函数，按照live_id分区、event_time升序排列。

调用sum(user_change)即可计算累计人数，获得的结果作为子查询t2。窗口范围为默认值unbound preceding至current row，正好符合题目需求。

第三步：针对子查询t2，按照live_id分区，取user_count的最大值即为各直播间最大同时在线人数。

1.4 代码实现

select

live_id,

max(user_count) max_user_count

from

(

select

user_id,

live_id,

sum(user_change) over(partition by live_id order by event_time) user_count

from

(

select user_id,

live_id,

in_datetime event_time,

1 user_change

from live_events

union all

select user_id,

live_id,

out_datetime,

-1

from live_events

)t1

)t2

group by live_id;

第2题 会话划分问题

2.1 题目需求

现有页面浏览记录表（page_view_events）如下，表中有每个用户的每次页面访问记录。

user_id	page_id	view_timestamp
100	home	1659950435
100	good_search	1659950446
100	good_list	1659950457
100	home	1659950541
100	good_detail	1659950552
100	cart	1659950563
101	home	1659950435
101	good_search	1659950446
101	good_list	1659950457
101	home	1659950541
101	good_detail	1659950552
101	cart	1659950563
102	home	1659950435
102	good_search	1659950446
102	good_list	1659950457
103	home	1659950541
103	good_detail	1659950552
103	cart	1659950563

规定若同一用户的相邻两次访问记录时间间隔小于60s，则认为两次浏览记录属于同一会话。现有如下需求，为属于同一会话的访问记录增加一个相同的会话id字段，期望结果如下：

user_id	page_id	view_timestamp	session_id
100	home	1659950435	100-1
100	good_search	1659950446	100-1
100	good_list	1659950457	100-1
100	home	1659950541	100-2
100	good_detail	1659950552	100-2
100	cart	1659950563	100-2
101	home	1659950435	101-1
101	good_search	1659950446	101-1
101	good_list	1659950457	101-1
101	home	1659950541	101-2
101	good_detail	1659950552	101-2
101	cart	1659950563	101-2
102	home	1659950435	102-1
102	good_search	1659950446	102-1
102	good_list	1659950457	102-1
103	home	1659950541	103-1
103	good_detail	1659950552	103-1

2.2 数据准备

1)建表语句

drop table if exists page_view_events; create table if not exists page_view_events ( user_id int comment '用户id', page_id string comment '页面id', view_timestamp bigint comment '访问时间戳' ) comment '页面访问记录';

2）数据装载

insert overwrite table page_view_events values (100, 'home', 1659950435), (100, 'good_search', 1659950446), (100, 'good_list', 1659950457), (100, 'home', 1659950541), (100, 'good_detail', 1659950552), (100, 'cart', 1659950563), (101, 'home', 1659950435), (101, 'good_search', 1659950446), (101, 'good_list', 1659950457), (101, 'home', 1659950541), (101, 'good_detail', 1659950552), (101, 'cart', 1659950563), (102, 'home', 1659950435), (102, 'good_search', 1659950446), (102, 'good_list', 1659950457), (103, 'home', 1659950541), (103, 'good_detail', 1659950552), (103, 'cart', 1659950563);

2.3 思路分析

只要同一用户的两条页面浏览记录的时间差小于60秒就被归为一个会话。显然，首先要获得用户每次浏览和上次浏览的时间差，可以借助lag函数实现。

页面可以被分为两类：会话起始页和非起始页。前者与上次浏览的时间差大于等于60秒，后者与上次浏览的时间差小于60秒。根据这个特点可以将二者区分开来。

session_id由user_id和一个递增序号拼接而成，会话起始时间越大，序号越大，且同一会话所有页面的序号相同。如果将所有页面浏览记录按照访问时间升序排列，序号只有在遇到会话起始页时需要加一。由此，引出本题的解决思路：根据当前浏览记录和上次浏览记录的时间差的不同，区分会话起始页和非起始页。定义列session_start_point，会话起始页赋值为1，非起始页为0。

使用开窗函数，按照user_id分区、view_timestamp升序排列，统计截至当前行的session_start_point列的和，与user_id拼接在一起即可得到session_id。

第一步：获取上次页面浏览的访问时间（t1子查询）

开窗，按照user_id分区、view_timestamp升序排列，调用lag函数获取上一条数据view_timestamp的值。要注意，获取用户首次浏览的上次访问时间，返回值默认为null，为便于计算，此处赋默认值0，此时当前行减去上一行远大于60s，首次访问必然会被视为会话的起始页面，与事实相符。

第二步：计算session_start_point（t2子查询）。

第三步：拼接得到session_id（最终结果）。

2.4 代码实现

select user_id, page_id, view_timestamp, concat(user_id, '-', sum(session_start_point) over (partition by user_id order by view_timestamp)) session_id from ( select user_id, page_id, view_timestamp, if(view_timestamp - lagts >= 60, 1, 0) session_start_point from ( select user_id, page_id, view_timestamp, lag(view_timestamp, 1, 0) over (partition by user_id order by view_timestamp) lagts from page_view_events ) t1 ) t2;

第3题 间断连续登录用户问题

3.1 题目需求

现有各用户的登录记录表（login_events）如下，表中每行数据表达的信息是一个用户何时登录了平台。

user_id	login_datetime
100	2021-12-01 19:00:00
100	2021-12-01 19:30:00
100	2021-12-02 21:01:00

现要求统计各用户最长的连续登录天数，间断一天也算作连续，例如：一个用户在1,3,5,6登录，则视为连续6天登录。期望结果如下：

user_id	max_day_count
100	3
101	6
102	3
104	3
105	1

3.2 数据准备

1)建表语句

drop table if exists login_events; create table if not exists login_events ( user_id int comment '用户id', login_datetime string comment '登录时间' ) comment '直播间访问记录';

2）数据装载

INSERT overwrite table login_events VALUES (100, '2021-12-01 19:00:00'), (100, '2021-12-01 19:30:00'), (100, '2021-12-02 21:01:00'), (100, '2021-12-03 11:01:00'), (101, '2021-12-01 19:05:00'), (101, '2021-12-01 21:05:00'), (101, '2021-12-03 21:05:00'), (101, '2021-12-05 15:05:00'), (101, '2021-12-06 19:05:00'), (102, '2021-12-01 19:55:00'), (102, '2021-12-01 21:05:00'), (102, '2021-12-02 21:57:00'), (102, '2021-12-03 19:10:00'), (104, '2021-12-04 21:57:00'), (104, '2021-12-02 22:57:00'), (105, '2021-12-01 10:01:00');

3.3 思路分析

（1）知识储备

last_value(a, true)：获取当前窗口内最后一条数据a字段的值，true表示忽略null值。

（2）执行步骤

与上一题相同，本题也是会话划分问题。使用开窗函数获取上次登录日期，通过当前日期与上次登录日期的差值划分连续登录会话。然后按照会话分组，求得每个会话的首次登录日期和末次登录日期，即可获得持续天数，最后取所有会话持续天数的最大值即可。

第一步：将粒度聚合为user_id+login_dt（t1子查询）

按照user_id和格式化后的login_dt分组，取分区字段即可。

第二步：计算本次登录日期和上次登录日期的天数差diff列（t2子查询）

① 通过开窗函数lag获取同一用户每个登录日期的上次登录日期。

② 计算本次日期和上次日期的天数差。要注意，首次登录的上次登录日期为null，如果直接调用datediff求解会抛出异常，所以在使用lag函数时要赋默认值，赋默认值为“0000-00-00”，默认值的选择不能影响结果的正确性，下文详解。

第三步：划分会话。

上一题我们介绍了一种划分会话的方式，此处介绍第二种。

对diff列的值进行判断，根据判断结果为dt列赋值。如果diff大于2，则当天为会话的起始登录日期，将dt赋值为当日，否则为null。

针对上述查询结果执行开窗函数，按照user_id分区、login_dt升序排列，调用last_value函数取当前窗口范围内dt列的最后一条不为null的数据，这样一来处于相同会话的数据，返回值也会相同，记为group_dt。

显然，每个用户首次登录日期一定为登录会话的起始日期，只要保证它与上次日期的天数差大于2即可，因此上一步将lag函数的默认值定为“0000-00-00”是可行的。

第四步：最终结果。

按照user_id和group_dt分组，计算会话内最大登录日期和最小登录日期的天数差，再加1，即为该会话的持续天数，最后取同一用户所有会话持续天数的最大值即为最终结果。

3.4 代码实现

select

user_id,

max(recent_days) max_recent_days --求出每个用户最大的连续天数

from

(

select

user_id,

user_flag,

datediff(max(login_date),min(login_date)) + 1 recent_days --按照分组求每个用户每次连续的天数(记得加1)

from

(

select

user_id,

login_date,

lag1_date,

concat(user_id,'_',flag) user_flag --拼接用户和标签分组

from

(

select

user_id,

login_date,

lag1_date,

sum(if(datediff(login_date,lag1_date)>2,1,0)) over(partition by user_id order by login_date) flag --获取大于2的标签

from

(

select

user_id,

login_date,

lag(login_date,1,'1970-01-01') over(partition by user_id order by login_date) lag1_date --获取上一次登录日期

from

(

select

user_id,

date_format(login_datetime,'yyyy-MM-dd') login_date

from login_events

group by user_id,date_format(login_datetime,'yyyy-MM-dd') --按照用户和日期去重

)t1

)t2

)t3

)t4

group by user_id,user_flag

)t5

group by user_id;

第4题 日期交叉问题

4.1 题目需求

现有各品牌优惠周期表（promotion_info）如下，其记录了每个品牌的每个优惠活动的周期，其中同一品牌的不同优惠活动的周期可能会有交叉。

promotion_id	brand	start_date	end_date
1	oppo	2021-06-05	2021-06-09
2	oppo	2021-06-11	2021-06-21
3	vivo	2021-06-05	2021-06-15

现要求统计每个品牌的优惠总天数，若某个品牌在同一天有多个优惠活动，则只按一天计算。期望结果如下：

brand	promotion_day_count
vivo	17
oppo	16
redmi	22
huawei	22

4.2 数据准备

1)建表语句

drop table if exists promotion_info;

create table promotion_info

(

promotion_id string comment '优惠活动id',

brand string comment '优惠品牌',

start_date string comment '优惠活动开始日期',

end_date string comment '优惠活动结束日期'

) comment '各品牌活动周期表';

2）数据装载

insert overwrite table promotion_info values (1, 'oppo', '2021-06-05', '2021-06-09'), (2, 'oppo', '2021-06-11', '2021-06-21'), (3, 'vivo', '2021-06-05', '2021-06-15'), (4, 'vivo', '2021-06-09', '2021-06-21'), (5, 'redmi', '2021-06-05', '2021-06-21'), (6, 'redmi', '2021-06-09', '2021-06-15'), (7, 'redmi', '2021-06-17', '2021-06-26'), (8, 'huawei', '2021-06-05', '2021-06-26'), (9, 'huawei', '2021-06-09', '2021-06-15'), (10, 'huawei', '2021-06-17', '2021-06-21');

4.3 思路分析

分析题意，只要调整区间范围，使得区间不存在交叉，统计每个区间的持续天数，最后对天数求和即可。

第一步：获取截至当前的历史活动的最大结束日期（t1子查询）

要找到所有存在交叉的区间，只要判断每个区间和历史区间是否存在交叉即可。而某个区间和历史区间存在交叉，等价于该区间历史活动end_date的最大值大于等于当前活动的start_date。因此，首先需要获取截至当前的历史活动的end_date的最大值。

使用开窗函数，按照brand分区、start_date排序，限定窗口范围为第一行至当前行的前一行，调用max函数计算结束日期end_date的最大值即可。

第二步：处理交叉区间（t2子查询）。

对t1子查询的max_end_date列进行判断，max_end_date列存在以下三种情况。

①max_end_date为空，说明是该品牌的第一次促销活动，start_date不变。

②max_end_date小于start_date，说明本次活动区间与历史活动无交叉，start_date不变。

③max_end_date大于start_date，存在区间交叉，将start_date赋值为max_end_date加1。

经过上述处理，交叉区间的交集为空，并集连续，结果作为t2子查询。

第三步：最终结果

区间交叉有一种特殊情况，即某个区间被另外的区间包含，此时经过第二步的处理，该区间的start_date会大于end_date，这部分活动区间是可以舍弃的，因为该活动的所有日期都会被其它活动区间统计。因此，这里筛选start_date小于等于end_date的区间，使用datediff函数计算每个期间的持续天数，最后按照brand分组求和即可。

4.4 代码实现

select brand, sum(datediff(end_date,start_date)+1) promotion_day_count from ( select brand, max_end_date, if(max_end_date is null or start_date>max_end_date,start_date,date_add(max_end_date,1)) start_date, end_date from ( select brand, start_date, end_date, max(end_date) over(partition by brand order by start_date rows between unbounded preceding and 1 preceding) max_end_date from promotion_info )t1 )t2 where end_date>start_date group by brand;