Hive SQL业务场景：求平台最高峰同时直播人数

一、需求描述

现有某直播平台各主播登陆明细表：主播ID，上线时间，下线时间。

现在需要求出该直播平台最高峰期同时在线主播人数。

二、数据准备

-- 建表
create table if not exists dwd_user_login_dtl (
  `id` bigint comment '用户id',
  `start_time` string comment '上线时间',
  `end_time` string comment '下线时间'
) comment '用户上下线时间测试'

-- 插入样例数据
INSERT INTO dwd_user_login_dtl (`id`, `start_time`, `end_time`) VALUES  
(1, '2014-10-01 08:00:00', '2014-10-01 10:00:00'),  
(2, '2014-10-01 09:30:00', '2014-10-01 11:30:00'),  
(3, '2014-10-01 10:15:00', '2014-10-01 11:15:00'),  
(4, '2014-10-01 11:00:00', '2014-10-01 12:00:00'),  
(5, '2014-10-01 12:00:00', '2014-10-01 13:00:00'),  
(6, '2014-10-01 08:30:00', '2014-10-01 12:30:00'),  
(7, '2014-10-01 11:15:00', '2014-10-01 13:15:00'),  
(8, '2014-10-01 13:00:00', '2014-10-01 14:00:00'),  
(9, '2014-10-01 14:45:00', '2014-10-01 15:45:00'),  
(10, '2014-10-01 14:30:00', '2014-10-01 15:30:00'),  
(11, '2014-10-01 09:45:00', '2014-10-01 12:45:00'),  
(12, '2014-10-01 09:30:00', '2014-10-01 10:30:00'),  
(13, '2014-10-01 10:15:00', '2014-10-01 11:15:00'),  
(14, '2014-10-01 14:00:00', '2014-10-01 15:00:00'),  
(15, '2014-10-01 15:00:00', '2014-10-01 16:00:00'),  
(16, '2014-10-01 11:00:00', '2014-10-01 13:00:00'),  
(1, '2014-10-01 16:45:00', '2014-10-01 18:45:00'),  
(2, '2014-10-01 15:30:00', '2014-10-01 17:30:00'),  
(3, '2014-10-01 15:15:00', '2014-10-01 16:15:00'),  
(4, '2014-10-01 14:00:00', '2014-10-01 16:00:00');

三、需求分析

1. 对每个主播上线时间和下线时间两个字段组成一个字段，增加状态值字段（上线：1，下线：-1）
2. 然后通过开窗函数，按时间排序求和聚合
3. 最后求出最大值

四、需求实现

对每个主播上线时间和下线时间两个字段组成一个字段，增加状态值字段（上线：1，下线：-1）

select id
       ,start_time as log_time
       ,1 as flag
from dwd_user_login_dtl
union all 
select id
        ,end_time as log_time
        ,-1 as flag
from dwd_user_login_dtl

然后通过开窗函数，按时间排序求和聚合

select id
       ,log_time
       ,flag
       ,sum(flag) over(order by log_time) as acc_login 
from (
      select id
             ,start_time as log_time
             ,1 as flag
      from dwd_user_login_dtl
      union all 
      select id
              ,end_time as log_time
              ,-1 as flag
      from dwd_user_login_dtl
) a
order by log_time

求出同时在线人数最大值

select max(acc_login) as max_acc_login 
from (
      select id
             ,log_time
             ,flag
             ,sum(flag) over(order by log_time) as acc_login 
      from (
            select id
                   ,start_time as log_time
                   ,1 as flag
            from dwd_user_login_dtl
            union all 
            select id
                    ,end_time as log_time
                    ,-1 as flag
            from dwd_user_login_dtl
      ) a
      order by log_time
) b