一 同时在线人数问题
1 题目需求
现有各直播间的用户访问记录表（live_events）如下，表中每行数据表达的信息为，一个用户何时进入了一个直播间，又在何时离开了该直播间。
数据结构如下:
现要求统计各直播间最大同时在线人数，期望结果如下：
2 数据准备
-- 建表语句
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 '直播间访问记录';
-- 插入数据
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');
3 sql实现
1）将进入直播时间和离开直播时间使用flag标记起来，进入直播时间标记为1，离开直播时间标记为-1
select user_id
,live_id
,in_datetime as event_time
,1 as flag
from live_events
union
select user_id
,live_id
,out_datetime as event_time
,-1 as flag
from live_events
2）统计每个平台，当前时间的在线人数
select live_id
,event_time
,sum(flag) over(partition by live_id order by event_time) ct
from (
select user_id
,live_id
,in_datetime as event_time
,1 as flag
from live_events
union
select user_id
,live_id
,out_datetime as event_time
,-1 as flag
from live_events
)t1
3）统计每个平台同时在线最大人数
select live_id
,max(ct) max_online_ct
from (
select live_id
,event_time
,sum(flag) over(partition by live_id order by event_time) ct
from (
select user_id
,live_id
,in_datetime as event_time
,1 as flag
from live_events
union
select user_id
,live_id
,out_datetime as event_time
,-1 as flag
from live_events
)t1
)t2
group by live_id;
二 会话划分问题
1 题目需求
现有页面浏览记录表（page_view_events）如下，表中有每个用户的每次页面访问记录。
规定若同一用户的相邻两次访问记录时间间隔小于60s，则认为两次浏览记录属于同一会话。现有如下需求，为属于同一会话的访问记录增加一个相同的会话id字段，期望结果如下：
2 数据准备
-- 建表语句
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 '页面访问记录';
-- 插入数据
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);
3 sql实现
1）利用lag开窗函数，统计每个用户当前page_id时间与上个page_id时间的差值
select user_id
,page_id
,view_timestamp
,(view_timestamp - (lag(view_timestamp,1,0) over(partition by user_id order by view_timestamp))) as timestamp_diff
from page_view_events
2）如果相邻两个page_id的时间差大于60秒，标记为1，表示开始一个新的session，否则标记为0
select user_id
,page_id
,view_timestamp
,if(timestamp_diff > 60,1,0) as session_start_point
from (
select user_id
,page_id
,view_timestamp
,(view_timestamp - (lag(view_timestamp,1,0) over(partition by user_id order by view_timestamp))) as timestamp_diff
from page_view_events
) t1
3）按照user_id分组，view_timestamp排序，对session_start_point累积求和，开窗范围是过去所有时间到当前时间（range between unbouned preceding and current row ）
select user_id
,page_id
,view_timestamp
,concat(user_id,'-',sum(session_start_point) over(partition by user_id order by view_timestamp)) as session
from (
select user_id
,page_id
,view_timestamp
,if(timestamp_diff > 60,1,0) as session_start_point
from (
select user_id
,page_id
,view_timestamp
,(view_timestamp - (lag(view_timestamp,1,0) over(partition by user_id order by view_timestamp))) as timestamp_diff
from page_view_events
) t1
)t2
三 日期交叉问题
1 题目需求
现有各品牌优惠周期表（promotion_info）如下，其记录了每个品牌的每个优惠活动的周期，其中同一品牌的不同优惠活动的周期可能会有交叉。
现要求统计每个品牌的优惠总天数，若某个品牌在同一天有多个优惠活动，则只按一天计算。期望结果如下：
2 数据准备
-- 建表语句
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 '各品牌活动周期表';
-- 插入数据
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');
3 思路分析
对于像oppo品牌这样2次活动的时间时错开的，如图1所以，将2次的活动时间加起来就是该品牌的优惠活动时间，对于像redmi这样的品牌，如图2所示，在多次的活动中，有存在时间完全包含的，也有存在时间交叉的。
1）可以通过开窗函数，统计每次活动在历史时间到当前1行的最大结束时间，如果是该品牌第一次做活动，当前最大结束时间为空，新的开始时间就是当前开始时间
2）如果当前最大结束时间不为空，当前最大结束时间小于开始时间，表示和上次活动时间时错开的，新的开始时间也是当前的开始时间
3）如图2所示，redmi品牌的第二次活动时间全被包含在第一次的活动时间范围内，最大结束时间小于开始时间，将最大结束时间作为新的开始时间，但是新的开始时间大于结束时间，所以要过滤掉该活动记录
4）如图2所示，redmi品牌第三次活动时间与第一次存在交叉，最大结束时间大于开始时间，将最大结束时间+1天作为新的开始时间
图1
图2
4 sql实现
select brand
,sum(datediff(end_date,new_start_date) + 1) as promotion_day_count
from (
select brand
,if(max_end_date is null,start_date,if(start_date > max_end_date,start_date,date_add(max_end_date,1))) new_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 > new_start_date
group by brand
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
