第 5 题 同时在线问题

1、题目要求

如下为某直播平台主播开播及关播时间，根据该数据计算出平台最高峰同时在线的主播人数

id        stt 上线              edt下线
1001;2021-06-14 12:12:12;2021-06-14 18:12:12
1003;2021-06-14 13:12:12;2021-06-14 16:12:12
1004;2021-06-14 13:15:12;2021-06-14 20:12:12
1002;2021-06-14 15:12:12;2021-06-14 16:12:12
1005;2021-06-14 15:18:12;2021-06-14 20:12:12
1001;2021-06-14 20:12:12;2021-06-14 23:12:12
1006;2021-06-14 21:12:12;2021-06-14 23:15:12
1007;2021-06-14 22:12:12;2021-06-14 23:10:12

2、建表和加载数据

create table if not exists test5(
id int,
stt string,
edt string
)row format delimited fields terminated by ";";


load data local inpath '/opt/test/t5.txt' overwrite into table test5;

3、分析

        这个题目需要有流式数据的概念，每来一条数据我们都记录一个状态，比如主播上线我们记录为1，主播下线记录为-1

如下图，每一个箭头都是主播的上线和下线时间，

         1）合并上线时间和下线时间（union），并标记上线为1，下线为-1

select id,stt as stt,1 as flag
from test5
union 
select id,edt as stt,-1 as flag
from test5;

        结果：

id	      stt	           flag
1001	2021-06-14 12:12:12	1
1001	2021-06-14 18:12:12	-1
1001	2021-06-14 20:12:12	1
1001	2021-06-14 23:12:12	-1
1002	2021-06-14 15:12:12	1
1002	2021-06-14 16:12:12	-1
1003	2021-06-14 13:12:12	1
1003	2021-06-14 16:12:12	-1
1004	2021-06-14 13:15:12	1
1004	2021-06-14 20:12:12	-1
1005	2021-06-14 15:18:12	1
1005	2021-06-14 20:12:12	-1
1006	2021-06-14 21:12:12	1
1006	2021-06-14 23:15:12	-1
1007	2021-06-14 22:12:12	1
1007	2021-06-14 23:10:12	-1

        2）使用窗口函数，求从第一行到当前行的sum

select
stt,
sum(flag) over(order by stt) cnt
from (
    select id,stt as stt,1 as flag
    from test5
    union 
    select id,edt as stt,-1 as flag
    from test5
)t1;

        结果：

stt	               cnt
2021-06-14 12:12:12	1
2021-06-14 13:12:12	2
2021-06-14 13:15:12	3
2021-06-14 15:12:12	4
2021-06-14 15:18:12	5
2021-06-14 16:12:12	3
2021-06-14 16:12:12	3
2021-06-14 18:12:12	2
2021-06-14 20:12:12	1
2021-06-14 20:12:12	1
2021-06-14 20:12:12	1
2021-06-14 21:12:12	2
2021-06-14 22:12:12	3
2021-06-14 23:10:12	2
2021-06-14 23:12:12	1
2021-06-14 23:15:12	0

        3）找出同时在线人数最大值

select
max(cnt)
from (
    select
        stt,
        sum(flag) over(order by stt) cnt
    from (
        select id,stt as stt,1 as flag
        from test5
        union 
        select id,edt as stt,-1 as flag
        from test5
    )t1
)t2;

4、扩展一下，如果题目换成求什么时间段同时在线的人最多？

        1、首先时间段应该该是最多人到达后开始，到有人开始下线的这段时间，（思考会出现两条不同时间连续的数据在线人数相同吗？），

        2、比如实际情况会出现第三条数据吗？肯定不会，因为没有主播上下线，所以不会出现，所以当第二条数据是最大在线人数的时候，那么它的下一条数据一定有主播下线，所以拿到它的下一条数据的时间那就能够得到最大人数的时间段

2021-06-14 15:12:12	4
2021-06-14 15:18:12	5

2021-06-14 15:20:12	5

2021-06-14 16:12:12	3
2021-06-14 16:12:12	3
2021-06-14 18:12:12	2