Usare SQL su un database di film per decidere cosa guardare

Dimostreremo come utilizzare SQL per analizzare grandi insiemi di dati e ottenere informazioni preziose, in questo caso, per aiutarvi a scegliere quale film guardare successivamente utilizzando un insieme di dati di IMDb.

In questo articolo, scaricheremo un insieme di dati da IMDb. Non sapete cosa guardare stasera? State sfogliando Netflix all'infinito? Decidete cosa guardare usando la potenza di SQL! Caricheremo un set di dati IMDb esistente in SQL. Analizzeremo i dati in diversi modi, ad esempio ordinando i film in base alla loro valutazione, agli attori protagonisti o ad altri criteri simili.

Come già detto in questo post su come esercitarsi con l'SQL, il modo migliore per esercitarsi con l'SQL è fare esperienza pratica nella risoluzione di problemi del mondo reale, che è esattamente quello che faremo.

Se avete una conoscenza di base di SQL, dovreste essere in grado di seguire facilmente questo articolo. Se non avete alcuna esperienza nel campo dell'informatica, considerate l'opportunità di iniziare con questo percorso di apprendimento SQL dalla A alla Z, pensato per chi non ha esperienza nel campo dell'informatica e vuole iniziare la sua avventura con SQL.

Cominciamo con l'imparare a inserire i dati dei film nel nostro database SQL.

Completare il download del database SQL dei film

Vediamo passo dopo passo come scaricare i dati e caricarli in un sistema di gestione di database (DBMS). I DBMS più comuni sono MySQL, Oracle DB, PostgreSQL e SQL Server.

Sebbene questo articolo si concentri sui dati dei film, è possibile scegliere un set di dati completamente diverso. Consultate questo elenco di set di dati online gratuiti che potete utilizzare e trovate quello che vi interessa. L'importazione di questi set di dati sarà simile indipendentemente dal set di dati utilizzato.

Aprire qualsiasi tipo di SQL si stia utilizzando. Per questo esempio, utilizzerò SQL Server Management Studio, ma i passaggi dovrebbero essere simili per tutte le altre varietà di SQL. Iniziamo:

1. I file del dataset sono accessibili e scaricabili dal sito https://datasets.imdbws.com/. I dati vengono aggiornati quotidianamente.
2. Scaricate tutti i file elencati:
   1. basics.tsv.gz
   2. akas.tsv.gz
   3. basics.tsv.gz
   4. equipaggio.tsv.gz
   5. episodio.tsv.gz
   6. presidi.tsv.gz
   7. rating.tsv.gz
3. Estrarre i file zip scaricati. Il risultato finale sarà un file TSV (separato da tabulazioni) per ogni tabella.
4. Puliamo questi dati e convertiamoli in CSV in modo da renderli più funzionali:
   1. Aprire ciascun file in un foglio di calcolo come Google Sheets o Microsoft Excel.
   2. Trovare e sostituire tutte le occorrenze di "\N" con una cella vuota.
   3. Salvare il file come file CSV. In questo modo sarà più facile importarlo nel DBMS di vostra scelta.
5. Aprire il DBMS.
6. Creare un nuovo schema o una nuova tabella facendo clic con il pulsante destro del mouse sul riquadro di sinistra e selezionando "Nuovo database".
7. Fare clic con il tasto destro del mouse sul database → Attività → Importa file piatto e seguire la procedura guidata di importazione per creare una tabella per ogni file:
   1. Impostare i tipi di dati validi per ogni colonna da importare. Consiglio di usare nvarchar(MAX) per le colonne di stringhe, poiché non si sa quanto saranno lunghe le stringhe per ogni campo. È possibile modificare il tipo di dati della colonna in un secondo momento, se necessario.
   2. Consentire i valori nulli per tutte le righe. Questo eviterà problemi con l'importazione.
8. Ripetete questa procedura per tutti i file scaricati.

Dopo aver completato questi passaggi, il database dei filmati SQL sarà pronto! Ora siete pronti per iniziare ad analizzare e interrogare i dati.

Esercizi SQL su un database di film

Fortunatamente, questo set di dati è stato fornito con una documentazione descrittiva. Per avere un'idea ancora più precisa dei dati, è possibile selezionare rapidamente le prime 1000 righe di ogni tabella.

Cominciamo a cercare il nostro primo film. Immaginiamo di voler guardare un film horror. Come possiamo isolare solo i film horror? Fortunatamente, questo compito è spaventosamente semplice.

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

Se questa query genera confusione, aprite questo foglio informativo SQL per rinfrescare le vostre conoscenze. Tenete aperto questo foglio informativo per il resto del tutorial per aiutarvi!

E se volessimo affinare ulteriormente questo elenco di film horror? Potremmo limitare i risultati ai film horror creati dopo il 1990, con una valutazione media superiore a 9,0 e almeno 10.000 voti.

A tal fine è necessario ottenere i dati da più tabelle. Aprendo ogni tabella e dando un'occhiata alle intestazioni delle colonne, possiamo vedere che saranno coinvolte le seguenti tabelle:

• title_basics: gestisce il genere del film e l'anno di uscita (rappresentato dalla colonna startYear).
• title_ratings: gestisce la valutazione (averageRating) e i voti (numVotes).

Le due tabelle possono essere unite sulla colonna condivisa, tconst. Come spiegato nella documentazione di IMDb qui, tconst è un identificatore unico alfanumerico del titolo. Scriviamo la nostra query:

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'esecuzione di questa query restituisce un unico risultato, ma non quello desiderato! A un esame più attento, possiamo vedere che questo titolo è un videogioco, non un film. Modifichiamo la nostra query per includere solo i film ed espandiamo la ricerca riducendo il numero minimo di voti richiesti a 1.000 e la valutazione minima richiesta a 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'

Anche l'esecuzione di questa query produce un unico risultato! Sembra che non dovremo più decidere cosa guardare, perché c'è solo un'opzione che soddisfa i nostri criteri!

Trovare tutti i film di un determinato regista

Esaminiamo un altro scenario. E se volessimo vedere tutti i film diretti da Steven Spielberg? Come funzionerebbe?

Consultando le tabelle, possiamo determinare quanto segue:

• name_basics: Contiene i nomi di tutti gli attori, scrittori, registi e altri soggetti coinvolti nella creazione di titoli cinematografici e televisivi.
• title_crew: Funge da tabella di collegamento tra titoli, registi e scrittori. Useremo questa tabella per collegare Steven Spielberg ai titoli in cui è coinvolto.
• title_basics: Abbiamo già utilizzato questa tabella. Contiene informazioni sul titolo, come il nome, la data di uscita, la valutazione, ecc.

Mettiamoci al lavoro! Scriviamo una query per la tabella name_basics per cercare di trovare il famoso regista Steven Spielberg.

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

L'esecuzione di questa query produce un unico risultato:

Questo ci dà l'importante valore di nconst. Dalla documentazione, sappiamo che nconst è l'identificatore univoco alfanumerico del nome/persona.

Possiamo inserire questo valore nella tabella title_crew che contiene le informazioni sul regista e sullo sceneggiatore per tutti i titoli presenti in IMDb, e associare Steven Spielberg a tutti i titoli in cui è coinvolto.

    SELECT * from title_crew where directors LIKE 'nm0000229'
		

Eseguendo questa query si ottiene un elenco di 45 titoli. Dal valore della colonna Registi si può notare che Steven Spielberg era il regista di tutti i titoli.

Abbiamo bisogno di un modo per utilizzare questo elenco di titoli insieme alla tabella title_basics per ottenere il nome dei film invece del solo tconst. Utilizziamo una subquery per questo!

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

Eseguire questa query per vedere il risultato:

Ecco tutti i titoli dei film di Steven Spielberg presenti nel nostro database!

Non si fermi qui! Scrivete le vostre query personalizzate per estrarre ulteriori informazioni da questo grande insieme di dati. Ci sono molti modi per esercitarsi con l'SQL. Se pensate di averne abbastanza di lavorare con questo set di dati, date un'occhiata a questo post sui 12 modi per imparare l'SQL online per trovare altre eccellenti risorse di apprendimento.

Usare l'SQL su un grande database di film esistente

Avete imparato a importare e analizzare grandi insiemi di dati esistenti nel DBMS di vostra scelta e a usare SQL per analizzare un database di film. Si tratta di uno strumento potente nel vostro arsenale SQL. Inoltre, non dovrete più preoccuparvi di non poter scegliere un film da guardare! Completare esercizi SQL su database di film è un modo utile per imparare, ma se volete una struttura più articolata, date un'occhiata a questo SQL Practice Set di LearnSQL.com.