OVER y PARTITION BY: agregar sin colapsar — SQL Avanzado — Iterixs

SQL Avanzado

Enroll to access all the lessons in this course.

Content

Lesson 10 of 29· Funciones de ventana: fundamentos

OVER y PARTITION BY: agregar sin colapsar

Reading

El problema que resuelve `OVER`

Imagina que tu jefa te pide un informe de nómina: quiere ver cada empleado con su sueldo, y al lado, la nómina total de su región para saber qué peso tiene cada persona sobre el total de su equipo.

Con lo que ya sabes, tienes dos herramientas y ninguna hace exactamente eso:

Un SELECT name, salary FROM employees te da una fila por empleado… pero sin el total de la región.
Un GROUP BY region te da el total de la región… pero colapsa los 8 empleados en 3 filas y pierdes los nombres.

Lo que necesitas es un total por grupo que aparezca repetido en cada fila de detalle. Esa es exactamente la superpotencia de las funciones de ventana: calculan un agregado sobre un conjunto de filas relacionadas sin colapsar la salida. Al terminar esta lección sabrás usar OVER(PARTITION BY …) para poner el agregado del grupo junto a cada fila, y tendrás claro cuándo usar esto en lugar de GROUP BY.

Esta es la primera de las funciones de ventana, la familia de herramientas que separa una consulta "correcta" de una consulta analítica de nivel profesional.

Primero, recordemos qué hace `GROUP BY`

Usamos la tienda TechAndino. Empecemos por lo conocido: la nómina total por región. Ejecuta esto y fíjate en cuántas filas devuelve.

sql

-- GROUP BY colapsa: 8 empleados -> 3 filas (una por región)
SELECT
  region,
  COUNT(*)   AS empleados,
  SUM(salary) AS nomina_region
FROM employees
GROUP BY region
ORDER BY region;

Tres filas: Andina, Global, Sur. Los nombres individuales desaparecieron. Eso es lo que significa "agregar colapsando": el GROUP BY funde todas las filas de un grupo en una sola.

El detalle se perdió. Y el informe que te pidieron necesita el detalle y el total a la vez.

`OVER (PARTITION BY …)`: agregar sin colapsar

Una función de ventana se escribe añadiendo la cláusula OVER (…) a una función de agregación (o a una función específica de ventana). El OVER le dice a PostgreSQL: "no colapses las filas; en su lugar, para cada fila, calcula este agregado sobre una ventana de filas relacionadas y ponlo aquí al lado".

PARTITION BY region define esa ventana: "las filas que comparten la misma región que la fila actual". Es el equivalente al GROUP BY, pero sin fundir las filas.

Ejecuta la versión con ventana y compárala con la anterior: ahora salen las 8 filas de empleados, cada una con la nómina de su región repetida.

sql

-- PARTITION BY conserva: 8 empleados -> 8 filas, con el total repetido
SELECT
  name,
  region,
  salary,
  SUM(salary) OVER (PARTITION BY region) AS nomina_region,
  COUNT(*)    OVER (PARTITION BY region) AS empleados_region
FROM employees
ORDER BY region, salary DESC;

Fíjate en la magia: Carlos, Jorge, Ana y Diego (región Andina) muestran todos la misma nomina_region y el mismo empleados_region = 4. El agregado se calculó una vez por partición y se repartió a cada fila del grupo, sin perder ni un nombre.

Como cada fila conserva su valor individual y tiene el total del grupo al lado, ahora puedes hacer cálculos que mezclan ambos niveles en una sola pasada. Por ejemplo, qué porcentaje de la nómina de su región representa cada persona:

sql

-- Detalle y total conviven: % del sueldo sobre la nómina de la región
SELECT
  name,
  region,
  salary,
  ROUND(
    salary * 100.0 / SUM(salary) OVER (PARTITION BY region),
    1
  ) AS pct_de_su_region
FROM employees
ORDER BY region, pct_de_su_region DESC;

Ese salary / SUM(salary) OVER (…) es imposible con un GROUP BY normal en una sola consulta, porque salary (nivel fila) y SUM(salary) (nivel grupo) coexisten. Con ventanas es directo: por eso son la herramienta favorita para rankings, participaciones, comparativas y acumulados.

`GROUP BY` vs `PARTITION BY`: la tabla que debes memorizar

Son parientes cercanos y es fácil confundirlos. Esta es la diferencia esencial:

Aspecto	`GROUP BY`	`PARTITION BY` (dentro de `OVER`)
Filas de salida	Una por grupo (colapsa)	Todas las filas de entrada (conserva)
¿Se ven las columnas de detalle?	No, solo las agrupadas y los agregados	Sí, cualquier columna de la fila
Dónde vive el agregado	En la única fila del grupo	Repetido en cada fila del grupo
Cuándo elegirlo	Quieres un resumen (un total por categoría)	Quieres detalle + su agregado en la misma fila
Se combina con	`HAVING`, `SELECT` de columnas agrupadas	Cualquier columna, otras ventanas, cálculos fila-a-fila

Regla mental rápida: si quieres menos filas, GROUP BY; si quieres las mismas filas con un extra al lado, PARTITION BY.

Anatomía de `OVER`

La cláusula OVER tiene tres partes, todas opcionales:

Parte	Qué hace	Ejemplo
`PARTITION BY`	Divide las filas en grupos independientes; la función se reinicia en cada grupo	`PARTITION BY region`
`ORDER BY`	Ordena las filas dentro de cada partición (habilita cálculos posicionales: acumulados, `LAG`, ranking)	`ORDER BY order_date`
Marco (`ROWS BETWEEN …`)	Acota qué filas concretas entran en el cálculo (medias móviles, etc.)	`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`

Si escribes OVER () vacío, la ventana es toda la tabla: el agregado se calcula sobre todas las filas y se repite en cada una. Pruébalo — es la forma más rápida de añadir un gran total a cada fila:

sql

-- OVER () vacio = toda la tabla como una unica ventana
SELECT
  name,
  region,
  salary,
  SUM(salary)     OVER () AS nomina_total,
  ROUND(AVG(salary) OVER (), 0) AS sueldo_promedio
FROM employees
ORDER BY salary DESC;

`ORDER BY` dentro de `OVER` cambia el resultado

Hay dos ORDER BY distintos en una consulta con ventanas y es clave no confundirlos:

El ORDER BY final de la consulta solo ordena cómo ves las filas.
El ORDER BY dentro de OVER (…) ordena las filas para el cálculo de la ventana, y al hacerlo activa un comportamiento acumulativo: la función considera las filas hasta la actual según ese orden.

Compara: sin ORDER BY el COUNT(*) cuenta toda la partición; con ORDER BY cuenta "cuántas van hasta aquí". Este es un total acumulado de pedidos completados por fecha:

sql

-- ORDER BY dentro de OVER -> comportamiento acumulado (running count)
SELECT
  order_id,
  order_date,
  COUNT(*) OVER (ORDER BY order_date, order_id) AS pedidos_acumulados,
  COUNT(*) OVER ()                              AS pedidos_totales
FROM orders
WHERE status = 'completado'
ORDER BY order_date, order_id;

pedidos_acumulados crece 1, 2, 3, … mientras pedidos_totales es constante. El único cambio es el ORDER BY dentro de OVER. En la lección siguiente veremos el marco de ventana (ROWS BETWEEN), que controla con precisión ese "hasta aquí"; por ahora quédate con la idea: ordenar dentro de la ventana convierte un agregado en un acumulado.

¿Cuándo se ejecutan las funciones de ventana?

Entender el orden lógico evita el 90 % de los errores. Las funciones de ventana se evalúan casi al final, después de WHERE, GROUP BY y HAVING, pero antes del SELECT de alias y del ORDER BY final. Por eso operan sobre las filas que ya pasaron el filtrado y la agregación:

Diagram

Este orden explica el bonus avanzado: como las ventanas corren después del GROUP BY, puedes envolver un agregado dentro de una función de ventana. Aquí calculamos las ventas de cada empleado (con GROUP BY) y, encima, el total de ventas de su región (con la ventana), todo en una consulta:

sql

-- Ventana SOBRE un agregado: ventas por empleado y total de su region
SELECT
  e.name,
  e.region,
  SUM(oi.quantity * oi.unit_price) AS ventas_empleado,
  SUM(SUM(oi.quantity * oi.unit_price)) OVER (PARTITION BY e.region) AS ventas_region
FROM employees e
JOIN orders o       ON o.employee_id = e.employee_id
JOIN order_items oi ON oi.order_id  = o.order_id
WHERE o.status = 'completado'
GROUP BY e.employee_id, e.name, e.region
ORDER BY e.region, ventas_empleado DESC;

El SUM(...) interno agrega por empleado; el SUM(...) OVER (PARTITION BY region) externo suma esos subtotales por región. Se lee de dentro hacia fuera. No te preocupes si aún se ve denso: es un ejemplo del techo al que llegarás; lo importante hoy es el OVER (PARTITION BY …) básico.

Errores comunes (y cómo evitarlos)

1. Usar una función de ventana en WHERE. Como las ventanas corren después del WHERE, esto falla:

sql

-- ❌ ERROR: window functions are not allowed in WHERE
SELECT name, salary
FROM employees
WHERE SUM(salary) OVER (PARTITION BY region) > 10000;

Solución: calcula la ventana en una subconsulta o CTE y filtra en el nivel de afuera:

sql

-- ✅ Correcto
WITH x AS (
  SELECT name, salary,
         SUM(salary) OVER (PARTITION BY region) AS nomina_region
  FROM employees
)
SELECT * FROM x WHERE nomina_region > 10000;

2. Confundir PARTITION BY con GROUP BY. Si tu consulta tiene GROUP BY, la función de ventana opera sobre las filas ya agrupadas, no sobre las filas originales. PARTITION BY no reduce filas por sí solo.

3. Olvidar que OVER () vacío = toda la tabla. Si querías el total por región pero escribiste OVER (), obtendrás el gran total repetido en todas las filas. Revisa siempre el PARTITION BY.

4. Esperar que ORDER BY dentro de OVER ordene la salida. No lo hace: solo afecta el cálculo. La presentación la controla el ORDER BY final de la consulta.

ReviewOVER y PARTITION BY

1 of 5

Tu turno: ejercicio calificado

Practica el patrón central de la lección: agregar por grupo sin colapsar las filas.

Quieres una lista con cada empleado (una fila por persona) y, al lado, cuántos empleados hay en su región. Debes obtener las 8 filas, no 3. Usa COUNT(*) OVER (PARTITION BY …) y ordena por employee_id.

Exercise · SQL

Escribe una consulta que devuelva una fila por empleado con las columnas name, region y empleados_region (la cantidad de empleados de su misma región), ordenada por employee_id. Debe usar COUNT(*) OVER (PARTITION BY region) y devolver las 8 filas.

sql

-- Completa la función de ventana para contar los empleados de cada región
-- SIN colapsar las filas: la salida debe tener UNA fila por empleado (8 en total).
SELECT
  name,
  region,
  -- TODO: cuenta cuántos empleados comparten la MISMA región que esta fila
  COUNT(*) OVER (/* completa aquí */) AS empleados_region
FROM employees
ORDER BY employee_id;

This is a preview. Enroll to run and check the exercise.

Recapitulación

Una función de ventana = una función de agregación + OVER (…). Calcula sobre un conjunto de filas relacionadas sin colapsar la salida.
PARTITION BY define la ventana ("las filas de mi mismo grupo"). Es el GROUP BY de las ventanas, pero conserva todas las filas y repite el agregado en cada una.
OVER () vacío usa toda la tabla como ventana.
Un ORDER BY dentro de OVER activa cálculos acumulados; el ORDER BY final solo ordena la presentación.
Las ventanas corren después de WHERE/GROUP BY/HAVING: por eso no van en WHERE (usa una CTE) y pueden envolver un agregado.

Con esto ya puedes poner el total del grupo junto a cada fila de detalle, el bloque de construcción de rankings, participaciones y acumulados. En la siguiente lección afinaremos exactamente qué filas entran en cada cálculo con el marco de ventana (ROWS BETWEEN).

←→to move between lessons

SQL Avanzado

OVER y PARTITION BY: agregar sin colapsar

El problema que resuelve OVER

Primero, recordemos qué hace GROUP BY

OVER (PARTITION BY …): agregar sin colapsar

GROUP BY vs PARTITION BY: la tabla que debes memorizar

Anatomía de OVER

ORDER BY dentro de OVER cambia el resultado