文章目录
每分钟在线直播人数
一、题目
有如下数据记录直播平台主播上播及下播时间,根据该数据计算出平台每分钟的在线直播人数。
这里用主播名称做统计,前提是主播名称唯一,不能出现重复,平台有名称重复验证。
样例数据:
目标结果:
截取部分数据
需要每分钟都要显示人数,哪怕只有0的数据也要显示,不能出现以下结果。
二、分析
查询每分钟在线人数,这里处理逻辑基本和最高峰在线人数是一致的,但有一个问题,如果某一分钟内无任何操作记录,则不会出现该分钟的数据,我们就统计不到。所以这里额外考察了生成数据。
三、SQL实战
1、首先对原始数据进行处理,生成主播上下播的日志数据,同时增加人数变化字段,主播上播为1,主播下播-1。新数据包含 user_name,action_time,change_cnt。
查询语句:
--开播记录
select
user_name,
start_time as action_time,
1 as change_cnt
from t2_livestream_log
union all
--下播记录
select
user_name,
end_time as action_time,
-1 as change_cnt
from t2_livestream_log
查询结果:
截取部分数据
2、生成0~24*60-1条记录数据
查询语句:
select idx from (select posexplode(split(space(24*60),' ')) as (idx,value)) t;
查询结果:
截取部分数据
3、根据以上SQL生成每分钟一条记录的心跳记录,心跳记录change_cnt= 0,代表没有主播上播,也没有主播下播。
查询语句:
SELECT
'' as user_name,
from_unixtime(unix_timestamp('2024-06-29','yyyy-MM-dd')+item*60,'yyyy-MM-dd HH:mm:ss') as action_time,
0 as change_cnt
from (select posexplode(split(space(24*60),' ')) as (item,value)) t
union all
SELECT
'' as user_name,
from_unixtime(unix_timestamp('2024-06-30','yyyy-MM-dd')+item*60,'yyyy-MM-dd HH:mm:ss') as action_time,
0 as change_cnt
from (select posexplode(split(space(24*60),' ')) as (item,value)) t
查询结果:
截取部分数据
4、汇总所有数据之后,对change_cnt累积求和,然后求出每分钟的最大值即可。
查询语句:
with t_all as(
--开播记录
select
user_name,
start_time as action_time,
1 as change_cnt
from t2_livestream_log
union all
--下播记录
select
user_name,
end_time as action_time,
-1 as change_cnt
from t2_livestream_log
--心跳数据
union all
SELECT
'' as user_name,
from_unixtime(unix_timestamp('2024-06-29','yyyy-MM-dd')+item*60,'yyyy-MM-dd HH:mm:ss') as action_time,
0 as change_cnt
from (select posexplode(split(space(24*60),' ')) as (item,value)) t
union all
SELECT
'' as user_name,
from_unixtime(unix_timestamp('2024-06-30','yyyy-MM-dd')+item*60,'yyyy-MM-dd HH:mm:ss') as action_time,
0 as change_cnt
from (select posexplode(split(space(24*60),' ')) as (item,value)) t
)
select
date_format(action_time,'yyyy-MM-dd HH:mm') as act_minute,
max(online_cnt) as minute_max_cnt
from (select user_name,
action_time,
change_cnt,
sum(change_cnt) over (order by action_time asc) online_cnt
from t_all
)t1
group by date_format(action_time,'yyyy-MM-dd HH:mm')
;
查询结果:
截取部分数据
四、样例数据参考
CREATE TABLE IF NOT EXISTS t2_livestream_log (
user_name STRING, -- 主播名称
start_time STRING, -- 开始时间
end_time STRING -- 结束时间
);
insert into t2_livestream_log(user_name, start_time, end_time) values
('亚瑟','2024-06-29 01:00:00','2024-06-29 02:01:05'),
('曹操','2024-06-29 01:05:00','2024-06-29 02:03:18'),
('孙悟空','2024-06-29 02:00:00','2024-06-29 04:03:22'),
('花木兰','2024-06-29 03:15:07','2024-06-29 04:33:21'),
('露娜','2024-06-29 03:34:16','2024-06-29 06:10:45'),
('云缨','2024-06-29 05:22:00','2024-06-29 07:01:08'),
('姬小满','2024-06-29 06:11:03','2024-06-29 09:26:05'),
('哪吒','2024-06-29 08:00:00','2024-06-29 12:34:27'),
('亚瑟','2024-06-29 11:00:00','2024-06-29 16:03:18'),
('沈梦溪','2024-06-29 15:00:00','2024-06-29 17:01:05'),
('姜子牙','2024-06-30 01:00:00','2024-06-30 02:01:05'),
('高渐离','2024-06-30 01:05:00','2024-06-30 02:03:18'),
('张良','2024-06-30 02:00:00','2024-06-30 04:03:22'),
('甄姬','2024-06-30 03:15:07','2024-06-30 04:33:21'),
('金蝉','2024-06-30 03:34:16','2024-06-30 06:10:45'),
('鲁班七号','2024-06-30 05:22:00','2024-06-30 07:01:08'),
('后羿','2024-06-30 06:11:03','2024-06-30 09:26:05'),
('哪吒','2024-06-30 08:00:00','2024-06-30 12:34:27'),
('鲁班七号','2024-06-30 11:00:00','2024-06-30 16:03:18'),
('后羿','2024-06-30 15:00:00','2024-06-30 17:01:05');
- 📢博客主页:https://lansonli.blog.csdn.net
- 📢欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正!
- 📢本文由 Lansonli 原创,首发于 CSDN博客🙉
- 📢停下休息的时候不要忘了别人还在奔跑,希望大家抓紧时间学习,全力奔赴更美好的生活✨