Introducción al lenguaje SQL: Obtención de información relevante de la base de datos de Netflix
- Antonio Romero Camacho
- Apr 10, 2021
- 8 min read
Introducción al lenguaje SQL
SQL (Structured Query Language) es el lenguaje de programación diseñado específicamente para el acceso a Sistemas de Gestión de Bases de Datos Relacionales. Como la mayor parte de los sistemas actuales son de este tipo, y como el lenguaje SQL es el más ampliamente usado en éstos, se puede decir que este lenguaje es el empleado mayoritariamente en los sistemas existentes hoy en día.
Este lenguaje se emplea en sistemas que van desde ordenadores personales muy básicos con apenas 64MB de espacio en memoria central hasta los más potentes microprocesadores y multicomputadores.
Las principales ventajas que aporta SQL son dos:
Su enorme difusión, pues es empleado en la gran mayoría de los sistemas actuales
Su elevada expresividad. Por ejemplo, operaciones que costarían semanas de duro esfuerzo en ser desarrolladas en un lenguaje de programación tradicional pueden ser realizadas con SQL en tan sólo unos minutos y unas líneas de código.
Motivación del post
En este post presentamos cómo utilizar SQL para extraer información interesante sobre las películas y series que están en la plataforma Netflix. Esta dataset es de libre acceso y podéis descargarlo de la plataforma Kaggle.
Para el trabajo a realizar con la base de datos vamos a utilizar DBeaver. En esta herramienta de gestión de bases de datos cargaremos el dataset y podremos visualizar la tabla y lanzar preguntas a la base de datos a través de un script.
La tabla tiene las siguientes columnas:
show_id: identificador único para cada película / serie
type: indica si el registro es una película (movie) o serie (TV Show)
title: título de la película o serie
director: director de la película o serie
cast: lista de los actores y actrices que forman parte de la producción
country: país donde se produjo
date_added: fecha en la que se añadió a Netflix
release_year: año en el que se lanzó la película / serie
rating: TV rating de la película o serie (por ejemplo: R, TV-PG, PG_13)
duration: duración en minutos de la película y número de temporadas para las series
listed_in: lista de géneros en los que se podría incluir la serie o película
description: descripción sobre la serie o película
Todas las columnas contienen datos de tipo VARCHAR que son cadenas de caracteres. El único campo definido como número entero es el año de lanzamiento.
A continuación vamos a realizar distintas consultas a la base de datos que acabamos de presentar. Ya veréis lo sencillo e intuitivo que es obtener insights de una base de datos con lenguaje SQL.
1. ¿Qué películas se lanzaron en el año 2020?
Para formular la "query" tan sólo nos hacen falta tres líneas:
SELECT *
FROM netflix_titles
WHERE release_year = 2020
Lo que le estamos diciendo al Sistema Gestor de Bases de Datos (SGBD) es que:
a) Seleccione todas las columnas (*) o campos de las filas o registros de
b) La tabla llamada netflix_titles
c) Donde se cumpla la condición de que el año de lanzamiento (release_year) sea
igual a 2018
Los resultados obtenidos tras ejecutar la consulta se muestran en la Figura 3. La imagen es una captura de algunos de los registros que hemos obtenido como respuesta a la pregunta. Por ejemplo, una de las series resultado es The House of Flowers (con show_id s6465).
2. ¿Cuál es el título y el año de lanzamiento de la película más antigua de la plataforma Netflix?
En este caso necesitamos saber sólo el nombre y el año de lanzamiento de la película más antigua en nuestra base de datos. Para ello lanzaremos la siguiente consulta:
SELECT title AS titulo, release_year AS año_lanzamiento
FROM netflix_titles
ORDER BY año_lanzamiento ASC
En este caso lo que le estamos diciendo al SGBD es:
a) Selecciona el campo title al que llamaremos título y el campo release_year al que
llamaremos año_lanzamiento para todos los registros
b) De la tabla netflix_titles
c) Ordenados por año_lanzamiento de manera ascendente (ASC)
Al ejecutar la consulta obtendríamos todos los registros de la base de datos ordenados de menor a mayor, desde 1925 hasta 2021.
Sin embargo, estábamos interesados sólo en la película más antigua. Para ello añadiremos la cláusula LIMIT en la consulta anterior de la siguiente forma:
SELECT title AS titulo, release_year AS año_lanzamiento
FROM netflix_titles
ORDER BY año_lanzamiento ASC
LIMIT 1
Como sólo queremos mostrar el primer valor fijamos el límite a 1. Como aclaración, si hubiéramos querido mostrar los tres registros más antiguos hubiéramos escrito LIMIT 3. El resultado de la consulta final se muestra en la Figura 5.
Por tanto, podemos concluir que el documento gráfico más antiguo de la BBDD de Netflix es Pioneers: First Women Filmmakers que fue lanzado en el año1925.
3. ¿Cuál es el número medio de temporadas de las series disponibles en la plataforma Netflix?
Si observamos las columnas de la base de datos, vemos que en el campo duración (duration) de las series (TV Shows) se expresa en temporadas. Por ello sólo nos interesaran los registros del tipo serie.
En esta consulta será necesario recurrir a las funciones de agregado, más concretamente a la función de agregado media (en inglés average, AVG). La query se formularía de la siguiente manera:
SELECT AVG(duration) AS media_temporadas_serie
FROM netflix_titles
WHERE type = 'TV Show'
,
¿Cómo se leería la consulta que estamos a punto de lanzar?. La orden que le estamos mandando al gestor de bases de datos podría desgranarse en las siguientes sentencias:
a) Selecciona la media del campo duración (duration) renombrándolo como
media_temporadas_serie
b) De lo registros presentes en la tabla netflix_titles
c) Que satisfacen la condición de ser series, es decir, cuyo campo type es igual a TV Show
El resultado será un único valor igual al valor medio de las temporadas de los registros tipificados en la base de datos como series (Figura 7).
De acuerdo con el resultado, una serie de Netflix tiene de media 1.76 temporadas (entre 1 y 2 temporadas).
4. Considerando los registros de la base de datos que se han producido sólo en España o sólo en Reino Unido, ¿Cuántos de ellos son películas y cuántos series?
Como vais viendo el nivel de dificultad de las consultas va en aumento pero a la vez la información que vamos sacando es más interesante y nos permite sacar más conclusiones. Para realizar esta consulta seguiremos recurriendo a las funciones de agregado. En este caso necesitaremos la función COUNT que realiza el conteo de los registros que presenta una determinada columna. También necesitaremos introducir una nueva cláusula, la GROUP BY que se encarga de agrupar los registros. En este caso, agruparemos los registros por país para separar los que han sido producidos en España o en Reino Unido, y a continuación en cada grupo subdividiremos los resultados en subgrupos de películas y series.
Como curiosidad, la función de agregado de conteo COUNT se puede lanzar de dos maneras diferentes, que pueden proporcionar resultados diferentes si hay valores NULL en la BBDD:
COUNT(*): devuelve el conteo de todos los registros que resultan de la consulta SQL incluyendo los NULL
COUNT(nombre_columna): devuelve el conteo de todos los registros que resultan de la consulta SQL excluyendo los que tengan valores NULL en la columna seleccionada
La consulta para obtener los resultados que estamos buscando se formula como se muestra a continuación:
SELECT country, type, COUNT(title) AS conteo
FROM netflix_titles
WHERE country IN ('Spain', 'United Kingdom')
GROUP BY country, type
ORDER BY conteo DESC
La traducción de la consulta formulada sería la siguiente:
a) Selecciona los campos país (country), tipo de documento (type) y cuenta el número de
títulos no nulos (al que denominarás conteo)
b) De los registros presentes en la tabla netflix_titles
c) Cuyo país tome alguno de los valores mencionados entre paréntesis (España o Reino
Unido)
d) Y agrúpalos (GROUP BY) por país donde se visualizan y por tipo de documento
e) Por último, ordenalos (ORDER BY) en orden descendente (DESC) según los valores del
campo conteo
La tabla de resultados en este caso se muestra en la figura 8. Vemos que los datos están agrupados por país (primero Reino Unido porque presenta el máximo absoluto de valores de conteo) y luego por tipo de documento (serie o película) en orden descendente.
De esta forma tan visual podríamos extraer conclusiones como que:
El número de contenidos producidos en Reino Unido es mayor que el producido en España
En Reino Unido se han producido más series que películas. Sin embargo el conteo de películas y series es bastante cercano
En España, el número de películas producidas sólo en nuestro país prácticamente dobla al número de series.
El siguiente paso sería exportar estos datos y hacer unos gráficos para reportar los resultados a negocio. Para exportar la tabla resultado, seleccionamos la query, hacemos click derecho sobre la selección y pinchamos en ejecutar (Execute) y a continuación en exportar desde consulta (Export From Query).
A continuación seleccionaremos el formato en el que queremos la información (tipo de archivo, delimitados, nombre del archivo, etc). En nuestro caso hemos seleccionado exportar a archivo CSV y se le ha asignado el nombre Consulta4_netflix.
Una vez exportado el archivo ya podemos abrir el CSV en Excel o Python sin problema. Nosotros vamos a leer los resultados para llevar a cabo una visualización sencilla. De esta forma mostramos todo el proceso desde la consulta hasta la generación del resultado final que proporcionaremos a negocio.
La visualización la hemos generado con la biblioteca Plotly. La idea es representar la información en dos gráficos tipo pie chart, uno por país. A continuación se muestra el código para generarlo.
Se puede generar un HTML para poder interacciones con el gráfico y obtener información adicional al posicionar el ratón en las distintas zonas del donut. Para ello habría que añadir la siguiente línea de código: plotly.offline.plot(fig, filename='graph.html').
5.¿Cuál ha sido la evolución a lo largo de los años del número de películas o series subidas a la plataforma?
En este caso es necesario agrupar los registros por años y realizar u conteo del número de registros por año. Sería conveniente ordenar los resultados en orden creciente para tenerlos organizados cronológicamente para cuando éstos sean representados gráficamente. La consulta a realizar quedaría de la siguiente forma:
SELECT SUBSTRING(date_added, -4,4) AS year_added, COUNT(title) AS num_pel_añadidas
FROM netflix_titles
GROUP BY year_added
ORDER BY year_added ASC
Hemos añadido un elemento nuevo en la cláusula SELECT, llamado SUBSTRING. SUBSTRING nos permite quedarnos con una porción de una columna de tipo VARCHAR. Los argumentos de los que se alimenta son tres: i) nombre de la columna, ii) índice desde donde se quiere empezar a recoger la cadena de caracteres, iii) longitud de la cadena a extraer. En este caso pediremos tomar la subcadena desde el índice -4 (empezando por la parte de atrás) y será de longitud 4. Así de esta forma extraeremos de la columna date_added el año en el que se añadieron las películas y series a Netflix.
La transcripción de la consulta sería la siguiente:
a) Seleccionar el substring año de la columna date_added renombrándolo como
year_added y realizar el conteo de los registros no nulos renombrándolo como
num_pel_añadidas
b) De la tabla netflix_titles
c) Agrupados por año y
d) Ordenados de forma ascendente
Los resultados que nos proporciona la herramienta de gestión de bases de datos se muestran en la Figura 14. Vemos que antes de 2018 tenemos una primera fila donde no hay ningún año. Esto se debe a que hay registros en la base de datos donde el campo year_added no tiene información. También vemos como en 2021 el número de películas añadidas es bajo, lo cual es normal teniendo en cuenta que este año prácticamente acaba de empezar. Es por esto que estos dos registros de la tabla de resultados son en principio prescindibles para la representación gráfica que vamos a hacer en Python.
Antes de representar los resultados, realizaremos la lectura y limpieza de los resultados, eliminando la fila sin año específico y los resultados de 2021 por no ser comparable con los valores de años anteriores.
Tras la limpieza de los datos pasamos a representarlos con Plotly Express. utilizaremos una línea para ver la variación de las subidas en función del tiempo.
La gráfica muestra una tendencia estrictamente creciente desde 2013 hasta 2019. El año 2020 parece que fue también un año raro para Netflix, que sufrió una caída en el número de nuevos materiales en su plataforma tras 7 años de constante subida.
Conclusión
Este post muestra unas pequeñas pinceladas para iniciaros en el trabajo con bases de datos relaciones y sacar información mediante consultas SQL sencillas. Espero que os haya resultado interesante.
Para cualquier duda o consulta podéis poneros en contacto conmigo a través del directorio del blog.
Comments