Auf einen Blick: Snowflake-SQL-Aggregatfunktionen führen Berechnungen über Zeilen durch, einschließlich Zählen und statistischer Analyse, während Tabellen-Joins Daten aus mehreren Tabellen kombinieren. Schlüsselfunktionen wie stddev_pop() und listagg() verbessern die Datenzusammenfassung und -organisation.
- Aggregierte Funktionen berechnen Werte über bestimmte Zeilen.
- Das Zusammenfügen von Tabellen ermöglicht eine kombinierte Datenabruf.
- Statistische Funktionen berechnen Durchschnitte und Abweichungen.
In diesem Artikel erklären wir, wie man aggregierte Funktionen mit Snowflake verwendet.
Was sind aggregierte Funktionen?
Aggregierte Funktionen sind solche, die eine Berechnung über alle Zeilen oder Teilmengen von Zeilen in einer Tabelle durchführen.
Zum Beispiel ist die einfachste Aggregatfunktion count(). Man könnte alle Kunden in einer Tabelle mit count(*) ohne Gruppen- oder Where-Klausel zählen. Das * sagt Snowflake, dass er alle Spalten ansehen soll, aber man hätte auch nur eine Spalte einfügen können, da das dasselbe bedeutet.
select count(*) from orders
Wenn Sie jedoch Bestellungen über eine Teilmenge zählen möchten, könnten Sie zum Beispiel Kunden nach Bestelltyp zählen:
select ordertype, count(*) from orders
group by ordertype;
Erstellen Sie einige Beispieldaten
Lassen Sie uns einige Beispieldaten erstellen, um einige dieser Funktionen zu erforschen. Melden Sie sich bei Snowflake an und klicken Sie auf die Schaltfläche Datenbank erstellen , um eine Datenbank namens Inventory zu erstellen. Öffnen Sie als Nächstes den Arbeitsblatt-Editor und fügen Sie diese beiden SQL-Befehle ein:
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 Daten ein. Die Daten sehen so aus:
insert into customers (customernumber,customername,phonenumber,postalcode,locale,datecreated,email) values ('ee56d97a-fcaa-11ea-ab7a-0ec120e133fc','zopvxqhwocrtsonemrcf','3119110','vqlx','','2020-09-22','[email protected]');
insert into orders (customernumber,ordernumber,comments,orderdate,ordertype,shipdate,discount,quantity,productnumber) values ('ee56d97a-fcaa-11ea-ab7a-0ec120e133fc','ee56d97b-fcaa-11ea-ab7a-0ec120e133fc','shsyuaraxxftdzooafbg','2020-09-22','sale','2020-10-01','0.7751890540939359','40','ee56d97c-fcaa-11ea-ab7a-0ec120e133fc');
Verbindungstabellen
Die Kunden- und Bestelltabellen sind durch die Bestellnummer verknüpft. Offensichtlich müsste man sie in einem Satz zusammenbringen, wenn man sowohl Kunden- als auch Bestelldaten zusammen braucht. Das macht man mit einem Join, der diese Menge vorübergehend erstellt
Sie verbinden die beiden Tabellen in der Spalten-Element-Kundennummer. Beachten Sie:
- Wir verwenden as, um einen Alias zu erstellen, um die Tabellennamen abzukürzen und das Tippen zu erleichtern.
- Wir verwenden Join statt Inner Join. (Andere Tutorials fügen Inner Join oft hinzu, aber es verwirrt die Sache, wenn es sich um dasselbe handelt. Oft schreiben sie das auch, um das mit einem linken, rechten oder äußeren Join zu kontrastieren, die wie kartesische Produkte sind, also jede von n Aufträgen an jeden von m Kunden anhängen und so eine Menge von n*m Zeilen erzeugen.)
select c.customernumber, c.customername, o.ordernumber, c.datecreated, o.orderdate, o.shipdate from customers as c
join orders as o on c.customernumber = o.customernumber;
Standardabweichung
Lassen Sie uns die Standardabweichung in den Versandzeiten berechnen. Das machen wir in drei Schritten:
- Verbinden Sie Kunden- und Bestelltabellen
- Verwenden Sie die Datediff()- Funktion, um die Versandzeit zu berechnen, also wie lange der Kunde warten muss.
- Jede äußere Abfrage bezieht sich auf eine innere Abfrage, indem sie in Klammern [()] eingewickelt wird. Die Abfrage wird also in Stufen aufgebaut.
Hier ist die vollständige Anfrage. Siehe unten, um zu sehen, wie sie aufgeteilt ist.
select
avg(shiptime),
stddev_pop(shiptime)
from
(
select
customernumber,
customername,
orderdate,
shipdate,
datediff(days, orderdate, shipdate) as shiptime
from
(
select
c.customernumber,
c.customername,
o.ordernumber,
c.datecreated,
o.orderdate,
o.shipdate
from
customers as c
join orders as o on c.customernumber = o.customernumber
)
order by
shiptime desc
)
Wir bauen die Abfrage in Stufen auf. Beginne unten (also innerste) Abfrage und arbeite dich nach oben:
- Fügen Sie die Kunden- und Bestelltabelle ein, damit wir Kunden- und Bestelldetails in einem Satz haben und beide auflisten können. (Wir hätten diesen Schritt überspringen können, da wir nur die Bestelltabelle verwenden.)
(
select
c.customernumber,
c.customername,
o.ordernumber,
c.datecreated,
o.orderdate,
o.shipdate
from
customers as c
join orders as o on c.customernumber = o.customernumber
)
- Berechnen Sie die Versandzeit mit der datediff()-Funktion
select
count(*),
datediff(days, orderdate, shipdate) as shiptime
from
orders
group by
shiptime
order by
shiptime
- Berechnen Sie die Standardabweichung über die Versandzeiten:
select avg(shiptime), stddev_pop(shiptime) from (step b)
Hier sind die Ergebnisse:
Hier sind die Ergebnisse:
Hier sind die Ergebnisse:
AVG(SHIPTIME) STDDEV_POP(SHIPTIME)
8.539063 3.512038155
Diskretes Perzentil
Das 95. Perzentil bedeutet, dass 95 % der Bevölkerung angezeigt werden. Das ist eine gängige Statistik, da Daten außerhalb dieses Bereichs im Allgemeinen als Ausreißer gelten.
Hier zeigen wir, wie man das 25. Perzentil berechnet:
Ergebnisse in:
select customernumber , PERCENTILE_disc( 0.25 ) within group (order by quantity)
from orders
where customernumber = '5d2b742e-fcaa-11ea-ab7a-0ec120e133fc'
group by customernumber
order by customernumber
Ergebnisse in:
CUSTOMERNUMBER PERCENTILE_DISC( 0.25 ) WITHIN GROUP (ORDER BY QUANTITY)
5d2b742e-fcaa-11ea-ab7a-0ec120e133fc 9
Mach eine Überprüfung und siehst, dass Bestellmengen 92, 55 und 9 sind. Der einzige im unteren 25%-Perzentil ist also 9.
select quantity from orders
where customernumber = '5d2b742e-fcaa-11ea-ab7a-0ec120e133fc'
order by quantity desc;
Hier sind die Ergebnisse:
QUANTITY
92
55
9
listagg
Die Listagg-Funktion listet Bestellungen nach Kunden in einem Array auf und fügt sie in ein anderes Format ein, das du in eine where-Klausel verwenden könntest, die eine Liste von Elementen aufruft.
select listagg(ordernumber, '|')
from orders
where customernumber = '5d2b742e-fcaa-11ea-ab7a-0ec120e133fc'
Hier sind die Ergebnisse:
LISTAGG(ORDERNUMBER, '|')
5d2b742f-fcaa-11ea-ab7a-0ec120e133fc|5d2b7431-fcaa-11ea-ab7a-0ec120e133fc|5d2b7433-fcaa-11ea-ab7a-0ec120e133fc
Wenn Sie Abfragen ausführen, sollten Sie sie mit anderen Abfragen abgleichen, um Ihre Arbeit doppelt zu überprüfen. Hier listen wir Kundennummern direkt in Reihen auf.
select orderumber from orders
where where customernumber = '5d2b742e-fcaa-11ea-ab7a-0ec120e133fc'
Modus
Die Funktion modus() zeigt die häufigsten Werte an:
Ergebnisse in:
select mode(quantity)
from orders
Ergebnisse in:
MODE(QUANTITY)
13
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.