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 / OFFSET

Debido 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.

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 WITH simple hoy en día se inserta como una subconsulta a menos que se referencie varias veces o se marque explícitamente como MATERIALIZED.
• "Las funciones de ventana necesitan GROUP BY para funcionar." Una función de ventana puede ejecutarse sobre todo el conjunto de resultados sin GROUP BY alguno, ya que PARTITION BY dentro de la cláusula OVER define su propia agrupación independiente del GROUP BY de la consulta.
• "LATERAL es solo una subconsulta correlacionada." Una subconsulta correlacionada en la lista SELECT solo puede devolver un valor escalar por fila externa, mientras que LATERAL en la cláusula FROM puede 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 con FILTER maneja columnas de pivote fijas y conocidas sin instalar nada adicional.

Preguntas Frecuentes

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 BY y 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.