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

发布于:2022-12-17 ⋅ 阅读:(179) ⋅ 点赞:(0)

需求如下:

目前有表:

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

如:         

用户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

本文含有隐藏内容,请 开通VIP 后查看

网站公告

今日签到

点亮在社区的每一天
去签到