Получить временной интервал между остановкой (строка x) и запуском (строка x+1)

У меня есть таблица в PostgreSQL 9.5 с двумя временными метками в каждой строке, timestampstart и timestampstop.

CREATE TABLE routes(
    ID serial PRIMARY KEY,
    TIMESTAMPSTART timestamp default NULL,
    TIMESTAMPEND timestamp default NULL
);

Теперь я хочу рассчитать интервал не между стартом и остановкой, а между остановкой и следующим пуском. Поэтому мне нужен интервал между TIMESTAMPSTOP из строки x и TIMESTAMPSTART в строке x+1. Кстати, ID не в хронологическом порядке!


postgresql sql postgresql-9.5 intervals sql-timestamp
person Martin    schedule 16.04.2016    source источник
comment
Что должно произойти, если нет следующей строки? Что делать, если есть повторяющиеся значения? Упорядочить по ID в дополнение к отметкам времени?   -  person Erwin Brandstetter    schedule 17.04.2016

Ответы (2)


arrow_upward
1
arrow_downward

Если между «событиями» нет пересечений, то можно сделать простую оконную функцию. Запрос может быть таким простым, как:

SELECT id, lead(timestampstart) OVER (ORDER BY timestampstart) -
           timestampend AS timetonext
FROM routes;

SQLFiddle

Это решение на порядок быстрее, чем решение Эрвина: http://www.sqlfiddle.com/#!15/551e2/4

person Patrick    schedule 17.04.2016
comment
Правда, с ограничением исключения на месте этот запрос был бы эквивалентным, намного проще и быстрее. Одно отличие в угловом случае: мой запрос предполагает timestampstart > r.timestampend, в то время как это также допускает равенство: timestampstart >= r.timestampend. Типы диапазонов (или оператор SQL OVERLAPS) по умолчанию обрабатывают верхнюю границу как исключительную, следующий интервал может начинаться с той же отметки времени, что и последний, и это не считается перекрытием. - person Erwin Brandstetter; 17.04.2016

arrow_upward
1
arrow_downward

Вы не можете просто использовать простой функция окна lead() или lag() в зависимости от того, какой вы хотите интервал между двумя разными столбцами.

Возможны различные варианты соединения с добавлением предикатов. С индексом timestampstart соединение LATERAL с LIMIT 1, вероятно, будет самым быстрым.

Предполагая, что timestampstart равно UNIQUE, в противном случае вам нужно определить, как разорвать связи. Ограничение UNIQUE также предоставит индекс, необходимый для производительности:

SELECT r.*, r1.timestampstart - r.timestampend AS interval_til_start_of_next_row
FROM   routes r
LEFT   JOIN LATERAL (
   SELECT timestampstart
   FROM   routes
   WHERE  timestampstart > r.timestampend
   ORDER  BY timestampstart  -- or BY timestampstart, id - to break ties if not unique
   LIMIT  1
   ) r1 ON true;
person Erwin Brandstetter    schedule 17.04.2016