Wie kann man Ereignisse innerhalb dynamischer Zeitintervalle in PostgreSQL effizient zählen?

So zählen Sie Ereignisse effizient nach Zeitintervallen

Einführung

Anwendungen erfordern häufig das Extrahieren von Erkenntnissen aus ereignisbasierten Daten, beispielsweise das Zählen von Ereignissen innerhalb bestimmter Zeitintervalle Zeitintervalle. Diese Aufgabe stellt eine Herausforderung dar, wenn die Intervallgröße dynamisch variiert.

Problemstellung

Eine Datentabelle mit zeitgestempelten Ereignissen erfordert eine dynamische SQL-Abfrage, die Ereignisse nach beliebigen Zeitintervallen gruppiert, einschließlich wöchentlich, täglich, stündlich oder sogar 15-Minuten-Intervalle.

Lösung

Postgres 14 oder Neuere

Für die Postgres-Versionen 14 und 以降 bietet die Funktion date_bin() eine unkomplizierte Lösung. Um eine Zeile pro Zeitfenster mit Daten abzurufen, verwenden Sie die folgende Abfrage:

SELECT date_bin('15 min', e.ts, '2018-05-01') AS start_time
     , count(e.ts) AS events
FROM   event e
GROUP  BY 1
ORDER  BY 1;

Um alle Zeitfenster seit einem bestimmten Datum zu erhalten, passen Sie die Abfrage wie folgt an:

SELECT start_time, COALESCE(events, 0) AS events
FROM  (
   SELECT generate_series(timestamp '2018-05-01', max(ts), interval '15 min')
   FROM   event
   ) g(start_time)
LEFT   JOIN (
   SELECT date_bin('15 min', e.ts, '2018-05-01'), count(e.ts)
   FROM   event e
   WHERE  e.ts >= '2018-05-01'  -- filter early (optional)
   GROUP  BY 1
   ) e(start_time, events) USING (start_time)
ORDER  BY 1;

Postgres 13 oder älter

Für ältere Postgres-Versionen kann die folgende Abfrage verwendet werden:

WITH grid AS (
   SELECT start_time
        , lead(start_time, 1, 'infinity') OVER (ORDER BY start_time) AS end_time
   FROM  (
      SELECT generate_series(min(ts), max(ts), interval '17 min') AS start_time
      FROM   event
      ) sub
   )
SELECT start_time, count(e.ts) AS events
FROM   grid       g
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.end_time
GROUP  BY start_time
ORDER  BY start_time;

Diese Abfrage stellt eine bereit Zeile für jedes Zeitfenster, mit einer Zählung von 0 für Zeitfenster ohne Ereignisse.

Optimierung

Das Vorabspeichern von Intervalldaten in der Ereignistabelle kann die Leistung verbessern. Allerdings verdoppelt dieser Ansatz die Tabellengröße. Wenn der Kompromiss akzeptabel ist, kann dies zu einer erheblichen Geschwindigkeitssteigerung führen.

Zusätzliche Überlegungen

• Behandeln Sie obere und untere Zeitgrenzen korrekt.
• Verwenden Sie to_char ()-Funktion zum Formatieren von Zeitstempeln für Anzeigezwecke.
• Verwenden Sie die Funktion „generate_series()“, um einen vollständigen Zeitsatz zu generieren Slots.

Das obige ist der detaillierte Inhalt vonWie kann man Ereignisse innerhalb dynamischer Zeitintervalle in PostgreSQL effizient zählen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!