Funciones de ventana ① — OVER y PARTITION BY

Datos usados en este artículo — sales y employee

Una función de ventana calcula cosas como totales por grupo, rangos y totales acumulados fila a fila, y añade una sola columna a tu resultado.

Mientras que GROUP BY colapsa muchas filas en una sola fila resumen, una función de ventana deja cada fila de detalle en su lugar y solo agrega el valor calculado al lado.

Antes de saltar a los ejercicios, mira las definiciones de columnas y los datos de muestra de las dos tablas que usaremos: sales y employee.

① Ejecuta PRAGMA table_info(sales); y PRAGMA table_info(employee); para revisar las definiciones de columnas de ambas tablas.

② Ejecuta SELECT * FROM sales LIMIT 5; y SELECT * FROM employee LIMIT 5; para previsualizar las primeras 5 filas de cada una. Fíjate en que la columna dept_id de employee es NULL para algunos empleados.

OVER () — calcula sobre todas las filas y añade una columna

Empecemos con la forma más simple: aggregate(column) OVER ().

Con paréntesis vacíos, la ventana cubre el conjunto de resultados completo (cada fila seleccionada).

Añadir SUM(amount) OVER () como cuarta columna te da una nueva columna grand_total donde cada fila tiene el mismo valor (el total general).

El número de filas no cambia: solo obtienes una columna más.

-- OVER () con paréntesis vacíos cubre el conjunto de resultados completo
-- Las 5 filas de detalle se mantienen, y grand_total lleva el mismo valor en cada fila
SELECT sale_id, emp_id, amount,
  SUM(amount) OVER ()   AS grand_total,
  COUNT(*)    OVER ()   AS row_count,
  AVG(amount) OVER ()   AS grand_avg
FROM sales;

Imagina que quieres una lista de ventas individuales, con el total general y la participación de cada venta en ese total mostrados al lado. (Si tu consulta corre correctamente, aparecerá una explicación.)

① Selecciona sale_id, emp_id y amount de sales.

② Agrega SUM(amount) OVER () como cuarta columna con el alias grand_total. Cada fila llevará el mismo total general.

③ Agrega *amount 100.0 / SUM(amount) OVER ()** como quinta columna con el alias pct_of_total: la participación de cada fila en el total general.

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

Comparación con GROUP BY — colapsar o conservar las filas

Poner GROUP BY y una función de ventana lado a lado hace evidente la diferencia.

Con GROUP BY emp_id + SUM(amount), colapsas las filas de cada empleado en una sola fila que devuelve solo el total.

Con SUM(amount) OVER (), cada fila de detalle se queda en su sitio, y simplemente obtienes una columna más con el valor agregado.

Elige una u otra según lo que quieras: solo los totales, o tanto los totales como el detalle en una misma tabla.

-- GROUP BY: colapsa a 1 fila por empleado — el detalle desaparece
SELECT emp_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
WHERE emp_id = 2
GROUP BY emp_id;
-- resultado: 1 fila (emp_id=2, cnt=5, total=2150000)

-- Función de ventana: conserva las 5 filas de detalle, con el total general al lado
SELECT sale_id, emp_id, amount,
  SUM(amount) OVER () AS grand_total
FROM sales
WHERE emp_id = 2;
-- resultado: 5 filas, grand_total = 2150000 en cada fila

Escribe el mismo cálculo "dame el total" de dos formas — una con GROUP BY y otra con OVER () — y compara los resultados lado a lado.

① Empieza con la versión GROUP BY: filtra sales por emp_id = 2, luego selecciona emp_id, COUNT(*) AS cnt y SUM(amount) AS total, y agrega con GROUP BY emp_id. Observa cómo colapsa a 1 fila.

② Luego escribe la versión con función de ventana: conserva las filas de detalle de emp_id = 2 y añade SUM(amount) OVER () AS grand_total. Selecciona sale_id, emp_id, amount, grand_total. Deberías obtener 5 filas, cada una con el mismo total al lado.

③ Ejecuta ambas consultas en la misma consola y compara "1 fila" vs. "5 filas" con tus propios ojos. El verificador mira la salida de la función de ventana, así que termina ② con ORDER BY sale_id para fijar el orden.

PARTITION BY — divide la ventana en grupos

Donde OVER () trata todo el resultado como una sola ventana (el rango de filas sobre el que agregar), OVER (PARTITION BY column) divide la ventana según los valores de esa columna.

Con PARTITION BY emp_id, la ventana se divide por empleado, y cada fila obtiene el total de su propio empleado al lado.

En el diagrama de abajo, las 4 filas de emp_id=1 obtienen el mismo "emp_id=1 total = 970.000" añadido, y las 5 filas de emp_id=2 obtienen "emp_id=2 total = 2.150.000".

-- Añade total, promedio y conteo por empleado a cada fila de detalle
SELECT sale_id, emp_id, amount,
  SUM(amount)   OVER (PARTITION BY emp_id) AS emp_total,
  AVG(amount)   OVER (PARTITION BY emp_id) AS emp_avg,
  COUNT(*)      OVER (PARTITION BY emp_id) AS emp_count
FROM sales
ORDER BY emp_id, sale_id;

-- Porcentaje del total del empleado: ¿qué participación tiene esta venta del total de ese empleado?
SELECT sale_id, emp_id, amount,
  amount * 100.0 / SUM(amount) OVER (PARTITION BY emp_id) AS pct_of_emp
FROM sales
ORDER BY emp_id, sale_id;

Imagina que quieres mostrar cada venta junto al total, promedio y conteo de ventas de ese empleado.

① Selecciona sale_id, emp_id y amount de sales.

② Usando una ventana particionada por empleado, añade tres columnas con los alias emp_total (suma), emp_avg (promedio) y emp_count (conteo).

③ El verificador comprueba el orden de las filas, así que termina con ORDER BY emp_id, sale_id (ascendente por emp_id, luego por sale_id dentro de cada empleado).

Combina con JOIN para obtener agregaciones por departamento

Las funciones de ventana también funcionan sobre resultados de JOIN.

Haz JOIN entre sales y employee para traer dept_id (ID de departamento), luego PARTITION BY e.dept_id te permite adjuntar el total de ventas del departamento de ese empleado a cada fila de venta.

La columna de PARTITION BY puede ser una que hayas traído de otra tabla mediante JOIN.

Las funciones de ventana se evalúan después de FROM / JOIN / WHERE, una vez que el conjunto de filas está fijado: así el flujo es "JOIN para ensamblar las filas que necesitas → distribuir las agregaciones de ventana sobre ese conjunto de filas".

-- JOIN entre sales y employee y añade el total y conteo por departamento a cada detalle
SELECT s.sale_id, e.name, e.dept_id, s.amount,
  SUM(s.amount) OVER (PARTITION BY e.dept_id) AS dept_total,
  COUNT(*)      OVER (PARTITION BY e.dept_id) AS dept_sales
FROM sales AS s
JOIN employee AS e ON s.emp_id = e.emp_id
ORDER BY e.dept_id, s.sale_id;

Imagina que quieres un informe que liste cada venta con el nombre y departamento del empleado, además del total de ventas de ese departamento al lado.

① Haz INNER JOIN entre sales (alias s) y employee (alias e) por s.emp_id = e.emp_id.

② Selecciona s.sale_id, e.name, e.dept_id y s.amount.

③ Con una ventana particionada por departamento (PARTITION BY e.dept_id), añade dept_total como quinta columna mostrando el total de ventas del departamento.

④ El verificador comprueba el orden de las filas, así que termina con ORDER BY e.dept_id, s.sale_id (ascendente por dept_id, luego por sale_id dentro de cada departamento).