Как найти, где прерывается временной интервал в таблице базы данных SQLite3

У меня есть база данных SQLite3 с одной таблицей, подобной этой:
Time Val1 Val2 Val3
10:12:03 3.4 4.3 5
10:12:04 3.2 4.1 6
10:12:05 3.3 4.6 5
11:03:19 2.4 5.7 4
11:03:20 2.5 5.2 4
11:03:21 2.7 5.9 5
...

Временной интервал между двумя строками больше 1 секунды означает конец первого рабочего интервала и начало второго рабочего интервала.
Итак, первый рабочий интервал 10:12:03 - 10:12:05
второй рабочий интервал 11:03:19 - 11:03:21
и так далее.

Как узнать все временные метки начала интервалов (10:12:03, 11:03:19...) с помощью SQL?


sql sqlite date window-functions gaps-and-islands
person Akra Bato    schedule 26.05.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Это проблема пробелов и островов: вы хотите отобразить строки, которые не являются смежными с предыдущими (то есть в начале каждого острова).

Вот подход с использованием оконных функций:

select t.*
from (
    select 
        t.*,
        lag(time) over(order by time) lag_time
        from mytable t
) t
where
    lag_time is null
    or time >  datetime(lag_time, '+ 1 second')

lag() извлекает time из предыдущей строки. Затем мы фильтруем строки, которые отличаются от предыдущей строки более чем на одну секунду (или для которых нет предыдущей записи).

person GMB    schedule 26.05.2020