16 SQL-Techniken, die jeder Anfänger kennen muss

Auf einer Skala von 1 bis 10, wie gut sind Ihre Data Warehousing-Kenntnisse?

Möchten Sie über 7/10 hinausgehen? Dann ist dieser Artikel genau das Richtige für Sie.

Wie gut ist Ihr SQL? Möchten Sie sich so schnell wie möglich auf ein Vorstellungsgespräch vorbereiten?

In diesem Blogbeitrag werden die kompliziertesten Data-Warehouse-SQL-Techniken ausführlich erläutert. Ich werde den BigQuery-Standard-SQL-Dialekt verwenden, um ein paar Gedanken zu diesem Thema niederzuschreiben.

1. Inkrementelle Tabellen und MERGE

Die Aktualisierung der Tabelle ist wichtig. Es ist in der Tat wichtig. Im Idealfall handelt es sich um Transaktionen, bei denen es sich um einen PRIMÄR-Schlüssel, eindeutige Ganzzahlen und eine automatische Inkrementierung handelt. Die Tabellenaktualisierung ist in diesem Fall einfach:

Dies ist nicht immer der Fall, wenn in modernen Data Warehouses mit denormalisierten Sternschema-Datensätzen gearbeitet wird. Möglicherweise werden Sie damit beauftragt, Sitzungen mit SQL zu erstellen und/oder Datensätze mit nur einem Teil der Daten inkrementell zu aktualisieren. transaction_id ist möglicherweise nicht vorhanden. Stattdessen müssen Sie sich mit einem Datenmodell befassen, bei dem der eindeutige Schlüssel von der neuesten bekannten transaction_id (oder dem Zeitstempel) abhängt. Beispielsweise hängt user_id im Datensatz last_online vom Zeitstempel der letzten bekannten Verbindung ab. In diesem Fall möchten Sie vorhandene Benutzer update und neue insert .

MERGE und inkrementelle Updates

Sie können MERGE verwenden oder den Vorgang in zwei Aktionen aufteilen. Einer zum Aktualisieren bestehender Datensätze durch neue und einer zum Einfügen völlig neuer Datensätze, die nicht beendet werden (LEFT JOIN-Situation).

MERGE ist eine Anweisung, die im Allgemeinen in relationalen Datenbanken verwendet wird. Der MERGE-Befehl von Google BigQuery ist eine der DML-Anweisungen (Data Manipulation Language). Es wird häufig verwendet, um drei Hauptfunktionen atomar in einer einzigen Anweisung auszuführen. Diese Funktionen sind UPDATE, INSERT und DELETE.

• Die UPDATE- oder DELETE-Klausel kann verwendet werden, wenn zwei oder mehr Daten übereinstimmen.
• Die INSERT-Klausel kann verwendet werden, wenn zwei oder mehr Daten unterschiedlich sind und nicht übereinstimmen.
• Die UPDATE- oder DELETE-Klausel kann auch verwendet werden, wenn die angegebenen Daten nicht mit der Quelle übereinstimmen.

Das bedeutet, dass Sie mit dem Google BigQuery MERGE-Befehl Google BigQuery-Daten zusammenführen können, indem Sie Daten in Ihren Google BigQuery-Tabellen aktualisieren, einfügen und löschen.

Betrachten Sie diese SQL:

2. Wörter zählen

Führen Sie UNNEST() aus und prüfen Sie, ob das benötigte Wort in der Liste enthalten ist, die Sie benötigen. Dies kann in vielen Situationen nützlich sein, z. B. bei der Stimmungsanalyse im Data Warehouse:

3. Verwendung der IF()-Anweisung außerhalb der SELECT-Anweisung

Dies gibt uns die Möglichkeit, einige Codezeilen einzusparen und in Bezug auf den Code aussagekräftiger zu sein. Normalerweise möchten Sie dies in eine Unterabfrage einfügen und einen Filter in der where- Klausel hinzufügen, aber Sie können stattdessen auch Folgendes tun:

Ein weiteres Beispiel dafür, wie man es NICHT mit partitionierten Tabellen verwendet. Tu das nicht . Dies ist ein schlechtes Beispiel, denn da die passenden Tabellensuffixe wahrscheinlich dynamisch bestimmt werden (basierend auf etwas in Ihrer Tabelle), wird Ihnen ein vollständiger Tabellenscan in Rechnung gestellt.

Sie können es auch in HAVING Klausel und AGGREGATE Funktionen verwenden.

4. Verwenden von GROUP BY ROLLUP

Die ROLLUP-Funktion wird verwendet, um eine Aggregation auf mehreren Ebenen durchzuführen. Dies ist nützlich, wenn Sie mit Dimensionsdiagrammen arbeiten müssen.

Die folgende Abfrage gibt die gesamten Guthabenausgaben pro Tag nach dem in der where- Klausel angegebenen Transaktionstyp (is_gift) zurück und zeigt außerdem die Gesamtausgaben für jeden Tag und die Gesamtausgaben in allen verfügbaren Daten an.

5. Konvertieren Sie die Tabelle in JSON

Stellen Sie sich vor, Sie müssten Ihre Tabelle in ein JSON-Objekt konvertieren, bei dem jeder Datensatz ein Element eines verschachtelten Arrays ist. Hier wird die Funktion to_json_string() nützlich:

Dann können Sie es überall verwenden: Datumsangaben, Marketing-Trichter, Indizes, Histogrammdiagramme usw.

6. Verwendung von PARTITION BY

Gegeben sind die Spalten user_id , date und total_cost . Wie zeigen Sie für JEDES Datum den Gesamtumsatzwert für JEDEN Kunden an und behalten dabei alle Zeilen bei? Sie können dies folgendermaßen erreichen:

7. Gleitender Durchschnitt

Sehr oft werden BI-Entwickler damit beauftragt, ihren Berichten und fantastischen Dashboards einen gleitenden Durchschnitt hinzuzufügen. Dies kann ein MA-Liniendiagramm mit 7, 14, 30 Tagen/Monaten oder sogar einem Jahr sein. Wie machen wir das?

8. Datumsfelder

Dies ist sehr praktisch, wenn Sie mit der Benutzerbindung arbeiten oder einen Datensatz auf fehlende Werte, z. B. Daten, überprüfen möchten. BigQuery verfügt über eine Funktion namens GENERATE_DATE_ARRAY :

9. Zeilennummer()

Dies ist nützlich, um etwas Aktuelles aus Ihren Daten zu erhalten, z. B. den neuesten aktualisierten Datensatz usw., oder sogar um Duplikate zu entfernen:

10. NTILE()

Eine weitere Nummerierungsfunktion. Wirklich nützlich, um Dinge wie Login duration in seconds zu überwachen, wenn Sie eine mobile App haben. Ich habe zum Beispiel meine App mit Firebase verbunden und wenn sich Benutzer login kann ich sehen, wie lange es für sie gedauert hat.

Diese Funktion unterteilt die Zeilen basierend auf der Zeilenreihenfolge in Buckets vom Typ constant_integer_expression “ und gibt die 1-basierte Bucket-Nummer zurück, die jeder Zeile zugewiesen ist. Die Anzahl der Zeilen in den Buckets kann sich höchstens um 1 unterscheiden. Die Restwerte (der Rest der Anzahl der Zeilen dividiert durch Buckets) werden für jeden Bucket einzeln verteilt, beginnend mit Bucket 1. Wenn der Wert von constant_integer_expression “ NULL, 0 oder negativ ist, Es liegt ein Fehler vor.

11. Rang / dichter_Rang

Sie werden auch Nummerierungsfunktionen genannt. Ich neige dazu, DENSE_RANK als Standard-Ranking-Funktion zu verwenden, da es im Gegensatz zu RANK nicht das nächste verfügbare Ranking überspringt. Es gibt aufeinanderfolgende Rangwerte zurück. Sie können es mit einer Partition verwenden, die die Ergebnisse in verschiedene Buckets unterteilt. Zeilen in jeder Partition erhalten die gleichen Ränge, wenn sie die gleichen Werte haben. Beispiel:

Ein weiteres Beispiel mit Produktpreisen:

12. Pivot/Unpivot

Pivot ändert Zeilen in Spalten. Es ist alles, was es tut. Unpivot bewirkt das Gegenteil .

13. Erster_Wert / letzter_Wert

Dies ist eine weitere nützliche Funktion, die dabei hilft, für jede Zeile ein Delta gegenüber dem ersten/letzten Wert in dieser bestimmten Partition zu ermitteln.

14. Konvertieren Sie eine Tabelle in ein Array von Strukturen und übergeben Sie sie an UDF

Dies ist nützlich, wenn Sie eine benutzerdefinierte Funktion (UDF) mit komplexer Logik auf jede Zeile oder Tabelle anwenden müssen. Sie können Ihre Tabelle jederzeit als Array von TYPE STRUCT-Objekten betrachten und dann jedes einzelne davon an UDF übergeben. Es hängt von Ihrer Logik ab. Ich verwende es zum Beispiel, um die Ablaufzeiten von Käufen zu berechnen:

Auf ähnliche Weise können Sie Tabellen erstellen, ohne UNION ALL verwenden zu müssen. Ich verwende es zum Beispiel, um einige Testdaten für Unit-Tests zu simulieren. Auf diese Weise können Sie dies sehr schnell tun, indem Sie einfach Alt + Shift + Down in Ihrem Editor verwenden.

15. Erstellen von Event-Trichtern mit FOLLOWING UND UNBOUNDED FOLLOWING

Ein gutes Beispiel könnten Marketing-Trichter sein. Ihr Datensatz enthält möglicherweise sich ständig wiederholende Ereignisse desselben Typs. Idealerweise möchten Sie jedoch jedes Ereignis mit dem nächsten Ereignis eines anderen Typs verketten. Dies kann nützlich sein, wenn Sie eine Liste von Ereignissen, Einkäufen usw. benötigen, um einen Trichterdatensatz zu erstellen. Wenn Sie mit PARTITION BY arbeiten, haben Sie die Möglichkeit, alle folgenden Ereignisse zu gruppieren, unabhängig davon, wie viele davon in jeder Partition vorhanden sind.

16. Regexp

Sie würden es verwenden, wenn Sie etwas aus unstrukturierten Daten extrahieren müssen, z. B. Wechselkurse, benutzerdefinierte Gruppierungen usw.

Arbeiten mit Wechselkursen mithilfe von Regexp

Betrachten Sie dieses Beispiel mit Wechselkursdaten:

Arbeiten mit App-Versionen mithilfe von Regexp

Manchmal möchten Sie möglicherweise regexp verwenden, um Haupt- , Release- oder Mod- Versionen für Ihre App abzurufen und einen benutzerdefinierten Bericht zu erstellen:

Abschluss

SQL ist ein leistungsstarkes Tool, das bei der Manipulation von Daten hilft. Hoffentlich sind diese SQL-Anwendungsfälle aus dem digitalen Marketing für Sie nützlich. Es ist in der Tat eine praktische Fähigkeit und kann Ihnen bei vielen Projekten helfen. Diese SQL-Schnipsel haben mir das Leben erheblich erleichtert und ich verwende sie fast täglich bei der Arbeit. Darüber hinaus sind SQL und moderne Data Warehouses wesentliche Werkzeuge für die Datenwissenschaft. Seine robusten Dialektfunktionen ermöglichen eine einfache Modellierung und Visualisierung von Daten. Da SQL die Sprache ist, die von Data Warehouses und Business-Intelligence-Experten verwendet wird, ist es eine ausgezeichnete Wahl, wenn Sie Daten mit ihnen teilen möchten. Dies ist die gebräuchlichste Art der Kommunikation mit fast allen Data Warehouse-/Lake-Lösungen auf dem Markt.

Ursprünglich veröffentlicht auf mydataschool.com von datamike

Mike ist ein leidenschaftlicher und digital fokussierter Mensch mit viel Tatendrang und Enthusiasmus, der die Herausforderungen liebt, die der gesamte Mix des digitalen Marketings mit sich bringt. Lebt in Großbritannien und schloss 2015 den MBA an der Newcastle University ab.