Utiliser SQL sur une base de données de films pour décider de ce qu'il faut regarder

Nous allons montrer comment utiliser SQL pour analyser de grands ensembles de données et obtenir des informations précieuses, dans ce cas, pour vous aider à choisir quel film regarder ensuite en utilisant un ensemble de données IMDb.

Dans cet article, nous allons télécharger un répertoire de jeux de données à partir d'IMDb. Vous ne savez pas quoi regarder ce soir ? Vous parcourez Netflix sans fin ? Décidez de ce que vous allez regarder en utilisant la puissance de SQL ! Nous allons charger un ensemble de données IMDb de films dans SQL. Nous analyserons les données de différentes manières, par exemple en triant les films en fonction de leur classement, des acteurs qui jouent dans le film ou d'autres critères similaires.

Comme indiqué dans cet article de blog sur la pratique de SQL, la meilleure façon de pratiquer SQL est d'acquérir une expérience pratique en résolvant des problèmes réels, ce qui est exactement ce que nous allons faire.

Si vous avez une connaissance de base de SQL, vous devriez être en mesure de suivre cet article facilement. Si vous n'avez aucune expérience en informatique, envisagez de commencer par ce parcours d'apprentissage SQL de A à Z, conçu pour les personnes sans expérience en informatique qui souhaitent se lancer dans l'aventure avec SQL.

Commençons par apprendre comment transférer les données des films dans notre base de données SQL.

Terminer le téléchargement de la base de données de films SQL

Nous allons suivre pas à pas le processus de téléchargement de nos données et de leur chargement dans un système de gestion de base de données (SGBD). Les SGBD les plus courants sont MySQL, Oracle DB, PostgreSQL et SQL Server.

Bien que cet article porte sur les données de films, vous pouvez choisir un ensemble de données totalement différent. Consultez cette liste de jeux de données en ligne gratuits que vous pouvez utiliser et trouvez celui qui vous intéresse. L'importation de ces jeux de données sera similaire, quel que soit le jeu de données que vous utilisez.

Ouvrez la variété de SQL que vous utilisez. Pour cet exemple, je vais utiliser SQL Server Management Studio, mais les étapes devraient être similaires pour toutes les autres variétés de SQL. Commençons :

1. Les fichiers de l'ensemble de données sont accessibles et téléchargeables sur https://datasets.imdbws.com/. Les données sont rafraîchies quotidiennement.
2. Téléchargez tous les fichiers listés :
   1. basics.tsv.gz
   2. akas.tsv.gz
   3. basics.tsv.gz
   4. crew.tsv.gz
   5. episode.tsv.gz
   6. principals.tsv.gz
   7. ratings.tsv.gz
3. Extrayez les fichiers zip téléchargés. Le résultat final sera un fichier TSV (séparé par des tabulations) pour chaque tableau.
4. Nettoyons ces données et convertissons-les en CSV pour qu'elles soient plus exploitables :
   1. Ouvrez chaque fichier dans une application de feuille de calcul comme Google Sheets ou Microsoft Excel.
   2. Trouvez et remplacez toutes les occurrences de "\N" par une cellule vide.
   3. Enregistrez le fichier en tant que fichier CSV. Il sera ainsi plus facile à importer dans le SGBD de votre choix.
5. Ouvrez votre SGBD.
6. Créez un nouveau schéma ou une nouvelle table en cliquant avec le bouton droit de la souris dans le volet de gauche et en sélectionnant "New Database" (nouvelle base de données).
7. Faites un clic droit sur la base de données → Tâches → Importer un fichier plat et suivez l'assistant d'importation pour créer une table pour chaque fichier :
   1. Définissez des types de données valides pour chaque colonne que vous importez. Je vous recommande d'utiliser nvarchar(MAX) pour les colonnes de chaînes de caractères, car vous ne savez pas quelle sera la longueur des chaînes de caractères pour chaque champ. Vous pouvez modifier le type de données de la colonne ultérieurement si nécessaire.
   2. Autorisez les valeurs nulles pour toutes les lignes. Cela évitera les problèmes lors de l'importation.
8. Répétez ce processus pour chacun des fichiers que vous avez téléchargés.

Après avoir terminé ces étapes, votre base de données de films SQL sera en place ! Vous êtes maintenant prêt à commencer l'analyse et l'interrogation des données.

Exercices SQL sur une base de données de films

Heureusement, cet ensemble de données est accompagné d'une documentation descriptive. Pour vous faire une idée encore plus précise des données, vous pouvez sélectionner rapidement les 1000 premières lignes de chaque table.

Commençons à chercher notre premier film. Imaginez que vous voulez regarder un film d'horreur. Comment pouvons-nous isoler uniquement les films d'horreur ? Heureusement, cette tâche est d'une simplicité déconcertante.

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

Si cette requête prête à confusion, ouvrez cette antisèche SQL pour rafraîchir vos connaissances. Gardez cette aide-mémoire ouverte pendant le reste du tutoriel pour vous aider !

Et si nous voulions affiner davantage cette liste de films d'horreur ? Nous pourrions restreindre les résultats aux films d'horreur créés après 1990, avec une note moyenne supérieure à 9,0 et au moins 10 000 votes.

Pour ce faire, nous devrons obtenir des données de plusieurs tableaux. En ouvrant chaque tableau et en jetant un coup d'œil aux en-têtes de colonne, nous pouvons voir que les tableaux suivants seront impliqués :

• title_basics: gère le genre du film et l'année de sortie (représentée par la colonne startYear).
• title_ratings: gère le classement (averageRating) et les votes (numVotes).

Les deux tables peuvent être jointes sur la colonne partagée, tconst. Comme expliqué dans la documentation IMDb ici, tconst est un identifiant unique alphanumérique du titre. Écrivons notre requête :

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

L'exécution de cette requête renvoie un seul résultat, mais pas celui que nous voulons ! En regardant de plus près, nous pouvons voir que ce titre est un jeu vidéo, et non un film. Modifions notre requête pour n'inclure que les films, et élargissons la recherche en réduisant le nombre minimum de votes requis à 1 000 et la note minimale requise à 8.0.

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'

L'exécution de cette requête donne également un seul résultat ! On dirait que nous n'aurons plus à décider quoi regarder, puisqu'il n'y a qu'une seule option qui correspond à nos critères !

Recherche de tous les films d'un réalisateur donné

Passons à un autre scénario. Et si nous voulions voir tous les films que Steven Spielberg a réalisés ? Comment cela fonctionne-t-il ?

En consultant les tableaux, nous pouvons déterminer ce qui suit :

• name_basics: Elle contient les noms de tous les acteurs, scénaristes, réalisateurs et autres personnes impliquées dans la création des titres de films et de télévision.
• title_crew: Elle sert de table de liaison entre les titres, les réalisateurs et les auteurs. Nous allons utiliser cette table pour relier Steven Spielberg aux titres auxquels il a participé.
• title_basics: Nous avons déjà utilisé cette table. Elle contient des informations sur les titres, comme le nom, la date de sortie, le classement, etc.

Mettons-nous au travail ! Écrivons une requête pour la table name_basics afin d'essayer de trouver le célèbre réalisateur Steven Spielberg.

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

L'exécution de cette requête donne un seul résultat :

Cela nous donne la valeur importante de nconst. D'après la documentation, nous savons que nconst est l'identifiant alphanumérique unique du nom/de la personne.

Nous pouvons introduire cette valeur dans la table title_crew qui contient les informations sur le réalisateur et le scénariste de tous les titres d'IMDb, et associer Steven Spielberg à tous les titres auxquels il a participé.

    SELECT * from title_crew where directors LIKE 'nm0000229'
		

En exécutant cette requête, on obtient une liste de 45 titres. Vous pouvez voir, d'après la valeur de la colonne Directors, que Steven Spielberg était le réalisateur de tous ces films.

Nous avons besoin d'un moyen d'utiliser cette liste de titres avec la table title_basics pour obtenir le nom des films au lieu du simple tconst. Utilisons une sous-requête pour cela !

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

Exécutez cette requête pour voir le résultat :

Voilà, tous les titres de films de Steven Spielberg de notre base de données !

Ne vous arrêtez pas là ! Rédigez vos propres requêtes personnalisées pour extraire davantage d'informations de ce grand ensemble de données. Il existe de nombreuses façons de pratiquer le SQL. Si vous en avez assez de travailler avec cet ensemble de données, consultez cet article sur les 12 façons d'apprendre SQL en ligne pour trouver d'autres excellentes ressources d'apprentissage.

Utilisation de SQL sur une grande base de données de films existante

Vous avez appris à importer et à analyser de grands ensembles de données existants dans le SGBD de votre choix et à utiliser SQL pour analyser une base de données de films. Il s'agit d'un outil puissant dans votre arsenal SQL. Sans compter que vous n'aurez plus jamais à vous soucier de ne pas pouvoir choisir un film à regarder ! Faire des exercices SQL sur des bases de données de films est un moyen utile d'apprendre, mais si vous souhaitez plus de structure, consultez le site Exercices Pratiques de SQL de LearnSQL.com.