关注+星标，干货第一时间送达
"3,2,1 上链接。。。"
"家人们，买买买。。。"
这一幕，简直不要太熟悉，
它已经完全充斥了包括小编在内的普通人们的日常生活了。
这就是"直播"。
一个看似早已出现，耳熟能详的东西，
却又在互联网顶峰的几年前，开始爆发出无与伦比的生机，
影响千千万万的商家和用户。
其带来的商业价值，
摧拉枯朽，扫荡一切。
至少目前来看，还没有其他的商机，
比得了"直播"。
今天，咱们要聊的就是"直播"中非常重要的一个数据指标：
"在线人数"，
是衡量一个直播间的流量和价值转换的重要因素。
一起来看看。
【温馨提示：建表语句及数据导入脚本，已经放在文章末尾】
一、需求场景
题目：
从直播间用户访问表live_events中，统计各个直播间最大同时在线人数。
直播间用户访问表：
正确结果：
二、解决方案
演示工具：Hive-3.1.3 + DataGrip2022
乍一看，不好求解的。
首先要思考：
怎么计算进入直播间，和退出直播间的人数变化呢？
怎么计算同时在线呢？
咱们可以换个思路，原有的数据列满足不了，
可以增加一列：change，
用来表示进入或者退出直播间的行为，
分别用 1 表示进入直播间，-1表示退出直播间。
然后，根据时间，进行正序累加，
便能计算出同时在线人数了。
最后，最大的同时在线人数，
取 max() 即可。
（1）采用union，补充增加的直播间进出行为指标
select user_id,live_id,in_datetime as dt, 1 as change
from live_events
union
select user_id,live_id,out_datetime as dt, -1 as change
from live_events
结果集：
（2）按照直播间live_id分组，时间戳排序，累计算出当前时间下，直播间最大人数
select live_id,user_id,
sum(change) over (partition by live_id order by dt) as people
from (
select user_id,live_id,in_datetime as dt, 1 as change
from live_events
union
select user_id,live_id,out_datetime as dt, -1 as change
from live_events
)t1
结果集：
代码中的：
sum(change) over (partition by live_id order by dt) as people
意思是：
计算当前分组内，最初行到当前行，change 列数据的和
代码完整的可以写成：
sum(change) over (partition by live_id order by dt rows between unbounded preceding and current_row ) as people
其他的函数，如: count()、avg() 等
都可以如此搭配 over() 开窗使用。
（3）按照直播间live_id分组，使用max() 函数，筛选直播间最大同时在线人数
select live_id,max(people) as mx_people
from (
select live_id,user_id,
sum(change) over (partition by live_id order by dt) as people
from (
select user_id,live_id,in_datetime as dt, 1 as change
from live_events
union
select user_id,live_id,out_datetime as dt, -1 as change
from live_events
)t1
)t2
group by live_id
结果集：
懂了不？
还是比较简单的。
三、源数据
直播间用户访问表live_events：
create table live_events
(
user_id int comment '用户id',
live_id int comment '直播id',
in_datetime string comment '进入直播间时间',
out_datetime string comment '离开直播间时间'
)
row format serde 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
stored as
inputformat 'org.apache.hadoop.mapred.TextInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location 'hdfs://mycluster/user/hive/warehouse/hql_test2.db/live_events'
tblproperties ('bucketing_version' = '2');
alter table live_events
set tblproperties ('comment' = '直播间访问记录');
INSERT INTO hql_test2.live_events (user_id, live_id, in_datetime, out_datetime) VALUES (100, 1, '2021-12-01 19:00:00', '2021-12-01 19:28:00');
INSERT INTO hql_test2.live_events (user_id, live_id, in_datetime, out_datetime) VALUES (100, 1, '2021-12-01 19:30:00', '2021-12-01 19:53:00');
INSERT INTO hql_test2.live_events (user_id, live_id, in_datetime, out_datetime) VALUES (100, 2, '2021-12-01 21:01:00', '2021-12-01 22:00:00');
INSERT INTO hql_test2.live_events (user_id, live_id, in_datetime, out_datetime) VALUES (101, 1, '2021-12-01 19:05:00', '2021-12-01 20:55:00');
INSERT INTO hql_test2.live_events (user_id, live_id, in_datetime, out_datetime) VALUES (101, 2, '2021-12-01 21:05:00', '2021-12-01 21:58:00');
INSERT INTO hql_test2.live_events (user_id, live_id, in_datetime, out_datetime) VALUES (102, 1, '2021-12-01 19:10:00', '2021-12-01 19:25:00');
INSERT INTO hql_test2.live_events (user_id, live_id, in_datetime, out_datetime) VALUES (102, 2, '2021-12-01 19:55:00', '2021-12-01 21:00:00');
INSERT INTO hql_test2.live_events (user_id, live_id, in_datetime, out_datetime) VALUES (102, 3, '2021-12-01 21:05:00', '2021-12-01 22:05:00');
INSERT INTO hql_test2.live_events (user_id, live_id, in_datetime, out_datetime) VALUES (104, 1, '2021-12-01 19:00:00', '2021-12-01 20:59:00');
INSERT INTO hql_test2.live_events (user_id, live_id, in_datetime, out_datetime) VALUES (104, 2, '2021-12-01 21:57:00', '2021-12-01 22:56:00');
INSERT INTO hql_test2.live_events (user_id, live_id, in_datetime, out_datetime) VALUES (105, 2, '2021-12-01 19:10:00', '2021-12-01 19:18:00');
INSERT INTO hql_test2.live_events (user_id, live_id, in_datetime, out_datetime) VALUES (106, 3, '2021-12-01 19:01:00', '2021-12-01 21:10:00');
OK，这就是本期的内容了，下期再见！
文章转载自皮皮克克，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
