aggregieren Zeitreinigdaten: 5-minute Intervalle in SQL

Die Analyse von Zeitreihendaten erfordert häufig die Gruppierung von Daten in bestimmte Intervalle. Dieser Leitfaden zeigt, wie Daten mithilfe von SQL in 5-Minuten-Intervalle gruppiert werden, wobei ein Szenario behandelt wird, in dem Daten innerhalb eines definierten Zeitrahmens aggregiert werden müssen. Das Beispiel verwendet Daten aus den Tabellen "Zeit" und "ID", wobei die Ereignisse des Namens "John" gezählt werden. Die Herausforderung liegt darin, von der Gruppierung der einzelnen Zeitstempel zu einer 5-minütigen Intervallaggregation zu wechseln.

Lösungen für verschiedene Datenbanksysteme

Der optimale Ansatz hängt von Ihrem Datenbanksystem ab. Hier sind Lösungen für Postgresql und MySQL:

postgresql

postgresql bietet einen flexiblen Ansatz mit extract ('epoch') , um den Unix -Zeitstempel (Sekunden seit Epoch) und inval :

SELECT
    date_trunc('minute', timestamp)   INTERVAL '5 minutes' * (extract(minute from timestamp)::int / 5) AS five_minute_interval,
    name,
    COUNT(b.name)
FROM time a, id b
WHERE ... -- Your WHERE clause here
GROUP BY five_minute_interval, name
ORDER BY five_minute_interval;

Diese Abfrage verkürzt zuerst den Zeitstempel mit Date_Trunc

mysql

MySQL bietet eine einfachere Lösung mit unix_timestamp ()

SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp) / 300) * 300) AS five_minute_interval,
    name,
    COUNT(b.name)
FROM time a, id b
WHERE ... -- Your WHERE clause here
GROUP BY five_minute_interval, name
ORDER BY five_minute_interval;

Diese Abfrage wandelt den Zeitstempel in einen Unix -Zeitstempel um, führt eine ganzzahlige Abteilung um 300 (Sekunden in 5 Minuten) durch und wandelt dann das Ergebnis mit von_unixtime ()

zu einem Zeitstempel um. Beide Abfragen gruppieren die Ergebnisse nach dem berechneten 5-Minuten-Intervall und dem Namen und liefern die gewünschte aggregierte Ausgabe. Denken Sie daran, ... durch Ihre spezifische wobei Klausel. Die Order by