SQL für eine Filmdatenbank verwenden, um zu entscheiden, was man sich ansehen soll

Wir zeigen Ihnen, wie Sie mit SQL große Datensätze analysieren und wertvolle Erkenntnisse gewinnen können. In diesem Fall helfen wir Ihnen bei der Auswahl des nächsten Films, den Sie sich ansehen möchten, indem wir einen IMDb-Datensatz verwenden.

In diesem Artikel werden wir ein Datensatzverzeichnis von IMDb herunterladen. Sie wissen nicht, was Sie sich heute Abend ansehen sollen? Durchstöbern Sie Netflix ohne Ende? Entscheiden Sie mit Hilfe von SQL, was Sie sehen wollen! Wir werden einen bestehenden IMDb-Datensatz in SQL laden. Wir werden die Daten auf verschiedene Arten analysieren, z. B. durch Sortieren der Filme nach ihrer Bewertung, nach den Schauspielern, die in dem Film mitspielen, oder nach anderen ähnlichen Kriterien.

Wie in diesem Blog-Beitrag über das Üben von SQL erwähnt, kann man SQL am besten üben, indem man praktische Erfahrungen beim Lösen von realen Problemen sammelt, und genau das werden wir tun.

Wenn Sie über Grundkenntnisse in SQL verfügen, sollten Sie diesem Artikel problemlos folgen können. Wenn Sie keinerlei IT-Erfahrung haben, sollten Sie mit diesem SQL von A bis Z beginnen, der sich an Personen richtet, die keine IT-Erfahrung haben und ihr Abenteuer mit SQL beginnen möchten.

Fangen wir damit an, wie wir die Filmdaten in unsere SQL-Datenbank bekommen.

Fertigstellung des SQL-Filmdatenbank-Downloads

Gehen wir den Prozess des Herunterladens unserer Daten und des Ladens in ein Datenbankmanagementsystem (DBMS) Schritt für Schritt durch. Zu den gängigen DBMS gehören MySQL, Oracle DB, PostgreSQL und SQL Server.

Obwohl sich dieser Artikel auf Filmdaten konzentriert, können Sie auch einen ganz anderen Datensatz wählen. Schauen Sie sich diese Liste kostenloser Online-Datensätze an, die Sie verwenden können, und finden Sie denjenigen, der Sie interessiert. Der Import dieser Datensätze ist unabhängig davon, welchen Datensatz Sie verwenden, ähnlich.

Öffnen Sie die von Ihnen verwendete Variante von SQL. In diesem Beispiel verwende ich SQL Server Management Studio, aber die Schritte sollten für alle anderen SQL-Varianten ähnlich sein. Fangen wir an:

1. Die Datensatzdateien können unter https://datasets.imdbws.com/ abgerufen und heruntergeladen werden . Die Daten werden täglich aufgefrischt.
2. Laden Sie alle aufgeführten Dateien herunter:
   1. grundlagen.tsv.gz
   2. akas.tsv.gz
   3. grundlagen.tsv.gz
   4. Mannschaft.tsv.gz
   5. Folge.tsv.gz
   6. Direktoren.tsv.gz
   7. bewertungen.tsv.gz
3. Entpacken Sie die heruntergeladenen Zip-Dateien. Das Endergebnis ist eine TSV-Datei (tabulatorgetrennt) für jede Tabelle.
4. Bereinigen Sie diese Daten und konvertieren Sie sie in CSV, damit sie in einem besser verwertbaren Zustand sind:
   1. Öffnen Sie jede Datei in einer Tabellenkalkulationsanwendung wie Google Sheets oder Microsoft Excel.
   2. Suchen Sie alle Vorkommen von "\N" und ersetzen Sie sie durch eine leere Zelle.
   3. Speichern Sie die Datei als CSV-Datei. Dies erleichtert den Import in das DBMS Ihrer Wahl.
5. Öffnen Sie Ihr DBMS.
6. Erstellen Sie ein neues Schema oder eine neue Tabelle, indem Sie mit der rechten Maustaste auf den linken Bereich klicken und "Neue Datenbank" auswählen. Ich habe meine neue Datenbank "imdb" genannt.
7. Klicken Sie mit der rechten Maustaste auf die Datenbank → Aufgaben → Flat File importieren und folgen Sie dem Importassistenten, um eine Tabelle für jede Datei zu erstellen:
   1. Legen Sie für jede Spalte, die Sie importieren, einen gültigen Datentyp fest. Ich empfehle die Verwendung von nvarchar(MAX) für Zeichenkettenspalten, da Sie nicht wissen, wie lang die Zeichenketten für jedes Feld sein werden. Sie können den Datentyp der Spalten später bei Bedarf ändern.
   2. Lassen Sie Nullwerte für alle Zeilen zu. Dadurch werden Probleme beim Import vermieden.
8. Wiederholen Sie diesen Vorgang für alle Dateien, die Sie heruntergeladen haben.

Wenn Sie diese Schritte abgeschlossen haben, ist Ihre SQL-Filmdatenbank fertig! Sie können nun mit der Analyse und Abfrage der Daten beginnen.

SQL-Übungen mit einer Filmdatenbank

Glücklicherweise wurde dieser Datensatz mit einer beschreibenden Dokumentation geliefert. Um einen noch besseren Überblick über die Daten zu erhalten, können Sie schnell die ersten 1000 Zeilen aus jeder Tabelle auswählen.

Lassen Sie uns mit der Suche nach unserem ersten Film beginnen. Stellen Sie sich vor, Sie wollen einen Horrorfilm sehen. Wie können wir nur die Horrorfilme herausfiltern? Zum Glück ist diese Aufgabe erschreckend einfach.

SELECT *
FROM title_basics
WHERE genres LIKE '%Horror%'

Wenn diese Abfrage Verwirrung stiftet, öffnen Sie diesen SQL-Spickzettel, um Ihr Wissen aufzufrischen. Lassen Sie diesen Spickzettel auch für den Rest des Tutorials geöffnet, um Ihnen zu helfen!

Was wäre, wenn wir die Liste der Horrorfilme noch weiter verfeinern wollten? Wir könnten die Ergebnisse auf Horrorfilme beschränken, die nach 1990 entstanden sind, eine Durchschnittsbewertung von über 9,0 haben und mindestens 10.000 Stimmen haben.

Dazu müssen wir Daten aus mehreren Tabellen abrufen. Wenn wir jede Tabelle öffnen und einen Blick auf die Spaltenüberschriften werfen, sehen wir, dass die folgenden Tabellen betroffen sind:

• title_basics: behandelt das Genre des Films und das Erscheinungsjahr (dargestellt durch die Spalte startYear).
• title_ratingsBewertung (averageRating) und Stimmen (numVotes).

Die beiden Tabellen können über die gemeinsame Spalte tconst verbunden werden. Wie in der IMDb-Dokumentation hier erläutert, ist tconst ein alphanumerischer eindeutiger Bezeichner des Titels. Lassen Sie uns unsere Abfrage schreiben:

SELECT titleType, primaryTitle, startYear, genres, averageRating, numVotes
FROM title_basics
INNER JOIN title_ratings ON title_basics.tconst = title_ratings.tconst
WHERE genres LIKE '%Horror%' AND startYear > 1989 AND averageRating > 9.0 AND numVotes > 10000

Die Ausführung dieser Abfrage liefert ein einziges Ergebnis, aber nicht das gewünschte Ergebnis! Bei näherer Betrachtung sehen wir, dass es sich bei diesem Titel um ein Videospiel und nicht um einen Film handelt. Ändern wir unsere Abfrage so ab, dass sie nur Filme enthält, und erweitern wir die Suche, indem wir die Mindestanzahl der erforderlichen Stimmen auf 1.000 und die Mindestbewertung auf 8,0 reduzieren.

SELECT *
FROM title_basics
INNER JOIN title_ratings ON title_basics.tconst = title_ratings.tconst
WHERE genres LIKE '%Horror%' AND startYear > 1989 AND averageRating > 8.0 AND numVotes > 1000 AND titleType = 'movie'

Die Ausführung dieser Abfrage liefert ebenfalls ein einziges Ergebnis! Es sieht so aus, als müssten wir uns nicht mehr entscheiden, was wir uns ansehen wollen, da es nur eine Option gibt, die unseren Kriterien entspricht!

Suche nach allen Filmen eines bestimmten Regisseurs

Lassen Sie uns ein anderes Szenario durchspielen. Was, wenn wir alle Filme sehen wollen, bei denen Steven Spielberg Regie geführt hat? Wie würde das funktionieren?

Wenn wir uns die Tabellen ansehen, können wir Folgendes feststellen:

• name_basics: Sie enthält die Namen aller Schauspieler, Autoren, Regisseure und anderer Personen, die an der Erstellung von Film- und Fernsehtiteln beteiligt waren.
• title_crew: Sie dient als Verknüpfungstabelle für Titel, Regisseure und Autoren. Wir werden diese Tabelle verwenden, um Steven Spielberg mit den Titeln zu verbinden, an denen er beteiligt ist.
• title_basics: Wir haben diese Tabelle bereits verwendet. Sie enthält Titelinformationen wie Name, Erscheinungsdatum, Bewertung usw.

Machen wir uns an die Arbeit! Schreiben wir eine Abfrage für die Tabelle name_basics, um zu versuchen, den berühmten Regisseur Steven Spielberg zu finden.

SELECT nconst, primaryName, birthYear, deathYear, primaryProfession, knownForTitles
FROM name_basics
WHERE primaryName LIKE 'steven spielberg'

Die Ausführung dieser Abfrage führt zu einem einzigen Ergebnis:

Dies gibt uns den wichtigen Wert nconst. Aus der Dokumentation wissen wir, dass nconst der alphanumerische eindeutige Bezeichner des Namens/der Person ist.

Diesen Wert können wir in die Tabelle title_crew füttern, die die Informationen zu Regisseur und Autor für alle Titel in IMDb enthält, und Steven Spielberg allen Titeln zuordnen, an denen er beteiligt war.

    SELECT * from title_crew where directors LIKE 'nm0000229'
		

Die Ausführung dieser Abfrage führt zu einer Liste von 45 Titeln. Aus dem Wert der Spalte Regisseure können Sie ersehen, dass Steven Spielberg der Regisseur von allen war.

Wir brauchen eine Möglichkeit, diese Liste von Titeln zusammen mit der title_basics Tabelle zu verwenden, um den Namen der Filme zu erhalten, anstatt nur den tconst. Verwenden wir dafür eine Subquery!

SELECT titleType, primaryTitle, startYear, genres
FROM title_basics
WHERE titleType LIKE 'movie'
AND tconst IN
(SELECT tconst FROM title_crew WHERE directors LIKE 'nm0000229')

Führen Sie diese Abfrage aus, um das Ergebnis zu sehen:

Da haben wir sie, alle Steven Spielberg-Filmtitel aus unserer Datenbank!

Hören Sie hier nicht auf! Schreiben Sie Ihre eigenen benutzerdefinierten Abfragen, um weitere Erkenntnisse aus diesem großen Datensatz zu gewinnen. Es gibt viele Möglichkeiten, SQL zu üben. Wenn Sie genug von der Arbeit mit diesem Datensatz haben, finden Sie in diesem Beitrag über 12 Möglichkeiten, SQL online zu lernen, weitere hervorragende Lernressourcen.

Anwendung von SQL auf eine große bestehende Filmdatenbank

Sie haben gelernt, wie man große bestehende Datensätze in ein DBMS Ihrer Wahl importiert und analysiert und wie man SQL zur Analyse einer Filmdatenbank verwendet. Dies ist ein mächtiges Werkzeug in Ihrem SQL-Arsenal. Ganz zu schweigen davon, dass Sie sich nie wieder Sorgen machen müssen, dass Sie keinen Film auswählen können! Das Ausfüllen von SQL-Übungen zu Filmdatenbanken ist ein hilfreicher Weg, um zu lernen, aber wenn Sie mehr Struktur wünschen, sehen Sie sich diese SQL-Übungssatz von LearnSQL.com an.