want to use sql count online people with begintime and endtime

I want to use sql count online people with begintime and endtime on presto。

my data like ：

userid  begintime   endtime
023150000030040 2020-03-05 12:50:46 2020-03-05 12:50:49
023150004186637 2020-03-05 10:31:19 2020-03-05 10:31:24
023150000788581 2020-03-05 00:59:01 2020-03-05 01:02:00
023150004411606 2020-03-05 19:55:42 2020-03-05 20:02:51
023150004066308 2020-03-05 18:48:03 2020-03-05 18:58:03
023150002033547 2020-03-05 12:39:24 2020-03-05 12:42:21
023150000030040 2020-03-05 13:26:02 2020-03-05 13:26:04
023150003690798 2020-03-05 02:04:50 2020-03-05 02:14:50
023150000030040 2020-03-05 13:57:10 2020-03-05 13:57:12
023150004460558 2020-03-05 16:44:48 2020-03-05 16:47:58

I want to Count people online every hour. Now I have a stupid way to count. My sql like :

select '01' as hour,COUNT(distinct T.userid)
from datamart_ott_b2b_jsydcp.f_tplay t where t.topicdate  ='2020-03-05'   
and t.begintime < date_parse('2020-03-05 01', '%Y-%m-%d %h')
and t.endtime > date_parse('2020-03-05 00', '%Y-%m-%d %h')

union all 

select  '02' as hour,COUNT(distinct T.userid)
from datamart_ott_b2b_jsydcp.f_tplay t where t.topicdate  ='2020-03-05'   
and t.begintime < date_parse('2020-03-05 02', '%Y-%m-%d %h')
and t.endtime > date_parse('2020-03-05 01', '%Y-%m-%d %h')

.......

Is there an easier way to do this? THX

Answer

In Prestodb, you can generate an array with integer values and then unnest them to get the hours. Then use joins and group by to do the calculations you want:

select hh.hh as hour, cont(distinct t.userid)
from (select sequence(0, 23) hhs
     ) h cross join
     unnest(h.hhs)  as hh(hh) left join
     datamart_ott_b2b_jsydcp.f_tplay t
     on hour(begintime) <= hh.hh and
        hour(enddtime) >= hh.hh
where t.topicdate  = '2020-03-05'  
group by hh.hh
order by hh.hh;

Advertisement

Answer