Auf einen Blick: Snowflake bietet grundlegende Analysen durch statistische Funktionen und konzentriert sich auf aggregierte Kennzahlen wie Kovarianz und Durchschnitte. Dieses Tutorial veranschaulicht die Verwendung dieser Funktionen mit Wetterdaten und nutzt SQL-Abfragen und Fensterfunktionen für effektive Analysen.
- Snowflake bietet wesentliche statistische Funktionen, bietet jedoch keine fortschrittlichen maschinellen Lernfähigkeiten.
- Wichtige Beispiele sind Kovarianzberechnungen und grundlegende Statistiken wie Durchschnitte und Ränge.
Snowflake betreibt kein maschinelles Lernen. Es hat nur einfache lineare Regression und grundlegende statistische Funktionen. (Wenn du maschinelles Lernen mit Snowflake machen willst, musst du die Daten in Spark oder ein anderes Drittanbieterprodukt eingeben.)
Man kann jedoch Analytik in Snowflake machen, ausgestattet mit etwas mathematischem Wissen sowie Aggregatfunktionen und Fensterfunktionen. Grundlegende Analytik reicht in den meisten Situationen und ist der erste Schritt zu einer ausgefeilteren Analyse.
In diesem Tutorial zeigen wir Ihnen, wie man Snowflake-statistische Funktionen mit einigen Beispielen verwendet. Wir werden diese Funktionen demonstrieren:
Zuerst lasst uns unsere Arbeit aufbauen.
Beispieldaten
Du brauchst einige Daten, um dieses Beispiel durchzugehen. Lade hier 1.000 stündliche Wetteraufzeichnungen herunter.
(Wir haben 20 Jahre Wetterdaten für Paphos, Zypern, von OpenWeather gekauft. Dies ist nur eine kleine Teilmenge von 1.000 Datensätzen, die in das CSV-Format konvertiert wurden. Wir haben mit diesen Daten in anderen Tutorials gearbeitet, darunter Loading CSV Files from S3 to Snowflake.)
Lade diese Daten so auf Amazon S3 hoch:
Kovarianz
Durchschnitt, Maximum, Minimum, Standardabweichung
Korrelation
Rang
aws s3 cp paphosWeather.csv s3://gluebmcwalkerrowe/paphosWeather.csv
Tabelle in Snowflake erstellen
Leider liest Snowflake den Header-Datensatz nicht und erstellt die Tabelle nicht für dich. (Vermutlich liegt das daran, dass sie es bevorzugen, wenn du die Spaltendatentypen sowie die Zahlengenauigkeit und -größe selbst definierst.)
create table weather(
dt integer,
temp decimal(6,2),
temp_min decimal(6,2),
temp_max decimal(6,2),
pressure int,
humidity int,
speed decimal(6,2),
deg int,
main varchar(50),
description varchar(50))
Kopiere die Datendatei in den Snowflake-Bühnenbereich
Dann erstelle einen Snowflake-Bühnenbereich wie diesen.
(Es spielt keine Rolle, welche Snowflake-Zugangsdaten Sie haben: Geben Sie Ihre Amazon AWS-Zugangsdaten ein. Tatsächlich gibt es keinen Ort, Ihre Snowflake-Zugangsdaten nachzuschlagen, da sie mit Ihren Amazon IAM-Zugangsdaten identisch sind, selbst wenn Sie sie auf einer Snowflake-Instanz ausführen, also Snowflake nicht lokal auf Ihren EC2-Servern installiert haben.)
create or replace stage paphosweather
url='s3://gluebmcwalkerrowe/paphosWeather.csv'
credentials=(aws_key_id='xxxxxxx' aws_secret_key='xxxxxxx')
Kopiere jetzt die Daten in die oben erstellte Tabelle.
copy into weather
from 's3://gluebmcwalkerrowe/paphosWeather.csv'
credentials=(aws_key_id='xxxxxxx' aws_secret_key='xxxxxxxx')
file_format = (type = csv field_delimiter = ',' skip_header = 1);
Die Kovarianz c der Variablen x und y beträgt > 0, wenn eine Erhöhung von x zu einer Erhöhung von y führt. Es ist ein Maß dafür, wie zwei Variablen zueinander zusammenhängen. Zeigen wir mal, wie das in Snowflake geht.
Jede der untenstehenden Abfragen umschließt eine innere Abfrage in eine äußere Abfrage. Dies ist notwendig, weil:
- Die innere Abfrage führt einige Berechnungen aus und bildet eine kleinere Menge, die das Ergebnis dieser Berechnungen enthält.
- Die äußere Abfrage läuft als Nächstes. Sie verwendet die von der inneren Abfrage übermittelten Spalten, um den Abfrageprozess zu einem zweistufigen Prozess zu machen. Mit anderen Worten: In Schritt 1 erstellen wir eine Menge A. In Schritt 2 erzeugen wir entweder eine weitere Menge, nennen wir sie B, oder einen Skalar, was eine einzelne Zahl bedeutet.
Die Daten, die wir haben, sind stündliche Wetterdaten von einem Ort: Paphos, Zypern. Meteorologen sagen, dass sinkender barometrischer Druck zu einer Erhöhung der Luftgeschwindigkeit führt. Lassen Sie uns das messen, indem wir die Kovarianz zwischen dem Rückgang des Luftdrucks und der Erhöhung der Windgeschwindigkeit berechnen.
(Denken Sie daran, dass wir das untenstehende Tutorial mit 117.000 stündlichen Wetterdatendaten von OpenWeather für eine einzelne Stadt, Paphos, Zypern, über 20 Jahre hinweg durchgeführt haben. Sie können 1.000 Datensätze, 41 Tage, von hier herunterladen. Oder Sie kaufen Daten zu Ihrem Standort, um historische Verhältnisse dort zu untersuchen.)
Wir berechnen:
- Die Änderung des Luftdrucks verwendet die Fensterfunktion lag (), um den Druck zu zwei Zeitpunkten zu betrachten. Eine Fensterfunktion führt eine Berechnung über benachbarte Zeilen in den Abfrageergebnissen durch. Wir verwenden also lag(pressure,8), um die Änderung des Luftdrucks über die vorherigen 8 Stunden zu berechnen, also 8 Zeilen hinter der aktuellen Zeile. Jeder Datensatz in der Datenbank ist 1 Stunde lang.
- Dann berechnen wir auch die Änderung der Windgeschwindigkeit über dieselben 8 Stunden mit Lag().
Verwenden Sie eine einfache Subtraktion, um die Änderung zu berechnen. Wir ordnen die Daten nach der Datumsspalte dt, da wir mit der Zeit arbeiten, also müssen die Daten in der richtigen Reihenfolge sein.
select to_timestamp(dt)
pressure, main,
lag(pressure, 1)over (order by dt) as pressure1,
lag(pressure, 8) over (order by dt) as pressure8,
pressure - pressure8 as pressurechange,
speed ,
lag(speed, 1) over (order by dt) as speed1,
lag(speed, 8) over (order by dt) as speed8,
speed8 - speed1 as windchange
from weather
order by dt desc
Nun hüllen wir die innere Funktion innerhalb der äußeren Funktion. Beachte, dass:
- Da die innere Funktion den Namen für die Berechnungen Druckänderung und Windänderung gab, schreiben wir einfach covar_pop(Druckänderung, Windänderung).
- Wir fügen die Hauptsäule hinzu, die bewölkt, regnerisch, Gewitter ist. usw., um die Kovarianz für jede Wetterlage zu zeigen.
select main, covar_pop(pressurechange, windchange) as covariance from
(select to_timestamp(dt)
pressure, main,
lag(pressure, 1)over (order by dt) as pressure1,
lag(pressure, 8) over (order by dt)as pressure8,
pressure - pressure8 as pressurechange,
speed ,
lag(speed, 1) over (order by dt) as speed1,
lag(speed, 8) over (order by dt) as speed8,
speed8 - speed1 as windchange
from weather
order by dt desc)
group by main
Hier sehen wir, dass ein Rückgang des Luftdrucks zu einer Zunahme der Windgeschwindigkeit führt, wie wir es erwarten würden. Dieser Effekt ist bei Gewittern am stärksten.
| HAUPTPERSONEN | KOVARIANZ |
|---|---|
| Clouds' | 2.682560523 |
| Klar' | 2.578284619 |
| Gewitter' | 3.578449916 |
| Rain' | 3.328280913 |
| Staub' | 1.697657949 |
| Squall' | 2.921487603 |
| Mist' | 2.513627372 |
| Nebel' | 2.9256 |
| Haze' | 3.565528153 |
| Tornado' | -1.425 |
Mittelwert, Maximum, Minimum und Standardabweichung sind aggregierte Funktionen. Das bedeutet, sie erwarten eine Gruppe nach Klausel. Dies sind die grundlegendsten Statistiken. Sie geben Ihnen eine Vorstellung davon, wie sich die durchschnittliche und durchschnittliche Variation einiger Metriken im Laufe der Zeit zeigen. Beachten Sie, dass:
- to_timestamp() rechnet die Epochenzeit (die seit dem 01.01.1970 Sekunden ist) in jjjj-mm-dd hh:mm:ss.
- Wir verwenden date_part(), um den Monat und das Jahr herauszuziehen.
select round(avg(temp),2) as average, round(stddev(temp),2) as std, max(temp) as maxtemp,
min(temp) as mintemp, date_part(year, to_timestamp(dt)) as year, date_part(month,to_timestamp(dt)) as month
from weather
where to_timestamp(dt) > '2017-01-01'
group by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt))
order by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt))
Hier sind die Ergebnisse:
| DURCHSCHNITT | STD | MAXTEMP | MINTEMP | JAHR | MONAT |
|---|---|---|---|---|---|
| 53.4 | 5.57 | 64.08 | 37.42 | 2017 | 1 |
| 54.4 | 6.37 | 72.84 | 40.44 | 2017 | 2 |
| 59.17 | 5.1 | 70.84 | 45.91 | 2017 | 3 |
| 63.79 | 5.36 | 76.78 | 51.67 | 2017 | 4 |
| 79.28 | 0.1 | 79.39 | 79.21 | 2017 | 8 |
| 78.35 | 4.9 | 88.5 | 67.21 | 2017 | 9 |
| 71.86 | 4.86 | 82.2 | 62.13 | 2017 | 10 |
| 64.08 | 5.29 | 78.31 | 50.61 | 2017 | 11 |
| 59.55 | 5.69 | 69.87 | 45.84 | 2017 | 12 |
| 56.5 | 4.99 | 66.42 | 44.31 | 2018 | 1 |
| 58.81 | 4.47 | 68.04 | 47.88 | 2018 | 2 |
| 61.63 | 5.27 | 77.61 | 50.56 | 2018 | 3 |
| 65.91 | 6.07 | 78.75 | 51.94 | 2018 | 4 |
| 75.88 | 1.43 | 77.54 | 75.04 | 2018 | 8 |
| 79.21 | 5.01 | 88.81 | 66.67 | 2018 | 9 |
| 73.28 | 5.97 | 85.15 | 58.28 | 2018 | 10 |
Wir können einen Jahresvergleich erhalten, indem wir die Ergebnisse zuerst nach Monat und dann nach Jahr sortieren:
select round(avg(temp),2) as average, round(stddev(temp),2) as std, max(temp) as maxtemp,
min(temp) as mintemp, date_part(year, to_timestamp(dt)) as year, date_part(month,to_timestamp(dt)) as month
from weather
group by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt))
order by date_part(month,to_timestamp(dt)) ,date_part(year, to_timestamp(dt))
Offensichtlich korrelierten die Monate mit der Temperatur, da es im Sommer heißer und im Winter kälter wird. Wie stark ist diese Korrelation? Man würde denken, sie wäre fast 100%. Hier ist sie im milden Klima Zyperns auf 70% geschlossen.
Dies führt zu einer skalaren Statistik, da sie einen Wert zurückgibt, corr (Monat, Durchschnitt), statt Zeilen.
select corr(month, average) from
(select round(avg(temp),2) as average, round(stddev(temp),2) as std, max(temp) as maxtemp,
min(temp) as mintemp, date_part(year, to_timestamp(dt)) as year, date_part(month,to_timestamp(dt)) as month
from weather
where to_timestamp(dt) > '2017-01-01'
group by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt))
order by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt)))
Führt zu diesem skalaren Wert. Sie sind also positiv korreliert, aber weniger, als wir erwarten würden.
CORR(MONTH, AVERAGE)
0.6127760999
Jetzt berechnen wir die heißesten durchschnittlichen Monate seit 2017. Das könnten wir mit einer regulären SQL-Funktion machen, aber wenn wir die Rank()- Windows-Funktion verwenden, können wir jede Zeile in der resultierenden Menge nummerieren. Diese Zahl ist der Rang.
Auch das ist eine zweistufige Funktion, weil wir stündliche Wetteraufzeichnungen in monatliche umwandeln müssen, damit wir nicht zu viele Zeilen haben:
- Berechnen Sie die Durchschnittstemperatur für jeden Monat.
- Wählen Sie die Metriken aus, die wir gerankt haben möchten. Dies wird durch die Eingabe des Spaltenmittelwerts in die (Reihenfolge nach Durchschnittsbeschreibung [absteigend])- Anweisung gegeben.
select year, month, average, rank() over (order by average desc) as hottest from
(select round(avg(temp),2) as average, round(stddev(temp),2) as std, max(temp) as maxtemp,
min(temp) as mintemp, date_part(year, to_timestamp(dt)) as year, date_part(month,to_timestamp(dt)) as month
from weather
where to_timestamp(dt) > '2017-01-01'
group by date_part(year, to_timestamp(dt)), date_part(month,to_timestamp(dt)))
Hier sind die Ergebnisse (in Fahrenheit).
| JAHR | MONAT | DURCHSCHNITT | HEISSEST |
|---|---|---|---|
| 2020 | 9 | 81.73 | 1 |
| 2019 | 8 | 79.36 | 2 |
| 2017 | 8 | 79.28 | 3 |
| 2018 | 9 | 79.21 | 4 |
| 2017 | 9 | 78.35 | 5 |
| 2019 | 9 | 77.74 | 6 |
| 2020 | 8 | 76.48 | 7 |
| 2018 | 8 | 75.88 | 8 |
| 2019 | 10 | 73.79 | 9 |
| 2018 | 10 | 73.28 | 10 |
| 2017 | 10 | 71.86 | 11 |
| 2019 | 11 | 68.13 | 12 |
| 2018 | 11 | 66.25 | 13 |
| 2018 | 4 | 65.91 | 14 |
Zusätzliche Ressourcen
Für weitere Tutorials wie dieses entdecken Sie diese Ressourcen:
Schneeflocken-Leitfaden
- Snowflake 101: Einführung in die Snowflake Data Cloud
- Wie man Amazon S3-Daten in Snowflake importiert
- Wie man JSON-Daten in Snowflake abfragt
- CSV-Dateien von S3 in Snowflake laden
- Snowflake: Verwendung von Analytik und statistischen Funktionen
- Snowflake-Fensterfunktionen: Partitionieren nach und ordnen nach
- Schneeflocken-Verzögerungsfunktion und gleitende Durchschnitte
- Snowflake SQL Aggregate Functions & Table Joins
- Benutzerdefinierte Funktionen (UDFs) in Snowflake
- Schneeflockenströme erstellen und nutzen
- Verwendung von gespeicherten Verfahren in Snowflake
- Schneeflocke vs. Redshift: Was ist der Unterschied und wie wählt man?
Diese Beiträge sind meine eigenen und spiegeln nicht unbedingt die Position, Strategien oder Meinung von BMC wider.