     
 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:   https://gist.github.com/mshakhomirov/18775cbbe8288af864ad79247c0de63d?embedable=true#file-1-1-sql  Dies ist nicht immer der Fall, wenn in modernen Data Warehouses mit denormalisierten Sternschema-Datensätzen gearbeitet wird. Möglicherweise werden Sie damit beauftragt,   mit SQL zu erstellen und/oder Datensätze mit nur einem Teil der Daten inkrementell zu aktualisieren.   ist möglicherweise nicht vorhanden. Stattdessen müssen Sie sich mit einem Datenmodell befassen, bei dem der eindeutige Schlüssel von der neuesten bekannten   (oder dem Zeitstempel) abhängt. Beispielsweise hängt   im Datensatz   vom Zeitstempel der letzten bekannten Verbindung ab. In diesem Fall möchten Sie vorhandene Benutzer   und neue   . Sitzungen transaction_id transaction_id user_id last_online update insert  MERGE und inkrementelle Updates  Sie können   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. MERGE  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:   https://gist.github.com/mshakhomirov/5ad1a7518c54bc030d1c78b56fe3cf82?embedable=true#file-1-2-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:   https://gist.github.com/mshakhomirov/694e040539b0d1b556f8e053d315a3bf?embedable=true#file-2-sql  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   Klausel hinzufügen, aber Sie können stattdessen auch   tun: where- Folgendes   https://gist.github.com/mshakhomirov/933e6a358e49dcccd4e547a5509c8fda?embedable=true#file-3-sql  Ein weiteres Beispiel dafür, wie man es   mit   Tabellen verwendet.   . Dies ist ein schlechtes Beispiel, denn da die passenden Tabellensuffixe wahrscheinlich dynamisch bestimmt werden (basierend auf etwas in Ihrer Tabelle),  NICHT partitionierten Tu das nicht wird Ihnen ein vollständiger Tabellenscan in Rechnung gestellt.   https://gist.github.com/mshakhomirov/1c62d79cd9690140c569cd047b9d491f?embedable=true#file-3-2-sql  Sie können es auch in   Klausel und   Funktionen verwenden. HAVING AGGREGATE  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   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. where-   https://gist.github.com/mshakhomirov/4cf738aaad967fe92c4fb7192874fadf?embedable=true#file-4-sql  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   nützlich: to_json_string()   https://gist.github.com/mshakhomirov/aac1f93312ae305ba80c915fe4a2a386?embedable=true#file-5-sql  Dann können Sie es überall verwenden: Datumsangaben, Marketing-Trichter, Indizes, Histogrammdiagramme usw.  6. Verwendung von PARTITION BY  Gegeben sind die Spalten   ,   und   . 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: user_id date total_cost   https://gist.github.com/mshakhomirov/e4f11721eb5a3182150df08f25b70d64?embedable=true#file-6-sql  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?   https://gist.github.com/mshakhomirov/ebf5488d0036bc9b84ae05889346d986?embedable=true#file-7-sql  8. Datumsfelder  Dies ist sehr praktisch, wenn Sie mit   arbeiten oder einen Datensatz auf fehlende Werte, z. B. Daten, überprüfen möchten. BigQuery verfügt über eine Funktion namens   : der Benutzerbindung GENERATE_DATE_ARRAY   https://gist.github.com/mshakhomirov/2ba5a67053f85794462dab98e56ad74d?embedable=true#file-8-sql  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:   https://gist.github.com/mshakhomirov/05d0c04c5975207d98552ffd436add8b?embedable=true#file-9-sql  10. NTILE()  Eine weitere Nummerierungsfunktion. Wirklich nützlich, um Dinge wie   zu überwachen, wenn Sie eine mobile App haben. Ich habe zum Beispiel meine App mit Firebase verbunden und wenn sich Benutzer   kann ich sehen, wie lange es für sie gedauert hat.  Login duration in seconds login  Diese Funktion unterteilt die Zeilen basierend auf der Zeilenreihenfolge in Buckets vom Typ   “ 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   “ NULL, 0 oder negativ ist, Es liegt ein Fehler vor. constant_integer_expression constant_integer_expression   https://gist.github.com/mshakhomirov/16fe941aa8c4ed79e4aad8b7049b307a?embedable=true#file-10-sql  11. Rang / dichter_Rang  Sie werden auch   genannt. Ich neige dazu,     zu verwenden, da es im Gegensatz zu   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.  Nummerierungsfunktionen DENSE_RANK als Standard-Ranking-Funktion RANK Beispiel:   https://gist.github.com/mshakhomirov/459b68c5f3d1e8284c01e516db1d8dcb?embedable=true#file-11-1-sql   Ein weiteres Beispiel mit Produktpreisen:   https://gist.github.com/mshakhomirov/4c90a6fc8516d8264e172676a83a1048?embedable=true#file-11-2-sql  12. Pivot/Unpivot  Pivot ändert Zeilen in Spalten. Es ist alles, was es tut. Unpivot bewirkt das   . Gegenteil   https://gist.github.com/mshakhomirov/f90b035ba259e672d4d51a669e0cd1fc?embedable=true#file-12-sql  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.   https://gist.github.com/mshakhomirov/ea4de9144b97bf8c196cab07609c309e?embedable=true#file-13-sql  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:   https://gist.github.com/mshakhomirov/35d956fa9db86b12b44ab62c00f42a40?embedable=true#file-14-sql  Auf ähnliche Weise können Sie Tabellen erstellen, ohne   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   +   +   in Ihrem Editor verwenden. UNION ALL Alt Shift Down   https://gist.github.com/mshakhomirov/6ea226c1b5b789d4a31691ce065c20d7?embedable=true#file-14-2-sql  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.   https://gist.github.com/mshakhomirov/05fd7d79d8acf3b173181a5d950ab6e7?embedable=true#file-15-sql  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:   https://gist.github.com/mshakhomirov/9ca6e153da19c491034bd57995875308?embedable=true#file-16-1-sql  Arbeiten mit App-Versionen mithilfe von Regexp  Manchmal möchten Sie möglicherweise   verwenden, um   ,   oder   Versionen für Ihre App abzurufen und einen benutzerdefinierten Bericht zu erstellen: regexp Haupt- Release- Mod-   https://gist.github.com/mshakhomirov/b1f442a296ffef52c7baa1245e1dc316?embedable=true#file-16-2-sql  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   von  mydataschool.com 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.

Read me on Medium.com

Read My Stories

Dieses Audio ist in der Originalsprache der Geschichte produziert!

Zu lang; Lesen

Make resilience your competitive advantage

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

About Author

KOMMENTARE

Hängeetiketten

DIESER ARTIKEL WURDE VORGESTELLT IN

Related Stories

Starting 2025 with New Features: Settings Dashboard, HackerNoon Decoded, Updated Search UI, and More

HackerNoon Decoded 2024: Celebrating Our Programming Community!

HackerNoon Decoded 2024: Wir feiern unsere Produktmanagement-Community!

HackerNoon Decoded 2024: Wir feiern unsere Remote-Work-Community!

Starting 2025 with New Features: Settings Dashboard, HackerNoon Decoded, Updated Search UI, and More

HackerNoon Decoded 2024: Celebrating Our Programming Community!

HackerNoon Decoded 2024: Wir feiern unsere Produktmanagement-Community!

HackerNoon Decoded 2024: Wir feiern unsere Remote-Work-Community!

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps