Wie kann man Zeilen in SQL effizient nach Zeitintervallen zählen?

Zeilen nach Zeitintervallen zählen: Enthüllung des besten Ansatzes

Anwendungen befassen sich häufig mit zeitgestempelten Ereignissen und erfordern die Analyse von Daten auf der Grundlage einer bestimmten Zeit Intervalle. Um die Anzahl der Ereignisse innerhalb jedes Intervalls effektiv zu zählen, können Entwickler über verschiedene Ansätze nachdenken.

1. Dynamische SQL-Abfrage für beliebige Intervalle

Die Verwendung einer SQL-Abfrage zum Gruppieren von Ereignissen nach beliebigen Zeitintervallen ist eine effiziente und bevorzugte Methode. Allerdings unterstützt Postgres diese Funktionalität nicht nativ.

2. Brute-Force-Methode mit Zeitsortierung

Das Abfragen aller Datensätze innerhalb eines Start-/Endzeitrahmens und das manuelle Zählen dieser Datensätze nach gewünschten Intervallen ist ein unkomplizierter, aber umständlicher Ansatz. Es erfordert eine Sortierung der Ergebnisse nach Zeitstempel und es fehlt die dynamische Intervallanpassung.

3. Intervalldaten vorab speichern

Durch die Pflege separater Felder für jedes Intervall in der Ereignistabelle können Laufzeitberechnungen vermieden und die Berichterstellung vereinfacht werden. Allerdings verdoppelt diese Methode die Tabellengröße, was möglicherweise nicht optimal ist.

Best Practice

Die Best Practice hängt von den spezifischen Anforderungen der Anwendung und der Datengröße ab:

• Für eine große Anzahl von Intervallen oder eine beliebige Intervallanpassung eignet sich eine dynamische SQL-Abfrage. Während Postgres keine integrierte Unterstützung bietet, gibt es Problemumgehungen mithilfe von Fensterfunktionen wie „lead()“.
• Für eine kleine Anzahl fester Intervalle (z. B. Tage oder Wochen) Intervalldaten vorab speichern kann die Abfrageausführung vereinfachen, erhöht aber die Tabellengröße.
• Für eine Echtzeitlösung oder wenn Tabellenwachstum ein Problem darstellt, die Brute-Force-Methode mit Zeitsortierung ist möglicherweise besser geeignet.

Postgres 14 mit der Funktion date_bin()

Für Postgres 14 und neuer die Funktion date_bin() ermöglicht die direkte Gruppierung von Zeitstempeln in Intervallen. Dies vereinfacht Abfragen und liefert effiziente Ergebnisse.

Beispiel: Zählen von Ereignissen in jedem 15-Minuten-Intervall

Zählen von Ereignissen in jedem 15-Minuten-Intervall innerhalb eines bestimmten Zeitbereichs mit date_bin() Funktion:

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;

Hinweis:

Für Postgres 13 und früher sind alternative Ansätze mit generic_series() und Fensterfunktionen verfügbar.

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