clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805496 fiddles created (40756 in the last week).

CREATE TABLE tmp ( date timestamp UNIQUE NOT NULL , id_type integer NOT NULL ) ;
 hidden batch(es)


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 );
16 rows affected
 hidden batch(es)


INSERT INTO tmp(date, id_type) SELECT date + interval '1 day' * g, id_type FROM tmp, generate_series(1,8000) g;
128000 rows affected
 hidden batch(es)


VACUUM ANALYZE tmp;
 hidden batch(es)


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;
 hidden batch(es)


-- 1. EXPLAIN (ANALYZE, TIMING OFF) 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);
QUERY PLAN
Sort (cost=12045.82..12046.82 rows=400 width=28) (actual rows=64008 loops=1)
Sort Key: (min(tmp.date))
Sort Method: external merge Disk: 2640kB
-> HashAggregate (cost=12024.53..12028.53 rows=400 width=28) (actual rows=64008 loops=1)
Group Key: tmp.id_type, count((((lag(tmp.id_type) OVER (?) <> tmp.id_type)) OR NULL::boolean)) OVER (?)
Batches: 5 Memory Usage: 4145kB Disk Usage: 3680kB
-> WindowAgg (cost=0.29..9464.21 rows=128016 width=20) (actual rows=128016 loops=1)
-> WindowAgg (cost=0.29..6263.81 rows=128016 width=13) (actual rows=128016 loops=1)
-> Index Scan using tmp_date_key on tmp (cost=0.29..4023.53 rows=128016 width=12) (actual rows=128016 loops=1)
Planning Time: 0.286 ms
Execution Time: 276.741 ms
 hidden batch(es)


-- 2. EXPLAIN (ANALYZE, TIMING OFF) 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);
QUERY PLAN
Sort (cost=36213.06..36214.06 rows=400 width=28) (actual rows=64008 loops=1)
Sort Key: (min(tmp.date))
Sort Method: external merge Disk: 2640kB
-> HashAggregate (cost=36191.77..36195.77 rows=400 width=28) (actual rows=64008 loops=1)
Group Key: tmp.id_type, (row_number() OVER (?) - (row_number() OVER (?)))
Batches: 5 Memory Usage: 4401kB Disk Usage: 3664kB
-> WindowAgg (cost=31071.13..33631.45 rows=128016 width=20) (actual rows=128016 loops=1)
-> Sort (cost=31071.13..31391.17 rows=128016 width=20) (actual rows=128016 loops=1)
Sort Key: tmp.date
Sort Method: external merge Disk: 4264kB
-> WindowAgg (cost=15022.73..17583.05 rows=128016 width=20) (actual rows=128016 loops=1)
-> Sort (cost=15022.73..15342.77 rows=128016 width=12) (actual rows=128016 loops=1)
Sort Key: tmp.id_type, tmp.date
Sort Method: external merge Disk: 2768kB
-> Seq Scan on tmp (cost=0.00..1972.16 rows=128016 width=12) (actual rows=128016 loops=1)
Planning Time: 0.136 ms
Execution Time: 305.821 ms
 hidden batch(es)


-- 3. EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM f_tmp_groups();
QUERY PLAN
Function Scan on f_tmp_groups (cost=0.25..10.25 rows=1000 width=20) (actual rows=64008 loops=1)
Planning Time: 0.039 ms
Execution Time: 101.863 ms
 hidden batch(es)