Auf einen Blick: Das Tutorial zur Snowflake Lag Function zeigt, wie man gleitende Durchschnitte mit der Lag-Funktion von SQL berechnet, um Verkaufstrends zu analysieren. Es bietet SQL-Codeschnipsel zur Erstellung von Beispieltabellen und zur Überprüfung von Berechnungen.
- Verwendet die Verzögerungsfunktion für die Berechnung des gleitenden Durchschnitts.
- Bietet SQL-Code-Schnipsel und Beispieldaten für die praktische Anwendung an.
Dieses Tutorial zeigt dir, wie du die Lag-Funktion benutzt, um gleitende Durchschnitte in Snowflake zu berechnen.
Es baut auf der Arbeit auf, die wir in Snowflake SQL Aggregate Functions & Table Joins und Snowflake Window Functions: Partition By and Order By geteilt haben.
Verwendung von Lag zur Berechnung eines gleitenden Durchschnitts
Wir können die Lag()- Funktion verwenden, um einen gleitenden Durchschnitt zu berechnen. Wir verwenden den gleitenden Durchschnitt, wenn wir Trends erkennen oder die Volatilität von der Tageszahl reduzieren wollen, da sie stark variieren kann.
Mit anderen Worten: Es ist besser, eine Woche Umsatz als einen Tag zu betrachten, um zu sehen, wie sich ein Produkt entwickelt.
Beispieldaten erstellen
Um das zu untersuchen, erstellen Sie zuerst diese beiden Tabellen.
CREATE TABLE customers
(
customernumber varchar(100) PRIMARY KEY,
customername varchar(50),
phonenumber varchar(50),
postalcode varchar(50),
locale varchar(10),
datecreated date,
email varchar(50)
);
CREATE TABLE orders
(
customernumber varchar(100) PRIMARY KEY,
ordernumber varchar(100),
comments varchar(200),
orderdate date,
ordertype varchar(10),
shipdate date,
discount number,
quantity int,
productnumber varchar(50)
)
Dann fügen Sie diese SQL-Daten ein. Der obere Teil der Daten sieht so aus:
insert into customers (customernumber,customername,phonenumber,postalcode,locale,datecreated,email) values ('440','tiqthogsjwsedifisiir','3077854','vdew','','2020-09-27','[email protected]');
insert into orders (customernumber,ordernumber,comments,orderdate,ordertype,shipdate,discount,quantity,productnumber) values ('440','402','swgstdhmju','2020-09-27','sale','2020-10-01','0.7005950240358919','61','BB111');
insert into customers (customernumber,customername,phonenumber,postalcode,locale,datecreated,email) values ('802','hrdngzutwelfhgwcyznt','1606845','rnmk','','2020-09-27','[email protected]');
insert into orders (customernumber,ordernumber,comments,orderdate,ordertype,shipdate,discount,quantity,productnumber) values ('802','829','jybwzvoyzb','2020-09-27','sale','2020-10-06','0.3702248922841853','75','FF4444');
insert into customers (customernumber,customername,phonenumber,postalcode,locale,datecreated,email) values ('199','ogvaevvhhqtjcqggafnv','8452159','hyxm','','2020-09-27','[email protected]');
SQL-Ansatz schreiben
Nun wollen wir den gleitenden Durchschnitt der Gesamtverkäufe der letzten vier Tage berechnen.
Hier haben wir eine Select-Anweisung innerhalb einer Select-Anweisung, weil wir pro Tag eine Gesamtbestellung wollen. Dann schaut die Lag-Anweisung diesen einen Datensatz an, um den Vortag zu betrachten.
select shipdate, (quantity + lag(quantity,1) over (order by shipdate) +
lag(quantity,2) over (order by shipdate) +
lag(quantity,3)over (order by shipdate) + lag(quantity,4)
over (order by shipdate)) / 5 as movingaverage from
(select shipdate, sum(quantity) as quantity from orders group by shipdate);
Hier ist der gleitende Durchschnitt. Die ersten Zeilen sind null, da die Verzögerungsfunktion weiter zurückblickt als das Fenster für Zeilen, die nicht existieren.
Wir können beweisen, dass diese Berechnung korrekt ist, indem wir sie auf eine andere Weise berechnen.
Fassen wir Bestellungen nach Versanddatum zusammen.
select shipdate, sum(quantity)
from orders group by shipdate
order by shipdate;
Dann kopieren wir die Ergebnisse in eine Tabelle:
- Links befindet sich die Windows-Funktion.
- Rechts befindet sich die obige Frage.
Ich habe eine Spalte mit der Tabellenkalkulationsfunktion average() hinzugefügt, um anzuzeigen, dass die Zahlen gleich sind. So kannst du leicht sehen, wie die Windows-Lag-Funktion funktioniert.
windows function sum and group by =AVERAGE(E3:E7)
SHIPDATE MOVINGAVERAGE SHIPDATE SUM(QUANTITY) moving average
2020-09-30 2020-09-30 427
2020-10-01 2020-10-01 230
2020-10-02 2020-10-02 657
2020-10-03 2020-10-03 604
2020-10-04 488.6 2020-10-04 525 488.6
2020-10-05 462 2020-10-05 294 462
2020-10-06 547.2 2020-10-06 656 547.2
2020-10-07 485.2 2020-10-07 347 485.2
2020-10-08 470.8 2020-10-08 532 470.8
2020-10-09 486.2 2020-10-09 602 486.2
2020-10-10 495.2 2020-10-10 339 495.2
2020-10-11 465.8 2020-10-11 509 465.8
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.