Блог

Главная - Вопросы по программированию - Группировать данные в интервалах без 0

Группировать данные в интервалах без 0

#mysql #sql #datetime #gaps-and-islands

#mysql #sql #дата и время #пробелы и острова

Вопрос:

У меня есть следующая таблица:

  --------------------- -------- ---------- 
| 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        |
 --------------------- -------- ----------

Я пытаюсь выбрать непрерывные интервалы, по которым двигался автомобиль (игнорируя интервалы, где расстояние = 0), поэтому результаты будут примерно такими:

  --------------------- --------------------- -------- -------------- 
|                     |                     |        |              |
| 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           |
 --------------------- --------------------- -------- --------------

Есть идеи, как этого добиться?

Ответ №1:

Это проблема пробелов и островов. Острова — это смежные записи с ненулевыми расстояниями.

Вот подход, который использует разницу между номерами строк для идентификации групп:

 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