Odpowiedź

Problem przetwarzania danych czasowych w relacyjnych bazach danych pojawił się na styku analityki i klasycznego programowania w SQL. W SQL-92 brakuje specjalnych funkcji okiennych, dlatego trzeba używać podzapytań do obliczania metryk dynamicznych (suma ruchoma, średnia itd.) oraz warunków czasowych.

Problem — brak standardowych narzędzi do agregacji opartej na ruchomych oknach, znajdowania poprzednich/następnych wartości w czasie oraz efektywnej grupowania według dowolnych interwałów kalendarzowych (np. obliczanie tygodniowych/miesięcznych wskaźników).

Rozwiązanie:

Używając tylko standardowych środków, stosuje się podzapytania do korelacji dla każdego wiersza lub grupowania według obliczanych kryteriów (np. miesiąca, tygodnia):

Przykład kodu:

-- Przykład grupowania według tygodni bez funkcji okiennych
SELECT 
   YEAR(event_date) AS year,
   WEEK(event_date) AS week,
   SUM(value) AS total
FROM timeseries
GROUP BY YEAR(event_date), WEEK(event_date)
ORDER BY year, week;

-- Korelowane podzapytanie do znajdowania poprzedniego wpisu
SELECT t1.id, t1.event_date, t1.value,
       (
         SELECT t2.value 
           FROM timeseries t2 
          WHERE t2.event_date < t1.event_date
          ORDER BY t2.event_date DESC
          LIMIT 1
       ) as prev_value
FROM timeseries t1;

Kluczowe cechy:

Należy wyraźnie grupować i agregować według funkcji czasowych (YEAR, MONTH, WEEK itd.).
Aby uzyskać obliczenia ruchome, konieczne jest korzystanie z korelowanych podzapytań lub tabel tymczasowych.
Wydajność spada przy dużych ilościach danych — podzapytanie dla każdego wiersza szybko spowalnia wykonanie.

Pytania z ukrytymi pułapkami.

Czy WEEK(event_date) zawsze jednoznacznie definiuje kalendarzowy tydzień dla dowolnych dat?

Nie — różne DBMS (a nawet różne parametry jednej DBMS) różnie definiują pierwszy tydzień roku (np. ISO 8601 vs amerykański system), co może prowadzić do różnych wyników w agregacji. Należy wyraźnie określić tryb działania funkcji lub użyć YEARWEEK.

SELECT YEARWEEK(event_date, 1)  -- 1: tydzień ISO zaczyna się od poniedziałku
FROM timeseries;

Czy korelowane podzapytanie automatycznie usuwa duplikaty podczas szukania poprzedniej wartości?

Nie, korelowane podzapytanie nie filtruje duplikatów domyślnie. Jeśli w tabeli jest kilka wydarzeń na tę samą datę — podzapytanie zwróci pierwsze z nich według sortowania, ale pozostałe zignoruje.

Czy można grupować dane według dat, nie uwzględniając czasu?

Tak, ale trzeba jawnie odrzucić część czasu, na przykład przy pomocy DATE(event_date) lub TRUNC(event_date) w różnych DBMS:

SELECT DATE(event_datetime), COUNT(*)
FROM events
GROUP BY DATE(event_datetime)

Typowe błędy i antywzorce

Używanie YEAR/MONTH/WEEK bez uwzględnienia lokalizacji i standardów kalendarzowych dla okresów raportowania
Tworzenie zbyt złożonych korelowanych podzapytań do ruchomych okien — eksplozyjny wzrost czasu wykonania przy dużych ilościach danych
Praca z funkcjami czasowymi bez uwzględnienia strefy czasowej

Przykład z życia

Negatywny przypadek

Zespół obliczał tygodniową analitykę za pomocą funkcji WEEK(date), nie ustawiając parametrów na ISO-8601. W rezultacie raporty za pierwszy tydzień stycznia "ginęły" — część wydarzeń przypisywano do grudnia ubiegłego roku według amerykańskiej logiki. Analityka się nie zgadzała.

Plusy:

Szybko zrealizowano dekompozycję okresów

Minusy:

Dane niepoprawne, raporty nie zgadzają się z logiką biznesową

Pozytywny przypadek

Specjalista wdrożył YEARWEEK(date, 1) oraz tabelę kalendarza, znacznie zwiększając zgodność raportów między krajami i jednostkami biznesowymi.