Subconsultas y funciones de ventana

“El último pedido de cada cliente”, “cuánto representa cada venta sobre el total de su sucursal”, “el ranking de productos por mes”. Son preguntas cotidianas que con GROUP BY salen torcidas, porque agrupar pierde las filas individuales. Las funciones de ventana existen justamente para eso.

Correlacionada y no correlacionada

Una subconsulta es una consulta dentro de otra, y hay dos clases. La no correlacionada se calcula una sola vez y su resultado se usa como valor o como lista: “los productos cuyo precio supera el promedio general”.

La correlacionada referencia la consulta externa, así que conceptualmente se evalúa una vez por fila: “los empleados que ganan más que el promedio de su departamento”. Los optimizadores suelen reescribirla como join, pero si no pueden, el costo se multiplica por la cantidad de filas.

EXISTS, IN y NOT IN

Para preguntas de existencia, EXISTS es la forma más segura: corta apenas encuentra una fila y no sufre con los nulos. IN es equivalente y legible con listas chicas.

NOT IN es la que hay que evitar cuando la subconsulta puede devolver nulos: el resultado se vuelve vacío entero, sin error ni aviso. NOT EXISTS no tiene ese problema y expresa lo mismo. Es de los pocos casos donde conviene fijar una regla y no pensarlo cada vez.

Las CTE convierten lo anidado en una secuencia

Las expresiones comunes de tabla —WITH nombre AS (...)— dan nombre a un resultado intermedio y convierten una consulta anidada ilegible en una secuencia de pasos con nombre.

Además son la única forma estándar de escribir recursión en SQL: recorrer una jerarquía de categorías, una estructura de empleados y jefes, o un grafo de dependencias. Un detalle a tener en cuenta: en algunos motores la CTE se materializa —se calcula y guarda— y eso puede impedir que el filtro exterior se empuje hacia adentro.

La ventana calcula sin colapsar filas

Una función de ventana calcula sobre un conjunto de filas relacionadas con la actual, pero sin colapsarlas: cada fila conserva su identidad y gana una columna calculada.

La sintaxis es función() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY define los grupos —como un GROUP BY que no achica— y ORDER BY define el orden dentro de cada uno. Con eso, SUM(importe) OVER (PARTITION BY sucursal) pone el total de la sucursal al lado de cada venta.

Las pocas funciones que se usan de verdad

Las más útiles son pocas. ROW_NUMBER numera dentro de la partición: filtrando por el número uno se obtiene “el último pedido de cada cliente”, que es la consulta que más se escribe mal. RANK y DENSE_RANK hacen ranking manejando los empates distinto.

LAG y LEAD traen el valor de la fila anterior o siguiente: sirven para calcular variaciones contra el período previo sin hacer join de la tabla consigo misma. Y las acumuladas —SUM con un marco de filas— dan totales corridos y promedios móviles.

Dos detalles del orden de evaluación

Dos detalles importantes. Las funciones de ventana se evalúan después del WHERE y del GROUP BY, así que no se pueden filtrar en el mismo nivel: hay que envolver la consulta en una CTE o subconsulta y filtrar afuera. Es el paso que falta cuando WHERE ROW_NUMBER() = 1 da error.

Y el marco por defecto cuando hay ORDER BY dentro del OVER no es toda la partición sino desde el inicio hasta la fila actual, lo que convierte una suma en una suma acumulada sin que uno lo haya pedido. Si se quiere el total de la partición, hay que sacar el ORDER BY o declarar el marco completo.

Las ventanas que se usan todo el tiempo

SELECT
  o.id,
  o.customer_id,
  o.total,
  -- el número de pedido de ese cliente, del más nuevo al más viejo
  ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.created_at DESC) AS pedido_nro,
  -- cuánto gastó ese cliente en total, sin perder las filas
  SUM(o.total)  OVER (PARTITION BY o.customer_id)                           AS gastado,
  -- el total del pedido anterior del mismo cliente
  LAG(o.total)  OVER (PARTITION BY o.customer_id ORDER BY o.created_at)     AS anterior,
  -- acumulado en el tiempo
  SUM(o.total)  OVER (PARTITION BY o.customer_id ORDER BY o.created_at)     AS acumulado
FROM orders o;

Las cuatro resuelven cosas que con GROUP BY no salen: numerar dentro de un grupo, agregar sin colapsar filas, mirar la fila anterior y acumular en el tiempo. La diferencia entre las dos últimas es sólo el ORDER BY adentro del OVER: sin él, la suma es de toda la partición; con él, es acumulada hasta la fila actual.

El uso más frecuente es ROW_NUMBER seguido de quedarse con la primera: es la forma estándar de resolver «el último de cada grupo», y reemplaza subconsultas correlacionadas que el motor a veces no puede optimizar.

Subconsulta, join o ventana

Cierre

Subconsultas para preguntas anidadas, NOT EXISTS en lugar de NOT IN, CTEs para dar nombre a los pasos y recorrer jerarquías. Y funciones de ventana para calcular por grupo sin perder las filas: ROW_NUMBER para el último de cada uno, LAG para comparar con el período anterior, filtrando siempre un nivel afuera.