Funciones de ventana ② — ORDER BY y marcos (ROWS / RANGE)

Ventanas con ORDER BY — produciendo un total acumulado

Añade ORDER BY dentro de OVER y el objetivo de la agregación cambia a "desde el inicio de la ventana hasta la fila actual": el valor crece fila a fila, produciendo un total acumulado (la suma desde el inicio hasta la fila actual).

La forma es SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date).

PARTITION BY emp_id divide la ventana por empleado, ORDER BY sale_date ordena las ventas de cada empleado por fecha, y luego el total acumulado se calcula por empleado.

El diagrama de abajo traza cómo crece el total acumulado para las 5 ventas de emp_id=29 en orden de fecha.

-- Sin ORDER BY: cada fila obtiene el total invariable del empleado
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id) AS emp_total
FROM sales
ORDER BY emp_id, sale_date;

-- Con ORDER BY: total acumulado por empleado desde el inicio hasta la fila actual
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS running_total
FROM sales
ORDER BY emp_id, sale_date;

Imagina que quieres un total acumulado de ventas por empleado, ordenado por fecha. (Si tu consulta corre correctamente, aparecerá una explicación.)

① Selecciona emp_id, sale_date y amount de sales.

② Calcula SUM(amount) sobre una ventana particionada por empleado y ordenada por fecha, y añádelo como cuarta columna con el alias running_total.

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

El marco — qué filas se agregan realmente dentro de la ventana

Cuando añades ORDER BY a una función de ventana, el rango de filas sobre las que cada fila realmente agrega se llama el marco (frame).

Si no escribes el marco explícitamente, el valor por defecto es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (desde el inicio de la ventana hasta la fila actual).

Esa es exactamente la razón por la que añadir ORDER BY en el ejercicio anterior te dio un total acumulado: este marco por defecto hace el trabajo.

-- A: marco omitido (por defecto = RANGE UNBOUNDED PRECEDING ... CURRENT ROW)
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS run_default
FROM sales WHERE emp_id = 14
ORDER BY sale_date;

-- B: el marco por defecto escrito explícitamente (mismo resultado que A)
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS run_explicit
FROM sales WHERE emp_id = 14
ORDER BY sale_date;

Los marcos se construyen a partir de estas 5 palabras clave, que se usan para decir "desde dónde" y "hasta dónde" en relación con la fila actual.

Confirmemos el comportamiento de total acumulado escribiendo el marco explícitamente.

① Selecciona emp_id, sale_date y amount de sales.

② Sobre una ventana particionada por empleado y ordenada por sale_date, calcula SUM(amount) con el marco explícito ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, y añádelo como cuarta columna con el alias running_total.

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

ROWS BETWEEN n PRECEDING — sumas y promedios móviles

Establece el marco como ROWS BETWEEN n PRECEDING AND CURRENT ROW y la agregación se reduce a "las últimas n filas + la fila actual".

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW cubre las 3 filas más recientes (incluyendo la actual), de modo que SUM se convierte en una suma móvil y AVG en un promedio móvil.

Esta es la forma fundamental para suavizar una tendencia reciente.

Cerca del inicio de la ventana, el marco tiene menos filas de las que extraer (la fila 1 tiene 1 fila, la fila 2 tiene 2 filas, de la fila 3 en adelante tiene 3 filas).

El diagrama de abajo recorre las 5 ventas de emp_id=14 fila a fila, mostrando qué termina en el marco en cada "fila actual".

-- Promedio móvil de las 3 ventas más recientes (2 filas previas + actual)
SELECT emp_id, sale_date, amount,
  AVG(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg3
FROM sales WHERE emp_id = 2
ORDER BY sale_date;

-- La suma móvil de las 2 ventas más recientes (1 fila previa + actual) usa la misma forma
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_sum2
FROM sales WHERE emp_id = 2
ORDER BY sale_date;

Imagina que quieres una suma móvil por empleado sobre las 3 ventas más recientes (las 2 anteriores + la actual), ordenadas por fecha.

① Selecciona emp_id, sale_date y amount de sales.

② Sobre una ventana particionada por empleado y ordenada por sale_date, calcula SUM(amount) con el marco ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, y añádelo como cuarta columna con el alias moving_sum3.

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

ROWS vs. RANGE — cómo se tratan las filas empatadas

Los marcos vienen en dos modos de conteo: ROWS y RANGE.

ROWS cuenta filas físicas ("las 2 filas previas" significa exactamente 2 filas). Incluso con valores empatados, cada fila se cuenta por separado, así que el total acumulado crece fila a fila.

RANGE agrupa las filas que tienen el mismo valor de ORDER BY (empates) en un solo cubo. Las filas empatadas comparten un marco y terminan con el mismo total acumulado.

Los dos diagramas de abajo muestran cómo ROWS y RANGE manejan cada uno las 3 filas donde amount = 100000.

-- ORDER BY amount tiene 3 filas empatadas en 100000, así que ROWS y RANGE divergen
SELECT sale_id, amount,
  SUM(amount) OVER (ORDER BY amount ROWS  UNBOUNDED PRECEDING) AS rows_run,
  SUM(amount) OVER (ORDER BY amount RANGE UNBOUNDED PRECEDING) AS range_run
FROM sales
WHERE amount = 100000
ORDER BY sale_id;
-- ROWS cuenta una fila a la vez; RANGE agrupa las 3 filas empatadas de 100000 en un solo marco

Imagina que quieres ver, en un solo resultado, cómo cambia un total acumulado entre ROWS y RANGE cuando varias filas comparten el mismo valor. Hay 3 ventas donde amount = 100000.

① Selecciona sale_id y amount de sales.

② Sobre una ventana ordenada por amount, añade SUM(amount) con el marco ROWS UNBOUNDED PRECEDING con el alias rows_run, y otro SUM(amount) con el marco RANGE UNBOUNDED PRECEDING con el alias range_run.

③ Filtra solo las filas donde amount = 100000.

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