Group data in intervals without 0

Question

I have got following table:

+---------------------+--------+----------+
| MeasureInterval     | Car_id | Distance |
+---------------------+--------+----------+
| 2020-12-15 17:00:00 | 1      | 20       |
+---------------------+--------+----------+
| 2020-12-15 17:05:00 | 1      | 30       |
+---------------------+--------+----------+
| 2020-12-15 17:10:00 | 1      | 17       |
+---------------------+--------+----------+
| 2020-12-15 17:15:00 | 1      | 0        |
+---------------------+--------+----------+
| 2020-12-15 17:20:00 | 1      | 0        |
+---------------------+--------+----------+
| 2020-12-15 17:25:00 | 1      | 10       |
+---------------------+--------+----------+
| 2020-12-15 17:30:00 | 1      | 15       |
+---------------------+--------+----------+
| 2020-12-15 17:35:00 | 1      | 0        |
+---------------------+--------+----------+
| 2020-12-15 17:40:00 | 1      | 0        |
+---------------------+--------+----------+
| 2020-12-15 17:45:00 | 1      | 0        |
+---------------------+--------+----------+

I am trying to select the continous intervals where the car was moving (ignoring the intervals where Distance=0), so it this the results would be something like:

+---------------------+---------------------+--------+--------------+
|                     |                     |        |              |
| MeasureInterval_min | MeasureInterval_max | Car_id | Distance_sum |
+---------------------+---------------------+--------+--------------+
| 2020-12-15 17:00:00 | 2020-12-15 17:10:00 | 1      | 67           |
+---------------------+---------------------+--------+--------------+
| 2020-12-15 17:25:00 | 2020-12-15 17:30:00 | 1      | 25           |
+---------------------+---------------------+--------+--------------+

Any idea how to achieve this?

score 2 · Accepted Answer · answered Dec 22 '20 at 12:41

This is a gaps-and-island problem. Islands are adjacent records with non-zero distances.

Here is an approach that uses the difference between row numbers to identify the groups:

select 
    min(measureinterval) as measureinterval_min,
    max(measureinterval) as measureinterval_max,
    car_id,
    sum(distance) as distance
from (
    select t.*,
        row_number() over(partition by carid order by measureinterval) rn1,
        row_number() over(partition by carid, (distance = 0) order by measureinterval) rn2
    from mytable t
) t
where distance > 0
group by car_id, rn1 - rn2

Group data in intervals without 0

1 Answers1