Search code examples
hadoophivehqlimpala

Impala : Running sum of 1 hour

I want to count records of each ID with in 1 Hour. I tried out some IMPALA queries but without any luck.

I have input data as follows:

enter image description here

And expected output would be:

enter image description here

I tried : 

select
      concat(month,'/',day,'/',year,' ',hour,':',minute) time, id,
     count(1) over(partition by id order by concat(month,'/',day,'/',year,' ',hour,':',minute) range between '1 hour' PRECEDING AND CURRENT ROW) request
       from rt_request
       where
    concat(year,month,day,hour) >= '2019020318' 
group by id, concat(month,'/',day,'/',year,' ',hour,':',minute)

But I got exception.

RANGE is only supported with both the lower and upper bounds UNBOUNDED or one UNBOUNDED and the other CURRENT ROW.

Any suggestion/help would be appreciated. Thank you in advance!

Solution

  • I think you are looking for counts for the same hour across days for a given id. You can simply use row_number to do this.

    select time,id,row_number() over(partition by id,hour order by concat(month,'/',day,'/',year,' ',hour,':',minute)) as total
from tbl