PostgreSQL – Form Groups of Consecutive Rows with Same Value

gaps-and-islandsgroup bypostgresqlpostgresql-8.4window functions

I have a situation I think can be solved using window function but I'm not sure.

Imagine the following table

CREATE TABLE tmp
  ( date timestamp,        
    id_type integer
  ) ;

INSERT INTO tmp 
    ( date, id_type )
VALUES
    ( '2017-01-10 07:19:21.0', 3 ),
    ( '2017-01-10 07:19:22.0', 3 ),
    ( '2017-01-10 07:19:23.1', 3 ),
    ( '2017-01-10 07:19:24.1', 3 ),
    ( '2017-01-10 07:19:25.0', 3 ),
    ( '2017-01-10 07:19:26.0', 5 ),
    ( '2017-01-10 07:19:27.1', 3 ),
    ( '2017-01-10 07:19:28.0', 5 ),
    ( '2017-01-10 07:19:29.0', 5 ),
    ( '2017-01-10 07:19:30.1', 3 ),
    ( '2017-01-10 07:19:31.0', 5 ),
    ( '2017-01-10 07:19:32.0', 3 ),
    ( '2017-01-10 07:19:33.1', 5 ),
    ( '2017-01-10 07:19:35.0', 5 ),
    ( '2017-01-10 07:19:36.1', 5 ),
    ( '2017-01-10 07:19:37.1', 5 )
  ;

I'd like to have a new group at each change on column id_type.
E.G. 1st group from 7:19:21 to 7:19:25, 2nd starting and finishing at 7:19:26, and so on.
After it works, I want to include more criteria to define groups.

At this moment, using the query below …

SELECT distinct 
    min(min(date)) over w as begin, 
    max(max(date)) over w as end,   
    id_type
from tmp
GROUP BY id_type
WINDOW w as (PARTITION BY id_type)
order by  begin;

I get the following result:

begin                   end                     id_type
2017-01-10 07:19:21.0   2017-01-10 07:19:32.0   3
2017-01-10 07:19:26.0   2017-01-10 07:19:37.1   5

While I'd like:

begin                   end                     id_type
2017-01-10 07:19:21.0   2017-01-10 07:19:25.0   3
2017-01-10 07:19:26.0   2017-01-10 07:19:26.0   5
2017-01-10 07:19:27.1   2017-01-10 07:19:27.1   3
2017-01-10 07:19:28.0   2017-01-10 07:19:29.0   5
2017-01-10 07:19:30.1   2017-01-10 07:19:30.1   3
2017-01-10 07:19:31.0   2017-01-10 07:19:31.0   5
2017-01-10 07:19:32.0   2017-01-10 07:19:32.0   3
2017-01-10 07:19:33.1   2017-01-10 07:19:37.1   5

After I solve this first step, I'll add more columns to use as rules to break groups, and these others will be nullable.

Postgres Version: 8.4 (We have Postgres with Postgis, so it is not easy to upgrade. Postgis Functions changes names and there are other problems, but hopefully we are already re writing everything and the new version will use a newer version 9.X with postgis 2.x)

Best Answer

For a few points,

  • Don't call a non-temporary table tmp that just gets confusing.
  • Don't use text for timestamps (you're doing that in your example we can tell because the timestamp didn't get truncated and has .0)
  • Don't call a field that has time in it date. If it has date and time, it's a timestamp (and store it as one)

Better to use a window function..

SELECT id_type, grp, min(date), max(date)
FROM (
  SELECT date, id_type, count(is_reset) OVER (ORDER BY date) AS grp
  FROM (
    SELECT date, id_type, CASE WHEN lag(id_type) OVER (ORDER BY date) <> id_type THEN 1 END AS is_reset
    FROM tmp
  ) AS t
) AS g
GROUP BY id_type, grp
ORDER BY min(date);

Outputs 

 id_type | grp |          min          |          max          
---------+-----+-----------------------+-----------------------
       3 |   0 | 2017-01-10 07:19:21.0 | 2017-01-10 07:19:25.0
       5 |   1 | 2017-01-10 07:19:26.0 | 2017-01-10 07:19:26.0
       3 |   2 | 2017-01-10 07:19:27.1 | 2017-01-10 07:19:27.1
       5 |   3 | 2017-01-10 07:19:28.0 | 2017-01-10 07:19:29.0
       3 |   4 | 2017-01-10 07:19:30.1 | 2017-01-10 07:19:30.1
       5 |   5 | 2017-01-10 07:19:31.0 | 2017-01-10 07:19:31.0
       3 |   6 | 2017-01-10 07:19:32.0 | 2017-01-10 07:19:32.0
       5 |   7 | 2017-01-10 07:19:33.1 | 2017-01-10 07:19:37.1
(8 rows)

Explaination

First we need resets.. We generate them with lag()

SELECT date, id_type, CASE WHEN lag(id_type) OVER (ORDER BY date) <> id_type THEN 1 END AS is_reset
FROM tmp
ORDER BY date;

         date          | id_type | is_reset 
-----------------------+---------+----------
 2017-01-10 07:19:21.0 |       3 |         
 2017-01-10 07:19:22.0 |       3 |         
 2017-01-10 07:19:23.1 |       3 |         
 2017-01-10 07:19:24.1 |       3 |         
 2017-01-10 07:19:25.0 |       3 |         
 2017-01-10 07:19:26.0 |       5 |        1
 2017-01-10 07:19:27.1 |       3 |        1
 2017-01-10 07:19:28.0 |       5 |        1
 2017-01-10 07:19:29.0 |       5 |         
 2017-01-10 07:19:30.1 |       3 |        1
 2017-01-10 07:19:31.0 |       5 |        1
 2017-01-10 07:19:32.0 |       3 |        1
 2017-01-10 07:19:33.1 |       5 |        1
 2017-01-10 07:19:35.0 |       5 |         
 2017-01-10 07:19:36.1 |       5 |         
 2017-01-10 07:19:37.1 |       5 |         
(16 rows)

Then we count to get groups.

SELECT date, id_type, count(is_reset) OVER (ORDER BY date) AS grp
FROM (
  SELECT date, id_type, CASE WHEN lag(id_type) OVER (ORDER BY date) <> id_type THEN 1 END AS is_reset
  FROM tmp
  ORDER BY date
) AS t
ORDER BY date

         date          | id_type | grp 
-----------------------+---------+-----
 2017-01-10 07:19:21.0 |       3 |   0
 2017-01-10 07:19:22.0 |       3 |   0
 2017-01-10 07:19:23.1 |       3 |   0
 2017-01-10 07:19:24.1 |       3 |   0
 2017-01-10 07:19:25.0 |       3 |   0
 2017-01-10 07:19:26.0 |       5 |   1
 2017-01-10 07:19:27.1 |       3 |   2
 2017-01-10 07:19:28.0 |       5 |   3
 2017-01-10 07:19:29.0 |       5 |   3
 2017-01-10 07:19:30.1 |       3 |   4
 2017-01-10 07:19:31.0 |       5 |   5
 2017-01-10 07:19:32.0 |       3 |   6
 2017-01-10 07:19:33.1 |       5 |   7
 2017-01-10 07:19:35.0 |       5 |   7
 2017-01-10 07:19:36.1 |       5 |   7
 2017-01-10 07:19:37.1 |       5 |   7
(16 rows)

Then we wrap in a subselect GROUP BY and ORDER and select the min max (range)

SELECT id_type, grp, min(date), max(date)
FROM (
  .. stuff
) AS g
GROUP BY id_type, grp
ORDER BY min(date);
Related Question