Antwoord

De vraag van de verwerking van tijdreeksen in relationele databases is ontstaan op het snijpunt van analytics en klassieke programmering met SQL. In SQL-92 zijn er geen speciale vensterfuncties, dus men moet zich behelpen met subquery's voor het berekenen van dynamische metrics (lopende som, gemiddelde, enz.) en tijdscriteria.

Probleem – gebrek aan standaardtools voor aggregatie over een lopend venster, het vinden van de vorige/volgende waarde in de tijd, en effectieve groepering over willekeurige kalenderintervallen (bijvoorbeeld het berekenen van wekelijkse/maandelijkse cijfers).

Oplossing:

Door alleen gebruik te maken van standaardmiddelen, worden correlatie-subquery's toegepast voor elke rij of groepering op een berekend criterium (bijvoorbeeld maand, week):

Voorbeeld code:

-- Voorbeeld van groeperen per week zonder vensterfuncties
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;

-- Correlatieve subquery voor het vinden van de vorige record
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;

Belangrijke kenmerken:

Groeperen en aggregeren moet expliciet gebeuren op tijdsfuncties (YEAR, MONTH, WEEK, enz.).
Voor het verkrijgen van lopende berekeningen moeten correlatieve subquery's of tijdelijke tabellen gebruikt worden.
De prestaties lijden bij grote hoeveelheden gegevens — een subquery voor elke rij vertraagt de uitvoering snel.

Voorkeurs vragen.

Bepaalt WEEK(event_date) altijd eenduidig de kalenderweek voor elke datum?

Nee — verschillende DBMS'en (en zelfs parameters van één DBMS) definiëren de eerste week van het jaar op verschillende manieren (bijvoorbeeld, ISO 8601 vs Amerikaanse systeem), wat kan leiden tot verschillende resultaten bij aggregeren. Men moet expliciet de modus van de functie opgeven of YEARWEEK gebruiken.

SELECT YEARWEEK(event_date, 1)  -- 1: ISO week begint op maandag
FROM timeseries;

Verwijdert een correlatieve subquery automatisch duplicaten bij het zoeken naar de vorige waarde?

Nee, een correlatieve subquery filtert duplicaten niet standaard. Als er meerdere gebeurtenissen op één datum in de tabel staan, zal de subquery de eerste daarvan volgens sortering teruggeven, maar de rest negeren.

Is het mogelijk om via GROUP BY te aggregeren op data zonder rekening te houden met de tijd?

Ja, maar men moet expliciet het tijdsdeel wegwerpen, bijvoorbeeld met DATE(event_date) of TRUNC(event_date) in verschillende DBMS'en:

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

Typische fouten en anti-patronen

Gebruik van YEAR/MONTH/WEEK zonder rekening te houden met lokale en kalenderstandaarden voor rapportageperiodes
Het maken van te complexe correlatieve subquery's voor lopende vensters — explosieve stijging van de uitvoeringstijd bij grote hoeveelheden
Werken met tijdsfuncties zonder rekening te houden met tijdzones

Voorbeeld uit het leven

Negatief geval

Het team berekende wekelijkse analytics via WEEK(date), zonder de parameters op ISO-8601 in te stellen. Als gevolg hiervan "verdwenen" de rapporten voor de eerste week van januari — sommige gebeurtenissen werden volgens de Amerikaanse logica aan december van het voorgaande jaar toegewezen. De analytics kwam niet overeen.

Voordelen:

Snel de decompositie van periodes gerealiseerd

Nadelen:

Gegevens zijn incorrect, rapporten komen niet overeen met de bedrijfslogica

Positief geval

De specialist implementeerde YEARWEEK(date, 1) en een kalender tabel, wat de consistentie van rapporten tussen landen en zakelijke eenheden aanzienlijk verbeterde.