SQL：统计每5min在线人数思路

需求如下：

目前有表：

用户开始游戏时间和游戏结束时间

如：         

用户id	游戏开始时间	游戏结束时间
000001	2022-06-01 00:36:33	2022-06-01 00:54:08

需要得到的数据：

时间	时刻	在线人数
2022-06-01	00:00:00	20
2022-06-01	00:05:00	22
2022-06-01	00:10:00	24
......
2022-06-30	00:55:00	20

每天每个小时有12条记录

思路：

 

可以看到 开始时间和结束时间判断对应的时间点是否在线的规则是不一样的。

判断游戏时长大于1h的为异常数据，已经过滤了游戏耗时大于1h的数据，所以目前数据的情况是

1，游戏开始时间和结束时间是在同一天，同一个小时内；

2，游戏开始时间和结束时间是同一天，不同小时内；

3，游戏开始时间和结束时间不是同一天，不同小时内；

故将以上三种情况做分析：

情况1：游戏开始时间和结束时间是在同一天，同一个小时内，则只需要膨胀游戏的开始时间得到相对应的时间点，再判断，这个点是否在开始时间和结束时间的区间内

 

情况2和情况3是一样的：游戏的开始时间和结束时间都需要分别去做膨胀，然后再去判断这个点是否在开始时间和结束时间的区间内 ，膨胀后的数据union all

最后把 以上两个 情况得到的结果 union all

SQL编写：

根据这两种情况去做写sql

从上思路中发现，我们需要先创建一个一小时12个点的表：

00:00，00:05，00:10，00:15，00:20，00:25，00:30，00:35，00:40，00:45，00:50，00:55

可以从现有的这个表去创造

用floor（）函数去判断，当前这个分钟属于哪个点 如 04分

floor(4/5)=0 ，floor(6/5)=1，floor(12/5)=2... 以此类推可以得到上面的点

create table time_table as 

select hour

, case 

when level = '0'  then '00:00'

when level = '1'  then '05:00'

when level = '2'  then '10:00'

when level = '3'  then '15:00'

when level = '4'  then '20:00'

when level = '5'  then '25:00'

when level = '6'  then '30:00'

when level = '7'  then '35:00'

when level = '8'  then '40:00'

when level = '9'  then '45:00'

when level = '10' then '50:00'

when level = '11' then '55:00'

end as min

from 

(

       select 

                substr(start_time,12,2) as hour

               ,floor(substr(start_time,15,2) / 5 ） as level

       from table_a 

    where dayno>=20220601 

         and dayno<=20220630

     group by 1,2

) t1

得到时间表之后，和源表关联

第一种情况

只需要关联开始时间的hour就可以，因为开始和结束时间都是同一个hour下，这样关联每一条记录会膨胀12条数据

t1 join t2

on    t1.hour = substr(t2.start_time,12,2)

聚合条件为

sum(case 
when unix_timestamp(point) - unix_timestamp(start_time) >= 0 and  unix_timestamp(point) - unix_timestamp(over_time) <= 0 then 1
else 0
end) as online_user

第二种情况

需要分成两段（开始时间一段，结束时间一段），然后再把两段union all起来

开始时间的关联

t1 join t2

on    t1.hour = substr(t2.start_time,12,2)

聚合条件为

sum(case 
when unix_timestamp(point) - unix_timestamp(start_time) >= 0 then 1
else 0
end) as online_user

结束时间的关联：

t1 join t2

on    t1.hour = substr(t2.over_time,12,2)

聚合条件为

sum(case when 
unix_timestamp(over_time) - unix_timestamp(point) >= 0 then 1
else 0
end) as online_user