Wie kann man Ereignisse in PostgreSQL effizient nach Zeitintervallen zählen?

Effizientes Zählen von Ereignissen nach Zeitintervallen

Problemstellung

Stellen Sie sich eine Ereignistabelle vor, die Ereignisse mit Zeitstempel enthält. Das Ziel besteht darin, die Anzahl der Ereignisse zu melden, die innerhalb bestimmter Zeitintervalle auftreten, z. B. Tage, Stunden oder benutzerdefinierte Intervalle. Das Ziel besteht darin, den effizientesten Ansatz für die dynamische Generierung dieser Informationen durch eine einzige SQL-Abfrage in Postgres zu ermitteln.

Lösung

Verwendung von Date_bin() (Postgres 14 oder neuer)

Postgres 14 führt die Funktion „date_bin()“ ein und stellt eine bereit Einfache Lösung für dieses Problem:

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;

Generieren eines vollständigen Zeilensatzes (Postgres 13 oder älter)

Für frühere Versionen von Postgres generiert die folgende Abfrage eine vollständigen Satz von Zeitfenstern und führt einen LEFT JOIN durch, um die Ereignisse innerhalb jedes Zeitfensters zu zählen Intervall:

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;

Überlegungen

• Postgres 9.6 oder höher ist für „generate_series()“ erforderlich.
• Für große Datensätze Durch das Erstellen eines separaten Felds „Intervall“ in der Tabelle und das Vorspeichern der Zeitintervallwerte kann die Leistung verbessert werden, es ist jedoch zusätzlicher Speicher erforderlich Leerzeichen.
• Verwenden Sie bei der Angabe des Zeitintervalls die entsprechende Postgres-Syntax, z. B. „Intervall '17 Minuten“.
• Der Operator „BETWEEN“ kann zum Filtern von Ereignissen innerhalb eines bestimmten Zeitraums verwendet werden Zeitbereich, aber achten Sie auf die ordnungsgemäße Handhabung der Ober- und Untergrenzen.
• Verwenden Sie „to_char()“, um die Spalte „start_time“ für Anzeigezwecke zu formatieren, ohne die ORDER zu beeinflussen DURCH Betrieb.

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