SQL para Ciencia de Datos Comprender y Aprovechar las Uniones

Comprender y aprovechar las uniones en SQL para Ciencia de Datos

La ciencia de datos es un campo interdisciplinario que depende en gran medida de extraer conocimientos y tomar decisiones informadas a partir de grandes cantidades de datos. Una de las herramientas fundamentales en el arsenal de un científico de datos es SQL (Structured Query Language), un lenguaje de programación diseñado para administrar y manipular bases de datos relacionales.

En este artículo, me centraré en una de las características más poderosas de SQL: las uniones (joins).

• GPT y más allá Los fundamentos técnicos de los LLMs
• Actores que apoyan el uso de la IA, y aquellos que no lo hacen
• 5 Millonarios Utilizando ChatGPT

¿Qué son las uniones en SQL?

Las uniones en SQL te permiten combinar datos de múltiples tablas de bases de datos en función de columnas comunes. De esta manera, puedes fusionar información y crear conexiones significativas entre conjuntos de datos relacionados.

Tipos de uniones en SQL

Existen varios tipos de uniones en SQL:

• Unión interna
• Unión externa izquierda
• Unión externa derecha
• Unión externa completa
• Unión cruzada

Expliquemos cada tipo.

Unión interna en SQL

Una unión interna devuelve solo las filas donde hay coincidencia en ambas tablas que se unen. Combina filas de dos tablas en función de una clave o columna compartida, descartando las filas que no coinciden.

Visualizamos esto de la siguiente manera.

En SQL, este tipo de unión se realiza utilizando las palabras clave JOIN o INNER JOIN.

Unión externa izquierda en SQL

Una unión externa izquierda devuelve todas las filas de la tabla izquierda (o primera) y las filas coincidentes de la tabla derecha (o segunda). Si no hay coincidencia, devuelve valores NULL para las columnas de la tabla derecha.

Podemos visualizarlo de esta manera.

Cuando deseas utilizar esta unión en SQL, puedes hacerlo utilizando las palabras clave LEFT OUTER JOIN o LEFT JOIN. Aquí tienes un artículo que habla sobre left join vs left outer join.

Unión externa derecha en SQL

Una unión derecha es lo opuesto a una unión izquierda. Devuelve todas las filas de la tabla derecha y las filas coincidentes de la tabla izquierda. Si no hay coincidencia, devuelve valores NULL para las columnas de la tabla izquierda.

En SQL, este tipo de unión se realiza utilizando las palabras clave RIGHT OUTER JOIN o RIGHT JOIN.

Unión externa completa en SQL

Una unión externa completa devuelve todas las filas de ambas tablas, filas coincidentes cuando es posible y rellena los valores NULL para las filas que no coinciden.

Las palabras clave en SQL para esta unión son FULL OUTER JOIN o FULL JOIN.

Unión cruzada en SQL

Este tipo de unión combina todas las filas de una tabla con todas las filas de la segunda tabla. En otras palabras, devuelve el producto cartesiano, es decir, todas las combinaciones posibles de las filas de las dos tablas.

Aquí está la visualización que lo hará más fácil de entender.

Cuando realizas una unión cruzada en SQL, la palabra clave es CROSS JOIN.

Comprendiendo la sintaxis de las uniones en SQL

 

Para realizar una unión en SQL, debes especificar las tablas que deseas unir, las columnas que se utilizarán para hacer coincidir y el tipo de unión que deseas realizar. La sintaxis básica para unir tablas en SQL es la siguiente:

SELECT columnas
FROM tabla1
JOIN tabla2
ON tabla1.columna = tabla2.columna;

 

Este ejemplo muestra cómo usar JOIN.

Se hace referencia a la primera (o izquierda) tabla en la cláusula FROM. Luego, se sigue con JOIN y se hace referencia a la segunda (o derecha) tabla.

Luego viene la condición de unión en la cláusula ON. Aquí es donde especificas qué columnas usarás para unir las dos tablas. Por lo general, es una columna compartida que es una clave primaria en una tabla y una clave externa en la segunda tabla.

Nota: Una clave primaria es un identificador único para cada registro en una tabla. Una clave externa establece un vínculo entre dos tablas, es decir, es una columna en la segunda tabla que hace referencia a la primera tabla. Te mostraremos en los ejemplos lo que eso significa.

Si quieres usar LEFT JOIN, RIGHT JOIN o FULL JOIN, simplemente usas estas palabras clave en lugar de JOIN, ¡todo lo demás en el código es exactamente igual!

Las cosas son un poco diferentes con CROSS JOIN. Por su naturaleza, une todas las combinaciones de filas de ambas tablas. Es por eso que no se necesita la cláusula ON y la sintaxis se ve así.

SELECT columnas
FROM tabla1
CROSS JOIN tabla2;

 

En otras palabras, simplemente haces referencia a una tabla en FROM y la segunda en CROSS JOIN.

Alternativamente, puedes hacer referencia a ambas tablas en FROM y separarlas con una coma, esto es una forma abreviada de CROSS JOIN.

SELECT columnas
FROM tabla1, tabla2;

 

Self Join: Un tipo especial de unión en SQL

 

También hay una forma específica de unir las tablas: uniendo la tabla consigo misma. Esto también se llama unión auto referencial de la tabla.

No es exactamente un tipo de unión distinto, ya que cualquiera de los tipos de unión mencionados anteriormente también se puede usar para unir consigo misma.

La sintaxis para la unión consigo misma es similar a lo que te mostré antes. La diferencia principal es que la misma tabla se referencia en FROM y JOIN.

SELECT columnas
FROM tabla1 t1
JOIN tabla1 t2
ON t1.columna = t2.columna;

 

Además, debes darle a la tabla dos alias para distinguirlas. Lo que estás haciendo es unir la tabla consigo misma y tratarla como dos tablas.

Solo quería mencionarlo aquí, pero no entraré en más detalles. Si estás interesado en la unión consigo misma, por favor consulta esta guía ilustrada sobre la unión consigo misma en SQL.

 

Ejemplos de Unión en SQL

 

Es hora de mostrarte cómo funciona todo lo que mencioné en la práctica. Usaré preguntas de entrevista de unión en SQL de StrataScratch para mostrar cada tipo de unión en SQL.

 

1. Ejemplo de JOIN

 

Esta pregunta de Microsoft quiere que enumeres cada proyecto y calcules el presupuesto del proyecto por empleado.

Proyectos Costosos

“Dada una lista de proyectos y empleados asignados a cada proyecto, calcula la cantidad de presupuesto del proyecto asignado a cada empleado. La salida debe incluir el título del proyecto y el presupuesto del proyecto redondeado al entero más cercano. Ordena tu lista por proyectos con el presupuesto más alto por empleado primero.”

 

Datos

 

La pregunta proporciona dos tablas.

ms_projects

id:	int
title:	varchar
budget:	int

 

ms_emp_projects

emp_id:	int
project_id:	int

 

Ahora, la columna id en la tabla ms_projects es la clave primaria de la tabla. La misma columna se puede encontrar en la tabla ms_emp_projects, aunque con un nombre diferente: project_id. Esta es la clave externa de la tabla, haciendo referencia a la primera tabla.

Utilizaré estas dos columnas para unir las tablas en mi solución.

 

Código

 

SELECT title AS proyecto,
       ROUND((budget/COUNT(emp_id)::FLOAT)::NUMERIC, 0) AS relacion_presupuesto_empleado
FROM ms_projects a
JOIN ms_emp_projects b 
ON a.id = b.project_id
GROUP BY title, budget
ORDER BY relacion_presupuesto_empleado DESC;

 

Uní las dos tablas utilizando JOIN. La tabla ms_projects se referencia en FROM, mientras que ms_emp_projects se referencia después de JOIN. Les di a ambas tablas un alias, lo que me permite no usar los nombres largos de las tablas más adelante.

Ahora, debo especificar las columnas en las que quiero unir las tablas. Ya mencioné cuáles son las claves primarias en una tabla y la clave externa en otra tabla, así que las usaré aquí.

Equivalo estas dos columnas porque quiero obtener todos los datos donde el ID del proyecto es el mismo. También utilicé los alias de las tablas delante de cada columna.

Ahora que tengo acceso a los datos en ambas tablas, puedo listar las columnas en SELECT. La primera columna es el nombre del proyecto y la segunda columna está calculada.

Este cálculo utiliza la función COUNT() para contar el número de empleados por cada proyecto. Luego divido el presupuesto de cada proyecto por el número de empleados. También convierto el resultado en valores decimales y lo redondeo a cero decimales.

 

Resultado

 

Esto es lo que devuelve la consulta.

 

 

2. Ejemplo de LEFT JOIN

 

Vamos a practicar esta unión con la pregunta de entrevista de Airbnb. Quiere que encuentres el número de pedidos, el número de clientes y el costo total de los pedidos para cada ciudad.

Clientes y Detalles de Pedidos

“Encuentra el número de pedidos, el número de clientes y el costo total de los pedidos para cada ciudad. Incluye solo las ciudades que hayan realizado al menos 5 pedidos y cuenta todos los clientes en cada ciudad incluso si no realizaron un pedido.

Imprime cada cálculo junto con el nombre de la ciudad correspondiente”.

 

Datos

 

Se te proporcionan las tablas customers y orders.

customers

id:	int
first_name:	varchar
last_name:	varchar
city:	varchar
address:	varchar
phone_number:	varchar

 

orders

id:	int
cust_id:	int
order_date:	datetime
order_details:	varchar
total_order_cost:	int

 

Las columnas compartidas son id de la tabla customers y cust_id de la tabla orders. Usaré estas columnas para unir las tablas.

 

Código

 

Aquí está cómo resolver esta pregunta utilizando LEFT JOIN.

SELECT c.city,
       COUNT(DISTINCT o.id) AS pedidos_por_ciudad,
       COUNT(DISTINCT c.id) AS clientes_por_ciudad,
       SUM(o.total_order_cost) AS costo_pedidos_por_ciudad
FROM customers c
LEFT JOIN orders o ON c.id = o.cust_id
GROUP BY c.city
HAVING COUNT(o.id) >=5;

 

Referencio la tabla customers en FROM (esta es nuestra tabla izquierda) y la uno con LEFT JOIN a la tabla orders en las columnas de ID de cliente.

Ahora puedo seleccionar la ciudad, usar COUNT() para obtener el número de pedidos y clientes por ciudad, y usar SUM() para calcular el costo total de los pedidos por ciudad.

Para obtener todos estos cálculos por ciudad, agrupo los resultados por ciudad.

Hay una solicitud adicional en la pregunta: “Solo incluir ciudades que hayan realizado al menos 5 pedidos…” Utilizo HAVING para mostrar solo las ciudades con cinco o más pedidos y lograr eso.

La pregunta es, ¿por qué usé LEFT JOIN y no JOIN? La clave está en la pregunta: “…y contar todos los clientes en cada ciudad incluso si no realizaron un pedido.” Es posible que no todos los clientes hayan realizado pedidos. Esto significa que quiero mostrar todos los clientes de la tabla customers, lo cual encaja perfectamente con la definición de LEFT JOIN.

Si hubiera usado JOIN, el resultado habría sido incorrecto, ya que habría omitido a los clientes que no realizaron ningún pedido.

Nota: ¡La complejidad de las uniones en SQL no se refleja en su sintaxis, sino en su semántica! Como viste, cada unión se escribe de la misma manera, solo cambia la palabra clave. Sin embargo, cada unión funciona de manera diferente y, por lo tanto, puede generar resultados diferentes según los datos. Por eso, es crucial que comprendas completamente lo que hace cada unión y elijas la que devolverá exactamente lo que deseas.

 

Resultado

 

Ahora, veamos el resultado.

 

 

3. Ejemplo de RIGHT JOIN

 

El RIGHT JOIN es la imagen especular de LEFT JOIN. Es por eso que también podría haber resuelto el problema anterior utilizando RIGHT JOIN. Permíteme mostrarte cómo hacerlo.

 

Datos

 

Las tablas siguen siendo las mismas; solo usaré un tipo diferente de unión.

 

Código

 

SELECT c.city,
       COUNT(DISTINCT o.id) AS pedidos_por_ciudad,
       COUNT(DISTINCT c.id) AS clientes_por_ciudad,
       SUM(o.total_order_cost) AS costo_pedidos_por_ciudad
FROM orders o
RIGHT JOIN customers c ON o.cust_id = c.id 
GROUP BY c.city
HAVING COUNT(o.id) >=5;

 

Esto es lo que ha cambiado. Al usar RIGHT JOIN, cambié el orden de las tablas. Ahora la tabla orders se convierte en la izquierda y la tabla customers en la derecha. La condición de unión sigue siendo la misma. Solo cambié el orden de las columnas para reflejar el orden de las tablas, pero no es necesario hacerlo.

Al cambiar el orden de las tablas y usar RIGHT JOIN, nuevamente mostraré todos los clientes, incluso si no han realizado pedidos.

El resto de la consulta es igual que en el ejemplo anterior. Lo mismo ocurre con el resultado.

Nota: En la práctica, RIGHT JOIN se usa relativamente raramente. LEFT JOIN parece más natural para los usuarios de SQL, por lo que lo usan con mucha más frecuencia. Cualquier cosa que se pueda hacer con RIGHT JOIN también se puede hacer con LEFT JOIN. Por eso, no hay una situación específica en la que se prefiera RIGHT JOIN.

 

Salida

 

 

4. Ejemplo de FULL JOIN

 

La pregunta de Salesforce y Tesla quiere que cuentes la diferencia neta entre el número de productos lanzados por las compañías en 2020 y el número de productos lanzados por las compañías en el año anterior.

Nuevos productos

“Se te proporciona una tabla de lanzamientos de productos por compañía y por año. Escribe una consulta para contar la diferencia neta entre el número de productos lanzados por las compañías en 2020 y el número de productos lanzados por las compañías en el año anterior. Muestra el nombre de las compañías y la diferencia neta de productos lanzados para 2020 en comparación con el año anterior.”

 

Datos

 

La pregunta proporciona una tabla con las siguientes columnas.

lanzamientos_de_autos

año:	int
nombre_de_la_compañía:	varchar
nombre_del_producto:	varchar

 

¿Cómo demonios voy a unir tablas cuando solo hay una tabla? Hmm, ¡veamos eso también!

 

Código

 

Esta consulta es un poco más complicada, así que la revelaré gradualmente.

SELECT nombre_de_la_compañía,
       nombre_del_producto AS marca_2020
FROM lanzamientos_de_autos
WHERE año = 2020;

 

La primera instrucción SELECT encuentra el nombre de la compañía y el nombre del producto en 2020. Esta consulta se convertirá más tarde en una subconsulta.

La pregunta quiere que encuentres la diferencia entre 2020 y 2019. Así que escribamos la misma consulta pero para 2019.

SELECT nombre_de_la_compañía,
       nombre_del_producto AS marca_2019
FROM lanzamientos_de_autos
WHERE año = 2019;

 

Ahora convertiré estas consultas en subconsultas y las uniré usando el FULL OUTER JOIN.

SELECT *
FROM
  (SELECT nombre_de_la_compañía,
          nombre_del_producto AS marca_2020
   FROM lanzamientos_de_autos
   WHERE año = 2020) a
FULL OUTER JOIN
  (SELECT nombre_de_la_compañía,
          nombre_del_producto AS marca_2019
   FROM lanzamientos_de_autos
   WHERE año = 2019) b 
ON a.nombre_de_la_compañía = b.nombre_de_la_compañía;

 

Las subconsultas se pueden tratar como tablas y, por lo tanto, se pueden unir. Le di a la primera subconsulta un alias y la coloqué en la cláusula FROM. Luego uso FULL OUTER JOIN para unirla con la segunda subconsulta en la columna del nombre de la compañía.

Al utilizar este tipo de JOIN en SQL, obtendré todas las compañías y productos en 2020 fusionados con todas las compañías y productos en 2019.

   

Ahora puedo finalizar mi consulta. Seleccionemos el nombre de la compañía. Además, usaré la función COUNT() para encontrar el número de productos lanzados en cada año y luego lo restaré para obtener la diferencia. Finalmente, agruparé la salida por compañía y también la ordenaré alfabéticamente por compañía.

Aquí está la consulta completa.

SELECT a.nombre_de_la_compañía,
       (COUNT(DISTINCT a.marca_2020)-COUNT(DISTINCT b.marca_2019)) AS net_products
FROM
  (SELECT nombre_de_la_compañía,
          nombre_del_producto AS marca_2020
   FROM lanzamientos_de_autos
   WHERE año = 2020) a
FULL OUTER JOIN
  (SELECT nombre_de_la_compañía,
          nombre_del_producto AS marca_2019
   FROM lanzamientos_de_autos
   WHERE año = 2019) b 
ON a.nombre_de_la_compañía = b.nombre_de_la_compañía
GROUP BY a.nombre_de_la_compañía
ORDER BY nombre_de_la_compañía;

 

Resultado

 

Aquí está la lista de empresas y la diferencia de productos lanzados entre 2020 y 2019.

 

 

5. Ejemplo de CROSS JOIN

 

Esta pregunta de Deloitte es excelente para mostrar cómo funciona CROSS JOIN.

Máximo de dos números

“Dado una columna de números, considera todas las posibles permutaciones de dos números asumiendo que los pares de números (x,y) y (y,x) son dos permutaciones diferentes. Luego, para cada permutación, encuentra el máximo de los dos números.

Genera tres columnas: el primer número, el segundo número y el máximo de los dos.”

La pregunta quiere que encuentres todas las posibles permutaciones de dos números asumiendo que los pares de números (x,y) y (y,x) son dos permutaciones diferentes. Luego, necesitamos encontrar el máximo de los números para cada permutación.

 

Datos

 

La pregunta nos da una tabla con una columna.

deloitte_numbers

number:

int

 

 

Código

 

Este código es un ejemplo de CROSS JOIN, pero también de self join.

SELECT dn1.number AS number1,
       dn2.number AS number2,
       CASE
           WHEN dn1.number > dn2.number THEN dn1.number
           ELSE dn2.number
       END AS max_number
FROM deloitte_numbers AS dn1
CROSS JOIN deloitte_numbers AS dn2;

 

Referencio la tabla en FROM y le doy un alias. Luego hago un CROSS JOIN con ella misma al referenciarla después de CROSS JOIN y darle a la tabla otro alias.

Ahora es posible usar una tabla como si fueran dos. Selecciono la columna number de cada tabla. Luego uso la sentencia CASE para establecer una condición que mostrará el número máximo de los dos números.

¿Por qué se utiliza CROSS JOIN aquí? Recuerda, es un tipo de unión SQL que mostrará todas las combinaciones de todas las filas de todas las tablas. ¡Eso es exactamente lo que la pregunta está pidiendo!

 

Resultado

 

Aquí está la captura de pantalla de todas las combinaciones y el número más alto de los dos.

 

 

Utilizando JOINs de SQL para Ciencia de Datos

 

Ahora que sabes cómo utilizar JOINs de SQL, la pregunta es cómo utilizar ese conocimiento en ciencia de datos.

Los JOINs de SQL desempeñan un papel crucial en tareas de ciencia de datos como la exploración de datos, la limpieza de datos y la ingeniería de características.

Aquí tienes algunos ejemplos de cómo se pueden aprovechar los JOINs de SQL:

1. Combinar datos: Unir tablas te permite reunir diferentes fuentes de datos, lo que te permite analizar relaciones y correlaciones en múltiples conjuntos de datos. Por ejemplo, unir una tabla de clientes con una tabla de transacciones puede proporcionar información sobre el comportamiento y los patrones de compra de los clientes.

1. Validación de datos: Los JOINs se pueden utilizar para validar la calidad y la integridad de los datos. Al comparar datos de diferentes tablas, puedes identificar inconsistencias, valores faltantes o valores atípicos. Esto te ayuda en la limpieza de datos y asegura que los datos utilizados para el análisis sean precisos y confiables.

1. Ingeniería de características: Los JOINs pueden ser fundamentales para crear nuevas características para modelos de aprendizaje automático. Al fusionar tablas relevantes, puedes extraer información significativa y generar características que capturen relaciones importantes dentro de los datos. Esto puede mejorar el poder predictivo de tus modelos.

1. Agregación y análisis: Los JOINs te permiten realizar agregaciones y análisis complejos en múltiples tablas. Al combinar datos de diversas fuentes, puedes obtener una vista completa de los datos y obtener información valiosa. Por ejemplo, unir una tabla de ventas con una tabla de productos puede ayudarte a analizar el rendimiento de ventas por categoría de producto o región.

 

Mejores prácticas para las uniones en SQL

 

Como ya mencioné, la complejidad de las uniones no se muestra en su sintaxis. Viste que la sintaxis es relativamente sencilla.

Las mejores prácticas para las uniones también reflejan eso, ya que no se preocupan por el código en sí, sino por lo que hace la unión y cómo se desempeña.

Para aprovechar al máximo las uniones en SQL, considera las siguientes mejores prácticas.

1. Comprende tus datos: Familiarízate con la estructura y las relaciones dentro de tus datos. Esto te ayudará a elegir el tipo de unión adecuado y seleccionar las columnas correctas para hacer coincidir.

1. Utiliza índices: Si tus tablas son grandes o se unen con frecuencia, considera agregar índices en las columnas utilizadas para la unión. Los índices pueden mejorar significativamente el rendimiento de la consulta.

1. Ten en cuenta el rendimiento: Unir tablas grandes o múltiples tablas puede ser computacionalmente costoso. Optimiza tus consultas filtrando datos, utilizando tipos de unión adecuados y considerando el uso de tablas temporales o subconsultas.

1. Prueba y valida: Siempre valida los resultados de tus uniones para garantizar su corrección. Realiza comprobaciones de integridad y verifica que los datos unidos se alineen con tus expectativas y lógica empresarial.

 

Conclusión

 

Las uniones en SQL son un concepto fundamental que te permite, como científico de datos, combinar y analizar datos de múltiples fuentes. Al entender los diferentes tipos de uniones en SQL, dominar su sintaxis y aprovecharlos de manera efectiva, los científicos de datos pueden obtener información valiosa, validar la calidad de los datos y tomar decisiones basadas en datos.

Te mostré cómo hacerlo en cinco ejemplos. Ahora depende de ti aprovechar el poder de SQL y las uniones en tus proyectos de ciencia de datos y lograr mejores resultados.     Nate Rosidi es un científico de datos y estratega de productos. También es profesor adjunto de análisis y fundador de StrataScratch, una plataforma que ayuda a los científicos de datos a prepararse para sus entrevistas con preguntas reales de las principales empresas. Conéctate con él en Twitter: StrataScratch o LinkedIn.  

We will continue to update Zepes; if you have any questions or suggestions, please contact us!