Erstellen Sie Ihr eigenes Spotify Wrapped mit SQL (denn warum nicht?)

Jeder zeigt sein Spotify Wrapped in den sozialen Medien, aber was wäre, wenn du dein eigenes erstellen könntest und alles sehen könntest, was Spotify auslässt? Mit ein bisschen SQL kannst du deine Hörgewohnheiten analysieren, deine wahren Top-Tracks sehen und sogar herausfinden, wie viel Zeit du mit einer Schleife deines Lieblingssongs verbracht hast. Das ist einfach, macht Spaß und ist eine gute Möglichkeit, Ihre SQL-Kenntnisse zu verbessern. In diesem Artikel zeige ich Ihnen, wie es geht.

Du weißt doch, dass jedes Jahr Spotify Wrapped erscheint und jeder seine Top-Titel, Interpreten und Hörstatistiken veröffentlicht? Das macht Spaß, aber seien wir mal ehrlich - manchmal will man mehr als nur das, was Spotify einem zeigt. Wie viel Zeit deines Lebens hast du damit verschwendet, dir diesen einen Lieblingssong anzuhören? Oder wer war wirklich dein Lieblingskünstler, wenn Spotify keine Podcasts eingeschmuggelt hat?

Wenn du dich schon immer mal mit deinen eigenen Musikdaten beschäftigen wolltest, habe ich etwas Cooles für dich: Erstelle dein eigenes Spotify Wrapped mit SQL. Es ist nicht so kompliziert, wie es klingt. Du bekommst die volle Kontrolle über deine Daten, kannst mit ihnen angeben und hast einen guten Grund, SQL zu üben, während du zum Spaß Charts erstellst.

Schritt 1: Laden Sie Ihre Spotify-Daten herunter

Als Erstes müssen Sie Ihre Hördaten von Spotify herunterladen. Das ist überraschend einfach - Spotify ist gesetzlich verpflichtet, Ihnen Zugang zu allen gesammelten persönlichen Daten zu gewähren, und das Verfahren ist ziemlich einfach.

So gehen Sie vor:

• Rufen Sie die Spotify-Datenschutzseite auf. Wenn Sie noch nicht angemeldet sind, müssen Sie sich einloggen. Hier bewahrt Spotify alle Tools für die Verwaltung und den Zugriff auf deine Daten auf.
• Sobald Sie angemeldet sind, blättern Sie zu dem Abschnitt mit der Aufschrift "Ihre Daten herunterladen". Klicken Sie darauf, und Spotify fordert Sie auf, die Anfrage zu bestätigen. Es geht nicht sofort - Spotify braucht ein bisschen Zeit, um alles zusammenzutragen (meiner Erfahrung nach 1-2 Tage). Sie erhalten eine E-Mail, wenn die Daten fertig sind, also aktualisieren Sie nicht ständig Ihren Posteingang, wie ich es getan habe.
• Wenn die E-Mail eintrifft, laden Sie die ZIP-Datei herunter, die Sie erhalten haben. In dieser ZIP-Datei finden Sie mehrere Dateien, aber diejenige, die uns interessiert, ist StreamingHistory.json. Diese Datei ist eine wahre Fundgrube - sie enthält ein detailliertes Protokoll aller Titel, die Sie gespielt haben, wann Sie sie gespielt haben und wie lange. Das ist im Grunde Ihr Spotify-Leben in Datenform.

Eine Vorwarnung: Als ich mir die Datei StreamingHistory.json zum ersten Mal ansah, hatte ich das Gefühl, dass ich mich in meinen eigenen Musikgeschmack einhacke. Auf den ersten Blick ist sie nicht besonders hübsch - nur Textzeilen mit Zeitstempeln, Interpretennamen und Titeln - aber glauben Sie mir, wenn man sie erst einmal mit SQL bearbeitet, ist sie sehr mächtig.

Also, schnappen Sie sich diese Datei und lassen Sie uns Ihre Hörgewohnheiten in etwas viel Cooleres als Spotify Wrapped verwandeln.

Schritt 2: Bereiten Sie Ihre Daten für SQL vor

Die Sache ist die: Spotify stellt Ihnen Ihre Daten als JSON-Datei zur Verfügung. JSON (JavaScript Object Notation) ist eine Möglichkeit, Daten in einem Textformat zu speichern, das Maschinen lieben, das aber nicht gerade menschenfreundlich ist. Für Entwickler eignet es sich hervorragend, aber SQL benötigt Ihre Daten in einem Tabellenformat - wie eine Tabellenkalkulation. Der erste Schritt besteht also darin, StreamingHistory.json in eine CSV-Datei zu konvertieren.

Keine Sorge, das ist einfacher, als es klingt. Sie brauchen keine ausgefallenen Tools oder Programmierkenntnisse - Excel, Google Sheets oder sogar ein einfacher Online-Konverter reichen aus. So wird's gemacht:

Wenn Sie Excel oder Google Sheets verwenden:

• Öffnen Sie Excel (oder Sheets) und suchen Sie nach der Option zum Importieren einer Datei. In Excel gehen Sie zu Daten -> Daten abrufen -> Aus Datei -> JSON. In Google Sheets ist der Vorgang etwas manueller, so dass Sie möglicherweise zunächst einen Online-Konverter benötigen.
• Laden Sie Ihre StreamingHistory.json-Datei hoch. Excel parst das JSON und wandelt es in Zeilen und Spalten um.

Wenn Sie eine schnelle Online-Lösung wünschen:

Verwenden Sie ein Tool wie json-csv.com oder ein ähnliches. Auf diesen Websites können Sie Ihre JSON-Datei hochladen und sie in Sekundenschnelle in eine CSV-Datei umwandeln. Laden Sie die resultierende Datei herunter, und schon sind Sie startklar.

Was ist in der CSV-Datei enthalten?

Nach der Konvertierung sieht Ihre CSV-Datei wie eine übersichtliche kleine Tabelle mit vier Schlüsselspalten aus:

Column Name	Description
endTime	When you listened to the track (timestamp).
artistName	The name of the artist.
trackName	The title of the song you listened to.
msPlayed	How long you played the song (in milliseconds).

Zum Beispiel:

endTime	artistName	trackName	msPlayed
2024-06-05 12:00	Metallica	Enter Sandman	325000
2024-06-05 12:05	Korn	Freak on a Leash	244000

Betrachten Sie dies als Ihre Rohdaten - das Rückgrat Ihrer Analyse. Mit endTime können Sie genau nachverfolgen, wann Sie ein Lied gespielt haben, während msPlayed angibt, wie lange Sie zugehört haben. Sobald Sie die CSV-Datei fertig haben, können Sie sie direkt in SQL laden und Ihre Hörgewohnheiten wie ein Profi abfragen.

Schritt 3: Daten in PostgreSQL laden (pgAdmin)

Jetzt, wo Ihre Spotify-Daten als CSV-Datei bereitstehen, ist es an der Zeit, sie mit pgAdmin in PostgreSQL zu laden. An dieser Stelle verwandeln wir Ihren rohen Hörverlauf in eine SQL-freundliche Tabelle. Keine Sorge, ich werde Sie durch den gesamten Prozess führen.

1. Erstellen Sie die Tabelle in pgAdmin

Zuerst brauchen Sie eine Tabelle, die die Daten enthält. Öffnen Sie pgAdmin und verbinden Sie sich mit Ihrer PostgreSQL-Datenbank. Sobald Sie verbunden sind:

1. Öffnen Sie ein neues Abfragefenster(Rechtsklick auf Ihre Datenbank -> Abfragetool).
2. Führen Sie das folgende SQL-Skript aus, um eine Tabelle zu erstellen:

CREATE TABLE streaming_history (
    endTime TIMESTAMP,
    artistName VARCHAR(255),
    trackName VARCHAR(255),
    msPlayed INT
);
    

Klicken Sie auf die Schaltfläche Ausführen (das Blitzsymbol), und Ihre Tabelle wird erstellt.

2. Importieren Sie die CSV-Datei in Ihre Tabelle

Lassen Sie uns nun Ihre CSV-Datei in die soeben erstellte Tabelle laden:

• Im linken Menü von pgAdmin erweitern Sie Ihre Datenbank -> Schemas -> Tabellen.
• Suchen Sie Ihre Tabelle (streaming_history) und klicken Sie mit der rechten Maustaste auf sie.
• Wählen Sie Import/Export aus dem Menü.

Es öffnet sich ein neues Fenster. Gehen Sie wie folgt vor:

• Dateiname: Navigieren Sie zum Speicherort Ihrer CSV-Datei.
• Format: Wählen Sie CSV.
• Begrenzungszeichen: Setzen Sie hier ein Komma (,).
• Kopfzeile: Stellen Sie sicher, dass diese Option aktiviert ist, wenn Ihre CSV-Datei Spaltennamen in der ersten Zeile enthält.

Klicken Sie auf OK, um den Importvorgang zu starten.

3. Überprüfen Sie die Daten

Um sicherzustellen, dass alles korrekt geladen wurde, können Sie eine einfache SQL-Abfrage ausführen, um Ihre Tabelle zu überprüfen:

SELECT * FROM streaming_history LIMIT 10;
    

Dadurch werden die ersten 10 Zeilen Ihrer Tabelle angezeigt. Wenn Sie Ihre Titel, Interpretennamen und Wiedergabedauer sauber in Spalten angeordnet sehen, sind Sie startklar!

Schritt 4: Analysieren Sie Ihre Wiedergabeliste mit SQL-Abfragen

Sie haben Ihre Daten in PostgreSQL gespeichert, und jetzt ist es an der Zeit, die Macht von SQL zu entfesseln. Mit nur wenigen Abfragen können Sie Erkenntnisse gewinnen, die selbst Spotify Wrapped nicht zeigt. Holen Sie sich einen Kaffee (oder legen Sie Ihre Lieblingsplaylist auf), und los geht's.

Top 5 der meistgespielten Tracks

Willst du sehen, welche Songs du absolut übermäßig abspielst? Diese Abfrage fasst die Zeit zusammen, die du mit jedem Titel verbracht hast, und sortiert sie in absteigender Reihenfolge.

SELECT trackName, artistName, SUM(msPlayed) / 60000 AS totalMinutes
FROM streaming_history
GROUP BY trackName, artistName
ORDER BY totalMinutes DESC
LIMIT 5;

Was sie tut:

• Gruppiert Ihre Daten nach Titelname und Interpret.
• Summiert die Gesamtspielzeit (in Millisekunden) und rechnet sie in Minuten um.
• Sortiert die Titel nach Gesamtminuten und zeigt die Top 5 an.

So siehst du endlich, ob der eine Song, von dem du schwörst, dass du ihn "nur einmal gehört" hast, tatsächlich dein Jahr dominiert.

Gesamte Hördauer

Hast du dich schon einmal gefragt, wie viele Stunden deines Lebens du dieses Jahr mit Musikhören verbracht hast? Hier erfährst du es:

SELECT SUM(msPlayed) / 3600000 AS totalHours
FROM streaming_history;

Was es tut:

• Summiert die Gesamtzeit (Millisekunden), die ihr mit dem Anhören von Titeln verbracht habt.
• Rechnen Sie das in Stunden um.

Ihr könntet lachen, weinen oder eure Lebensentscheidungen in Frage stellen, wenn ihr diese Zahl seht. Auf jeden Fall ist es eine lustige Zahl, die man wissen sollte.

Monatliche Hörtrends

Möchten Sie sehen, wie sich Ihre Musikgewohnheiten im Laufe der Monate verändert haben? Diese Abfrage schlüsselt es auf:

SELECT DATE_TRUNC('month', endTime) AS month, SUM(msPlayed) / 3600000 AS hours
FROM streaming_history
GROUP BY month
ORDER BY month;

Was sie tut:

• Gruppiert Ihre Hördaten nach Monat mit DATE_TRUNC.
• Summiert die Gesamtspielzeit und rechnet sie in Stunden um.
• Sortiert die Daten chronologisch, damit Sie Trends erkennen können.

So können Sie feststellen, ob Sie während der Sommerferien, des Winterschlafs oder der stressigen Abgabetermine mehr hören.

Schritt 5: Visualisieren Sie Ihre Daten in Power BI

Nachdem Sie nun Ihre Erkenntnisse aus SQL gewonnen haben, ist es an der Zeit, diese Ergebnisse mit Power BI in ansprechende Grafiken zu verwandeln. Und so geht's:

Verbinden Sie Power BI mit PostgreSQL

• Öffnen Sie Power BI.
• Gehen Sie zu Startseite -> Daten abrufen -> PostgreSQL-Datenbank.
• Geben Sie Ihren Servernamen und Ihre Datenbankanmeldedaten ein.
• Laden Sie die Tabelle streaming_history in Power BI.

Erstellen Sie Ihre Visuals Verwenden Sie diese Vorschläge, um Ihre Daten zum Leben zu erwecken:

• Top Tracks: Balkendiagramm
• X-Achse: Name des Tracks
• Y-Achse: Gehörte Gesamtminuten
• Top-Künstler: Kreisdiagramm
• Zeigt den Anteil der Zeit, die für jeden Künstler aufgewendet wurde.
• Monatliche Hörtrends: Liniendiagramm
• X-Achse: Monate
• Y-Achse: Insgesamt gehörte Stunden
• Gesamte Hördauer: Kartenvisualisierung
• Zeigen Sie die gesamte Hördauer in Stunden als große, fette Zahl an.
• Gestalten Sie es wie Spotify
• Verwenden Sie dunkle Themen mit hellen Farben (Grün, Lila, Gelb), um eine Spotify-Atmosphäre zu schaffen.
• Fügen Sie Titel wie "Top 5 NuMetal Tracks" oder "Monthly Listening Habits" hinzu, um dem Ganzen Persönlichkeit zu verleihen.

Ihr Dashboard sieht schlank und professionell aus und ist bereit zum Teilen. Hier sind meine Top 10 Tracks des Jahres 2024!

Schritt 6: Freigeben und Flexen

Sobald Ihr Power BI-Dashboard fertig ist, haben Sie sich das Recht verdient, es zu zeigen. Und so geht's:

• Exportieren Sie es: Speichern Sie das Dashboard als PDF oder Bild.
• Teilen Sie es: Veröffentlichen Sie es in den sozialen Medien, senden Sie es an Freunde oder drucken Sie es aus, wenn Sie es ganz klassisch haben möchten.
• Geben Sie ein wenig an: Zeigen Sie allen, dass Sie nicht nur Rockmusik hören, sondern sie auch wie ein Profi analysieren.

Stellen Sie sich Statistiken vor wie "Ich habe dieses Jahr 120 Stunden lang Metallica gehört" oder "Duality von Slipknot war mein meistgehörter Song mit 15 Wiederholungen". Sofortiger Respekt.

Abschließende Überlegungen

Die Erstellung eines eigenen Spotify Wrapped macht nicht nur Spaß, sondern ist eine Mischung aus Liebe zur Musik und praktischer Datentüftelei. Mit SQL kannst du Einblicke gewinnen, die Spotify nicht anzeigt, z. B. welche Titel du in der Wiederholung gespielt hast oder wie sich deine Musikgewohnheiten im Laufe des Jahres verändert haben.

In diesem Artikel erfährst du, wie du:

• SQL verwenden, um Ihre Top-Tracks, Top-Künstler und Hör-Trends zu ermitteln.
• Einfache, aber leistungsstarke Abfragen wie GROUP BY, SUM() und DATE_TRUNC ausführen.
• All diese Daten mit Power BI in saubere, Spotify-ähnliche Grafiken umwandeln.

Und jetzt kommt der Clou: Die von Ihnen verwendeten SQL-Techniken - Sortieren von Daten, Gruppieren und Berechnen von Summen - sind nur der Anfang. Wenn Sie noch mehr lernen und sich bei der Arbeit mit Daten richtig wohlfühlen möchten, sollten Sie sich den Kurs SQL von A bis Z auf LearnSQL.de. Er führt Sie Schritt für Schritt durch alles, was Sie wissen müssen, und Sie werden mit realen Daten wie diesen arbeiten können.

Mit SQL können Sie alles analysieren - Ihre Musik, Ihre Arbeitsdaten oder sogar Ihre Nebenprojekte. Es ist ein Werkzeug, das immer wieder neue Möglichkeiten bietet.

Schnappen Sie sich also Ihre Daten, schreiben Sie ein paar weitere Abfragen und sehen Sie, wie sehr Rock und Metal Ihr Jahr geprägt haben. Und hey, wenn du etwas Wildes entdeckst, wie z. B. dass du "Espresso" 200 Mal gespielt hast oder dass Ariana Grande 80 % deiner Playlist ausmacht - wir haben das alle schon erlebt - kein Urteil. Mal sehen, wer deine Playlist wirklich beherrscht!