Beantwortung von Geschäftsfragen mit SQL

https://www.youtube.com/watch?v=tqottkssys

In diesem Projekt -Thrombough werden wir erforschen So verwenden Sie SQL für die Datenanalyse aus einem digitalen Musikgeschäft und beantworten Sie kritische geschäftliche Fragen. Durch die Zusammenarbeit mit der Chinook-Datenbank-einer Beispieldatenbank, die einen digitalen Medienspeicher ähnelt, der iTunes ähnelt-werden wir zeigen, wie SQL datengeformte Entscheidungen in einem geschäftlichen Kontext vorantreiben kann.

Der Chinook -Datenbank Enthält Informationen zu Künstlern, Alben, Tracks, Kunden und Verkaufsdaten. Durch strategische SQL -Abfragen helfen wir dem Unternehmen dabei, seine Markttrends zu verstehen, die Leistung der Mitarbeiter zu bewerten und Wachstumschancen zu identifizieren. Dieses Projekt zeigt reale SQL-Anwendungen, die Datenanalysten täglich begegnen.

Wir werden Sie durch das Schreiben von immer komplexeren SQL Gemeinsame Tabellenausdrücke (CTEs) und Unterabfragen.

Was du lernen wirst

Am Ende dieses Tutorials werden Sie wissen, wie man:

• Navigieren Sie mit mehreren Tabellen komplexe relationale Datenbankschemata
• Schreiben Sie SQL -Abfragen mit Verbindungen, um Daten über mehrere Tabellen hinweg zu verbinden
• Verwenden Sie gemeinsame Tabellenausdrücke (CTEs), um komplexe Abfragen zu organisieren
• Wenden Sie Unterabfragen an, um Prozentsätze und vergleichende Metriken zu berechnen
• Analysieren Sie Geschäftsdaten, um umsetzbare Erkenntnisse zu liefern
• Verbinden Sie SQL -Abfragen mit Python in Jupyter

Bevor Sie beginnen: Vorabstrukturierung

Folgen Sie diese vorbereitenden Schritte, um das Beste aus diesem Projekt zu machen:

1. Überprüfen Sie das Projekt
2. Bereiten Sie Ihre Umgebung vor
   • Wenn Sie die DataQuest -Plattform verwenden, ist für Sie bereits alles eingerichtet
   • Wenn Sie vor Ort arbeiten, benötigen Sie:
3. Machen Sie sich mit SQL -Grundlagen vertraut
   • Sie sollten mit grundlegenden SQL -Schlüsselwörtern vertraut sein: SELECTAnwesend FROMAnwesend GROUP BYUnd JOIN
   • Einige Erfahrungen mit CTEs und Unterabfragen sind hilfreich, aber nicht erforderlich
   • Neu im Markdown? Wir empfehlen, die Grundlagen zu lernen: Markdown -Handbuch

Einrichten Ihrer Umgebung

Bevor wir in unsere Analyse eingehen, lassen Sie uns unsere Jupyter -Umgebung so einrichten, dass sie mit SQL zusammenarbeiten. Wir werden einige SQL Magic -Befehle verwenden, mit denen wir SQL direkt in Jupyter -Zellen schreiben können.

%%seize
%load_ext sql
%sql sqlite:///chinook.db

Erkenntnis lernen: die %%seize Magic Command unterdrückt alle Ausgabenachrichten aus der Zelle und hält unser Pocket book sauber. Der %load_ext sql Der Befehl lädt die SQL -Erweiterung und lädt %sql sqlite:///chinook.db verbindet uns mit unserer Datenbank.

Überprüfen wir nun unsere Verbindung und untersuchen, welche Tabellen in unserer Datenbank verfügbar sind:

%%sql

SELECT title 
FROM sqlite_master 
WHERE kind='desk';

Diese spezielle SQLite -Abfrage zeigt uns alle Tabellennamen in unserer Datenbank. Die Chinook -Datenbank enthält 11 Tabellen, die verschiedene Aspekte eines digitalen Musikgeschäfts darstellen:

• album: Albumdetails
• artist: Künstlerinformationen
• buyer: Kundeninformationen mit zugewiesenen Assist -Vertretern
• worker: Laden Sie Mitarbeiter, einschließlich Vertriebsunterstützungsagenten
• style: Musikgenres
• bill: Verkaufstransaktionen
• invoice_line: Einzelne Elemente in jeder Rechnung
• media_type: Formattypen (MP3, AAC usw.)
• playlist: Kuratierte Wiedergabelisten
• playlist_track: Tracks in jeder Wiedergabeliste
• monitor: Liedinformationen

Verständnis des Datenbankschemas

Die Arbeit mit relationalen Datenbanken bedeutet zu verstehen, wie Tabellen miteinander verbunden sind. Die Chinook -Datenbank verwendet primäre und fremde Schlüssel, um diese Beziehungen aufzubauen. Hier ist eine vereinfachte Sicht auf die wichtigsten Beziehungen zwischen den Tabellen, mit denen wir arbeiten werden:

• buyer ist verbunden mit worker durch support_rep_id
• bill ist verbunden mit buyer durch customer_id
• invoice_line ist verbunden mit bill durch invoice_id
• monitor ist verbunden mit albumAnwesend invoice_lineUnd style durch album_idAnwesend track_idUnd genre_idjeweils

Lassen Sie uns einige unserer wichtigsten Tabellen vorschauen, um die Daten zu verstehen, mit denen wir arbeiten:

%%sql

SELECT * 
FROM monitor 
LIMIT 5;

%%sql

SELECT * 
FROM invoice_line 
LIMIT 5;

Lernerkenntnisse: Wenn Sie mit einer neuen Datenbank arbeiten, können Sie Ihre Tabellen immer mit Grenzwert einstellen, um die Datenstruktur zu verstehen, bevor komplexe Abfragen geschrieben werden. Auf diese Weise können Sie Spaltennamen, Datentypen und potenzielle Beziehungen identifizieren, ohne Ihre Ausgabe mit Hunderten von Zeilen zu überfluten.

Geschäftsfrage 1: Auf welche Musikgenres sollten wir uns in den USA konzentrieren?

Der Chinook Retailer möchte verstehen, welche Musikgenres auf dem US -amerikanischen Markt am beliebtesten sind. Diese Informationen helfen ihnen, zu entscheiden, welche neuen Alben zu ihrem Katalog erweitern sollen. Lassen Sie uns eine Anfrage erstellen, um die Popularität der Style durch Verkauf zu analysieren.

Aufbau unserer Analyse mit einem CTE

Wir werden einen gemeinsamen Tabellenausdruck (CTE) verwenden, um einen temporären Ergebnissatz zu erstellen, der Daten aus mehreren Tabellen kombiniert:

%%sql

WITH genre_usa_tracks AS (
    SELECT
        il.invoice_line_id,
        g.title AS style,
        t.track_id,
        i.billing_country AS nation
    FROM monitor t
    JOIN style g ON t.genre_id = g.genre_id
    JOIN invoice_line il ON t.track_id = il.track_id
    JOIN bill i ON il.invoice_id = i.invoice_id
    WHERE i.billing_country = 'USA'
)
SELECT
    style,
    COUNT(*) AS tracks_sold,
    COUNT(*) * 100.0 / (SELECT COUNT(*) FROM genre_usa_tracks) AS proportion
FROM genre_usa_tracks
GROUP BY style
ORDER BY tracks_sold DESC;

Lernerkenntnisse: CTEs machen komplexe Abfragen lesbarer, indem sie in logische Schritte zerlegt werden. Hier erstellen wir zuerst einen gefilterten Datensatz der USA -Einkäufe und analysieren sie dann. Die 100,0 in unserer prozentualen Berechnung stellt sicher, dass wir Dezimalergebnisse anstelle einer ganzzahligen Division erhalten.

Unsere Ergebnisse zeigen das Felsen Musik dominiert den USA -Markt mit über 50% des Umsatzes, gefolgt von lateinischAnwesend MetallUnd Different & Punk. Dies deutet darauf hin, dass der Laden diese Genres bei der Auswahl eines neuen Inventars priorisieren sollte.

Wichtige Erkenntnisse aus der Genreanalyse

• Rock dominiert: Mit 561 verkauften Tracks (53,4%) ist Rock bei weitem das beliebteste Style
• Lateinische Musiküberraschung: Das zweitbeliebteste Style ist lateinisch (10,3%), was auf einen signifikanten Marktsegment hinweist
• Langer Schwanzeffekt: Viele Genres haben sehr kleine Prozentsätze, was auf Nischenmärkte hinweist

Geschäftsfrage 2: Analyse der Vertriebsleistung der Mitarbeiter

Das Unternehmen möchte die Leistung der Vertriebsunterstützungsagenten bewerten, um High -Performer und Verbesserungsbereiche zu identifizieren. Lassen Sie uns analysieren, welche Mitarbeiter die meisten Einnahmen erzielen.

%%sql

SELECT
    e.first_name || ' ' || e.last_name AS employee_name,
    e.hire_date,
    COUNT(DISTINCT c.customer_id) AS customer_count,
    SUM(i.whole) AS total_sales_dollars,
    SUM(i.whole) / COUNT(DISTINCT c.customer_id) AS avg_dollars_per_customer
FROM buyer c
JOIN bill i ON c.customer_id = i.customer_id
JOIN worker e ON c.support_rep_id = e.employee_id
GROUP BY e.employee_id, e.hire_date
ORDER BY total_sales_dollars DESC;

Erkenntnis lernen: Wenn Sie verwendet werden GROUP BY Denken Sie bei aggregierten Funktionen daran, alle nicht-aggregierten Spalten in Ihre aufzunehmen GROUP BY Klausel. Dies ist in den meisten SQL -Geschmacksrichtungen erforderlich (obwohl SQLite mehr verzeihend ist). Der || Der Bediener verkettet Saiten in SQLite.

Leistungsanalyseergebnisse

Unsere Analyse zeigt interessante Muster:

• Jane Peacock führt mit den höchsten durchschnittlichen Greenback professional Kunden, obwohl sie nicht die meisten Kunden haben
• Margaret Park’s Die Leistung ist solide, mit Metriken in der Nähe von Jane’s, was auf ein konsistentes Maß an Kundenwertdelieferung hinweist
• Steve Johnsonder neueste Mitarbeiter, zeigt eine vielversprechende Leistung bei Metriken, die ähnlich erfahrener Mitarbeiter ähnlich sind

Geschäftsfrage 3: Kombinieren von SQL mit Python zur Visualisierung

Während sich SQL bei der Datenabnahme und -Transformation auszeichnet, ermöglicht das Kombinieren mit Python leistungsstarke Visualisierungen. Lassen Sie uns zeigen, wie Sie die SQL -Abfrageergebnisse an Python weitergeben:

import pandas as pd

# Retailer our question as a string
question = """
SELECT
    style,
    COUNT(*) AS tracks_sold
FROM genre_usa_tracks
GROUP BY style
ORDER BY tracks_sold DESC
LIMIT 10;
"""

# Execute the question and retailer outcomes
consequence = %sql $question

# Convert to pandas DataFrame
df = consequence.DataFrame()

Erkenntnis lernen: die %sql Inline Magic (einzelnes Prozentzeichen) ermöglicht es uns, SQL auszuführen und die Ergebnisse in Python zu erfassen. Die Greenback -Signalsyntax ($question) Lassen Sie uns Python -Variablen in SQL Magic -Befehlen verweisen.

Herausforderungen und Überlegungen

Während unserer Analyse haben wir mehrere wichtige SQL -Konzepte gestoßen, die es wert sind, hervorzuheben:

1. Integer Division Fallstrick

Bei der Berechnung von Prozentsätzen führt SQL standardmäßig eine ganzzahlige Abteilung aus:

-- This returns 0 for all percentages
SELECT COUNT(*) / (SELECT COUNT(*) FROM desk) AS proportion

-- This returns correct decimals
SELECT COUNT(*) * 100.0 / (SELECT COUNT(*) FROM desk) AS proportion

2. Die Auswahl anschließen

Wir haben benutzt INNER JOIN Im Laufe dessen wollten wir nur Aufzeichnungen, die in allen verwandten Tabellen existieren. Wenn wir Kunden ohne Rechnungen einbeziehen müssten, würden wir verwenden LEFT JOIN stattdessen.

3.. Aufgrund der Leistung

Unsere prozentuale Berechnung verwendet eine Unterabfrage, die für jede Zeile ausgeführt wird. Für größere Datensätze sollten Sie Fensterfunktionen oder die Vorbereitung der Summen in einem CTE verwenden.

Teilen Sie Ihre Arbeit mit Github GISTs

Github GISTs bieten eine hervorragende Möglichkeit, Ihre SQL -Projekte ohne die Komplexität der vollständigen Repositorys zu teilen. Hier erfahren Sie, wie Sie Ihre Arbeit teilen können:

1. Navigieren zu gist.github.com
2. Erstellen Sie einen neuen Kern
3. Nennen Sie Ihre Datei mit der .ipynb Erweiterung für Jupyter -Notizbücher oder .sql Für SQL -Skripte
4. Fügen Sie Ihren Code ein und erstellen Sie entweder ein öffentliches oder geheimes Kern

GISTs rendern die Jupyter -Notizbücher automatisch mit allen Ausgaben erhalten, sodass sie perfekt für die gemeinsame Nutzung der Analyseergebnisse mit Stakeholdern oder in Ihr Projektportfolio einbezogen werden.

Zusammenfassung der Analyse

In diesem Projekt haben wir gezeigt, wie SQL kritische Geschäftsfragen für einen digitalen Musikgeschäft beantworten kann:

1. Genreanalyse: Wir haben identifiziert Felsen als dominantes Style auf dem USA -Markt (53,4%) mit lateinisch Musik als überraschender zweiter Platz
2. Leistung der Mitarbeiter: Wir haben Vertriebsmitarbeiter bewertet und festgestellt, dass Jane Peacock im Durchschnittsumsatz professional Kunden führt
3. Technische Fähigkeiten: Wir haben CTEs, Unterabfragen, Mehrfachverbindungen und aggregierte Funktionen zur Lösung realer Geschäftsprobleme angewendet

Diese Erkenntnisse ermöglichen datengesteuerte Entscheidungen über Inventarmanagement, Mitarbeiterausbildung und Marktstrategie.

Nächste Schritte

Um diese Analyse zu erweitern und Ihre SQL -Fähigkeiten zu vertiefen, berücksichtigen Sie diese Herausforderungen:

1. Zeitbasierte Analyse: Wie verändern sich die Verkaufstrends im Laufe der Zeit? Fügen Sie die Datumsfilterung hinzu, um saisonale Muster zu identifizieren
2. Kundensegmentierung: Welche Kunden sind am wertvollsten? Erstellen Sie Kundensegmente basierend auf dem Kaufverhalten
3. Produktempfehlungen: Welche Tracks werden üblicherweise zusammen gekauft? Verwenden Sie Selfjoins, um Assoziationen zu finden
4. Internationale Märkte: Erweitern Sie die Genreanalyse, um Präferenzen in verschiedenen Ländern zu vergleichen

Wenn Sie neu in SQL sind und dieses Projekt herausfordernd haben, beginnen Sie mit unserer SQL -Grundlagen Fertigkeitspfad zum Aufbau der für die komplexen Analyse erforderlichen fundierten Fähigkeiten. Der Kurs behandelt wesentliche Themen wie Joins, Aggregationen und Unterabfragen, die wir in diesem Projekt verwendet haben.

Viel Spaß beim Abfragen!