High-SQL-Abfragen für Datenwissenschaftler

Bild vom Autor

Ich weiß, dass das Wort „Python“ im Zusammenhang mit Information Science wahrscheinlich das am häufigsten verwendete Wort ist. Bis zu einem gewissen Grad gibt es dafür einen Grund. Aber in diesem Artikel möchte ich mich auf SQL konzentrieren, das oft übersehen wird, wenn reden über Information Science. Ich lege Wert auf Reden, weil SQL in der Praxis keineswegs übersehen wird. Im Gegenteil, es ist eine der heiligen Dreifaltigkeit der Programmiersprachen in der Information Science: SQL, Python und R.

SQL ist für die Datenabfrage und -bearbeitung gedacht, verfügt aber auch über respektable Datenanalyse- und Berichtsfunktionen. Ich zeige einige der wichtigsten SQL-Konzepte, die Sie als Datenwissenschaftler benötigen, und einige einfache Beispiele von StrataScratch und LeetCode.

Anschließend stelle ich zwei gängige Geschäftsszenarien vor, in denen alle oder die meisten dieser SQL-Konzepte angewendet werden müssen.

Wichtige SQL-Konzepte für Datenwissenschaftler

Hier ist eine Übersicht über die Konzepte, die ich besprechen werde.

1. Abfragen und Filtern von Daten

Hier beginnt normalerweise Ihre praktische Arbeit als Datenwissenschaftler: Sie fragen eine Datenbank ab und extrahieren nur die Daten, die Sie für Ihre Aufgabe benötigen.

Dabei handelt es sich typischerweise um relativ einfache WÄHLEN Aussagen mit dem AUS Und WO Klauseln. Um die eindeutigen Werte zu erhalten, verwenden Sie UNTERSCHEIDBARWenn Sie mehrere Tabellen verwenden müssen, fügen Sie außerdem VERBINDENS.

Sie müssen häufig SORTIEREN NACH um Ihren Datensatz besser zu organisieren.

Beispiel für die Kombination zweier Tabellen: Möglicherweise müssen Sie die Namen der Personen sowie die Stadt und den Staat auflisten, in dem sie leben, indem Sie zwei Tabellen verknüpfen und die Ausgabe nach Nachnamen sortieren.

SELECT FirstName,
       LastName, 
       Metropolis, 
       State
FROM Individual p LEFT JOIN Tackle a
ON p.PersonId = a.PersonId
ORDER BY LastName ASC;

2. Arbeiten mit NULLs

NULLsind Werte, die Datenwissenschaftlern oft nicht gleichgültig sind – sie wollen entweder nur NULLs, sie möchten sie entfernen oder durch etwas anderes ersetzen.

Sie können Daten mit oder ohne auswählen NULLs verwendet IST NULL oder IST NICHT NULL In WO.

Ersetzen NULLs mit einigen anderen Werten erfolgt typischerweise mit Bedingte Ausdrücke:

• NULLIF()
• VERSCHMELZEN()
• FALL Stellungnahme

Beispiel für IS NULL: Mit dieser Abfrage können Sie alle Kunden finden, die nicht vom Kunden mit der ID = 2 geworben wurden.

SELECT title 
FROM buyer 
WHERE referee_id IS NULL OR referee_id <> 2;

Beispiel für COALESCE(): Ich kann dieses Beispiel überarbeiten, indem ich sage, dass ich alle Daten abfragen möchte, aber auch eine Spalte hinzufüge, die 0% als Host-Antwortrate anzeigt, anstatt NULL.

SELECT *,
       COALESCE(host_response_rate, '0%') AS edited_host_response_rate
FROM airbnb_search_details;

3. Datentypkonvertierung

Als Datenwissenschaftler konvertieren Sie häufig Daten. Daten liegen oft nicht im gewünschten Format vor, sodass Sie sie an Ihre Bedürfnisse anpassen müssen. Dies geschieht normalerweise mit GIESSEN()aber es gibt auch einige Alternativen, abhängig von Ihrer SQL-Variante.

Beispiel für Casting-Daten: Diese Abfrage wirft die Sterndaten aus VARCHAR Zu GANZE ZAHL und entfernt die Werte, die keine ganzzahligen Werte sind.

SELECT business_name,
       review_id,
       user_id,
       CAST(stars AS INTEGER) AS cast_stars,
       review_date,
       review_text,
       humorous,
       helpful,
       cool
FROM yelp_reviews
WHERE stars  '?';

4. Datenaggregation

Um die Daten, mit denen sie arbeiten, besser zu verstehen (oder einfach weil sie Berichte erstellen müssen), müssen Datenwissenschaftler sehr häufig Daten aggregieren.

In den meisten Fällen müssen Sie Aggregatfunktionen Und GRUPPIERE NACHEinige der gängigen Aggregatfunktionen sind:

• ZÄHLEN()
• SUMME()
• AVG()
• MINDEST()
• MAX()

Wenn Sie aggregierte Daten filtern möchten, verwenden Sie HABEN anstatt WO.

Beispiel für Summe: Mit dieser Abfrage können Sie die Bankkonten aller Benutzer summieren und nur diejenigen anzeigen, deren Saldo über 1.000 liegt.

SELECT u.title, 
       SUM(t.quantity) AS stability
FROM Customers u
JOIN Transactions t
ON u.account = t.account
GROUP BY u.title
HAVING SUM(t.quantity) > 10000;

5. Umgang mit Terminen

Für Datenwissenschaftler ist die Arbeit mit Datumsangaben alltäglich. Auch hier werden die Datumsangaben nur manchmal nach Ihrem Geschmack oder Bedarf formatiert. Um die Flexibilität der Datumsangaben zu maximieren, müssen Sie manchmal Teile der Datumsangaben extrahieren oder neu formatieren. Um dies in PostgreSQL zu tun, verwenden Sie am häufigsten diese Datums-/Uhrzeitfunktionen:

• EXTRAKT()
• DATE_PART()
• DATE_TRUNC()
• TO_CHAR()

Eine der häufigsten Operationen mit Datumsangaben besteht darin, eine Differenz zwischen den Datumsangaben zu ermitteln oder Datumsangaben zu addieren. Dies erreichen Sie, indem Sie die beiden Werte einfach subtrahieren oder addieren oder indem Sie die dafür vorgesehenen Funktionen verwenden, je nachdem, welche Datenbank Sie verwenden.

Beispiel für das Extrahieren des Jahres: Die folgende Abfrage extrahiert das Jahr aus dem TERMINZEIT Geben Sie die Spalte ein, um die Anzahl der Verstöße professional Jahr für das Roxanne Cafe anzuzeigen.

SELECT EXTRACT(YEAR FROM inspection_date) AS year_of_violation,
       COUNT(*) AS n_violations
FROM sf_restaurant_health_violations
WHERE business_name="Roxanne Cafe" AND violation_id IS NOT NULL
GROUP BY year_of_violation
ORDER BY year_of_violation ASC;

Beispiel für Datumsformatierung: Mit der folgenden Abfrage formatieren Sie das Startdatum als ‚JJJJ-MM‘ mit TO_CHAR().

SELECT TO_CHAR(started_at, 'YYYY-MM'),
       COUNT(*) AS n_registrations
FROM noom_signups
GROUP BY 1;

6. Umgang mit Textual content

Neben Datums- und Zahlenwerten enthalten Datenbanken sehr oft Textwerte. Manchmal müssen diese Werte bereinigt, neu formatiert, vereinheitlicht, aufgeteilt und zusammengeführt werden. Aus diesem Grund verfügt jede Datenbank über viele Textfunktionen. In PostgreSQLeinige der beliebtesten sind:

• CONCAT() oder ||
• SUBSTRING()
• LÄNGE()
• ERSETZEN()
• TRIMMEN()
• POSITION()
• OBERER, HÖHER() und UNTERE()
• REGEXP_REPLACE() und REGEXP_MATCHES() und REGEXP_SPLIT_TO_ARRAY()
• LINKS() und RECHTS()
• LTRIM() und RTRIM()

Normalerweise gibt es in allen Datenbanken einige überlappende Zeichenfolgenfunktionen, jede hat jedoch einige unterschiedliche Funktionen.

Beispiel zum Ermitteln der Textlänge: Diese Abfrage verwendet die LÄNGE() Funktion zum Auffinden ungültiger Tweets anhand ihrer Länge.

SELECT tweet_id 
FROM Tweets 
WHERE LENGTH(content material) > 15;

7. Rating-Daten

Das Rating von Daten ist eine der weitverbreitetsten Aufgaben in der Datenwissenschaft. So lassen sich damit beispielsweise die am besten oder am schlechtesten verkauften Produkte, die Quartale mit den höchsten Umsätzen, Songs nach der Anzahl der Streams und die best- und am schlechtesten bezahlten Mitarbeiter ermitteln.

Die Rangfolge wird mithilfe von Fensterfunktionen erstellt (auf die wir im nächsten Abschnitt näher eingehen):

• ZEILENNUMMER()
• RANG()
• DENSE_RANK()

Beispiel für eine Rangfolge: Diese Abfrage verwendet DENSE_RANK() um Gastgeber nach der Anzahl der angebotenen Betten zu bewerten.

SELECT host_id, 
       SUM(n_beds) AS number_of_beds,
       DENSE_RANK() OVER(ORDER BY SUM(n_beds) DESC) AS rank
FROM airbnb_apartments
GROUP BY host_id
ORDER BY number_of_beds DESC;

8. Fensterfunktionen

Mit Fensterfunktionen in SQL können Sie die Zeilen berechnen, die mit der aktuellen Zeile verknüpft sind. Diese Eigenschaft wird nicht nur zum Rating von Daten verwendet. Je nach Kategorie der Fensterfunktion können sie viele verschiedene Verwendungszwecke haben. Weitere Informationen dazu finden Sie im Fensterfunktionen Artikel. Ihr Hauptmerkmal ist jedoch, dass sie analytische und aggregierte Daten gleichzeitig anzeigen können. Mit anderen Worten, sie reduzieren bei der Berechnung keine einzelnen Zeilen.

Beispiel für die Fensterfunktion FIRST_VALUE(): Ein Beispiel für eine Fensterfunktion ist die Anzeige der letzten Benutzeranmeldung für ein bestimmtes Jahr. ERSTER_WERT() Die Fensterfunktion erleichtert dies.

SELECT DISTINCT user_id,
       FIRST_VALUE(time_stamp) OVER (PARTITION BY user_id ORDER BY time_stamp DESC) AS last_stamp
FROM Logins
WHERE EXTRACT(YEAR FROM time_stamp) = 2020;

9. Unterabfragen und CTEs

Unterabfragen und CTEs (bekannt als aufgeräumtere Unterabfragen) ermöglichen Ihnen, eine fortgeschrittenere Berechnungsebene zu erreichen. Wenn Sie Unterabfragen und CTEs kennen, können Sie schreiben komplexe SQL-Abfragenwobei Unterabfragen oder CTEs für Unterberechnungen verwendet werden, auf die in der Hauptabfrage verwiesen wird.

Beispiel für Unterabfragen und CTEs: Die folgende Abfrage verwendet die Unterabfrage, um das erste Jahr des Produktverkaufs zu ermitteln. Diese Daten werden dann in WHERE für die Hauptabfrage verwendet, um Daten zu filtern.

SELECT product_id, 
       yr AS first_year, 
       amount, 
       worth 
FROM Gross sales 
WHERE (product_id, yr) IN (
    SELECT product_id, 
           MIN(yr) AS yr 
    FROM Gross sales 
    GROUP BY product_id
);

Der Code kann mit CTE statt einer Unterabfrage geschrieben werden.

WITH first_year_sales AS (
    SELECT product_id, 
           MIN(yr) AS first_year 
    FROM Gross sales 
    GROUP BY product_id
)

SELECT s.product_id, 
       s.yr AS first_year, 
       s.amount, 
       s.worth 
FROM Gross sales s
JOIN first_year_sales AS fys 
ON s.product_id = fys.product_id AND s.yr = fys.first_year;

Geschäftsbeispiele für die Verwendung von SQL

Sehen wir uns nun einige Geschäftsfälle an, in denen Datenwissenschaftler SQL verwenden und alle (oder die meisten) der zuvor besprochenen Konzepte anwenden können.

Das meistverkaufte Produkt finden

In diesem BeispielSie müssen sich mit Unterabfragen, Datenaggregation, der Verarbeitung von Datumsangaben, der Rangfolge von Daten mithilfe von Fensterfunktionen und der Filterung der Ausgabe auskennen.

Die Unterabfrage berechnet die Verkäufe jedes Produkts für jeden Monat und ordnet sie nach Verkäufen. Die Hauptabfrage wählt dann einfach die erforderlichen Spalten aus und lässt nur die Produkte mit dem ersten Rang übrig, d. h. die meistverkauften Produkte.

SELECT sale_month,
       description,
       total_paid
FROM
  (SELECT DATE_PART('MONTH', invoicedate) AS sale_month,
          description,
          SUM(unitprice * amount) AS total_paid,
          RANK() OVER (PARTITION BY DATE_PART('MONTH', invoicedate) ORDER BY SUM(unitprice * amount) DESC) AS sale_rank
   FROM online_retail
   GROUP BY sale_month,
            description) AS ranking_sales
WHERE sale_rank = 1;

Berechnung des gleitenden Durchschnitts

Der gleitende Durchschnitt ist eine gängige Geschäftsberechnung, bei der Datenwissenschaftler ihre umfangreichen SQL-Kenntnisse anwenden können, wie in dieses Beispiel.

Die Unterabfrage im folgenden Code berechnet die Einnahmen professional Monat. Die Hauptabfrage verwendet dann die AVG() Fensterfunktionen zur Berechnung des gleitenden 3-Monats-Durchschnittsumsatzes.

SELECT t.month,
       AVG(t.monthly_revenue) OVER(ORDER BY t.month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_revenue
FROM
  (SELECT TO_CHAR(created_at::DATE, 'YYYY-MM') AS month,
          SUM(purchase_amt) AS monthly_revenue
   FROM amazon_purchases
   WHERE purchase_amt>0
   GROUP BY 1
   ORDER BY 1) AS t
ORDER BY t.month ASC;

Abschluss

Alle diese SQL-Abfragen zeigen Ihnen, wie Sie SQL in Ihren Information Science-Aufgaben verwenden. Obwohl SQL nicht für komplexe statistische Analysen oder maschinelles Lernen gedacht ist, eignet es sich perfekt zum Abfragen, Bearbeiten, Aggregieren von Daten und Durchführen von Berechnungen.

Diese Beispielabfragen sollten Ihnen bei Ihrer Arbeit helfen. Wenn Sie keinen Information Science-Job haben, werden viele dieser Abfragen in Ihrem SQL-Interviewfragen.

Nate Rosidi ist Datenwissenschaftler und arbeitet in der Produktstrategie. Er ist außerdem außerordentlicher Professor für Analytik und Gründer von StrataScratch, einer Plattform, die Datenwissenschaftlern mit echten Interviewfragen von High-Unternehmen bei der Vorbereitung auf ihre Vorstellungsgespräche hilft. Nate schreibt über die neuesten Tendencies auf dem Arbeitsmarkt, gibt Interviewtipps, teilt Datenwissenschaftsprojekte und deckt alles ab, was mit SQL zu tun hat.