SQL Avanzado Desmitificado
El SQL avanzado no es un lenguaje diferente del SQL que ya escribes, es el mismo modelo declarativo estirado para cubrir problemas que antes forzaban un viaje de vuelta al código de la aplicación.
Las CTEs, las funciones de ventana, las uniones LATERAL y las consultas recursivas existen para permitir que una sola instrucción describa lógica que de otro modo necesitaría bucles, tablas temporales o varios viajes de ida y vuelta a la base de datos.
Esta página construye el modelo mental detrás de esas cuatro herramientas para que las páginas dedicadas a CTEs, funciones de ventana, uniones LATERAL y CTEs recursivas se lean como aplicaciones de una idea en lugar de cuatro sintaxis no relacionadas para memorizar.
Resumen
- Las características avanzadas de SQL te permiten nombrar resultados intermedios, calcular sobre conjuntos de filas sin colapsarlos, correlacionar subconsultas por fila y iterar hasta un punto fijo, todo dentro de una única instrucción declarativa.
- Por Qué Importa: Expresar esta lógica en SQL mantiene el trabajo cerca de los datos, evita viajes de ida y vuelta fila por fila y da al planificador de consultas visibilidad completa para optimizar todo el cálculo en conjunto.
- Conceptos Clave: expresión de tabla común (CTE), función de ventana, partición, correlación LATERAL, punto fijo recursivo.
- Cuándo Usar: Construir informes que clasifican o comparan filas dentro de grupos, recorrer jerarquías o grafos, obtener un resultado de "los N primeros por grupo", o nombrar una transformación de varios pasos para mejorar la legibilidad.
- Limitaciones / Compensaciones: Estas herramientas intercambian algo de simplicidad bruta por poder expresivo, y una CTE o consulta recursiva mal formada puede ocultar un problema de rendimiento que una unión simple habría expuesto inmediatamente.
- Temas Relacionados: planificación de consultas y EXPLAIN, ordenación soportada por índices, vistas materializadas, pensamiento basado en conjuntos frente a bucles procedurales.
Fundamentos
Cada característica avanzada de SQL en esta página sigue siendo solo una consulta sobre conjuntos de filas, la diferencia radica únicamente en cuánta estructura puedes imponer en ese cálculo antes de que el resultado final regrese.
Una CTE (la cláusula WITH) no es más que una subconsulta con nombre que puedes referenciar más tarde en la misma instrucción, que existe puramente para hacer que una consulta larga sea legible al dividirla en pasos con nombre.
Una función de ventana calcula un valor sobre un conjunto de filas relacionadas, llamada su partición, sin fusionar esas filas en una sola fila de salida como lo haría GROUP BY.
LATERAL permite que una subconsulta en la cláusula FROM vea columnas de elementos FROM que aparecen antes que ella, ejecutando efectivamente una pequeña consulta correlacionada una vez por cada fila externa mientras aún produce un único resultado basado en conjuntos.
Una CTE recursiva repite una consulta contra su propio resultado creciente hasta que no aparezcan nuevas filas, que es cómo SQL expresa recorridos de jerarquías y traversales de grafos sin un bucle del lado del cliente.
La forma más sencilla de tener las cuatro en mente es pensar en una instrucción SQL como una línea de ensamblaje: las CTEs nombran las estaciones, las funciones de ventana añaden columnas calculadas sin romper la línea en cintas separadas, LATERAL permite que una estación mire hacia atrás la salida de una estación anterior fila por fila, y la recursión permite que una estación alimente su propia salida de vuelta hasta que la línea se detenga naturalmente.
Mecánica e Interacciones
El planificador de consultas procesa las cláusulas de una instrucción en un orden lógico fijo que es diferente del orden en que las escribes, y comprender ese orden explica por qué las funciones de ventana pueden referenciar resultados de GROUP BY pero no al revés.
FROM / JOIN -> WHERE -> GROUP BY -> HAVING
-> window functions -> SELECT list
-> DISTINCT -> ORDER BY -> LIMIT / OFFSETDebido a que las funciones de ventana se ejecutan después de la agrupación y el filtrado, pero antes de que se ensamble la lista SELECT final, pueden clasificar o comparar filas ya agregadas sin necesidad de una segunda pasada sobre la tabla.
Una CTE no recursiva no es automáticamente una "barrera de optimización" como lo era en versiones anteriores de PostgreSQL; desde PostgreSQL 12, el planificador puede insertar una consulta WITH en la instrucción circundante exactamente como una subconsulta, a menos que se referencie más de una vez o se marque como MATERIALIZED.
Esa distinción es importante porque una CTE insertada permite al planificador empujar los filtros hacia abajo, mientras que una materializada se calcula una vez y luego se lee de nuevo, lo que puede ayudar cuando la misma CTE se reutiliza varias veces, pero puede perjudicar cuando evita la inserción de filtros.
La correlación LATERAL es mecánicamente similar a una subconsulta por fila, pero como vive en la cláusula FROM, puede devolver múltiples columnas y múltiples filas por cada fila externa, lo cual es exactamente lo que una subconsulta correlacionada simple en la lista SELECT no puede hacer.
SELECT a.id, recent.total
FROM app.accounts a
JOIN LATERAL (
SELECT total FROM app.orders o
WHERE o.account_id = a.id
ORDER BY o.created_at DESC LIMIT 1
) recent ON true;Las CTEs recursivas se ejecutan como un bucle de tabla de trabajo: el término no recursivo siembra el primer lote de filas, el término recursivo se ejecuta de nuevo solo contra las filas producidas en la iteración anterior, y el bucle se detiene en el instante en que una iteración produce cero filas nuevas.
Ese bucle no tiene detección de ciclos incorporada, por lo que una jerarquía autorreferenciada con un ciclo girará hasta que agregues una protección explícita, generalmente un array de claves visitadas comprobado con NOT (id = ANY(path)).
Consideraciones y Aplicaciones Avanzadas
Las funciones de ventana no tienen un tipo de índice dedicado propio, pero un índice que coincida con las columnas PARTITION BY y ORDER BY permite al planificador evitar un paso de ordenación explícito antes de calcular la ventana, lo cual a menudo es el mayor costo en una consulta de ventana.
Las CTEs recursivas tienen un costo real de materialización por iteración porque PostgreSQL tiene que almacenar la tabla de trabajo entre pasos, por lo que un recorrido de grafo sobre una tabla grande y mal acotada puede consumir mucha más work_mem y tiempo que el bucle acotado equivalente en código de aplicación.
El pivote de filas a columnas se puede hacer con agregación condicional usando FILTER, con crosstab() de la extensión tablefunc, o con jsonb_object_agg() cuando las columnas de destino no se conocen de antemano, y cada uno de estos intercambia simplicidad de forma fija por flexibilidad dinámica de manera diferente.
| Enfoque | Fortaleza | Debilidad | Mejor Ajuste |
|---|---|---|---|
CTE (WITH) | Nombra pasos intermedios para legibilidad y reutilización | La materialización puede bloquear la inserción de filtros si se reutiliza o se fuerza | Dividir una consulta larga en etapas nombradas y revisables |
| Función de ventana | Calcula rangos por fila o valores acumulados sin colapsar filas | No tiene tipo de índice dedicado, depende de índices amigables para la ordenación | Top-N por grupo, totales acumulados, comparaciones de fila a fila |
| Unión LATERAL | Subconsulta correlacionada que puede devolver múltiples filas y columnas por fila externa | Necesita un índice de soporte en la columna correlacionada o se degrada a bucles anidados sobre todo | Búsquedas "últimas N" por fila que una unión simple no puede expresar |
| CTE Recursiva | Expresa traversales de jerarquía y grafo de forma declarativa | Materializa una tabla de trabajo por iteración, necesita guardas de ciclo explícitas | Organigramas, listas de materiales, grafos de dependencias |
Pivote FILTER | No requiere extensión, escaneo único amigable para el planificador | El conjunto de columnas debe conocerse en el momento de la consulta | Pequeños conjuntos fijos de columnas de pivote |
Muchos equipos recurren a bucles del lado de la aplicación por costumbre, incluso después de que una consulta pudiera expresar la misma lógica de forma declarativa, y la compensación honesta es que las versiones SQL de estos patrones son menos familiares de leer de un vistazo, pero evitan la latencia de enviar filas de ida y vuelta entre la base de datos y el nivel de aplicación.
Conceptos Erróneos Comunes
- "Cada CTE es una barrera de optimización." Eso era cierto antes de PostgreSQL 12, pero una consulta
WITHsimple hoy en día se inserta como una subconsulta a menos que se referencie varias veces o se marque explícitamente comoMATERIALIZED. - "Las funciones de ventana necesitan
GROUP BYpara funcionar." Una función de ventana puede ejecutarse sobre todo el conjunto de resultados sinGROUP BYalguno, ya quePARTITION BYdentro de la cláusulaOVERdefine su propia agrupación independiente delGROUP BYde la consulta. - "LATERAL es solo una subconsulta correlacionada." Una subconsulta correlacionada en la lista
SELECTsolo puede devolver un valor escalar por fila externa, mientras queLATERALen la cláusulaFROMpuede devolver múltiples filas y columnas por fila externa. - "Las CTEs recursivas usan recursión real como una pila de llamadas de función." PostgreSQL las evalúa iterativamente como un bucle de punto fijo sobre una tabla de trabajo, no como llamadas de función anidadas, por lo que la recursión descontrolada se muestra como una tabla de trabajo en constante crecimiento en lugar de un desbordamiento de pila.
- "El pivoteo siempre requiere la extensión
tablefunc." La agregación condicional conFILTERmaneja columnas de pivote fijas y conocidas sin instalar nada adicional.
Preguntas Frecuentes
¿Cuál es la diferencia real entre una CTE y una subconsulta?
Una CTE es una subconsulta con nombre declarada en una cláusula WITH, y desde PostgreSQL 12, el planificador trata una CTE referenciada una sola vez y no recursiva exactamente como una subconsulta en línea, a menos que fuerces la materialización.
¿Cuándo debo forzar la materialización de una CTE?
- Cuando la misma CTE se referencia varias veces y volver a calcularla sería un desperdicio.
- Cuando deseas un límite de optimización deliberado para estabilizar un plan.
- Cuando la CTE realiza una operación
INSERT/UPDATE/DELETEcon efectos secundarios que debe ejecutarse exactamente una vez.
¿Las funciones de ventana se ejecutan antes o después de `GROUP BY`?
Las funciones de ventana se ejecutan después de GROUP BY y HAVING, pero antes de que se ensamble la lista SELECT final, por lo que pueden operar sobre filas ya agrupadas.
¿Puedo filtrar directamente sobre el resultado de una función de ventana?
No en el mismo SELECT, ya que las funciones de ventana se calculan después de WHERE, por lo que necesitas envolver la consulta en una CTE o subconsulta y filtrar la consulta externa en su lugar.
¿Por qué mi consulta de función de ventana se siente lenta incluso en una tabla pequeña?
Generalmente es la ordenación detrás de PARTITION BY/ORDER BY en lugar del cálculo de la ventana en sí, y un índice que coincida con esas columnas a menudo elimina esa ordenación por completo.
¿Cómo es LATERAL diferente de un JOIN normal?
Una condición JOIN normal no puede referenciar columnas calculadas dentro de la subconsulta unida, mientras que LATERAL permite que la subconsulta de la derecha vea columnas de elementos FROM a su izquierda, evaluada una vez por cada fila externa.
¿Cuál es un caso de uso realista para LATERAL?
Obtener el pedido más reciente de cada cuenta, o sus tres pedidos principales, en una sola consulta en lugar de ejecutar una consulta separada por cuenta desde el código de la aplicación.
¿Por qué mi CTE recursiva nunca se detiene?
- El término recursivo está produciendo nuevas filas en cada iteración, a menudo porque los datos de origen realmente contienen un ciclo.
- No hay una guarda explícita que rastree las filas visitadas.
- Soluciónalo llevando un array de ruta visitada y comprobando
NOT (id = ANY(path))antes de recursar más.
¿Es una CTE recursiva lo mismo que la recursión en un lenguaje de programación?
No, es un bucle iterativo de punto fijo sobre una tabla de trabajo, evaluado por el ejecutor paso a paso en lugar de a través de llamadas de función anidadas.
¿Necesito la extensión `tablefunc` para pivotar datos?
Solo cuando las columnas de pivote no se conocen de antemano o deseas la conveniencia de crosstab(), ya que la agregación condicional con FILTER maneja conjuntos de columnas fijos sin ninguna extensión.
¿Se pueden combinar CTEs y funciones de ventana?
Sí, y es un patrón común calcular una función de ventana dentro de una CTE y luego filtrar o unir contra su resultado en la consulta externa, ya que las funciones de ventana no se pueden filtrar en el mismo SELECT en el que aparecen.
¿Cuál es el mayor error de rendimiento con estas características?
Asumir que nombrar algo en una cláusula WITH lo hace automáticamente más rápido, cuando en realidad el comportamiento de inserción del planificador significa que el rendimiento de una CTE suele ser idéntico a escribir la misma lógica como una subconsulta simple.
¿Siempre debo preferir estas características de SQL sobre los bucles del lado de la aplicación?
Prefiérelas cuando la lógica sea naturalmente basada en conjuntos y de otro modo costaría múltiples viajes de ida y vuelta, pero mantén la lógica de negocio genuinamente procedural y ramificada en el código de la aplicación donde sea más fácil de probar y razonar.
Relacionados
- Fundamentos de SQL Avanzado - ejemplos prácticos de CTEs, funciones de ventana y LATERAL
- Expresiones de Tabla Comunes - sintaxis de CTE, nombres y materialización en profundidad
- Funciones de Ventana - la sintaxis completa
OVER,PARTITION BYy de marco - CTEs Recursivas - recorridos de jerarquía y grafo con guardas de ciclo
- Uniones LATERAL - subconsultas correlacionadas por fila en la cláusula
FROM - Fundamentos de EXPLAIN - lectura del plan detrás de cualquiera de estas consultas
Versiones de Stack: Esta página fue escrita para PostgreSQL 18.4 (línea estable 18, línea de mantenimiento 17), donde el comportamiento de inserción de CTE introducido en PostgreSQL 12 y las mecánicas de funciones de ventana y consultas recursivas descritas aquí siguen siendo actuales.