Floating date range group

Question

I am using PostgreSQL 9.2.
I have a table containing the time when some devices fail.

+----------+----------+---------------------+ | event_id | device | time | +----------+----------+---------------------+ | 1 | Switch4 | 2013-09-01 00:01:00 | | 2 | Switch1 | 2013-09-01 00:02:30 | | 3 | Switch10 | 2013-09-01 00:02:40 | | 4 | Switch51 | 2013-09-01 03:05:00 | | 5 | Switch49 | 2013-09-02 13:00:00 | | 6 | Switch28 | 2013-09-02 13:01:00 | | 7 | Switch9 | 2013-09-02 13:02:00 | +----------+----------+---------------------+ 

I want the rows to be grouped with a time difference of +/- 3 minutes, for example:

 +----------+----------+---------------------+--------+ | event_id | device | time | group | +----------+----------+---------------------+--------+ | 1 | Switch4 | 2013-09-01 00:01:00 | 1 | | 2 | Switch1 | 2013-09-01 00:02:30 | 1 | | 3 | Switch10 | 2013-09-01 00:02:40 | 1 | | 4 | Switch51 | 2013-09-01 03:05:00 | 2 | | 5 | Switch49 | 2013-09-02 13:00:00 | 3 | | 6 | Switch28 | 2013-09-02 13:01:00 | 3 | | 7 | Switch9 | 2013-09-02 13:02:00 | 3 | +----------+----------+---------------------+--------+ 

I tried to do this using the window function, but in the section

[RANGE | ROWS] BETWEEN frame_start AND frame_end, where frame_start and frame_end may be one of the UNEQUIPPED SUGGESTED value, CURRENT CURRENT ROW value, NEXT UNLIMITED NEXT,

the value must be an integer expression that does not contain any variables, aggregate functions or window functions

So, given this, I cannot specify a time interval. Now I doubt that the window function can solve my problem. could you help me?

Answer 1

SQL Fiddle

 select event_id, device, ts, floor(extract(epoch from ts) / 180) as group from t order by ts 

You can make the group number a sequence starting with 1 using the window function, but this is a small cost, which I do not know if necessary. It is he

 select event_id, device, ts, dense_rank() over(order by "group") as group from ( select event_id, device, ts, floor(extract(epoch from ts) / 180) as group from t ) s order by ts 

time is a reserved word. Choose a different column name.

Answer 2

SQLFiddle

 with u as ( select *, extract(epoch from ts - lag(ts) over(order by ts))/ 60 > 180 or lag(ts) over(order by ts) is null as test from t ) select *, sum(test::int) over(order by ts) from u 

Answer 3

This is only a slight improvement over @Clodoaldo's mostly good answer .

To get group numbers:

 SELECT event_id, device, ts ,dense_rank() OVER (ORDER BY trunc(extract(epoch from ts) / 180)) AS grp FROM tbl ORDER BY ts 

• Using ts instead of the (partially) reserved word time is good advice. Therefore, do not use the reserved word group . Use grp .

• Sequential numbers may be without a subquery.

• Use trunc() instead of floor() . Both are good, trunc() slightly faster .

Answer 4

http://www.depesz.com/2010/09/12/how-to-group-messages-into-chats/

should use a window. this is an example from a tutorial

 with xinterval( val ) as ( select 2 ), data( id, t ) as ( values ( 1000, 1 ), ( 1001, 2 ), ( 1002, 3 ), ( 1000, 7 ), ( 1003, 8 ) ), x( id, t, tx ) as ( select id, t, case (t - lag(t) over (order by t)) > xinterval.val when true then t when null then t end from data natural join xinterval ), xx( id, t, t2 ) as ( select id, t, max(tx) over (order by t) from x ) select id, t, text( min(t) over w ) || '-' || text( max(t) over w ) as xperiod from xx window w as ( partition by t2 ) order by t 

Answer 5

Make function

 CREATE OR REPLACE FUNCTION public.date_round ( base_date timestamp, round_interval interval ) RETURNS TIMESTAMP WITHOUT TIME ZONE AS $body$ DECLARE res TIMESTAMP; BEGIN res := TIMESTAMP 'epoch' + (EXTRACT(epoch FROM $1)::INTEGER + EXTRACT(epoch FROM $2)::INTEGER / 2) / EXTRACT(epoch FROM $2)::INTEGER * EXTRACT(epoch FROM $2)::INTEGER * INTERVAL '1 second'; IF (base_date > res ) THEN res := res + $2; END IF; RETURN res; END; $body$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER COST 100; 

And a group by the result of this function

 SELECT t.* FROM (SELECT p.oper_date, date_round(p.oper_date, '5 minutes') as grp FROM test p) t GROUP BY t.grp 

This is easy :)