CTE (Common Table Expressions)

Consultas temporales con WITH para mejor legibilidad

Las CTE (Common Table Expressions) permiten crear consultas auxiliares temporales usando la cláusula WITH.

Sintaxis básica

sql

1WITH nombre_cte AS (2    SELECT columna1, columna23    FROM tabla4    WHERE condición5)6SELECT * FROM nombre_cte;

CTE Recursiva

sql

1WITH RECURSIVE numeros AS (2    SELECT 1 as n3    UNION ALL4    SELECT n + 1 FROM numeros WHERE n < 105)6SELECT * FROM numeros;

Múltiples CTEs

sql

1WITH 2ventas_2024 AS (3    SELECT * FROM ventas WHERE EXTRACT(YEAR FROM fecha) = 20244),5top_productos AS (6    SELECT producto_id, SUM(total) as total7    FROM ventas_20248    GROUP BY producto_id9    ORDER BY total DESC10    LIMIT 511)12SELECT p.nombre, tp.total13FROM top_productos tp14JOIN productos p ON tp.producto_id = p.id;

Ejemplo: Jerarquía de empleados

sql

1WITH RECURSIVE jerarquia AS (2    SELECT id, nombre, gerente_id, 1 as nivel3    FROM empleados4    WHERE gerente_id IS NULL5    6    UNION ALL7    8    SELECT e.id, e.nombre, e.gerente_id, j.nivel + 19    FROM empleados e10    JOIN jerarquia j ON e.gerente_id = j.id11)12SELECT * FROM jerarquia ORDER BY nivel, nombre;

Sintaxis

sql

1WITH nombre_cte [(columna1, columna2, ...)] AS (2    consulta SELECT3)4SELECT * FROM nombre_cte;

Ejemplos

CTE simple

Usar CTE para filtrar usuarios activos

Ejemplo

sql

1WITH usuarios_activos AS (2    SELECT id, nombre, email3    FROM usuarios4    WHERE activo = true5)6SELECT * FROM usuarios_activos7ORDER BY nombre;

Resultado:

| id | nombre | email             |
|----|--------|-------------------|
| 1  | Ana    | ana@email.com     |
| 2  | Carlos | carlos@email.com  |

Window Functions