Expresiones Comunes de Tabla (CTEs) en MySQL

Introducción

En este laboratorio, aprenderá a utilizar las Expresiones Comunes de Tabla (CTEs) en MySQL. Las CTEs ayudan a simplificar consultas complejas dividiéndolas en pasos lógicos y legibles. Una CTE crea un conjunto de resultados temporal y con nombre al que puede hacer referencia dentro de una única instrucción SQL.

Comenzará configurando una base de datos y creando una CTE simple para filtrar datos de empleados. Luego, explorará las CTEs recursivas para manejar datos jerárquicos, como un organigrama. Finalmente, aprenderá a unir una CTE con una tabla para realizar un análisis de datos más avanzado.

Configurar la Base de Datos y una CTE Simple

En este primer paso, configurará el entorno de la base de datos y definirá una Expresión Común de Tabla (CTE) simple. Las CTEs se definen utilizando la cláusula WITH y actúan como una vista temporal que existe solo durante la duración de una consulta única. Esto hace que las consultas complejas sean más fáciles de leer y administrar.

Primero, abra la terminal desde su escritorio.

Conéctese al servidor MySQL como usuario root. En este entorno de laboratorio, puede usar sudo para conectarse sin contraseña.

sudo mysql -u root

Una vez conectado, verá el prompt de MySQL (mysql>). Ahora, cree una base de datos llamada labex_db y cambie a ella.

CREATE DATABASE IF NOT EXISTS labex_db;
USE labex_db;

A continuación, cree una tabla employees e inserte algunos datos de ejemplo. Esta tabla almacenará información básica sobre los empleados, incluido su departamento y salario.

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Doe', 'Sales', 60000.00),
(2, 'Jane', 'Smith', 'Marketing', 75000.00),
(3, 'Robert', 'Jones', 'Sales', 55000.00),
(4, 'Emily', 'Brown', 'IT', 90000.00),
(5, 'Michael', 'Davis', 'Marketing', 70000.00);

Ahora que la tabla está lista, definamos una CTE simple para seleccionar solo a los empleados del departamento de 'Sales'.

WITH SalesEmployees AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department = 'Sales'
)
SELECT * FROM SalesEmployees;

Analicemos esta consulta:

• WITH SalesEmployees AS (...): Esto define una CTE llamada SalesEmployees.
• La consulta dentro de los paréntesis selecciona empleados de la tabla employees donde el department es 'Sales'.
• SELECT * FROM SalesEmployees;: Esta es la consulta principal que recupera todos los datos de nuestro conjunto de resultados temporal SalesEmployees.

Debería ver la siguiente salida, que enumera solo a los empleados del departamento de Ventas:

+-------------+------------+-----------+----------+
| employee_id | first_name | last_name | salary   |
+-------------+------------+-----------+----------+
|           1 | John       | Doe       | 60000.00 |
|           3 | Robert     | Jones     | 55000.00 |
+-------------+------------+-----------+----------+
2 rows in set (0.00 sec)

Esto confirma que su primera CTE está funcionando correctamente. Por favor, permanezca en la shell de MySQL para el siguiente paso.

Crear una CTE Recursiva para Datos Jerárquicos

Las CTEs recursivas son una característica potente para consultar datos jerárquicos, como organigramas o categorías anidadas. Una CTE recursiva se refiere a sí misma para procesar una estructura similar a un árbol nivel por nivel. Consta de un "miembro ancla" (el punto de partida) y un "miembro recursivo" (la iteración).

En este paso, creará una tabla que representa una jerarquía de empleados y utilizará una CTE recursiva para mostrarla.

Primero, mientras aún se encuentra en la shell de MySQL, cree la tabla employee_hierarchy. Esta tabla incluye un manager_id que apunta al employee_id de otro empleado, creando la jerarquía.

CREATE TABLE IF NOT EXISTS employee_hierarchy (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

INSERT INTO employee_hierarchy (employee_id, employee_name, manager_id) VALUES
(1, 'David', NULL),
(2, 'Emily', 1),
(3, 'Frank', 1),
(4, 'Grace', 2),
(5, 'Henry', 2),
(6, 'Ivy', 3),
(7, 'Jack', 3);

En esta estructura, 'David' es el gerente de nivel superior porque su manager_id es NULL.

Ahora, escriba una CTE recursiva para recorrer esta jerarquía. Se requiere la palabra clave RECURSIVE.

WITH RECURSIVE EmployeeHierarchyCTE AS (
    -- Miembro ancla: selecciona el gerente de nivel superior
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employee_hierarchy
    WHERE manager_id IS NULL

    UNION ALL

    -- Miembro recursivo: se une consigo mismo para encontrar subordinados
    SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
    FROM employee_hierarchy e
    INNER JOIN EmployeeHierarchyCTE h ON e.manager_id = h.employee_id
)
SELECT * FROM EmployeeHierarchyCTE ORDER BY level, employee_name;

Analicemos esta consulta:

• Miembro Ancla: La primera instrucción SELECT encuentra la raíz de la jerarquía (donde manager_id es NULL) y le asigna el level 0.
• Miembro Recursivo: La segunda instrucción SELECT une employee_hierarchy con la propia CTE (EmployeeHierarchyCTE). Encuentra todos los empleados cuyo manager_id coincide con un employee_id que ya está en la CTE, e incrementa el level.
• UNION ALL: Este operador combina los resultados de los miembros ancla y recursivos.

La consulta producirá la siguiente salida, mostrando el organigrama completo con niveles:

+-------------+---------------+------------+-------+
| employee_id | employee_name | manager_id | level |
+-------------+---------------+------------+-------+
|           1 | David         |       NULL |     0 |
|           2 | Emily         |          1 |     1 |
|           3 | Frank         |          1 |     1 |
|           4 | Grace         |          2 |     2 |
|           5 | Henry         |          2 |     2 |
|           6 | Ivy           |          3 |     2 |
|           7 | Jack          |          3 |     2 |
+-------------+---------------+------------+-------+
7 rows in set (0.00 sec)

Ha utilizado con éxito una CTE recursiva para consultar datos jerárquicos. Por favor, permanezca en la shell de MySQL para el paso final.

Unir una CTE con una Tabla

Las CTEs se pueden unir con otras tablas al igual que las tablas regulares. Esto es útil para combinar datos agregados de una CTE con datos detallados de otra tabla. En este paso, creará una CTE para calcular el salario promedio por departamento y luego la unirá de nuevo a la tabla employees.

Mientras aún se encuentra en la shell de MySQL, ejecute la siguiente consulta. Define una CTE llamada AvgSalaryByDepartment y luego la une con la tabla employees.

WITH AvgSalaryByDepartment AS (
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT
    e.employee_id,
    e.first_name,
    e.department,
    e.salary,
    a.avg_salary
FROM
    employees e
JOIN
    AvgSalaryByDepartment a ON e.department = a.department;

Aquí está el desglose:

• La CTE AvgSalaryByDepartment calcula el salario promedio para cada departamento.
• La consulta principal luego une la tabla employees (con alias e) con esta CTE (con alias a) en la columna department.
• Esto le permite mostrar el salario de cada empleado junto con el salario promedio de su departamento.

La salida esperada será:

+-------------+------------+------------+----------+--------------+
| employee_id | first_name | department | salary   | avg_salary   |
+-------------+------------+------------+----------+--------------+
|           1 | John       | Sales      | 60000.00 | 57500.000000 |
|           2 | Jane       | Marketing  | 75000.00 | 72500.000000 |
|           3 | Robert     | Sales      | 55000.00 | 57500.000000 |
|           4 | Emily      | IT         | 90000.00 | 90000.000000 |
|           5 | Michael    | Marketing  | 70000.00 | 72500.000000 |
+-------------+------------+------------+----------+--------------+
5 rows in set (0.00 sec)

Puede verificar manualmente los resultados. Por ejemplo, el salario promedio para el departamento de 'Sales' es (60000 + 55000) / 2 = 57500, lo que coincide con la salida. Esto confirma que su consulta está funcionando correctamente.

Ha unido con éxito una CTE con una tabla. Ahora puede salir de la shell de MySQL.

exit;

Resumen

En este laboratorio, ha aprendido a utilizar eficazmente las Expresiones Comunes de Tabla (CTEs) en MySQL. Comenzó configurando una base de datos y tablas, luego avanzó en la creación de diferentes tipos de CTEs.

Ha aprendido a:

• Definir una CTE simple utilizando la cláusula WITH para filtrar datos y mejorar la legibilidad de las consultas.
• Crear una CTE recursiva para navegar y mostrar datos jerárquicos de una tabla.
• Unir una CTE con una tabla para combinar resultados agregados con datos detallados a nivel de fila para un análisis más complejo.

Estas habilidades son fundamentales para escribir consultas SQL limpias, mantenibles y potentes, especialmente cuando se trata de lógica o estructuras de datos complejas.