Si eres data scientist o analista de datos, los datos están en el centro de tu trabajo. Hay muchas fuentes de las que puedes obtenerlos. A menudo residen en una base de datos SQL; en ese caso, dominar los comandos de consulta en SQL puede ser clave para desempeñar bien tu función. En este artículo verás desde los comandos más básicos hasta operaciones más avanzadas que te servirán en el día a día como analista o data scientist.
Para explicar los comandos anteriores, usaremos la base de datos open source de alquiler de DVDs y veremos cómo aplicar estos comandos. Puedes consultar el diagrama entidad-relación (ERD) aquí.
Para este artículo, hemos montado la base de datos en local y hemos ejecutado todas las consultas con pgAdmin4, así que utilizamos PostgreSQL. Muchos, si no todos, los comandos funcionarán en otros servidores SQL. La instalación de PostgreSQL y la configuración de la base de datos quedan fuera del alcance de este artículo; te animamos a revisar recursos como la guía para principiantes de PostgreSQL de DataCamp para orientarte en estos pasos.
Recuperación simple de datos
SELECT FROM
El ejemplo más sencillo de recuperación es obtener todo el contenido de una tabla concreta. Si queremos ver todas las categorías de películas en la tabla "category", podemos ejecutar:
SELECT *
FROM category
Lo que devuelve (solo se muestran las 10 primeras filas):
Aquí indicamos el nombre de la tabla tras FROM. Como queremos todo el contenido, usamos * tras SELECT para seleccionar todas las columnas.
DISTINCT
Hay casos en los que solo nos interesan los valores únicos. Por ejemplo, ¿y si queremos ver todas las clasificaciones MPAA de la tabla "film"? Con DISTINCT obtenemos únicamente valores sin duplicados:
SELECT DISTINCT(rating)
FROM film
Lo que devuelve:
Recuperación de datos con condiciones simples
WHERE
También hay situaciones en las que queremos recuperar solo las filas que cumplen ciertas condiciones. Podemos definir condiciones que deben cumplirse antes de devolver los datos con la cláusula WHERE. En esencia, filtramos las filas según la condición. Por ejemplo, ¿y si queremos solo las películas clasificadas como PG-13?
SELECT title
FROM film
WHERE rating = 'PG-13'
Lo que devuelve (solo se muestran las 10 primeras filas):
Además, podemos listar las películas con tarifa de alquiler de 4.5 o superior:
SELECT title, rental_rate
FROM film
WHERE rental_rate >= 4.5
Lo que devuelve (solo se muestran las 10 primeras filas):
ORDER BY
Ordenar el resultado ayuda a entender mejor los datos. Por ejemplo, queremos la misma lista de películas PG-13, pero ordenada por duración de mayor a menor, e incluyendo esa duración. Con ORDER BY lo conseguimos fácilmente:
SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
Lo que devuelve (solo se muestran las 10 primeras filas):
LIMIT
En ocasiones, solo nos interesa un número limitado de registros. Aquí nos interesan las 10 películas PG-13 más largas, así que usamos la cláusula LIMIT.
SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
LIMIT 10
Lo que devuelve:
Agregaciones
GROUP BY & COUNT( )
Las agregaciones se usan para obtener un resumen del conjunto de datos y extraer insights. Suelen combinarse con la cláusula GROUP BY. Por ejemplo, si queremos saber cuántos alquileres ha realizado cada cliente, podemos contar los rentals y ordenar de mayor a menor.
SELECT customer_id, COUNT(rental_id)
FROM rental
GROUP BY customer_id
ORDER BY COUNT(rental_id) DESC
Lo que devuelve (solo se muestran las 10 primeras filas):
SUM( )
¿Y cuánto ha gastado cada cliente? Podemos calcularlo con la función agregada SUM. Aquí queremos al mayor gasto en primer lugar.
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
Lo que devuelve (solo se muestran las 10 primeras filas):
AVG( ) & HAVING
Podemos añadir condiciones tras calcular una agregación agrupada usando HAVING. Por ejemplo, queremos saber qué categorías de clasificación MPAA tienen películas con una duración media superior a 115 minutos.
SELECT rating, AVG(length)
FROM film
GROUP BY rating
HAVING AVG(length) > 115
Lo que devuelve:
MIN( ) & alias
No siempre es necesario agrupar para usar una agregación. Si queremos saber la duración de la película más corta, basta con MIN. Además, queremos que la columna se llame 'shortest_movie_length', algo que logramos con un alias usando 'AS'.
SELECT MIN(length) AS shortest_movie_length
FROM film
Lo que devuelve (solo se muestran las 10 primeras filas):
También puedes usar alias para nombres de tabla. Es útil con nombres largos, especialmente al hacer joins (ver más abajo). Conviene elegir bien los alias, ya que pueden cambiar mucho la legibilidad del código.
Hay otras funciones de agregación útiles; puedes conocerlas en el curso Introduction to SQL.
Joins
INNER JOIN (JOIN)
Habrás notado que con una sola tabla las posibilidades son limitadas. A menudo queremos combinar datos de varias tablas. Aquí entran los JOINs. Podemos unir tablas por una clave común. Por ejemplo, si buscamos películas que no estén en inglés:
SELECT film.title, language.name
FROM film
JOIN language
ON film.language_id = language.language_id
WHERE language.name != 'English'
(Ten en cuenta que en esta base de datos no hay películas en idiomas distintos del inglés. Con esta consulta obtendrás una tabla vacía.)
También puedes unir varias tablas. Abajo queremos conocer las categorías y su calificación media correspondiente. Además, devolvemos los valores ordenados por la calificación media, de mayor a menor.
SELECT category.name, AVG(film.rental_rate) AS average_rating
FROM film
JOIN film_category
ON film.film_id = film_category.film_id
JOIN category
ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
Lo que devuelve (solo se muestran las 10 primeras filas):
Hay varios tipos de join: INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, CROSS JOIN. Aquí usamos JOIN, que por defecto es un INNER JOIN: solo devuelve filas cuando hay coincidencia en ambas tablas. Si quieres aprender las diferencias entre tipos de join, echa un vistazo al curso Joining Data in SQL.
Cambiar tipos de datos
CAST( )
En el ejemplo anterior donde calculamos importes en dólares, quizá hayas notado que SQL trata los valores como números. Podemos mostrarlos como cantidades de dinero con la función CAST:
SELECT customer_id, CAST(SUM(amount) AS money)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
Lo que devuelve (solo se muestran las 10 primeras filas):
El dinero no es el único tipo al que podemos convertir valores. Podemos convertir números a float, texto e incluso a fecha y hora.
ROUND( )
De forma similar, también podemos redondear números. En el ejemplo de la calificación media de alquiler, los valores tienen muchos decimales. Con ROUND podemos redondear a dos:
SELECT category.name, ROUND(AVG(film.rental_rate), 2) AS average_rating
FROM film
JOIN film_category
ON film.film_id = film_category.film_id
JOIN category
ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC
Lo que devuelve (solo se muestran las 10 primeras filas):
Condiciones complejas
CASE statement
Combinando muchas de las funciones y cláusulas anteriores puedes construir consultas bastante complejas. Aun así, hay situaciones en las que lo básico no basta. Por ejemplo, si queremos añadir una columna con categorías por duración de película. Podemos etiquetar como 'Short Film' si dura menos de 50 minutos, 'Long Film' si dura más de 150 y 'Medium Film' para el resto. Lo conseguimos con un CASE:
SELECT title, length,
CASE
WHEN length < 50 THEN 'Short Film'
WHEN length > 150 THEN 'Long Film'
ELSE 'Medium Film'
END AS Movie_Length_Category
FROM film
Lo que devuelve (solo se muestran las 10 primeras filas):
Como ves, se devuelve el resultado cuando se cumple la condición indicada. Es similar a las sentencias "if-else" en lenguajes de programación.
Subqueries
Ahora quizá queramos saber cuáles son las películas más largas y sus títulos. Para ello usamos la cláusula WHERE que vimos arriba, con una condición más compleja:
SELECT title, length
FROM film
WHERE length = (SELECT max(length)
FROM film)
Lo que devuelve:
Con subconsultas podemos construir consultas complejas. En este ejemplo, queremos los títulos de películas cuya tarifa de alquiler es superior a la tarifa media de todas las comedias:
SELECT title, rental_rate
FROM film
WHERE rental_rate >
(SELECT AVG(film.rental_rate) AS Average_Rating
FROM film
JOIN film_category AS cat_id
ON film.film_id=cat_id.film_id
JOIN category AS cat
ON cat_id.category_id=cat.category_id
GROUP BY cat.name, cat_id.category_id
HAVING cat.name = 'Comedy')
Lo que devuelve (solo se muestran las 10 primeras filas):
En esta consulta, primero calculamos la tarifa media de alquiler de las comedias. Ese valor (dentro de los paréntesis) se usa después como condición en la cláusula WHERE para recuperar los títulos de las películas.
Common Table Expressions (CTEs)
En un ejemplo más complejo, podemos usar una common table expression (CTE) para guardar una tabla temporal desde la que extraer la información necesaria. Aquí queremos saber el número medio de alquileres que procesa cada miembro del staff por semana.
WITH weekly_rentals AS(
SELECT
staff_id,
DATE_PART('week', payment_date) AS week,
COUNT(rental_id) AS rental_numbers
FROM payment
GROUP BY staff_id, week)
SELECT staff_id, AVG(rental_numbers)
FROM weekly_rentals
GROUP BY staff_id
Lo que devuelve:
En este caso, guardamos el número de alquileres por semana en una tabla temporal llamada 'weekly_rentals' sobre la que luego ejecutamos otra consulta para obtener el resultado final.
Window functions
Las window functions son otro conjunto de habilidades de SQL importantes que pueden llevarte al siguiente nivel. Aunque suelen considerarse de nivel intermedio a avanzado, muchos analistas y científicos de datos las consideran esenciales para su trabajo. Merece la pena familiarizarse con ellas.
OVER( ) & PARTITION BY. Imagina que queremos obtener el número semanal de alquileres por miembro del staff y el total global de alquileres, junto con el ID de staff y el número de semana. Podemos reutilizar la CTE del ejemplo anterior, como se muestra:
WITH weekly_rentals AS(
SELECT
staff_id,
DATE_PART('week', payment_date) AS week,
COUNT(rental_id) AS rental_numbers
FROM payment
GROUP BY staff_id, week)
SELECT *, SUM(rental_numbers) OVER() AS total_rentals
FROM weekly_rentals
Lo que devuelve (solo se muestran las 10 primeras filas):
OVER en el código indica que es una window function. Aquí aplicamos la función (SUM) a todas las filas. También podemos usarla con PARTITION BY para aplicarla por "particiones". Ahora queremos el total de alquileres por semana en lugar del total general (además de las demás columnas). De nuevo, en el ejemplo siguiente reutilizamos la CTE generada antes.
WITH weekly_rentals AS(
SELECT
staff_id,
DATE_PART('week', payment_date) AS week,
COUNT(rental_id) AS rental_numbers
FROM payment
GROUP BY staff_id, week)
SELECT *, SUM(rental_numbers) OVER(PARTITION BY week) AS total_weekly_rentals
FROM weekly_rentals
Lo que devuelve (solo se muestran las 10 primeras filas):
La columna adicional contiene el total semanal de alquileres, en lugar de un acumulado global.
Aquí solo hemos visto un par de ejemplos sencillos de window functions que son importantes para trabajar como analista o data scientist en muchas empresas. Te animamos a aprender más sobre estos comandos con los enlaces proporcionados. También puedes echar un vistazo al curso Intermediate SQL para aprender o repasar tus habilidades de SQL. Además, puedes practicar con una base de datos open source y probar estos comandos pensando en preguntas relevantes que responder.
