Przedział sumy dat w tej samej kolumnie

Jak najlepiej zsumować różnice zakresu dat w tej samej kolumnie między wierszami przeplatania? Mam kolumnę Datetime i chcę obliczyć różnicę między wierszami. Chcę różnicę w sekundach. To pytanie nie dotyczy sposobu uzyskania różnicy między 2 znacznikami czasu, ale skupia się bardziej na tym, jak najskuteczniej obliczyć między wierszami w tej samej tabeli. W moim przypadku każdy wiersz ma typ zdarzenia datetime, który logicznie łączy 2 wiersze.

Szczegóły związane z grupowaniem typów zdarzeń początku i końca. (Pytanie Andriy M.) Początki i zakończenia „powinny” być następujące po sobie. Jeśli Start nie ma kolejnego końca, należy go pominąć w sumie. Przejście do następnego Rozpocznij, aby sprawdzić, czy ma koniec. Tylko sumy początkowych i końcowych par należy dodawać do sumy całkowitej liczby sekund.

Praca w Postgresql 9.x ...

Przykładowe dane w tabeli;

eventtype, eventdate
START, 2015-01-01 14:00
END, 2015-01-01 14:25
START, 2015-01-01 14:30
END, 2015-01-01 14:43
START, 2015-01-01 14:45
END, 2015-01-01 14:49
START, 2015-01-01 14:52
END, 2015-01-01 14:55

Uwaga: wszystkie daty początkowe i końcowe będą sekwencyjne.

Oto moja pierwsza próba. Wydaje się, że działa.

SELECT 
-- starts.*
SUM(EXTRACT(EPOCH FROM (eventdate_next - eventdate))) AS duration_seconds
FROM
( 
    WITH x AS (
        SELECT *, dense_rank() OVER (ORDER BY eventdate) AS rnk
        FROM   table
        AND eventdate > '2015-01-01 00:00:00.00'
        AND eventdate < '2016-01-01 23:59:59.59' 
        )
    SELECT x.eventdate, x.eventtype, y.eventdate AS eventdate_next,  y.eventtype AS eventtype_next
    FROM   x
    LEFT   JOIN (SELECT DISTINCT eventdate, eventtype, rnk FROM x) y ON y.rnk = (x.rnk + 1)
    ORDER  BY x.eventdate
) starts
WHERE
eventtype = 'START'   
GROUP BY eventtype 

Moja pierwsza próba oparta jest na świetnym przykładzie z stackoverflow Postgres 9.1 - Uzyskanie następnej wartości

Uwaga; Możesz skomentować GROUP BY i SUMĘ i cofnąć komentarz na temat początków. *, Aby uzyskać zapis dla każdego indywidualnego czasu trwania sumy.

Możesz użyć LEADfunkcji analitycznej, aby uzyskać następny wiersz eventtypei eventdatedane bieżącego wiersza:

SELECT
  eventtype,
  eventdate,
  LEAD(eventtype) OVER (ORDER BY eventdate) AS nexttype,
  LEAD(eventdate) OVER (ORDER BY eventdate) AS nextdate
FROM
  atable
WHERE
      eventdate >= '2015-01-01 00:00:00.00'
  AND eventdate <  '2016-01-01 23:59:59.59'

Używając powyższego zapytania jako tabeli pochodnej, możesz dalej filtrować dane wyjściowe eventtype = 'START' AND nexttype = 'END'i uzyskać różnicę w sumie:

SELECT
  SUM(EXTRACT(EPOCH FROM (nextdate - eventdate))) AS duration_seconds
FROM
  (
    SELECT
      eventtype,
      eventdate,
      LEAD(eventtype) OVER (ORDER BY eventdate) AS nexttype,
      LEAD(eventdate) OVER (ORDER BY eventdate) AS nextdate
    FROM
      atable
    WHERE
          eventdate >= '2015-01-01 00:00:00.00'
      AND eventdate <  '2016-01-01 23:59:59.59'
  ) AS s
WHERE
      eventtype = 'START'
  AND nexttype  = 'END'
;

Jako niewielką odmianę można zaimplementować podzapytanie jako CTE:

WITH cte AS
  (
    SELECT
      eventtype,
      eventdate,
      LEAD(eventtype) OVER (ORDER BY eventdate) AS nexttype,
      LEAD(eventdate) OVER (ORDER BY eventdate) AS nextdate
    FROM
      atable
    WHERE
          eventdate >= '2015-01-01 00:00:00.00'
      AND eventdate <  '2016-01-01 23:59:59.59'
  )
SELECT
  SUM(EXTRACT(EPOCH FROM (nextdate - eventdate))) AS duration_seconds
FROM
  cte
WHERE
      eventtype = 'START'
  AND nexttype  = 'END'
;

To przepisywanie może mieć wpływ na wydajność, ponieważ w przeciwieństwie do tabeli pochodnej, CTE jest zmaterializowane w PostgreSQL. Testy powinny ujawnić, czy istnieje różnica, a jeśli tak, to która opcja jest dla Ciebie lepsza.