Recursive CTE, PostgreSQL

Language: Layout:

DROP TABLE IF EXISTS t ;

CREATE TABLE t
    ("date" timestamp without time zone, id_type integer) ;
    
INSERT INTO t
    ("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)
;

WITH RECURSIVE q AS
(
    SELECT
        id_type,
        "date",
        /* We compute next id_type for convenience, plus row_number */
        row_number()  OVER (w) AS rn,
        lead(id_type) OVER (w) AS next_id_type
    FROM
        t
    WINDOW
        w AS (ORDER BY "date") 
)
, rec AS 
(
    /* Anchor */
    SELECT
        q.rn,
        q."date" AS "begin",
        /* When next_id_type is different from Look also at **next** row to find out whether we need to mark an end */
        case when q.id_type is distinct from q.next_id_type then q."date" END AS "end",
        q.id_type
    FROM
        q
    WHERE
        rn = 1
    
    UNION ALL
    
    /* Loop */
    SELECT
        q.rn,
        /* We keep copying 'begin' from one row to the next while type doesn't change */
        case when q.id_type = rec.id_type then rec.begin else q."date" end AS "begin",
        case when q.id_type is distinct from q.next_id_type then q."date" end AS "end",
        q.id_type
    FROM
        rec
        JOIN q ON q.rn = rec.rn+1
)
-- We filter the rows where "end" is not null, and project only needed columns
SELECT
    "begin", "end", id_type
FROM
    rec
WHERE
    "end" is not null ;


absolute service time: 0,49 sec