Get start of a time period from repeated groups

user_12842004 0
Viewed 78

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
Thanks in advance!

gaps-and-islands window-functions postgresql sql
3 Answers

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

user_58413060

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.

user_11440350

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

user_138780230
Related