Analysieren von Zeitreihendaten¶
Sie können in Snowflake Zeitreihendaten analysieren, indem Sie die speziell für diesen Zweck entwickelten Funktionen nutzen. Datenbankadministratoren, Data Scientists und Anwendungsentwickler müssen sicherstellen, dass die Zeitreihen effizient gespeichert und geladen und in vielen Fällen in einer vollständigen und konsistenten Form zusammengefasst werden, bevor sie die Daten für Geschäftsanalysten und andere Verbraucher zur Verfügung stellen.
Einführung: Was ist eine Zeitreihe?¶
Eine Zeitreihe besteht aus aufeinanderfolgenden Beobachtungen, die erfassen, wie sich Systeme, Prozesse und Verhaltensweisen über einen bestimmten Zeitraum hinweg verändern. Zeitreihendaten werden von einer Vielzahl von Geräten in einer Vielzahl von Branchen gesammelt. Gängige Beispiele sind Aktienhandelsdaten, die für Finanzanwendungen gesammelt werden, Wetterbeobachtungen, Temperaturmessungen, die von Sensoren in intelligenten Fabriken erfasst werden, oder Protokolle der Klicks von Benutzern in der digitalen Werbung.
Ein einzelner Datensatz in einer Zeitreihe besteht in der Regel aus den folgenden Komponenten:
Datum, Uhrzeit oder Zeitstempel mit einer einheitlichen Granularität (Millisekunden, Sekunden, Minuten, Stunden usw.).
Eine oder mehrere Messungen oder Metriken irgendeiner Art, in der Regel numerisch (Fakten, die Trends oder Anomalien in den Daten aufzeigen können).
Dimensionen von Interesse, die mit der Messung verbunden sind, z. B. ein Standort für eine Temperaturmessung oder ein Aktiensymbol für einen bestimmten Handel.
Die folgende Wetterbeobachtung enthält beispielsweise Zeitstempel für Beginn und Ende, eine Niederschlagsmessung (0.32) und Standortinformationen:
EVENTID | TYPE | SEVERITY | START_TIME | END_TIME | PRECIP | TIME_ZONE | CITY | COUNTY | STATE | ZIP
W100 | Rain | Moderate | 2020-12-20 16:35:00.000 | 2020-12-20 17:15:00.000 | 0.32 | US/Eastern | Southport | Brunswick | NC | 28461
Die folgenden von einem Fabrikgerät gesammelten Daten haben einen Namespace (IOT), eine Tag-ID oder Sensor-ID (3000), einen Zeitstempel für den Temperaturmesswert auf dem Gerät, den Temperaturmesswert selbst (21.1673) und einen „Broker-Zeitstempel“, der angibt, wann die Daten anschließend beim Datenbroker angekommen sind. Der Datenbroker könnte zum Beispiel ein Kafka-Server sein, der Daten in eine Snowflake-Tabelle einspeist.
DEVICE | LINE | DEVICE_TIMESTAMP | TEMP | BROKER_TIMESTAMP
IOT | 3000 | 2023-01-01 00:01:00.000 | 21.1673 | 2023-01-01 00:01:32.000
Eine Zeitreihe kann Spitzen aufweisen, wenn sich die Messwerte aus irgendeinem Grund dramatisch verändern. Die folgende Abbildung zeigt zum Beispiel eine Sequenz von Temperaturmessungen, die in 15-Sekunden-Intervallen vorgenommen wurden. Die Werte erreichten Spitzenwerte von über 40 °C, nachdem sie am Vortag konstant im Bereich von 35 °C gelegen hatten.
Die folgenden Abschnitte zeigen, wie Sie große Mengen dieser Art von Daten mit SQL-Funktionen und Verknüpfungen (Joins) analysieren und visualisieren können, die schnelle und genaue Ergebnisse liefern.
Speichern von Zeitreihendaten¶
Die folgenden datetime-Datentypen werden unterstützt:
DATE
TIME
TIMESTAMP (und Variationen, einschließlich TIMESTAMP_TZ)
Informationen zum Laden, Verwalten und Abfragen von Daten, die diese Datentypen verwenden, finden Sie unter Verwenden von Datums- und Zeitwerten.
Für das Speichern und Abfragen von Zeitreihendaten gibt es eine Reihe von häufig verwendeten SQL-Funktionen. Sie können zum Beispiel CONVERT_TIMEZONE verwenden, um Zeitstempel von einer Zeitzone in eine andere zu konvertieren, und Sie können Funktionen wie EXTRACT und TIMEADD verwenden, um zeitbasierte Daten nach Bedarf zu bearbeiten.
Bemerkung
Bei TIMESTAMP_TZ-Daten speichert Snowflake zum Zeitpunkt der Erstellung für einen bestimmten Wert nur den Offset einer gegebenen Zeitzone, nicht die Zeitzone selbst.
Um die Abfrageleistung zu optimieren, werden Tabellen, die für Zeitreihenanalysen verwendet werden, oft nach Zeit (und manchmal auch nach Sensor-ID oder einer ähnlichen Dimension) geclustert. Siehe Gruppierungsschlüssel und geclusterte Tabellen.
Aggregieren von Zeitreihendaten¶
Das Verwalten von Zeitreihendaten kann die Aggregation großer Mengen feinkörniger Datensätze in eine besser zusammengefasste Form erfordern (ein Prozess, der manchmal als „Downsampling“ bezeichnet wird). Bei einer großen Menge von Datensätzen mit einer bestimmten zeitlichen Granularität (Millisekunden, Sekunden, Minuten usw.) können Sie diese Datensätze auf eine gröbere Granularität hochrechnen und so effektiv eine kleinere Stichprobe erstellen.
Downsampling ist wertvoll, weil es die Größe eines Datensets und seine Speicheranforderungen verringert. Eine gröbere Granularität reduziert auch den Bedarf an Computeressourcen während der Ausführung der Abfrage. Ein weiterer wichtiger Grund für Downsampling ist, dass eine große Anzahl von Datensätzen in einer Zeitreihe aus Sicht eines Analysten redundant sein kann. Wenn ein Sensor beispielsweise einmal pro Sekunde einen neuen Wert ausgibt, dieser Messwert sich aber innerhalb eines 60-Sekunden-Intervalls kaum ändert, können die Daten zur Analyse auf die Minutenebene zusammengefasst werden.
Ein weiterer Fall für Downsampling tritt auf, wenn zwei verschiedene Datensets als ein einziger analysiert werden müssen, die Datensets jedoch eine unterschiedliche zeitliche Granularität aufweisen. Beispiel: Sensor A sammelt Daten in einer Fabrik alle 15 Sekunden, aber Sensor B sammelt die entsprechenden Daten alle 30 Sekunden. In diesem Fall könnte die Aggregation der Datensätze in 1-Minuten-Buckets eine gute Lösung sein. IDs und Dimensionen in jedem Datenset werden unverändert beibehalten, aber die numerischen Messungen werden summiert oder durch ein gemeinsames Zeitintervall gemittelt.
Beispiele für Downsampling¶
Sie können ein Datenset, das in einer Tabelle gespeichert ist, mit der Funktion TIME_SLICE verkleinern. Diese Funktion berechnet die Start- und Endzeiten von „Buckets“ fester Breite, sodass einzelne Datensätze gruppiert und zusammengefasst werden können, wobei Standardaggregatfunktionen wie SUM und AVG verwendet werden.
In ähnlicher Weise schneidet die Funktion DATE_TRUNC einen Teil einer Reihe von Datums- oder Zeitstempelwerten ab und reduziert so deren Granularität. In den folgenden Abschnitten finden Sie Beispiele für die einzelnen Funktionen.
Downsampling mit TIME_SLICE¶
Das folgende Beispiel zeigt eine Tabelle mit dem Namen sensor_data_ts, die Messwerte von zwei Werkssensoren enthält und 5,3 Millionen Zeilen umfasst. Diese Messwerte wurden pro Sekunde aufgenommen, sodass 5,3 Millionen Zeilen nur einen Monat an Daten darstellen, mit etwas mehr als 2,5 Millionen Zeilen pro Sensor. Sie können die Funktion TIME_SLICE verwenden, um beispielsweise eine Aggregation auf eine einzige Zeile pro Minute, pro Stunde oder pro Tag vorzunehmen.
Um dieses Beispiel auszuführen, erstellen und laden Sie zunächst die Tabelle sensor_data_ts. Informationen dazu finden Sie unter Erstellen der Tabelle „sensor_data_ts“. Hier ist eine kleine Stichprobe der Daten aus der Tabelle:
+-----------+-------------------------+-------------+-----------+-----------+
| DEVICE_ID | TIMESTAMP | TEMPERATURE | VIBRATION | MOTOR_RPM |
|-----------+-------------------------+-------------+-----------+-----------|
| DEVICE1 | 2024-03-01 00:00:00.000 | 32.6908 | 0.3158 | 1492 |
| DEVICE2 | 2024-03-01 00:00:00.000 | 35.2086 | 0.3232 | 1461 |
| DEVICE1 | 2024-03-01 00:00:01.000 | 35.9578 | 0.3302 | 1452 |
| DEVICE2 | 2024-03-01 00:00:01.000 | 26.2468 | 0.3029 | 1455 |
+-----------+-------------------------+-------------+-----------+-----------+
Die Tabelle enthält 60 Messwerte wie diese pro Minute für jedes Gerät, wie die folgende Abfrage zeigt:
SELECT device_id, count(*) FROM sensor_data_ts
WHERE TIMESTAMP >= ('2024-03-01 00:01:00')
AND TIMESTAMP < ('2024-03-01 00:02:00')
GROUP BY device_id;
+-----------+----------+
| DEVICE_ID | COUNT(*) |
|-----------+----------|
| DEVICE2 | 60 |
| DEVICE1 | 60 |
+-----------+----------+
In dieser Downsampling-Abfrage definiert die Funktion TIME_SLICE einminütige Buckets und gibt die Startzeit jedes Buckets zurück. Die Funktion AVG berechnet die durchschnittliche Temperatur für jeden Bucket pro Gerät. Die Funktion COUNT(*) ist nur als Referenz enthalten, um zu zeigen, wie viele Zeilen in jedem Bucket aufgenommen werden.
Die Spalten vibration und motor_rpm sind nicht enthalten, aber sie könnten auf die gleiche Weise wie die Spalte temperature oder durch Verwendung anderer Aggregatfunktionen aggregiert werden.
Wichtig
Wenn Sie dieses Beispiel selbst ausführen, wird Ihre Ausgabe nicht genau übereinstimmen, da die Tabelle sensor_data_ts mit zufällig generierten Werten geladen ist.
SELECT
TIME_SLICE(TO_TIMESTAMP_NTZ(timestamp), 1, 'MINUTE') minute_slice,
device_id,
COUNT(*),
AVG(temperature) avg_temp
FROM sensor_data_ts
WHERE TIMESTAMP >= ('2024-03-01 00:01:00')
AND TIMESTAMP < ('2024-03-01 00:02:00')
GROUP BY 1,2
ORDER BY 1,2;
+-------------------------+-----------+----------+---------------+
| MINUTE_SLICE | DEVICE_ID | COUNT(*) | AVG_TEMP |
|-------------------------+-----------+----------+---------------|
| 2024-03-01 00:01:00.000 | DEVICE1 | 60 | 32.4315466667 |
| 2024-03-01 00:01:00.000 | DEVICE2 | 60 | 30.4967783333 |
+-------------------------+-----------+----------+---------------+
Mit der Funktion TIME_SLICE können Sie kleinere, aggregierte Tabellen für Analysezwecke erstellen und den Downsampling-Prozess auf verschiedenen Ebenen (Stunde, Tag, Woche usw.) anwenden.
Downsampling mit DATE_TRUNC¶
Im folgenden Beispiel werden Daten aus einer Tabelle namens order_header im Schema raw.pos der Beispieldatenbank „Tasty Bytes“ ausgewählt. Diese Tabelle enthält 248M Zeilen.
Die Tabelle order_header hat eine TIMESTAMP-Spalte namens order_ts. Die Abfrage erstellt eine aggregierte Zeitreihe, indem diese Spalte als zweites Argument der Funktion DATE_TRUNC verwendet wird. Das erste Argument gibt ein day-Intervall an. Das bedeutet, dass die einzelnen Datensätze, die eine Granularität von Stunden/Minuten/Sekunden haben, zu Tagen zusammengefasst werden.
Die Abfrage gruppiert die Datensätze nach zwei Dimensionen: truck_id und location_id. Die Spalte avg_amount gibt den Durchschnittspreis pro Bestellung, pro Food Truck, pro Standort für jeden erfassten Geschäftstag zurück.
Die hier gezeigte Abfrage beschränkt die Ergebnisse auf die ersten 25 Zeilen für den 1. Januar 2022. Wenn Sie diesen Datumsfilter und die LIMIT-Klausel entfernen, verkleinert die Abfrage die ursprünglichen 248M Zeilen auf etwa 500.000 Zeilen.
SELECT DATE_TRUNC('day', order_ts)::date sliced_ts, truck_id, location_id, AVG(order_amount)::NUMBER(4,2) as avg_amount
FROM order_header
WHERE EXTRACT(YEAR FROM order_ts)='2022'
GROUP BY date_trunc('day', order_ts), truck_id, location_id
ORDER BY 1, 2, 3 LIMIT 25;
+------------+----------+-------------+------------+
| SLICED_TS | TRUCK_ID | LOCATION_ID | AVG_AMOUNT |
|------------+----------+-------------+------------|
| 2022-01-01 | 1 | 3223 | 19.23 |
| 2022-01-01 | 1 | 3869 | 20.15 |
| 2022-01-01 | 2 | 2401 | 39.29 |
| 2022-01-01 | 2 | 4199 | 34.29 |
| 2022-01-01 | 3 | 2883 | 35.01 |
| 2022-01-01 | 3 | 2961 | 39.15 |
| 2022-01-01 | 4 | 2614 | 35.95 |
| 2022-01-01 | 4 | 2899 | 40.29 |
| 2022-01-01 | 6 | 1946 | 26.58 |
| 2022-01-01 | 6 | 14960 | 18.59 |
| 2022-01-01 | 7 | 1427 | 26.91 |
| 2022-01-01 | 7 | 3224 | 28.88 |
| 2022-01-01 | 9 | 1557 | 35.52 |
| 2022-01-01 | 9 | 2612 | 43.80 |
| 2022-01-01 | 10 | 2217 | 32.35 |
| 2022-01-01 | 10 | 2694 | 32.23 |
| 2022-01-01 | 11 | 2656 | 44.23 |
| 2022-01-01 | 11 | 3327 | 52.00 |
| 2022-01-01 | 12 | 3181 | 52.84 |
| 2022-01-01 | 12 | 3622 | 49.59 |
| 2022-01-01 | 13 | 2516 | 31.13 |
| 2022-01-01 | 13 | 3876 | 28.13 |
| 2022-01-01 | 14 | 1359 | 72.04 |
| 2022-01-01 | 14 | 2505 | 68.75 |
| 2022-01-01 | 15 | 2901 | 41.90 |
+------------+----------+-------------+------------+
Verwenden von fensterbasierten Aggregationen für fortlaufende Berechnungen¶
Mithilfe von fensterbasierten Aggregatfunktionen können Sie beobachten, wie sich eine Kennzahl über die Zeit verändert, und eine Zeitreihe auf Trends hin analysieren. Fensterbasierte Aggregationen sind nützlich für die Analyse von Daten innerhalb definierter Teilmengen („Fenster“) eines größeren Datensets. Sie können fortlaufende Berechnung (wie gleitende Durchschnittswerte und Summen) für jede Zeile in einem Datenset durchführen und dabei eine Gruppe von Zeilen vor, nach oder um die aktuelle Zeile herum berücksichtigen. Diese Art der Analyse steht im Gegensatz zu regulären Aggregationen, die das gesamte Datenset zusammenfassen.
Durch die Verwendung von rangbezogenen Fensterrahmen mit expliziten Offsets können Sie einen sehr flexiblen Ansatz für die Berechnung dieser rollierenden Aggregationen anwenden. Der nach Zeitstempeln oder Zahlen geordnete Fensterrahmen RANGE BETWEEN wird nicht durch Lücken gestört, die in Zeitreihendaten auftreten können. In der folgenden Abbildung zum Beispiel hat die Tatsache, dass die Daten von Day 4 in der Datensatzreihe fehlen, keinen Einfluss auf die Berechnung der Aggregatfunktionen über ein gleitendes Zeitfenster von drei Tagen. Insbesondere die Rahmen 3, 4 und 5 werden korrekt berechnet, wobei berücksichtigt wird, dass die Daten von Day 4 unbekannt sind.
Im folgenden Beispiel wird eine gleitende Summe über Wetterdaten berechnet, die stündliche Niederschlagswerte in verschiedenen Städten und Landkreisen aufzeichnen. Sie können diese Art von Abfrage ausführen, um Trends in verschiedenen Zeitseriendatensätzen auszuwerten, z. B. von Sensoren und anderen IoT-Geräten, insbesondere wenn diese Datensätze bekanntermaßen oder voraussichtlich Lücken aufweisen.
Die Fensterfunktion umfasst in ihrem Rahmen den aktuellen Niederschlagsmesswert und alle Messwerte, die innerhalb des angegebenen Zeitintervalls vor dem aktuellen Messwert liegen. Die fortlaufende Berechnung basiert auf diesem flexiblen und logischen Bereich von Zeilen und nicht auf einer exakten Anzahl von Zeilen. Die erste Zeile für jede Stadt enthält übereinstimmende Werte für precip und moving_sum_precip. Danach wird die Summe für jede nachfolgende Zeile im Rahmen neu berechnet. Die Rohwerte schwanken erheblich, aber die gleitenden Summen haben einen starken Glättungseffekt.
Um dieses Beispiel auszuführen, befolgen Sie zunächst die folgenden Anweisungen: Erstellen und laden Sie die Tabelle heavy_weather. Diese sehr kleine Tabelle enthält sporadische stündliche Wetterbeobachtungen, mit vielen Lücken, einschließlich eines fehlenden Tages. Die Abfrage liefert die gleitende Summe der Niederschlagswerte, geordnet nach der Spalte start_time. Der Fensterrahmen definiert einen Bereich zwischen 12 Stunden vor der aktuellen Zeile und der aktuellen Zeile. Der Rahmen besteht also aus der aktuellen Zeile und nur aus den Zeilen, deren Zeitstempel bis zu 12 Stunden vor dem ORDER BY-Zeitstempel für die aktuelle Zeile liegt.
SELECT city, start_time, precip,
SUM(precip) OVER(
PARTITION BY city
ORDER BY start_time
RANGE BETWEEN INTERVAL '12 hours' PRECEDING AND CURRENT ROW) moving_sum_precip
FROM heavy_weather
WHERE city IN('South Lake Tahoe','Big Bear City')
GROUP BY city, precip, start_time
ORDER BY city;
+------------------+-------------------------+--------+-------------------+
| CITY | START_TIME | PRECIP | MOVING_SUM_PRECIP |
|------------------+-------------------------+--------+-------------------|
| Big Bear City | 2021-12-24 05:35:00.000 | 0.42 | 0.42 |
| Big Bear City | 2021-12-24 16:55:00.000 | 0.09 | 0.51 |
| Big Bear City | 2021-12-26 09:55:00.000 | 0.07 | 0.07 |
| South Lake Tahoe | 2021-12-23 16:23:00.000 | 0.56 | 0.56 |
| South Lake Tahoe | 2021-12-23 17:24:00.000 | 0.38 | 0.94 |
| South Lake Tahoe | 2021-12-23 18:30:00.000 | 0.28 | 1.22 |
| South Lake Tahoe | 2021-12-23 19:36:00.000 | 0.80 | 2.02 |
| South Lake Tahoe | 2021-12-24 06:49:00.000 | 0.17 | 0.97 |
| South Lake Tahoe | 2021-12-24 15:53:00.000 | 0.07 | 0.24 |
| South Lake Tahoe | 2021-12-26 05:43:00.000 | 0.16 | 0.16 |
| South Lake Tahoe | 2021-12-27 14:53:00.000 | 0.07 | 0.07 |
| South Lake Tahoe | 2021-12-27 17:53:00.000 | 0.07 | 0.14 |
+------------------+-------------------------+--------+-------------------+
Die drei moving_sum_precip Werte für Big Bear City werden wie folgt berechnet:
0,42 = 0,42 (keine vorangehenden Zeilen)
0,42 + 0,09 = 0,51 (die ersten beiden Zeilen liegen innerhalb des 12-Stunden-Fensters)
0,07 = 0,07 (keine vorangehenden Zeilen liegen innerhalb des 12-Stunden-Fensters)
Die South Lake Tahoe-Zeilen enthalten zum Beispiel diese Berechnungen:
0,56 + 0,38 + 0,28 + 0,80 = 2,02 (alle vier Zeilen für den 23.12.2024 liegen innerhalb von 12 Stunden voneinander)
0,80 + 0,17 = 0,97 (eine vorangehende Zeile liegt innerhalb des 12-Stunden-Fensters)
Andere Fensterfunktionen, wie die Ranking-Funktionen LEAD und LAG, werden ebenfalls häufig bei Zeitreihenanalysen verwendet. Verwenden Sie die Fensterfunktionen LEAD, um den nächsten Datenpunkt in der Zeitreihe relativ zum aktuellen Datenpunkt zu finden, und die Funktion LAG, um den vorherigen Datenpunkt zu finden.
Visualisierung von Abfrageergebnissen in Snowsight¶
Sie können Snowsight verwenden, um die Ergebnisse von Aggregationsabfragen zu visualisieren und ein besseres Gefühl für den Glättungseffekt von Berechnungen mit gleitenden Fensterrahmen zu erhalten. Klicken Sie im Arbeitsblatt der Abfrage neben Results auf die Schaltfläche Chart.
Die gelbe Linie im folgenden Balkendiagramm zeigt zum Beispiel einen viel gleichmäßigeren Trend für die Durchschnittstemperatur als die blaue Linie für den Rohwert der Temperatur. Die Abfrage selbst sieht wie folgt aus:
SELECT device_id, timestamp, temperature, AVG(temperature)
OVER (PARTITION BY device_id ORDER BY timestamp
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_temp
FROM sensor_data_ts
WHERE timestamp BETWEEN '2024-03-15 00:00:59.000' AND '2024-03-15 00:01:10.000'
ORDER BY 1, 2;
Verwenden der Aggregatfunktionen MIN_BY und MAX_BY¶
Die Möglichkeit, eine Spalte auf der Grundlage des Minimum- oder Maximumwerts einer anderen Spalte in derselben Zeile auszuwählen, ist eine häufige Anforderung an SQL-Entwickler, die mit Zeitreihendaten arbeiten. MIN_BY und MAX_BY sind Komfortfunktionen, die den Anfangs- und Endwert (oder den höchsten und niedrigsten oder den ersten und letzten) in einer Tabelle zurückgeben, wenn die Daten nach einer anderen Spalte, z. B. einem Zeitstempel, sortiert sind.
Das erste Beispiel sucht einfach den letzten (jüngsten) precip-Wert in der gesamten Tabelle. Die Funktion MAX_BY sortiert alle Zeilen nach ihrem start_time-Wert und gibt dann den precip-Wert für die „maximale“ Startzeit zurück.
Um die in den folgenden Beispielen verwendete Tabelle zu erstellen und zu laden, finden Sie entsprechende Informationen unter Erstellen der Tabelle „heavy_weather“.
SELECT MAX_BY(precip, start_time) most_recent_precip
FROM heavy_weather;
+--------------------+
| MOST_RECENT_PRECIP |
|--------------------|
| 0.07 |
+--------------------+
Sie können dieses Ergebnis überprüfen (und weitere Informationen darüber erhalten), indem Sie folgende Abfrage ausführen:
SELECT * FROM heavy_weather WHERE start_time=
(SELECT MAX(start_time) FROM heavy_weather);
+-------------------------+--------+-------+-------------+
| START_TIME | PRECIP | CITY | COUNTY |
|-------------------------+--------+-------+-------------|
| 2021-12-30 20:53:00.000 | 0.07 | Lebec | Los Angeles |
+-------------------------+--------+-------+-------------+
Sie können eine GROUP BY-Klausel hinzufügen, um weitere interessante Fragen zu diesen Daten zu stellen. Die folgende Abfrage findet zum Beispiel den letzten Niederschlagswert, der für jeden Ort in Kalifornien beobachtet wurde, geordnet nach precip-Werten (hoch bis niedrig). Die Ergebnisse werden nach city gruppiert, um den letzten precip-Wert für jeden einzelnen Ort zurückzugeben.
SELECT city, MAX_BY(precip, start_time) most_recent_precip
FROM heavy_weather
GROUP BY city
ORDER BY 2 DESC;
+------------------+--------------------+
| CITY | MOST_RECENT_PRECIP |
|------------------+--------------------|
| Alta | 0.89 |
| Bishop | 0.75 |
| Mammoth Lakes | 0.37 |
| Alturas | 0.23 |
| Mount Shasta | 0.09 |
| South Lake Tahoe | 0.07 |
| Big Bear City | 0.07 |
| Montague | 0.07 |
| Lebec | 0.07 |
+------------------+--------------------+
Bei der letzten Beobachtung in der Stadt Alta war der precip-Wert 0.89 und bei der letzten Beobachtung in den Städten South Lake Tahoe, Big Bear City, Montague und Lebec war der precip-Wert 0.07 für alle vier Orte. (Beachten Sie, dass die Abfrage Ihnen nicht mitteilt, wann diese Beobachtungen erfasst wurden.)
Mit der Funktion MIN_BY können Sie das „umgekehrte“ Resultset zurückgeben (ältester precip-Datensatz versus jüngster).
SELECT city, MIN_BY(precip, start_time) oldest_precip
FROM heavy_weather
GROUP BY city
ORDER BY 2 DESC;
+------------------+---------------+
| CITY | OLDEST_PRECIP |
|------------------+---------------|
| South Lake Tahoe | 0.56 |
| Big Bear City | 0.42 |
| Mammoth Lakes | 0.37 |
| Alta | 0.25 |
| Alturas | 0.23 |
| Bishop | 0.08 |
| Lebec | 0.08 |
| Mount Shasta | 0.08 |
| Montague | 0.07 |
+------------------+---------------+
Verknüpfen von Zeitreihendaten¶
Sie können das ASOF JOIN-Konstrukt verwenden, um Tabellen zu verknüpfen, die Zeitreihendaten enthalten. Obwohl ASOF JOIN-Abfragen durch die Verwendung von komplexer SQL, anderen Typen von Joins und Fensterfunktionen emuliert werden können, sind diese Abfragen einfacher zu schreiben (und oft leistungsfähiger), wenn Sie die ASOF JOIN-Syntax verwenden.
Eine häufige Anwendung für ASOF-Joins ist die Analyse von Finanzhandelsdaten. Für die Transaktionskostenanalyse sind beispielsweise „Slippage“-Berechnungen erforderlich, mit denen die Differenz zwischen dem zum Zeitpunkt der Kaufentscheidung einer Aktie notierten Kurs und dem tatsächlich gezahlten Preis bei Ausführung und Verbuchung der Order gemessen wird. Mit ASOF JOIN können diese Typen von Analysen beschleunigt werden. Da die Hauptfähigkeit dieser Join-Methode in der Analyse einer Zeitreihe in Bezug auf eine andere besteht, kann ASOF JOIN für die Analyse jedes beliebigen historischen Datensets nützlich sein. In vielen dieser Anwendungsfälle kann ASOF JOIN verwendet werden, um Daten zuzuordnen, wenn die Zeitstempel der Messwerte verschiedener Geräte nicht exakt übereinstimmen.
Es wird davon ausgegangen, dass die zu analysierenden Zeitreihendaten in zwei Tabellen vorliegen und dass es für jede Zeile in jeder Tabelle einen Zeitstempel gibt. Dieser Zeitstempel gibt das genauen („as of“) Zeitpunkt (Datum und Uhrzeit) für ein erfasstes Ereignis an. Für jede Zeile in der ersten (oder linken) Tabelle verwendet die Verknüpfung eine „Übereinstimmungsbedingung“ mit einem Vergleichsoperator, den Sie angeben, um eine einzelne Zeile in der zweiten (oder rechten) Tabelle zu finden, bei der der Zeitstempelwert eines der folgenden Merkmale aufweist:
Kleiner als oder gleich dem Zeitstempelwert in der linken Tabelle
Größer als oder gleich dem Zeitstempelwert in der linken Tabelle
Kleiner als der Zeitstempelwert in der linken Tabelle
Größer als der Zeitstempelwert in der linken Tabelle
Die passende Zeile auf der rechten Seite ist die mit der besten Übereinstimmung, deren Zeitpunkt gleich, früher oder später liegen kann, je nach dem angegebenen Vergleichsoperator.
Die Kardinalität des Ergebnisses von ASOF JOIN ist immer gleich der Kardinalität der linken Tabelle. Wenn die linke Tabelle 40 Millionen Zeilen enthält, gibt ASOF JOIN 40 Millionen Zeilen. Daher kann die linke Tabelle als „bewahrende“ Tabelle und die rechte Tabelle als „referenzierte“ Tabelle gedacht werden.
Verknüpfen zweier Tabellen anhand der engsten Übereinstimmung (Alignment)¶
In einer Finanzanwendung könnten Sie zum Beispiel eine Tabelle namens quotes und eine Tabelle namens trades haben. In der einen Tabelle wird die Historie der Kaufangebote für Aktien erfasst, in der anderen die Historie des tatsächlichen Handels. Ein Angebot zum Kauf von Aktien erfolgt vor dem Handel (oder möglicherweise zur „gleichen“ Zeit, je nach Granularität der erfassten Zeit). Beide Tabellen enthalten Zeitstempel, und beide enthalten andere Spalten von Interesse, die Sie vielleicht vergleichen möchten. Eine einfache ASOF JOIN-Abfrage liefert den (zeitlich) nächstgelegenen Aktienkurs vor dem jeweiligen Handel. Mit anderen Worten, die Frage lautet: Wie hoch war der Kurs einer bestimmten Aktie zu dem Zeitpunkt, als ich einen Handel tätigte?
Angenommen, die Tabelle trades enthält drei Zeilen und die Tabelle quotes enthält sieben Zeilen. Die Hintergrundfarbe der Zellen zeigt an, welche drei Zeilen aus quotes für ASOF JOIN in Frage kommen, wenn die Zeilen bei übereinstimmenden Aktiensymbolen verknüpft und ihre Zeitstempelspalten verglichen werden.
Tabelle TRADES (linke oder „bewahrende“ Tabelle)
Tabelle QUOTES (rechte oder „referenzierte“ Tabelle)
Dieses konzeptionelle Beispiel lässt sich leicht in eine konkrete ASOF JOIN-Abfrage umwandeln:
SELECT t.stock_symbol, t.trade_time, t.quantity, q.quote_time, q.price
FROM trades t ASOF JOIN quotes q
MATCH_CONDITION(t.trade_time >= quote_time)
ON t.stock_symbol=q.stock_symbol
ORDER BY t.stock_symbol;
+--------------+-------------------------+----------+-------------------------+--------------+
| STOCK_SYMBOL | TRADE_TIME | QUANTITY | QUOTE_TIME | PRICE |
|--------------+-------------------------+----------+-------------------------+--------------|
| AAPL | 2023-10-01 09:00:05.000 | 2000 | 2023-10-01 09:00:03.000 | 139.00000000 |
| SNOW | 2023-10-01 09:00:05.000 | 1000 | 2023-10-01 09:00:02.000 | 163.00000000 |
| SNOW | 2023-10-01 09:00:10.000 | 1500 | 2023-10-01 09:00:08.000 | 165.00000000 |
+--------------+-------------------------+----------+-------------------------+--------------+
Die ON-Bedingung gruppiert die übereinstimmenden Zeilen nach ihren Aktiensymbolen.
Um dieses Beispiel auszuführen, erstellen und laden Sie die Tabellen wie folgt:
CREATE OR REPLACE TABLE trades (
stock_symbol VARCHAR(4),
trade_time TIMESTAMP_NTZ(9),
quantity NUMBER(38,0)
);
CREATE OR REPLACE TABLE quotes (
stock_symbol VARCHAR(4),
quote_time TIMESTAMP_NTZ(9),
price NUMBER(12,8)
);
INSERT INTO trades VALUES
('SNOW','2023-10-01 09:00:05.000', 1000),
('AAPL','2023-10-01 09:00:05.000', 2000),
('SNOW','2023-10-01 09:00:10.000', 1500);
INSERT INTO quotes VALUES
('SNOW','2023-10-01 09:00:01.000', 166.00),
('SNOW','2023-10-01 09:00:02.000', 163.00),
('SNOW','2023-10-01 09:00:07.000', 166.00),
('SNOW','2023-10-01 09:00:08.000', 165.00),
('AAPL','2023-10-01 09:00:03.000', 139.00),
('AAPL','2023-10-01 09:00:07.000', 142.00),
('AAPL','2023-10-01 09:00:11.000', 142.00);
Weitere Beispiele für ASOF JOIN-Abfragen finden Sie unter Beispiele.
Füllen von Datenlücken mit ASOF JOIN¶
Zusätzlich zum Abgleichen der Daten von zwei Tabellen anhand nicht exakter Übereinstimmungen in zeitbasierten Spalten ist ASOF JOIN nützlich, um Lücken in einer Zeitreihe zu füllen, wenn in Ihrer Rohdatentabelle Zeilen für bestimmte Daten oder Zeitstempel fehlen. Dieser Vorgang wird auch als „Gapfilling“ oder „Interpolation“ bezeichnet Wenn Zeilen fehlen, weil fehlerhafte Geräte oder ein Stromausfall zu übersprungenen Sensormesswerten führen, können Sie ASOF JOIN verwenden, um Werte aus einer generierten Zeitreihe in die Tabelle zu interpolieren. Die fehlenden Zeilen werden mit dem letzten bekannten Wert für die fehlenden Messwerte aufgefüllt. Dieser Wert wird auch als „letzte übertragene Beobachtung“ (LOCF, Last Observation Carried Forward) bezeichnet. Die ASOF JOIN-Abfrage gibt einen vollständigen Satz von Zeilen zurück, die in chronologischer Reihenfolge und zusammenhängend sind.
Um ASOF JOIN für die Interpolation zu verwenden, gehen Sie wie folgt vor:
Identifizieren Sie die Lücken in Ihrer Tabelle, indem Sie eine einfache Abfrage durchführen.
Generieren Sie eine vollständige Zeitreihe mit der entsprechenden Körnung für den Zeitraum, den Sie abdecken möchten. Ihre Zeitreihe könnte beispielsweise eine einfache Sequenz von Daten für ein bestimmtes Jahr sein oder eine viel feinere Sequenz von Zeitstempeln pro Sekunde für eine bestimmte Anzahl von Tagen. Sie können SQL oder ein Tabellenkalkulationsprogramm verwenden, um die Liste der Werte zu generieren.
Die Zeitreihe benötigt außerdem eine aussagekräftige ID oder Dimension für jede Zeile, die Sie später in der ASOF JOIN ON-Bedingung angeben werden.
Schreiben Sie eine ASOF JOIN-Abfrage, die Werte in die fehlenden Zeilen interpoliert. Die generierte Zeitreihe ist die bewahrende Tabelle und die Rohdatentabelle ist die referenzierte Tabelle.
Für das folgende Beispiel benötigen Sie die Tabelle sensor_data_ts. Falls Sie diese noch nicht erstellt und geladen haben, finden Sie entsprechende Informationen unter Erstellen der Tabelle „sensor_data_ts“. Um die Notwendigkeit für eine Lückenfüller-Operation zu simulieren, löschen Sie einige Zeilen wie folgt aus der Tabelle:
DELETE FROM sensor_data_ts
WHERE device_id='DEVICE2'
AND TIMESTAMP > ('2024-03-07 00:01:15')
AND TIMESTAMP <= ('2024-03-07 00:01:20');
Das Ergebnis ist eine Tabelle, in der fünf Zeilen für DEVICE2 am 7. März (1:16 bis 1:20) fehlen.
+------------------------+
| number of rows deleted |
|------------------------|
| 5 |
+------------------------+
Führen Sie nun die folgenden Schritte aus, um die Lücken zu füllen.
Bemerkung
Wenn Sie dieses Beispiel selbst ausführen, wird Ihre Ausgabe nicht genau übereinstimmen, da die Tabelle sensor_data_ts mit zufällig generierten Werten geladen ist.
Schritt 1: Überprüfen, ob die Tabelle Lücken hat¶
Führen Sie die folgende Abfrage aus, um die Lücken zu identifizieren:
SELECT * FROM sensor_data_ts
WHERE device_id='DEVICE2'
AND TIMESTAMP >= ('2024-03-07 00:01:15')
AND TIMESTAMP <= ('2024-03-07 00:01:21')
ORDER BY TIMESTAMP;
+-----------+-------------------------+-------------+-----------+-----------+
| DEVICE_ID | TIMESTAMP | TEMPERATURE | VIBRATION | MOTOR_RPM |
|-----------+-------------------------+-------------+-----------+-----------|
| DEVICE2 | 2024-03-07 00:01:15.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:21.000 | 28.0426 | 0.2944 | 1448 |
+-----------+-------------------------+-------------+-----------+-----------+
Diese Abfrage gibt zwei Zeilen für DEVICE2 zurück: die letzte Zeile vor der Lücke und die erste Zeile nach der Lücke.
Schritt 2: Generieren einer vollständigen Zeitreihe, um die bekannten Lücken abzudecken¶
Um eine Zeitreihe mit feiner Körnung (eine Zeile pro Sekunde) für die Lücke in der Tabelle sensor_data_ts zu generieren, erstellen Sie die folgende Tabelle, die generierte Zeitstempel enthält:
CREATE OR REPLACE TABLE continuous_timestamps AS
SELECT 'DEVICE2' as DEVICE_ID,
DATEADD('SECOND', ROW_NUMBER() OVER (ORDER BY SEQ8()), '2024-03-07 00:01:15')::TIMESTAMP_NTZ AS TS
FROM TABLE(GENERATOR(ROWCOUNT => 5));
In dieser SQL-Anweisung steht 5 für die Anzahl der Sekunden, die Sie benötigen, um die Lücke zu schließen. Beachten Sie, dass der Wert für die Gerät-ID (DEVICE2) in den generierten Zeilen enthalten ist.
Die folgende Abfrage gibt die fünf generierten Zeilen zurück.
SELECT * FROM continuous_timestamps ORDER BY ts;
+-----------+-------------------------+
| DEVICE_ID | TS |
|-----------+-------------------------|
| DEVICE2 | 2024-03-07 00:01:16.000 |
| DEVICE2 | 2024-03-07 00:01:17.000 |
| DEVICE2 | 2024-03-07 00:01:18.000 |
| DEVICE2 | 2024-03-07 00:01:19.000 |
| DEVICE2 | 2024-03-07 00:01:20.000 |
+-----------+-------------------------+
Schritt 3: Interpolieren der Werte mit ASOF JOIN¶
Jetzt können Sie eine ASOF JOIN-Abfrage ausführen, die continuous_timestamps mit sensor_data_ts verknüpft und Werte für fehlende Zeilen für DEVICE2 interpoliert. Die Übereinstimmungsbedingung findet für jede fehlende Zeile die zeitlich nächstgelegene Zeile, und die ON-Bedingung garantiert, dass die Interpolation auf übereinstimmenden Gerät-IDs erfolgt.
Die Zeile, die den fehlenden Zeilen am nächsten kommt, ist die Zeile mit dem Zeitstempel 2024-03-07 00:01:16.000, vorausgesetzt, dass >= in der Übereinstimmungsbedingung angegeben ist, wie in diesem Beispiel gezeigt.
INSERT INTO sensor_data_ts(device_id, timestamp, temperature, vibration, motor_rpm)
SELECT t.device_id, t.ts, s.temperature, s.vibration, s.motor_rpm
FROM continuous_timestamps t
ASOF JOIN sensor_data_ts s
MATCH_CONDITION(t.ts >= s.timestamp)
ON t.device_id = s.device_id
WHERE TIMESTAMP >= ('2024-03-07 00:01:15')
AND TIMESTAMP < ('2024-03-07 00:01:21');
Diese INSERT-Anweisung wählt fünf Zeilen aus der ASOF JOIN-Operation aus und fügt sie in die Tabelle sensor_data_ts ein.
+-------------------------+
| number of rows inserted |
|-------------------------|
| 5 |
+-------------------------+
Um die Ergebnisse der Interpolation zu überprüfen, markieren Sie in der Tabelle sensor_data_ts diese fünf Zeilen sowie die beiden Zeilen, die ihnen direkt vorausgehen bzw. nachfolgen. Beachten Sie, dass die fünf interpolierten Zeilen die Werte der Spalten temperature, vibration und motor_rpm unverändert übernommen haben, die in der Zeile 2024-03-07 00:01:15.000 erfasst wurden. Die Interpolation war erfolgreich.
SELECT * FROM sensor_data_ts
WHERE device_id='DEVICE2'
AND TIMESTAMP >= ('2024-03-07 00:01:15')
AND TIMESTAMP <= ('2024-03-07 00:01:21')
ORDER BY TIMESTAMP;
+-----------+-------------------------+-------------+-----------+-----------+
| DEVICE_ID | TIMESTAMP | TEMPERATURE | VIBRATION | MOTOR_RPM |
|-----------+-------------------------+-------------+-----------+-----------|
| DEVICE2 | 2024-03-07 00:01:15.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:16.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:17.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:18.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:19.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:20.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:21.000 | 28.0426 | 0.2944 | 1448 |
+-----------+-------------------------+-------------+-----------+-----------+
Anwenden von ML-basierten Funktionen auf Zeitreihendaten¶
Sie können mit ML-Funktionen ein Modell trainieren, um eine prädiktive Analyse von Zeitreihendaten durchzuführen:
Bei der Prognoseerstellung werden historische Zeitreihendaten verwendet, um Vorhersagen über zukünftige Daten zu treffen. Ausgehend von einer erfassten Zeitreihe mit tatsächlich beobachteten Werten mit Zeitpunkten in der Vergangenheit prognostiziert das ML-Modell, wie die beobachteten Werte für Zeitpunkte in der Zukunft aussehen könnten.
Bei der Anomalieerkennung werden Ausreißer identifiziert, d. h. Datenpunkte, die von einem erwarteten Bereich abweichen. Im Zusammenhang mit einer Zeitreihe ist ein Ausreißer eine Messung, die viel größer oder kleiner ist als andere Messungen in einem ähnlichen Zeitintervall. Um Ausreißer zu finden, erstellt die ML-Funktion eine Prognose für den Zeitraum, der auf Anomalien geprüft wird, und vergleicht dann die Prognoseergebnisse mit den tatsächlichen Daten.
Top Insights findet die wichtigsten Dimensionen in einem Datenset, erstellt Segmente aus diesen Dimensionen und erkennt, welche dieser Segmente eine Kennzahl beeinflusst haben.
Bemerkung
Für die Zwecke des maschinellen Lernens müssen die Zeitstempel in Ihren Zeitreihen feste Zeitintervalle darstellen. Falls erforderlich, können Sie die Funktion DATE_TRUNC oder TIME_SLICE auf TIMESTAMP-Spalten anwenden, um Unregelmäßigkeiten beim Training des Prognosemodells zu entfernen.
Beispiel für die Anomalieerkennung in einer Zeitreihe¶
Das folgende Beispiel verwendet eine Ansicht mit nur 30 Zeilen, um ein Anomalieerkennungsmodell zu trainieren. Beginnen Sie mit dem Generieren von Daten in einer Tabelle, und erstellen Sie dann eine Ansicht auf diese Tabelle. Die Ansicht ist nicht erforderlich (Sie können eine Tabelle verwenden, um ein Modell zu trainieren), aber die Ansichtsoption gibt Ihnen eine gewisse Flexibilität, um Modelle iterativ zu trainieren, mit unterschiedlichen Zeilenzahlen und ohne die Quelldaten aktualisieren zu müssen.
Bemerkung
Wenn Sie dieses Beispiel selbst ausführen, wird Ihre Ausgabe nicht genau übereinstimmen, da die Tabelle sensor_data_30_rows mit zufällig generierten Werten geladen ist.
CREATE OR REPLACE TABLE sensor_data_30_rows (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT);
INSERT INTO sensor_data_30_rows (device_id, timestamp, temperature, vibration, motor_rpm)
SELECT 'DEVICE3', timestamp,
UNIFORM(30.2345, 36.3456, RANDOM()), --
UNIFORM(0.4000, 0.4718, RANDOM()), --
UNIFORM(1510, 1625, RANDOM()) --
FROM (
SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
FROM TABLE(GENERATOR(ROWCOUNT => 30))
);
CREATE OR REPLACE VIEW sensor_data_view AS SELECT * FROM sensor_data_30_rows;
Erstellen Sie nun das Modell:
CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION sensor_model(
INPUT_DATA => SYSTEM$REFERENCE('VIEW', 'sensor_data_view'),
TIMESTAMP_COLNAME => 'timestamp',
TARGET_COLNAME => 'temperature',
LABEL_COLNAME => '');
+---------------------------------------------+
| status |
|---------------------------------------------|
| Instance SENSOR_MODEL successfully created. |
+---------------------------------------------+
Wenn das Modell erfolgreich erstellt wurde, rufen Sie die Methode <Name_des_Modells>!DETECT_ANOMALIES auf, um Ausreißer in dem angegebenen Test-Datenset zu erkennen. Die Zeitstempel in den Testdaten müssen chronologisch auf die Zeitstempel in den Trainingsdaten folgen, aber es darf kein zu großer zeitlicher Abstand zwischen den Trainingsdaten und den Testdaten bestehen. Wenn Sie zum Beispiel Zeitstempel für jede Sekunde haben, sollten Sie keine Testdaten verwenden, die den Trainingsdaten um Millionen von Sekunden voraus sind.
In diesem Beispiel wird eine andere Tabelle als Testdaten verwendet, die nur drei Zeilen enthält. Diese Zeilen haben Zeitstempel, die denen in den Trainingsdaten sehr nahe kommen.
CREATE OR REPLACE TABLE sensor_data_device3 (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT);
INSERT INTO sensor_data_device3 VALUES
('DEVICE3','2024-03-01 00:00:30.000',36.0422,0.4226,1560),
('DEVICE3','2024-03-01 00:00:31.000',36.1519,0.4341,1515),
('DEVICE3','2024-03-01 00:00:32.000',36.1524,0.4321,1591);
CALL sensor_model!DETECT_ANOMALIES(
INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'sensor_data_device3'),
TIMESTAMP_COLNAME => 'timestamp',
TARGET_COLNAME => 'temperature'
);
Wenn der Aufruf der Anomalieerkennung beendet ist, gibt er eine Ausgabe ähnlich der folgenden zurück:
+-------------------------+---------+--------------+--------------+--------------+------------+--------------+-------------+
| TS | Y | FORECAST | LOWER_BOUND | UPPER_BOUND | IS_ANOMALY | PERCENTILE | DISTANCE |
|-------------------------+---------+--------------+--------------+--------------+------------+--------------+-------------|
| 2024-03-01 00:00:30.000 | 36.0422 | 30.809998241 | 25.583156942 | 36.036839539 | True | 0.9950380683 | 2.578470982 |
| 2024-03-01 00:00:31.000 | 36.1519 | 32.559470456 | 27.332629158 | 37.786311755 | False | 0.961667911 | 1.770378085 |
| 2024-03-01 00:00:32.000 | 36.1524 | 32.205610776 | 26.978769478 | 37.432452075 | False | 0.9741130751 | 1.945009377 |
+-------------------------+---------+--------------+--------------+--------------+------------+--------------+-------------+
Die Spalten TS und Y geben die Zeitstempel bzw. Temperaturwerte aus den Testdaten zurück. In diesem sehr kleinen Testfall fand die Funktion eine Anomalie (IS_ANOMALY=True). Weitere Informationen zu den Ausgabespalten finden Sie im Abschnitt „Rückgabewerte“ der Funktionsbeschreibung.
Erstellen der Tabelle „sensor_data_ts“¶
Wenn Sie die Beispiele in diesem Abschnitt, bei denen die Tabelle sensor_data_ts abgefragt wird, testen möchten, können Sie eine Kopie dieser Tabelle erstellen und laden, indem Sie das folgende SQL-Skript ausführen. Das Skript generiert einen Monat lang synthetische Daten für Sensormesswerte, indem es die Funktionen UNIFORM, RANDOM und GENERATOR aufruft. Daher wird Ihre Kopie der Tabelle keine identischen Ergebnisse liefern. Die Messwerte liegen im gleichen Bereich, aber sie sind nicht gleich.
CREATE OR REPLACE TABLE sensor_data_device1 (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT
);
INSERT INTO sensor_data_device1 (device_id, timestamp, temperature, vibration, motor_rpm)
SELECT 'DEVICE1', timestamp,
UNIFORM(25.1111, 40.2222, RANDOM()), -- Temperature range in °C
UNIFORM(0.2985, 0.3412, RANDOM()), -- Vibration range in mm/s
UNIFORM(1400, 1495, RANDOM()) -- Motor RPM range
FROM (
SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
FROM TABLE(GENERATOR(ROWCOUNT => 2678400)) -- seconds in 31 days
);
CREATE OR REPLACE TABLE sensor_data_device2 (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT
);
INSERT INTO sensor_data_device2 (device_id, timestamp, temperature, vibration, motor_rpm)
SELECT 'DEVICE2', timestamp,
UNIFORM(24.6642, 36.3107, RANDOM()), -- Temperature range in °C
UNIFORM(0.2876, 0.3333, RANDOM()), -- Vibration range in mm/s
UNIFORM(1425, 1505, RANDOM()) -- Motor RPM range
FROM (
SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
FROM TABLE(GENERATOR(ROWCOUNT => 2678400)) -- seconds in 31 days
);
INSERT INTO sensor_data_device1 SELECT * FROM sensor_data_device2;
DROP TABLE IF EXISTS sensor_data_ts;
ALTER TABLE sensor_data_device1 rename to sensor_data_ts;
DROP TABLE sensor_data_device2;
SELECT COUNT(*) FROM sensor_data_ts; -- verify row count = 5356800
Erstellen der Tabelle „heavy_weather“¶
Das folgende Skript erstellt und lädt die Tabelle heavy_weather, die in den Beispielen für die MAX_BY-Funktionen verwendet wird. Die Tabelle enthält 55 Zeilen mit Niederschlagsdaten für kalifornische Orte in der letzten Woche des Jahres 2021.
CREATE OR REPLACE TABLE heavy_weather
(start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));
INSERT INTO heavy_weather VALUES
('2021-12-23 06:56:00.000',0.08,'Mount Shasta','Siskiyou'),
('2021-12-23 07:51:00.000',0.09,'Mount Shasta','Siskiyou'),
('2021-12-23 16:23:00.000',0.56,'South Lake Tahoe','El Dorado'),
('2021-12-23 17:24:00.000',0.38,'South Lake Tahoe','El Dorado'),
('2021-12-23 18:30:00.000',0.28,'South Lake Tahoe','El Dorado'),
('2021-12-23 19:35:00.000',0.37,'Mammoth Lakes','Mono'),
('2021-12-23 19:36:00.000',0.80,'South Lake Tahoe','El Dorado'),
('2021-12-24 04:43:00.000',0.25,'Alta','Placer'),
('2021-12-24 05:26:00.000',0.34,'Alta','Placer'),
('2021-12-24 05:35:00.000',0.42,'Big Bear City','San Bernardino'),
('2021-12-24 06:49:00.000',0.17,'South Lake Tahoe','El Dorado'),
('2021-12-24 07:40:00.000',0.07,'Alta','Placer'),
('2021-12-24 08:36:00.000',0.07,'Alta','Placer'),
('2021-12-24 11:52:00.000',0.08,'Alta','Placer'),
('2021-12-24 12:52:00.000',0.38,'Alta','Placer'),
('2021-12-24 15:44:00.000',0.13,'Alta','Placer'),
('2021-12-24 15:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-24 16:55:00.000',0.09,'Big Bear City','San Bernardino'),
('2021-12-24 21:53:00.000',0.07,'Montague','Siskiyou'),
('2021-12-25 02:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 07:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 08:52:00.000',0.08,'Alta','Placer'),
('2021-12-25 09:48:00.000',0.18,'Alta','Placer'),
('2021-12-25 12:52:00.000',0.10,'Alta','Placer'),
('2021-12-25 17:21:00.000',0.23,'Alturas','Modoc'),
('2021-12-25 17:52:00.000',1.54,'Alta','Placer'),
('2021-12-26 01:52:00.000',0.61,'Alta','Placer'),
('2021-12-26 05:43:00.000',0.16,'South Lake Tahoe','El Dorado'),
('2021-12-26 05:56:00.000',0.08,'Bishop','Inyo'),
('2021-12-26 06:52:00.000',0.75,'Bishop','Inyo'),
('2021-12-26 06:53:00.000',0.08,'Lebec','Los Angeles'),
('2021-12-26 07:52:00.000',0.65,'Alta','Placer'),
('2021-12-26 09:52:00.000',2.78,'Alta','Placer'),
('2021-12-26 09:55:00.000',0.07,'Big Bear City','San Bernardino'),
('2021-12-26 14:22:00.000',0.32,'Alta','Placer'),
('2021-12-26 14:52:00.000',0.34,'Alta','Placer'),
('2021-12-26 15:43:00.000',0.35,'Alta','Placer'),
('2021-12-26 17:31:00.000',5.24,'Alta','Placer'),
('2021-12-26 22:52:00.000',0.07,'Alta','Placer'),
('2021-12-26 23:15:00.000',0.52,'Alta','Placer'),
('2021-12-27 02:52:00.000',0.08,'Alta','Placer'),
('2021-12-27 03:52:00.000',0.14,'Alta','Placer'),
('2021-12-27 04:52:00.000',1.52,'Alta','Placer'),
('2021-12-27 14:37:00.000',0.89,'Alta','Placer'),
('2021-12-27 14:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-27 17:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-30 11:23:00.000',0.12,'Lebec','Los Angeles'),
('2021-12-30 11:43:00.000',0.98,'Lebec','Los Angeles'),
('2021-12-30 13:53:00.000',0.23,'Lebec','Los Angeles'),
('2021-12-30 14:53:00.000',0.13,'Lebec','Los Angeles'),
('2021-12-30 15:15:00.000',0.29,'Lebec','Los Angeles'),
('2021-12-30 17:53:00.000',0.10,'Lebec','Los Angeles'),
('2021-12-30 18:53:00.000',0.09,'Lebec','Los Angeles'),
('2021-12-30 19:53:00.000',0.07,'Lebec','Los Angeles'),
('2021-12-30 20:53:00.000',0.07,'Lebec','Los Angeles')
;