Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE tmp ( > date timestamp UNIQUE NOT NULL > , id_type integer NOT NULL > ) ; > > <pre> > ✓ > </pre> <!-- --> > 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 ); > > <pre> 16 rows affected > </pre> <!-- --> > INSERT INTO tmp(date, id_type) > SELECT date + interval '1 day' * g, id_type > FROM tmp, generate_series(1,8000) g; > > <pre> 128000 rows affected > </pre> <!-- --> > VACUUM ANALYZE tmp; > > <pre> > ✓ > </pre> <!-- --> > CREATE OR REPLACE FUNCTION f_tmp_groups() > RETURNS TABLE (id_type int, grp_begin timestamp, grp_end timestamp) AS > $func$ > DECLARE > _row tmp; -- use table type for row variable > BEGIN > FOR _row IN > TABLE tmp ORDER BY date -- add more columns to make order deterministic > LOOP > CASE _row.id_type = id_type > WHEN TRUE THEN -- same group continues > grp_end := _row.date; -- remember last date so far > WHEN FALSE THEN -- next group starts > RETURN NEXT; -- return result for last group > id_type := _row.id_type; > grp_begin := _row.date; > grp_end := _row.date; > ELSE -- NULL for 1st row > id_type := _row.id_type; -- remember row data for starters > grp_begin := _row.date; > grp_end := _row.date; > END CASE; > END LOOP; > > RETURN NEXT; -- return last result row > END > $func$ LANGUAGE plpgsql; > > <pre> > ✓ > </pre> <!-- --> > -- 1. > EXPLAIN ANALYZE > SELECT id_type > , min(date) AS begin > , max(date) AS end > FROM ( > SELECT date, id_type, count(step OR NULL) OVER (ORDER BY date) AS grp > FROM ( > SELECT date, id_type > , lag(id_type) OVER (ORDER BY date) <> id_type AS step > FROM tmp > ) sub1 > ) sub2 > GROUP BY id_type, grp > ORDER BY min(date); > > <pre> > | QUERY PLAN | > | :------------------------------------------------------------------------------------------------------------------------------------------------------------------ | > | Sort (cost=26898.01..26930.01 rows=12802 width=20) (actual time=511.933..526.859 rows=64008 loops=1) | > | Sort Key: (min(sub2.date)) | > | Sort Method: external merge Disk: 2632kB | > | -&gt; GroupAggregate (cost=24232.42..26024.65 rows=12802 width=20) (actual time=374.881..463.911 rows=64008 loops=1) | > | -&gt; Sort (cost=24232.42..24552.46 rows=128016 width=20) (actual time=374.870..415.777 rows=128016 loops=1) | > | Sort Key: sub2.id_type, sub2.grp | > | Sort Method: external merge Disk: 4224kB | > | -&gt; Subquery Scan sub2 (cost=0.00..10744.34 rows=128016 width=20) (actual time=0.041..230.758 rows=128016 loops=1) | > | -&gt; WindowAgg (cost=0.00..9464.18 rows=128016 width=13) (actual time=0.040..192.611 rows=128016 loops=1) | > | -&gt; WindowAgg (cost=0.00..6263.78 rows=128016 width=12) (actual time=0.033..111.294 rows=128016 loops=1) | > | -&gt; Index Scan using tmp_date_key on tmp (cost=0.00..4023.50 rows=128016 width=12) (actual time=0.022..38.134 rows=128016 loops=1) | > | Total runtime: 538.023 ms | > </pre> <!-- --> > -- 2. > EXPLAIN ANALYZE > SELECT min(date) AS begin > , max(date) AS end > , id_type > FROM ( > SELECT date, id_type > , row_number() OVER (ORDER BY date) > - row_number() OVER (PARTITION BY id_type ORDER BY date) AS grp > FROM tmp > ) sub > GROUP BY id_type, grp > ORDER BY min(date); > > <pre> > | QUERY PLAN | > | :------------------------------------------------------------------------------------------------------------------------------------------------------------------ | > | Sort (cost=39308.50..39340.51 rows=12802 width=20) (actual time=603.614..618.263 rows=64008 loops=1) | > | Sort Key: (min(tmp.date)) | > | Sort Method: external merge Disk: 2632kB | > | -&gt; GroupAggregate (cost=36642.91..38435.14 rows=12802 width=20) (actual time=486.928..559.273 rows=64008 loops=1) | > | -&gt; Sort (cost=36642.91..36962.95 rows=128016 width=20) (actual time=486.918..511.419 rows=128016 loops=1) | > | Sort Key: tmp.id_type, (((row_number() OVER (?)) - row_number() OVER (?))) | > | Sort Method: external sort Disk: 4256kB | > | -&gt; WindowAgg (cost=18994.32..21874.68 rows=128016 width=12) (actual time=231.210..344.083 rows=128016 loops=1) | > | -&gt; Sort (cost=18994.32..19314.36 rows=128016 width=12) (actual time=231.184..275.473 rows=128016 loops=1) | > | Sort Key: tmp.id_type, tmp.date | > | Sort Method: external merge Disk: 4224kB | > | -&gt; WindowAgg (cost=0.00..5943.74 rows=128016 width=12) (actual time=0.012..94.416 rows=128016 loops=1) | > | -&gt; Index Scan using tmp_date_key on tmp (cost=0.00..4023.50 rows=128016 width=12) (actual time=0.009..35.779 rows=128016 loops=1) | > | Total runtime: 629.593 ms | > </pre> <!-- --> > -- 3. > EXPLAIN ANALYZE > SELECT * FROM f_tmp_groups(); > > <pre> > | QUERY PLAN | > | :---------------------------------------------------------------------------------------------------------------------- | > | Function Scan on f_tmp_groups (cost=0.00..260.00 rows=1000 width=20) (actual time=171.841..183.274 rows=64008 loops=1) | > | Total runtime: 191.398 ms | > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_8.4&fiddle=297912cdbfbd4b17f0e4864698b62363)*
back to fiddle