Get start of a time period from repeated groups

id – id of place where action happened
t – time of action

``+----+----------+ | id |    t     | +----+----------+ |  1 | 12:10:00 | |  1 | 12:10:05 | |  1 | 12:11:00 | |  1 | 13:04:03 | |  2 | 14:18:05 | |  2 | 15:00:09 | |  3 | 17:33:50 | |  1 | 20:03:14 | |  1 | 20:03:55 | |  1 | 20:10:23 | +----+----------+ ``

Goal is to get this output

``+----+----------+ | id |  start   | +----+----------+ |  1 | 12:10:00 | |  2 | 14:18:05 | |  3 | 17:33:50 | |  1 | 20:03:14 | +----+----------+ ``

start – time of first action at id

Scripts with rank, min, etc. keep grouping rows with id=1
I do not know how to resolve this issue and did not find similar post
Here is sqlfiddle with scripts

Asked on September 1, 2020 in

This type of problem can be termed as gaps-and-islands problem and can be achieved by the difference of row numbers and aggregation.

``select id,min(t),min(h) from ( select id       ,t       ,extract(hour from t) h       ,row_number() over (order by t) as seq1       ,row_number() over (partition by id order by t) as seq2   from records ) t group by id,(seq1-seq2) order by min(t); ``

Reference: db<>fiddle

It’s a typical gap and islands problem in which you can use some analytic functions such as `ROW_NUMBER()`, `LAG()`, `LEAD()` etc. mostly we consider applying analytic functions twice through manipulating `PARTITION` option, and substracting one result from the other such as

``SELECT DISTINCT tt.ID, FIRST_VALUE(t) OVER W AS start   FROM (SELECT t.*,                ROW_NUMBER() OVER(ORDER BY t)               - ROW_NUMBER() OVER(PARTITION BY ID ORDER BY t) AS rn                   FROM (SELECT ID, t FROM records) t) tt WINDOW W AS           (PARTITION BY rn ORDER BY t ROWS               BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  ORDER BY start;   +----+----------+  | id |  start   |  +----+----------+  |  1 | 12:10:00 |  |  2 | 14:18:05 |  |  3 | 17:33:50 |  |  1 | 20:03:14 |  +----+----------+ ``

Demo

The simplest way to solve this is to use `lag()`:

``select id, t as start from (select t.*, lag(id) over (order by t) as prev_id       from t      ) t where prev_id is distinct from id; ``

Basically, you just want the values when the `id` changes.

Note: I think that looking at this as a "typical" gaps-and-islands problem is overkill and complicates the solution.