Funciones de ventana ③ — Rangos, funciones analíticas y orden de ejecución

Rangos — ROW_NUMBER / RANK / DENSE_RANK

Las funciones de rango dedicadas son ROW_NUMBER() / RANK() / DENSE_RANK().

Las tres numeran filas en el orden dado por OVER (ORDER BY ...), pero manejan los empates (valores iguales) de forma distinta.

El SQL de abajo, y el diagrama que visualiza su resultado, recorren un caso concreto con totales empatados fila a fila.

-- Calcula el total de ventas de cada empleado y luego clasifica en orden descendente de total de 3 formas
SELECT emp_id,
  SUM(amount) AS total,
  ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS rn,
  RANK()       OVER (ORDER BY SUM(amount) DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dr
FROM sales
GROUP BY emp_id
ORDER BY total DESC;
-- Puedes agregar primero con GROUP BY y luego clasificar esas agregaciones

Imagina que quieres un ranking de empleados por total de ventas. Hay empates, así que te gustaría verlos los tres lado a lado: numeración consecutiva, saltando los siguientes rangos y sin saltar. (Si tu consulta corre correctamente, aparecerá una explicación.)

① Haz GROUP BY a la tabla sales por emp_id y selecciona SUM(amount) con el alias total.

② Sobre una ventana ordenada por total descendente, añade ROW_NUMBER() con el alias rn, RANK() con el alias rk y DENSE_RANK() con el alias dr.

③ El verificador comprueba el orden de las filas, así que termina con ORDER BY total DESC, emp_id (descendente por total, ascendente por emp_id para empates).

LAG / LEAD — trae valores de la fila anterior / siguiente

LAG(column) trae el valor de la fila anterior a la fila actual, y LEAD(column) trae el de la fila siguiente (lag = quedarse atrás, lead = ir adelante).

OVER (PARTITION BY ... ORDER BY ...) decide qué significan "anterior" y "siguiente" estableciendo el orden.

Úsalas para diferencias periodo contra periodo (vs. la venta anterior), deltas vs. el último mes, o comparaciones contra el siguiente evento agendado.

LAG(amount) devuelve NULL en la primera fila de cada partición (no hay fila anterior); de manera similar LEAD es NULL en la última fila.

El segundo argumento opcional controla el desplazamiento ("cuántas filas atrás") y el tercero establece un valor por defecto cuando la fila objetivo falta: por ejemplo, LAG(amount, 1, 0).

Resta con amount - LAG(amount) OVER (...) para obtener directamente la diferencia vs. la fila anterior.

-- Muestra la venta anterior (LAG) y la diferencia vs. la venta anterior
SELECT emp_id, sale_date, amount,
  LAG(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS prev_amount,
  amount - LAG(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS diff_prev
FROM sales
ORDER BY emp_id, sale_date;

-- Usa LEAD si quieres mostrar la siguiente venta junto a la fila actual
SELECT emp_id, sale_date, amount,
  LEAD(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS next_amount
FROM sales
ORDER BY emp_id, sale_date;

Imagina que quieres una lista por empleado de ventas ordenadas por fecha, con la venta anterior y la diferencia vs. esa venta anterior junto a cada fila.

① Selecciona emp_id, sale_date y amount de sales.

② Sobre una ventana particionada por empleado y ordenada por sale_date, añade el amount de la fila anterior con el alias prev_amount.

③ También añade amount menos el amount anterior con el alias diff_prev.

④ El verificador comprueba el orden de las filas, así que termina con ORDER BY emp_id, sale_date.

NTILE — divide filas ordenadas en n grupos del mismo tamaño

NTILE(n) divide las filas ordenadas por OVER (ORDER BY ...) en n grupos del tamaño más parecido posible, etiquetando cada fila con un número de grupo de 1 a n.

Úsalo para cuartiles, quintiles, "top 25%" y agrupamientos similares.

Cuando el número de filas no es divisible por n, los grupos anteriores reciben una fila extra cada uno (por ejemplo, dividir 5 filas en 4 grupos da 2 / 1 / 1 / 1).

Combínalo con PARTITION BY para hacer cosas como "dividir las ventas de cada empleado en cubos superiores / inferiores dentro de ese empleado".

El diagrama de abajo muestra qué sucede cuando las 5 ventas de emp_id=11 se ordenan por amount desc y se dividen con NTILE(4).

-- Por empleado, divide las ventas de ese empleado en 4 grupos por amount desc
SELECT emp_id, sale_date, amount,
  NTILE(4) OVER (PARTITION BY emp_id ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY emp_id, amount DESC;

-- Divide todas las ventas en 4 cubos por amount (el top 25% tiene quartile = 1)
SELECT sale_id, emp_id, amount,
  NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY amount DESC;

Imagina que quieres una lista de ventas dividida en 4 grupos por empleado (por amount desc), mostrada en orden de cuartil desde el grupo superior hacia abajo.

① Selecciona emp_id, sale_date y amount de sales.

② Sobre una ventana particionada por empleado y ordenada por amount desc, calcula NTILE(4) y añádelo como cuarta columna con el alias quartile.

③ El verificador comprueba el orden de las filas, así que termina con `ORDER BY quartile, emp_id, sale_id` (ORDER BY puede referirse al alias quartile calculado en SELECT).

Imagina que quieres filtrar solo al top 25% (quartile = 1). Prueba añadiendo WHERE quartile = 1 a la consulta del Ejercicio 3.

① Usa el mismo SELECT del Ejercicio 3 y añade WHERE quartile = 1 después de FROM sales.

② Ejecutar esto devuelve un error (un error significa que lo hiciste bien).

③ La siguiente sección, "Orden de ejecución de SQL", explica por qué. quartile funcionó en ORDER BY pero no funciona en WHERE: ten ese rompecabezas en mente mientras seguimos.

Orden de ejecución de SQL — por qué no puedes poner una función de ventana en WHERE

Te puedes preguntar: "¿no puedo simplemente filtrar por el rango que calculé, como WHERE rk = 1?"

Obtendrías un error. La razón es el orden lógico de ejecución de SQL.

El orden en que escribes las cláusulas (SELECT → FROM → WHERE ...) no es el orden en que se evalúan.

La evaluación va aproximadamente: FROM / JOIN (ensamblar tablas) → WHERE (filtrar filas) → GROUP BY (agrupar) → HAVING (filtrar grupos) → SELECT (calcular columnas: aquí corren las funciones de ventana) → ORDER BY (ordenar) → LIMIT (truncar).

Como WHERE corre antes de SELECT, no puede ver el rango que SELECT está a punto de calcular.

Para filtrar por un rango, empuja la consulta que calcula la función de ventana a una subconsulta (o CTE) y aplica WHERE por fuera.

El SELECT interno termina de calcular la función de ventana, y la consulta externa puede entonces tratar ese resultado como una columna más.

-- Esto da error: WHERE corre antes de SELECT, así que rk aún no existe
-- SELECT emp_id, RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
-- FROM sales GROUP BY emp_id WHERE rk <= 3;

-- Correcto: calcula el rango en una subconsulta interna, filtra por fuera
SELECT * FROM (
  SELECT emp_id,
    SUM(amount) AS total,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
  FROM sales
  GROUP BY emp_id
) AS ranked
WHERE rk <= 3
ORDER BY rk, emp_id;

Imagina que quieres solo el top 3 de empleados por total de ventas. Como estás filtrando por un rango, necesitas una subconsulta para sortear el orden de ejecución.

① En la subconsulta interna, haz GROUP BY a sales por emp_id y selecciona SUM(amount) con el alias total, más RANK() OVER (ORDER BY SUM(amount) DESC) con el alias rk. Pon a la subconsulta el alias ranked.

② Filtra la consulta externa con WHERE rk <= 3.

③ El verificador comprueba el orden de las filas, así que termina con ORDER BY rk, emp_id (ascendente por rk, luego por emp_id para empates).