SQL BOY YYDS! 石榴姐YYDS! Hive SQL同时在线问题分析

大数据真好玩

发布于 2021-07-30 10:22:22

1.4K0

文章被收录于专栏：暴走大数据暴走大数据

本文为《大数据成神之路-金牛宫》的群花「石榴姐yyds」原创，你可以在这里找到原文：https://blog.csdn.net/godlovedaniel/article/details/118651811。

石榴姐做为《大数据成神之路-金牛宫》的群花，已经在SQL BOY，哦不对，是SQL GIRL的路上越走越远了。

石榴姐YYDS！

本文是通过 SQL 计算同时在线问题，即求最高在线人数以及最高峰时间段。

需求分析

数据为主播ID,stt表示开播时间，edt表示下播时间。

求：

（1）该平台某一天主播同时在线人数最高为多少？
（2）出现最高峰的时间段是哪个时间?

数据准备

数据如下

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

建表

create table if not exists play(

id string,
stt string,
edt string

)

row format delimited
fields terminated by '\t'

;

加载数据

load data local inpath "/home/centos/dan_test/play.txt" into table play;
hive> select * from play;
OK
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
Time taken: 0.087 seconds, Fetched: 8 row(s)

数据分析

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

上述思路总结如下：

（1）将数据切分（按起始时间和结束时间）
（2）数据进行标签，开播的记录为记为1，下播的记录记为-1用于累加
（2）将数据按时间进行排序
（3）数据进入累加器进行累加
（4）获取累加器中当前累加值最大的数值

有了以上思路后，我们将其转换为SQL求解思路。

（1）将数据切分：实际上就是将开播时间和下播时间转换成一条条记录。也就是列转行，我们用熟悉的UNION操作，进行转换。

select id,stt dt from play
union
select id,edt dt from play

--------------------------------------------------------------------------------
OK
1001    2021-06-14 12:12:12
1001    2021-06-14 18:12:12
1001    2021-06-14 20:12:12
1001    2021-06-14 23:12:12
1002    2021-06-14 15:12:12
1002    2021-06-14 16:12:12
1003    2021-06-14 13:12:12
1003    2021-06-14 16:12:12
1004    2021-06-14 13:15:12
1004    2021-06-14 20:12:12
1005    2021-06-14 15:18:12
1005    2021-06-14 20:12:12
1006    2021-06-14 21:12:12
1006    2021-06-14 23:15:12
1007    2021-06-14 22:12:12
1007    2021-06-14 23:10:12
Time taken: 20.502 seconds, Fetched: 16 row(s)

(2) 数据标记。在上述SQL基础上直接进行标记即可.如果数据本来就是分开的则用case when进行标记。

select id,stt dt , 1 flag from play
union
select id,edt dt ,-1 flag from play

--------------------------------------------------------------------------------
OK
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
Time taken: 7.408 seconds, Fetched: 16 row(s)

（3）数据按照时间排序，进入累加器进行累加（按时间排序是累加的关键）

select id
     ,dt
     ,sum(flag) over(order by dt) as cur_cnt
from(
     select id,stt dt , 1 flag from play
     union
     select id,edt dt ,-1 flag from play
    ) t

--------------------------------------------------------------------------------
OK
1001    2021-06-14 12:12:12    1
1003    2021-06-14 13:12:12    2
1004    2021-06-14 13:15:12    3
1002    2021-06-14 15:12:12    4
1005    2021-06-14 15:18:12    5
1002    2021-06-14 16:12:12    3
1003    2021-06-14 16:12:12    3
1001    2021-06-14 18:12:12    2
1001    2021-06-14 20:12:12    1
1004    2021-06-14 20:12:12    1
1005    2021-06-14 20:12:12    1
1006    2021-06-14 21:12:12    2
1007    2021-06-14 22:12:12    3
1007    2021-06-14 23:10:12    2
1001    2021-06-14 23:12:12    1
1006    2021-06-14 23:15:12    0
Time taken: 8.133 seconds, Fetched: 16 row(s)

(4) 获取累加器中当前时刻累加的最大值，即为同时开播最多的人数

select max(cur_cnt)
from(
    select id
         ,dt
         ,sum(flag) over(order by dt) as cur_cnt
    from(
        select id,stt dt , 1 flag from play
        union
        select id,edt dt ,-1 flag from play
    ) t
) m

--------------------------------------------------------------------------------
OK
5
Time taken: 13.087 seconds, Fetched: 1 row(s)

问题2分析：

第二问求的是出现高峰时的时间段，也就是高峰时间的起始时间及结束时间，或持续时长。

借鉴第一问的结果进行分析：

select *,max(cur_cnt) over()
from(
     select id
           ,dt
           ,sum(flag) over(order by dt) as cur_cnt
     from(
          select id,stt dt , 1 flag from play
          union
          select id,edt dt ,-1 flag from play
     ) t
) m

通过上图我们可以看出当由峰值出的记录时间到下一条记录人数减少的时候这一段时间即为峰值持续的时间，或高峰的时间段，也就是求出峰值的下一条记录的时间与峰值对应记录的时间即为高峰时间段，因此利用lead()函数很容易求出问题的答案。SQL如下：

select max_cur_cnt 
      ,dt as start_time
       ,lead_dt as end_time
from(
     select *
            ,lead(dt,1,dt) over(order by dt) lead_dt
     from(
           select *,max(cur_cnt) over() as max_cur_cnt
           from(
                  select id
                           ,dt
                           ,flag
                           ,sum(flag) over(order by dt) as cur_cnt
                  from(
                          select id,stt dt , 1 flag from play
                          union
                          select id,edt dt ,-1 flag from play
                  ) t
           ) m
     ) n
) p
where cur_cnt=max_cur_cnt

计算结果如下：

小结

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

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

最后，放一张石榴姐侧颜背影杀照片：