Utilizar SQL en una base de datos de películas para decidir qué ver

Demostraremos cómo utilizar SQL para analizar grandes conjuntos de datos y obtener información valiosa, en este caso, para ayudarle a elegir qué película ver a continuación utilizando un conjunto de datos de IMDb.

En este artículo, descargaremos un directorio de conjuntos de datos de IMDb. ¿No estás seguro de qué ver esta noche? ¿Estás navegando por Netflix sin parar? Decide qué ver utilizando el poder de SQL. Cargaremos un conjunto de datos de películas de IMDb en SQL. Analizaremos los datos de diferentes maneras, como la clasificación de las películas por su calificación, por los actores que protagonizan la película o por otros criterios similares.

Como se mencionó en esta entrada del blog sobre cómo practicar SQL, la mejor manera de practicar SQL es adquiriendo experiencia práctica en la resolución de problemas del mundo real, que es exactamente lo que vamos a hacer.

Si tienes un conocimiento básico de SQL, deberías ser capaz de seguir este artículo fácilmente. Si no tienes ningún tipo de experiencia en TI, considera comenzar con este Curso de Aprendizaje de SQL de la A a la Z, diseñado para personas que no tienen experiencia en TI y quieren comenzar su aventura con SQL.

Empecemos aprendiendo a introducir los datos de la película en nuestra base de datos SQL.

Completando la descarga de la base de datos de películas SQL

Vamos a recorrer el proceso de descarga de nuestros datos y su carga en un sistema de gestión de bases de datos (DBMS), paso a paso. Los DBMS más comunes son MySQL, Oracle DB, PostgreSQL y SQL Server.

Aunque este artículo se centra en los datos de las películas, puedes elegir un conjunto de datos totalmente diferente. Consulta esta lista de conjuntos de datos gratuitos en línea que puedes utilizar y encuentra el que te interesa. La importación de estos conjuntos de datos será similar independientemente del conjunto de datos que utilice.

Abra cualquier variedad de SQL que esté utilizando. Para este ejemplo, utilizaré SQL Server Management Studio, pero los pasos deberían ser similares para todas las demás variedades de SQL que existen. Comencemos:

1. Los archivos del conjunto de datos se pueden acceder y descargar desde https://datasets.imdbws.com/. Los datos se actualizan diariamente.
2. Descarga todos los archivos de la lista:
   1. basics.tsv.gz
   2. akas.tsv.gz
   3. basics.tsv.gz
   4. tripulación.tsv.gz
   5. episodio.tsv.gz
   6. directores.tsv.gz
   7. ratings.tsv.gz
3. Extraiga los archivos zip descargados. El resultado final será un archivo TSV (separado por tabulaciones) para cada tabla.
4. Limpiemos estos datos y convirtámoslos a CSV para que estén en un estado más factible:
   1. Abra cada archivo en una aplicación de hoja de cálculo como Google Sheets o Microsoft Excel.
   2. Busque y sustituya todas las apariciones de "\N" por una celda vacía.
   3. Guarde el archivo como un archivo CSV. Esto facilitará la importación en el SGBD de su elección.
5. Abra su SGBD.
6. Cree un nuevo esquema o tabla haciendo clic con el botón derecho del ratón en el panel izquierdo y seleccionando "Nueva base de datos". He llamado a mi nueva base de datos "imdb".
7. Haga clic con el botón derecho del ratón en la base de datos → Tareas → Importar archivo plano y siga el asistente de importación para crear una tabla para cada archivo:
   1. Establezca tipos de datos válidos para cada columna que vaya a importar. Recomiendo utilizar nvarchar(MAX) para las columnas de cadena, ya que no sabes qué longitud tendrán las cadenas de cada campo. Puede cambiar el tipo de datos de la columna más tarde si es necesario.
   2. Permitir valores nulos para todas las filas. Esto evitará problemas con la importación.
8. Repita este proceso para cada uno de los archivos que haya descargado.

Una vez completados estos pasos, su base de datos de películas SQL estará lista. Ahora está listo para comenzar a analizar y consultar los datos.

Ejercicios SQL en una base de datos de películas

Afortunadamente, este conjunto de datos viene con cierta documentación descriptiva. Para tener una mejor idea de los datos, puede seleccionar rápidamente las 1000 filas principales de cada tabla.

Empecemos a buscar nuestra primera película. Imagina que quieres ver una película de terror. ¿Cómo podemos aislar sólo las películas de terror? Afortunadamente, esta tarea es terriblemente sencilla.

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

Si esta consulta le causa alguna confusión, abra esta hoja de trucos SQL para refrescar sus conocimientos. Ten esta hoja de trucos abierta durante el resto del tutorial para ayudarte.

¿Y si quisiéramos refinar aún más esta lista de películas de terror? Podríamos restringir los resultados a las películas de terror creadas después de 1990, con una calificación media superior a 9,0 y al menos 10.000 votos.

Esto implicará obtener datos de varias tablas. Abriendo cada tabla y echando un vistazo a las cabeceras de las columnas, podemos ver que se tratará de las siguientes tablas:

• title_basics: maneja el género de la película y el año de estreno (representado por la columna startYear).
• title_ratingsEl sistema de gestión de la información: maneja la calificación (averageRating) y los votos (numVotes).

Las dos tablas se pueden unir en la columna compartida, tconst. Como se explica en la documentación de IMDb aquí, tconst es un identificador alfanumérico único del título. Escribamos nuestra consulta:

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

La ejecución de esta consulta devuelve un único resultado, ¡pero no el que queremos! Si miramos más de cerca, podemos ver que este título es un videojuego, no una película. Modifiquemos la consulta para incluir sólo películas y ampliemos la búsqueda reduciendo el número mínimo de votos a 1.000 y la puntuación mínima 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'

La ejecución de esta consulta también arroja un único resultado. Parece que ya no tendremos que decidir qué ver, ya que sólo hay una opción que se ajusta a nuestros criterios.

Encontrar todas las películas de un determinado director

Veamos otro escenario. ¿Qué pasa si queremos ver todas las películas que ha dirigido Steven Spielberg? ¿Cómo funcionaría esto?

Mirando las tablas, podemos determinar lo siguiente:

• name_basics: Contiene los nombres de todos los actores, guionistas, directores y demás implicados en la creación de los títulos de cine y televisión.
• title_crew: Actúa como una tabla de enlace para los títulos, directores y escritores. Utilizaremos esta tabla para relacionar a Steven Spielberg con los títulos en los que ha participado.
• title_basics: Ya hemos utilizado esta tabla. Contiene información sobre el título, como el nombre, la fecha de estreno, la calificación, etc.

¡A trabajar! Escribamos una consulta para la tabla name_basics para intentar encontrar al famoso director Steven Spielberg.

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

La ejecución de esta consulta arroja un único resultado:

Esto nos da el importante valor de nconst. Por la documentación, sabemos que nconst es el identificador alfanumérico único del nombre/persona.

Podemos introducir este valor en la tabla title_crew que contiene la información sobre el director y el guionista de todos los títulos de IMDb, y relacionar a Steven Spielberg con todos los títulos en los que participa.

    SELECT * from title_crew where directors LIKE 'nm0000229'
		

Al ejecutar esta consulta se obtiene una lista de 45 títulos. Por el valor de la columna de directores se puede ver que Steven Spielberg fue el director de todos ellos.

Necesitamos una forma de utilizar esta lista de títulos junto con la tabla title_basics para obtener el nombre de las películas en lugar de sólo el tconst. Utilicemos una subconsulta para ello.

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

Ejecute esta consulta para ver el resultado:

Ahí lo tenemos, ¡todos los títulos de películas de Steven Spielberg de nuestra base de datos!

¡No se detenga aquí! Escribe tus propias consultas personalizadas para extraer más información de este gran conjunto de datos. Hay muchas maneras de practicar SQL. Si crees que ya has tenido suficiente con trabajar con este conjunto de datos, echa un vistazo a este post sobre 12 formas de aprender SQL en línea para encontrar más recursos de aprendizaje excelentes.

Uso de SQL en una gran base de datos de películas existente

Ha aprendido a importar y analizar grandes conjuntos de datos existentes en el DBMS de su elección y a utilizar SQL para analizar una base de datos de películas. Esta es una poderosa herramienta en su arsenal de SQL. Por no mencionar que nunca más tendrás que preocuparte por no poder elegir una película para ver. Completar ejercicios de SQL sobre bases de datos de películas es una forma útil de aprender, pero si quieres más estructura, echa un vistazo a este Ejercicios prácticos de SQL de LearnSQL.com.