Group consecutive rows based on one column

Question

Suppose I have this table from the result of a select * from journeys:

timestamp     | inJourney (1 = true and 0 = false)
--------------------------------------------------
time1         | 1
time2         | 1
time3         | 1
time4         | 0
time5         | 0
time6         | 1
time7         | 1
time8         | 1

Expected:

timestamp     | inJourney (1 = true and 0 = false)
--------------------------------------------------
time1         | 1
time4         | 0
time8         | 1

Note: the timestamp is not important because I only want to count the number of journeys.

Any idea what I have to do?

Same as this one: https://stackoverflow.com/a/30880137/939860. Or this one: https://dba.stackexchange.com/q/112846/3684 — Erwin Brandstetter, Apr 12 '19 at 14:59
@ErwinBrandstetter You're right. I didn't find that post. Thanks. — juan_marti, Apr 12 '19 at 15:02
Once you know the search term "gaps and islands", it gets easier. But there are still many variants of the problem. — Erwin Brandstetter, Apr 12 '19 at 15:03

score 9 · Accepted Answer · answered Apr 12 '19 at 14:45

9

This is a gaps-and-islands problem. Use the difference of row_number():

select injourney, min(timestamp), max(timestamp)
from (select t.*,
             row_number() over (order by timestamp) as seqnum,
             row_number() over (partition by injourney, order by timestamp) as seqnum_i
      from t
     ) t
group by injourney, (seqnum - seqnum_i)
order by min(timestamp);

answered Apr 12 '19 at 14:45

Gordon Linoff

1,242,037
58
646
786

Thank you so much. I wasn't aware of this problem (gaps-and-islands problem) but now I know. I've been in this for hours. You deserve a fantastic weekend. @GordonLinoff – juan_marti Apr 12 '19 at 14:55

score 2 · Answer 2 · answered Apr 12 '19 at 14:47

This is a gaps-and-islands problem, you can try to use ROW_NUMBER window function to get the gap from result set then use MIN

You can try this.

Query #1

SELECT MIN(timestamp),inJourney 
FROM (
SELECT *,
    ROW_NUMBER() OVER(ORDER BY timestamp)  - ROW_NUMBER() OVER(PARTITION BY inJourney ORDER BY timestamp) grp
  FROM journeys
) t1
GROUP BY grp,inJourney 
ORDER BY MIN(timestamp);

| min   | injourney |
| ----- | --------- |
| time1 | 1         |
| time4 | 0         |
| time6 | 1         |

View on DB Fiddle

Group consecutive rows based on one column

2 Answers2

Linked

Related