Wie gruppiere ich Zeitreihendaten mithilfe von SQL in 5-Minuten-Intervalle?

Zeitreihendaten aggregieren: 5-Minuten-Intervalle in SQL

Die Analyse von Zeitreihendaten erfordert häufig die Gruppierung von Daten in bestimmte Intervalle. Dieser Leitfaden zeigt, wie Sie Daten mithilfe von SQL in 5-Minuten-Intervallen gruppieren und ein Szenario behandeln, in dem Daten innerhalb eines definierten Zeitrahmens aggregiert werden müssen. Das Beispiel verwendet Daten aus den Tabellen „time“ und „id“ und zählt die Vorkommen des Namens „John“. Die Herausforderung besteht darin, von der Gruppierung einzelner Zeitstempel zur Aggregation in 5-Minuten-Intervallen überzugehen.

Lösungen für unterschiedliche Datenbanksysteme

Der optimale Ansatz variiert je nach Ihrem Datenbanksystem. Hier sind Lösungen für PostgreSQL und MySQL:

PostgreSQL

PostgreSQL bietet einen flexiblen Ansatz mit extract('epoch') zum Abrufen des Unix-Zeitstempels (Sekunden seit der Epoche) und INTERVAL:

<code class="language-sql">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;</code>

Diese Abfrage kürzt zunächst den Zeitstempel mit date_trunc auf die Minute. Anschließend berechnet es das 5-Minuten-Intervall durch Addition von Vielfachen von 5 Minuten basierend auf der Minute des ursprünglichen Zeitstempels.

MySQL

MySQL bietet eine einfachere Lösung mit UNIX_TIMESTAMP() und Ganzzahldivision:

<code class="language-sql">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;</code>

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

zurück in einen Zeitstempel um

Beide Abfragen gruppieren die Ergebnisse nach dem berechneten 5-Minuten-Intervall und dem Namen und liefern so die gewünschte aggregierte Ausgabe. Denken Sie daran, ... durch Ihre spezifische WHERE-Klausel zu ersetzen. Die ORDER BY-Klausel sorgt für eine chronologische Darstellung der Ergebnisse.

Das obige ist der detaillierte Inhalt vonWie gruppiere ich Zeitreihendaten mithilfe von SQL in 5-Minuten-Intervalle?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!