【HiveSql面试题】统计在线高峰期人数及出现高峰期的时间线

一、需求

id      start_time              end_time

1001	2021-12-01 08:08:05	    2021-12-01 19:02:07
1002	2021-12-01 08:15:07	    2021-12-01 19:05:10
1003	2021-12-01 09:10:20	    2021-12-01 21:02:07
1005	2021-12-01 09:26:05	    2021-12-01 19:52:15
1006	2021-12-01 10:08:05	    2021-12-01 22:05:06
1004	2021-12-01 10:20:07	    2021-12-01 18:03:05
1001	2021-12-01 20:08:05	    2021-12-01 23:02:07

        数据id为主播ID,start_time表示上播时间，end_time表示下播时间
        数据为某平台主播上下播时间表,统计出该平台主播同时在线高峰期人数及出现高峰期的时间线。

准备数据：

//创建表player
create table if not exists player(
	id string,
	start_time string,
	end_time string) 
row format delimited fields terminated by '\t'; 
//装载数据
load data local inpath '/opt/module/data/play1.txt' into table player;

二、解决思路

        本题如果直接从SQL本身很难下手，不妨我们换个思路，假定我们拿到的是一条数据，现在用java程序怎么做？其实就是一个累加器的思想（如SPARK的累加器）。首先我们需要将这样的的一条记录进行拆分，分成不同的记录或数据流进入累加器，然后给每条记录标记类型，此时的数据流按照时间顺序依次进入累加器，然后在累加器中进行叠加，其中累计的结果最大时候就是所求的结果。其实本质是利用累加器思想，但进入累加器的数据是按时间排好序的时序流数据（数据进入按时间先后顺序进入）。

        1）将数据按上播时间、下播时间切分，并标记上播、下播，然后用union all合并到一列，并按时间排序

        2）数据进入累加器进行累加算出该时间段在线人数,并补全该在线人数时间段的结束时间

        3）获取累加器中当前最大在线人数

        4）获取累加器中当前最大在线人数对应的数据

三、分步代码及测试

1.将数据按上播时间、下播时间切分，并标记上播、下播，然后用union合并到一列，并按时间排序

select 
	id,
	start_time at_time,
	'login' type
from player
union all
select
	id,
	end_time at_time,
	'logout' type
from player
order by at_time; t1

查询结果：

id      at_time             type

1001	2021-12-01 08:08:05	login 
1002	2021-12-01 08:15:07	login 
1003	2021-12-01 09:10:20	login 
1005	2021-12-01 09:26:05	login 
1006	2021-12-01 10:08:05	login 
1004	2021-12-01 10:20:07	login 
1004	2021-12-01 18:03:05	logout
1001	2021-12-01 19:02:07	logout
1002	2021-12-01 19:05:10	logout
1005	2021-12-01 19:52:15	logout
1001	2021-12-01 20:08:05	login 
1003	2021-12-01 21:02:07	logout 
1006	2021-12-01 22:05:06	logout 
1001	2021-12-01 23:02:07	logout 

2.数据进入累加器进行累加算出该时间段在线人数,并补全该在线人数时间段的结束时间

        该在线时间段在线人数：sum()开窗计算，type为login则+1，type为logout则-1，范围上无边界到当前行
        该在线人数时间段的结束时间：下一条数据时间即为本次时间段结束时间，使用lead()即可轻松算出，没有下一条数据则取本次数据时间

select
	at_time,
	lead(at_time,1,at_time) over(order by at_time) lead_time,
	sum(if(type = 'login',1,-1)) over(order by at_time rows between unbounded preceding and current row) online_ct
from t1; t2

查询结果：

at_time 	            lead_time 	            online_ct
2021-12-01 08:08:05		2021-12-01 08:15:07		1
2021-12-01 08:15:07		2021-12-01 09:10:20		2
2021-12-01 09:10:20		2021-12-01 09:26:05		3
2021-12-01 09:26:05		2021-12-01 10:08:05		4
2021-12-01 10:08:05		2021-12-01 10:20:07		5
2021-12-01 10:20:07		2021-12-01 18:03:05		6
2021-12-01 18:03:05		2021-12-01 19:02:07		5
2021-12-01 19:02:07		2021-12-01 19:05:10		4
2021-12-01 19:05:10		2021-12-01 19:52:15		3
2021-12-01 19:52:15		2021-12-01 20:08:05		2
2021-12-01 20:08:05		2021-12-01 21:02:07		3
2021-12-01 21:02:07		2021-12-01 22:05:06		2
2021-12-01 22:05:06		2021-12-01 23:02:07		1
2021-12-01 23:02:07		2021-12-01 23:02:07		0

3.获取累加器中当前最大在线人数

        按照在线人数倒序排序取第一条数据即为最大在线人数对应的数据

select
	max(online_ct) max_ct
from t2; 

查询结果：

max_ct
6

4.获取累加器中当前最大在线人数对应的数据

select
	at_time,
	lead_time,
	online_ct
from t2
where  t2.online_ct 
in (
    select
        max(online_ct) max_ct
    from t2
); 

查询结果：

at_time 	        lead_time 	        online_ct
2021-12-01 10:20:07	2021-12-01 18:03:05	6

四、最终代码及测试

with t2 as
(
    select
        at_time,
        lead(at_time, 1, at_time) over(order by at_time) lead_time,
        sum(if(type = 'login', 1, -1)) over(order by at_time rows between unbounded preceding and current row) online_ct
    from
        (
         select
             id,
             start_time at_time,
             'login'    type
         from player
         union all
         select
             id,
             end_time at_time,
             'logout' type
         from player
         order by at_time
        ) t1
)
select
    at_time,
    lead_time,
    online_ct
from t2
where t2.online_ct
in (
    select
        max(online_ct) max_ct
    from t2
);

测试结果：

at_time 	           lead_time           	  online_ct
2021-12-01 10:20:07    2021-12-01 18:03:05    6

五、小结

        本文针对SQL统计同时在线人数问题进行了分析，利用累加器思想对该问题进行求解，最终划归为时序数据，进行时序数据分析（常用技巧：打标签，形成序列，多序列进行分析），最后利用sum() over()对标签进行累加求出当前在线人数。本题最关键的点在于转换为时序数据及累加器的思想。

        事实上该问题的分析在业务上具有重要的意义，我们能够实时跟踪随着时间变化的在线人数，了解服务器的负载变化情况，服务器的实时并发数等。该问题在不同业务场景下，有不同意义，比如某个游戏的同时在线人数，比如某个服务器的实时并发数，比如某个仓库的货物积压数量，某一段时间内的同时处于服务过程中的最大订单量等。实际上求最大在线人数和求实时在线人数是一回事，最大人数依赖于当前在线人数表，只有先求出当前在线人数表，才能求出最大同时在线人数。