No hay nada llamado autounión en SQL. Escúchame.
A menudo, los analistas e ingenieros de datos necesitan encontrar patrones en los datos que no son obvios. Sin embargo, se pueden generar conocimientos e identificar patrones mediante el uso de prácticas SQL comunes, como Self Join.
Muchos principiantes suelen tener dificultades para comprender Self Join. Confunden Self Join en SQL con otro comando como Inner o Left Join. Pero Self Join no es una palabra clave en SQL. Tampoco es un comando en SQL.
La autounión es como una unión normal (interior/izquierda/derecha/exterior) entre dos tablas. Sin embargo, en una autounión, las dos tablas son iguales pero actúan como tablas diferentes a través de sus alias.
La autounión a menudo se considera una mala práctica en ingeniería de datos. Dicen que su uso es riesgoso. Sin embargo, hay situaciones en las que utilizar una autounión es práctico y la mejor manera de abordar el problema.
Veamos algunos ejemplos:
Datos jerárquicos:
Las autouniones son útiles para trabajar con datos jerárquicos. En un organigrama, podemos unir una tabla a sí misma en función de las relaciones gerente-empleado para encontrar informes de empleados, jefes de departamento, etc.
Generemos algunos datos gratuitos para probar esto.
create table employee
(
employee_id int,
employee_name varchar(10),
EmpSSN varchar(11),
manager_id int null,
city varchar(20)
);--Correct data
insert into employee values(1, 'Jack', '555-55-5555','','Kolkata');
insert into employee values (2, 'Joe', '555-56-5555',1,'Kolkata');
insert into employee values (3, 'Fred', '555-57-5555',2,'Dehli');
insert into employee values (4, 'Mike', '555-58-5555',2,'Kolkata');
insert into employee values (5, 'Cathy', '555-59-5555',2,'Dehli');
insert into employee values (6, 'Lisa', '555-70-5555',3,'Bangalore');
Aquí se almacenan los detalles de los empleados de una organización junto con su ID de gerente. Podemos utilizar Self join para identificar al gerente de todos los empleados distintos.
select emp.*,isnull(mgr.employee_name,'Boss') as managerName from employee emp
left join employee mgr on emp.manager_id = mgr.employee_id
Aquí la consulta devuelve el nombre del gerente correspondiente a cada empleado al unirse al mismo empleado tabla consigo misma en manager_id = Employee_id.
ADVERTENCIA: No olvide utilizar alias para el empleado tabla, para diferenciar entre dos partes de la autounión. Además, la columna de unión debe usarse correctamente.
De manera similar, incluso podemos encontrar los diferentes niveles de jerarquía uniendo recursivamente el CTE como una autounión consigo mismo.
WITH
EmployeeHierarchy AS (
SELECT
employee_id, employee_name, manager_id, 0
AS
level
FROM
employee
WHERE
manager_id = 0
UNION ALL
SELECT
emp.employee_id, emp.employee_name, emp.manager_id, eh.level + 1
FROM
employee emp
JOIN
EmployeeHierarchy eh
ON
emp.manager_id = eh.employee_id
)
SELECT
employee_id, employee_name, level
FROM
EmployeeHierarchy;
Productos y categorías:
Esto puede estar relacionado únicamente con datos jerárquicos, pero se trata de un subconjunto específico. Las autouniones pueden resultar extremadamente útiles para identificar todas las combinaciones de productos, categorías y subcategorías. En la industria manufacturera, esto puede proporcionar componentes y subcomponentes, en el comercio electrónico se puede utilizar para obtener productos o categorías similares.
Aprendamoslo a través de un ejemplo:
Cree una tabla e inserte datos ficticios:
create table bom (item_id int, parent_id int null,description varchar(50), quantity int)INSERT INTO bom (item_id, parent_id, description, quantity)
VALUES (1, NULL, 'Widget (Main Assembly)', 1),
(2, 1, 'Gear A', 2),
(3, 1, 'Spring B', 4),
(4, 2, 'Screw C (Small)', 10),
(5, 2, 'Screw C (Large)', 5),
(6, 3, 'Nut D', 1);
Creamos una tabla con columnas de item_id, parent_id, descripción y cantidad. También hemos insertado datos de muestra de una línea de fabricación, donde ‘Widget (ensamblaje principal)’ es el producto principal y engranajes, tornillos, tuercas, etc. son subproductos.
Podemos utilizar la autounión para identificar la relación padre-hijo, y la autounión recursiva puede identificar la secuencia completa del producto.
Repasemos esto con la consulta y los resultados:
WITH recursive_bom AS (
SELECT item_id, parent_id, description, quantity, cast(description as nvarchar(255)) AS full_path
FROM bom
WHERE parent_id IS NULL -- Starting point: Top-level items
UNION ALL
SELECT
b.item_id,
b.parent_id,
b.description,
b.quantity,
cast(CONCAT(rb.full_path, '.', b.description) as nvarchar(255)) AS full_path
FROM bom b
INNER JOIN recursive_bom rb ON b.parent_id = rb.item_id
)
SELECT item_id, description, quantity, full_path
FROM recursive_bom
ORDER BY full_path;
Datos de muestra y resultados
Segmentación de usuarios:
En Business and Data Analytics, un aspecto importante es la segmentación de usuarios. Los usuarios a menudo se clasifican en función de su comportamiento de compra, su frecuencia de interacción con la empresa, etc. Una autounión puede ser una excelente manera de identificar estos patrones en los datos de la transacción.
Consideremos el siguiente ejemplo:
Necesitamos identificar a los clientes que regresan dentro de un período de tiempo específico (7 días) para un negocio de comercio electrónico. Se pueden encontrar desafíos similares en Internet, por ejemplo aquí.
Creemos una tabla de prueba e insertemos algunos registros de muestra en la tabla.
Truco: puedes pedirle a ChatGpt que genere los datos de prueba según sea necesario.
Cree una tabla e inserte datos ficticios:
create table ecom_tran (
tranid int,
userid int,
created_date datetime,
itemname varchar(50)
)INSERT INTO ecom_tran (tranid, userid, created_date, itemname, price_paid)
VALUES
(1, 201, '2024-02-23 11:45:00', 'Running Shoes'),
(2, 202, '2024-02-24 10:00:00', 'Yoga Mat'),
(3, 203, '2024-02-26 14:10:00', 'Water Bottle'),
(4, 204, '2024-02-27 09:30:00', 'Gym Bag'),
(5, 205, '2024-02-28 12:00:00', 'Protein Powder'),
(6, 201, '2024-02-29 15:15:00', 'Phone Case'),
(7, 206, '2024-03-01 10:45:00', 'Webcam'),
(8, 202, '2024-03-02 16:30:00', 'Pen Drive'),
(9, 207, '2024-03-04 12:00:00', 'Powerbank'),
(10, 203, '2024-03-05 09:00:00', 'Monitor'),
(11, 101, '2024-03-06 11:00:00', 'Mouse'),
(12, 102, '2024-03-07 14:45:00', 'Speaker'),
(13, 103, '2024-03-08 10:10:00', 'Tablet'),
(14, 101, '2024-03-09 13:30:00', 'Headphones'),
(15, 104, '2024-03-10 17:00:00', 'Book'),
(16, 102, '2024-03-11 08:20:00', 'Coffee Maker'),
(17, 105, '2024-03-12 11:15:00', 'Smartwatch'),
(18, 101, '2024-03-13 15:45:00', 'Shirt'),
(19, 103, '2024-03-14 12:30:00', 'Laptop')
Enfoque de solución:
En la tabla de muestra creada, tenemos la columna ID de usuario, ID de transacción y fecha de creación que son relevantes para el desafío. Como se nos pidió que identifiquemos a los usuarios que realizaron al menos 2 compras en un período de 7 días, podemos considerar el siguiente enfoque:
- Comprueba cuántas transacciones diferentes han realizado los usuarios.
- Combine cada transacción consigo misma para identificar todos los pares posibles de transacciones realizadas por el mismo usuario.
- Calcule la diferencia de fechas entre las dos combinaciones.
- La diferencia de fecha debe ser > 0 y < 7. Esto garantizará que solo se devuelvan los registros en los que las transacciones se hayan realizado dentro de los 7 días.
- Podemos recopilar los distintos ID de usuario para identificar a los usuarios que han devuelto transacciones dentro de los 7 días.
Este es un caso de uso clásico para considerar la unión automática junto con la unión no equivalente.
SELECT a.userid,
a.tranid AS id1,
a.created_date AS created_at1,
b.tranid AS id2,
b.created_date AS created_at2,
mod(DATEDIFF(dd,a.created_date,b.created_date))
FROM ecom_tran a
JOIN ecom_tran b
ON a.userid=b.userid
AND a.tranid <> b.tranid
ORDER BY a.userid
La consulta anterior genera todas las combinaciones de transacciones realizadas por los mismos usuarios. Lo hemos logrado uniendo ecom_tran consigo mismo, con la ayuda de alias, en la columna ID de usuario. Esta unión interna garantiza que solo se devuelvan transacciones del mismo usuario.
Sin embargo, la unión no equivalente está activada. a.tranid <> b.tranid esto garantiza que no se repitan las mismas transacciones.
También calculamos la diferencia de fechas entre las dos transacciones.
Ahora, si solo filtramos aquellos en los que la diferencia de fecha entre las fechas de creación es > 0 y < 7, eso nos dará todas las transacciones que ocurrieron dentro de los 7 días del mismo usuario. Podemos tomar un valor distinto de la columna de ID de usuario para identificar simplemente a los usuarios que realizaron compras recurrentes dentro de los 7 días.
Conclusión:
Espero que tenga una comprensión general y una intuición sobre cómo funciona la autounión en SQL. Aunque las autouniones no son muy intuitivas de entender y usar, existen casos de uso específicos en los que son indispensables.
He cubierto sólo algunos de los escenarios posibles. Sin embargo, esto es suficiente para darle confianza al enfrentar cualquier pregunta de la entrevista SQL. Incluso si la pregunta puede necesitar una comprensión intuitiva para resolver los desafíos, estos conceptos ayudarán a identificar qué enfoque utilizar.