CODEWITHSUNDEEP
hivehiveql
soupe
soupe

Reputation: 95

hql split time into intervals

I have a Hive table with some data and i would like to split it in to 15 minutes intervals et return the total call duration for every interval

Hive Table example :

ID       Start                 End                Total Duration    
1      1502296261           1502325061                28800

My output should be shown as :

ID       Interval             Duration    
1   2017-08-09 18:30:00        839
1   2017-08-09 18:45:00        900
1   2017-08-09 19:00:00        900
...
1   2017-08-10 02:15:00        900
1   2017-08-10 02:30:00        61

What is the best solution to do that in a efficient way ?

Thanks.

Upvotes: 1

Views: 980

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

This is the basic solution.
The displayed timestamp (Interval) depends on your system timezone.

with t as (select stack(1,1,1502296261,1502325061) as (`ID`,`Start`,`End`))

select  t.`ID`                                                      as `ID`
       ,from_unixtime((t.`Start` div (15*60) + pe.pos)*(15*60))     as `Interval`
       ,    case 
                when    pe.pos = t.`End` div (15*60) - t.`Start` div (15*60) 
                then    t.`End`
                else    (t.`Start` div (15*60) + pe.pos + 1)*(15*60)
            end
        -   case 
                when    pe.pos = 0
                then    t.`Start`
                else    (t.`Start` div (15*60) + pe.pos)*(15*60)
            end                                                     as `Duration`

from    t
        lateral view 
            posexplode(split(space(int(t.`End` div (15*60) - t.`Start` div (15*60))),' ')) pe
;

+----+---------------------+----------+
| id |      interval       | duration |
+----+---------------------+----------+
|  1 | 2017-08-09 09:30:00 |      839 |
|  1 | 2017-08-09 09:45:00 |      900 |
|  1 | 2017-08-09 10:00:00 |      900 |
|  1 | 2017-08-09 10:15:00 |      900 |
|  1 | 2017-08-09 10:30:00 |      900 |
|  1 | 2017-08-09 10:45:00 |      900 |
|  1 | 2017-08-09 11:00:00 |      900 |
|  1 | 2017-08-09 11:15:00 |      900 |
|  1 | 2017-08-09 11:30:00 |      900 |
|  1 | 2017-08-09 11:45:00 |      900 |
|  1 | 2017-08-09 12:00:00 |      900 |
|  1 | 2017-08-09 12:15:00 |      900 |
|  1 | 2017-08-09 12:30:00 |      900 |
|  1 | 2017-08-09 12:45:00 |      900 |
|  1 | 2017-08-09 13:00:00 |      900 |
|  1 | 2017-08-09 13:15:00 |      900 |
|  1 | 2017-08-09 13:30:00 |      900 |
|  1 | 2017-08-09 13:45:00 |      900 |
|  1 | 2017-08-09 14:00:00 |      900 |
|  1 | 2017-08-09 14:15:00 |      900 |
|  1 | 2017-08-09 14:30:00 |      900 |
|  1 | 2017-08-09 14:45:00 |      900 |
|  1 | 2017-08-09 15:00:00 |      900 |
|  1 | 2017-08-09 15:15:00 |      900 |
|  1 | 2017-08-09 15:30:00 |      900 |
|  1 | 2017-08-09 15:45:00 |      900 |
|  1 | 2017-08-09 16:00:00 |      900 |
|  1 | 2017-08-09 16:15:00 |      900 |
|  1 | 2017-08-09 16:30:00 |      900 |
|  1 | 2017-08-09 16:45:00 |      900 |
|  1 | 2017-08-09 17:00:00 |      900 |
|  1 | 2017-08-09 17:15:00 |      900 |
|  1 | 2017-08-09 17:30:00 |       61 |
+----+---------------------+----------+

Upvotes: 1

Related Questions