Watt Watt - 1 year ago 55
SQL Question

In postgres how to get sum (or rollup) of a field within certain time range from a event log type table (which has timestamp)

I have a table which has data like this

event_type event_cnt timestamp
abc 2 2016-1-1 20:08:01
abc 3 2016-1-1 20:10:01
xyz 10 2016-1-1 20:10:01
abc 1 2016-1-1 20:15:01
xyz 5 2016-1-1 20:30:01
xyz 5 2016-1-1 20:31:01

I want a result which is a sum (cluster) of event_cnt within 2 minutes.

event_type event_cnt_within_2_min
abc 5 (which is 2+3 in two minutes)
xyz 10
abc 1
xyz 10 (which is 5+5 in two minutes)

I think there might be a way to use the analytical function to solve this, but I cannot get to working solution yet.

Answer Source

you can use lead function to check if the next event inserted in the next 2 min.

    select event_type,
           sum(case when datediff(minute,timestamp,lead_timestamp)<=2 then lead_event_cunt else 0 end) + 
           sum(event_cnt) as event_cunt_within_2_min
    select event_type,event_cnt,timestamp,
           lead (timestamp,1) over (partition by event_type order by timestamp) as lead_timestamp,
           lead (event_cunt,1) over (partition by event_type order by timestamp) as lead_event_cunt,
    from mytable)
    group by 1