基于一列对连续行进行分组

Question

假设我有一个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

预期：

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

注意：时间戳并不重要，因为我只想计算旅程的数量。

知道我该做什么吗？

Answer 1

这是一个差距和孤岛问题。 使用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);

Answer 2

这是一个

间隙和孤岛问题，您可以尝试使用ROW_NUMBER窗口函数从结果集中获取间隙，然后使用MIN

你可以试试这个。

查询 #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         |

---

在DB Fiddle上查看