大数据面试SQL(三):每分钟在线直播人数-LMLPHP

文章目录

每分钟在线直播人数

一、题目

二、分析

三、SQL实战

四、样例数据参考


每分钟在线直播人数

一、题目

有如下数据记录直播平台主播上播及下播时间,根据该数据计算出平台每分钟的在线直播人数。

这里用主播名称做统计,前提是主播名称唯一,不能出现重复,平台有名称重复验证。 

样例数据: 

大数据面试SQL(三):每分钟在线直播人数-LMLPHP

目标结果:

截取部分数据

大数据面试SQL(三):每分钟在线直播人数-LMLPHP

需要每分钟都要显示人数,哪怕只有0的数据也要显示,不能出现以下结果。

大数据面试SQL(三):每分钟在线直播人数-LMLPHP

二、分析

查询每分钟在线人数,这里处理逻辑基本和最高峰在线人数是一致的,但有一个问题,如果某一分钟内无任何操作记录,则不会出现该分钟的数据,我们就统计不到。所以这里额外考察了生成数据。

三、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

查询结果:

截取部分数据

大数据面试SQL(三):每分钟在线直播人数-LMLPHP

2、生成0~24*60-1条记录数据

查询语句:

select idx from (select posexplode(split(space(24*60),' ')) as (idx,value)) t;

查询结果:

截取部分数据

大数据面试SQL(三):每分钟在线直播人数-LMLPHP

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

查询结果:

截取部分数据

大数据面试SQL(三):每分钟在线直播人数-LMLPHP

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')
;

查询结果:

截取部分数据

大数据面试SQL(三):每分钟在线直播人数-LMLPHP

四、样例数据参考

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博客🙉
  • 📢停下休息的时候不要忘了别人还在奔跑,希望大家抓紧时间学习,全力奔赴更美好的生活✨
08-08 14:05