Funciones de ventana

En resumen: Las funciones de ventana con OVER y PARTITION BY calculan sobre un grupo de filas sin colapsarlas: rankings, totales acumulados o la fila anterior con LAG.

Problema

El promedio, pero también las filas

El precio promedio de cada categoría ya lo obtienes con GROUP BY:

SQLbase de datos: tienda

SELECT category_id, ROUND(AVG(price), 2) AS category_avg
FROM products
GROUP BY category_id;

SELECT category_id, ROUND(AVG(price), 2) AS category_avg FROM products GROUP BY category_id;

▶ EjecutarRestaurar la consultaBase nueva

Pero la gerencia quiere una tabla con todos los productos y, junto a cada uno, el promedio de su categoría, para ver cuánto se aparta cada uno. GROUP BY ha aplastado los productos: de 25 filas quedan 7. En la lección sobre subconsultas correlacionadas lo hiciste con una subconsulta para cada fila. Hay un camino más corto.

La idea

OVER y PARTITION BY

SQLbase de datos: tienda

SELECT name, category_id, price,
  ROUND(AVG(price) OVER (PARTITION BY category_id), 2) AS category_avg
FROM products;

SELECT name, category_id, price, ROUND(AVG(price) OVER (PARTITION BY category_id), 2) AS category_avg FROM products;

▶ EjecutarRestaurar la consultaBase nueva

AVG(price) OVER (PARTITION BY category_id) calcula el promedio sobre el grupo de la fila pero no aplasta las filas: están los 25 productos, cada uno con el promedio de su propia categoría.

OVER (…) dice sobre qué filas se calcula. Ese grupo de filas se llama ventana, y una función con OVER se llama función de ventana. PARTITION BY divide las filas en grupos, como GROUP BY, pero sin fusionarlas.

Una diferencia con la subconsulta correlacionada: para la tarjeta de regalo el promedio es 20 €, no NULL. PARTITION BY mete todos los NULL en un grupo, como hace GROUP BY, mientras que p2.category_id = p.category_id no era verdadero para NULL.

La idea

Un puesto en un ranking

ROW_NUMBER() da el número de la fila en la ventana. Con ORDER BY en OVER indicas cómo se ordenan las filas:

SQLbase de datos: tienda

SELECT name, category_id, price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
FROM products;

SELECT name, category_id, price, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place FROM products;

▶ EjecutarRestaurar la consultaBase nueva

En cada categoría la numeración empieza de nuevo, por el producto más caro: el producto más caro de «Cocina» (categoría 8) es el juego de cuchillos, con el número 1.

El paso natural siguiente es WHERE place = 1, para quedarte solo con los primeros. Pero:

SQLbase de datos: tienda

SELECT name, category_id, price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
FROM products
WHERE place = 1;

SELECT name, category_id, price, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place FROM products WHERE place = 1;

▶ EjecutarRestaurar la consultaBase nueva

Las funciones de ventana se calculan después de WHERE: primero se eligen las filas y luego se organizan en ventanas. Por eso el ranking va en un paso, y el filtro va fuera.

Pruébalo

El más caro de cada categoría

Muestra para cada categoría su nombre (columna category), su producto más caro y el precio (columnas name y price). El campo ya numera los productos. Ponlo en WITH y toma solo los primeros.

SQLbase de datos: tienda

SELECT name, category_id, price,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place
FROM products;

SELECT name, category_id, price, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS place FROM products;

Comprobar▶ Ejecutar

Míralo

Puestos compartidos

Ahora un ranking por unidades vendidas en «Pan y bollería» (categoría 3), con las tres funciones una al lado de otra:

SQLbase de datos: tienda

WITH sold AS (
  SELECT p.name, p.category_id, SUM(oi.quantity) AS sold
  FROM products p
  JOIN order_items oi ON oi.product_id = p.id
  GROUP BY p.id, p.name, p.category_id
)
SELECT name, sold,
  ROW_NUMBER() OVER (ORDER BY sold DESC, name) AS row_number,
  RANK() OVER (ORDER BY sold DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY sold DESC) AS dense_rank
FROM sold
WHERE category_id = 3;

WITH sold AS ( SELECT p.name, p.category_id, SUM(oi.quantity) AS sold FROM products p JOIN order_items oi ON oi.product_id = p.id GROUP BY p.id, p.name, p.category_id ) SELECT name, sold, ROW_NUMBER() OVER (ORDER BY sold DESC, name) AS row_number, RANK() OVER (ORDER BY sold DESC) AS rank, DENSE_RANK() OVER (ORDER BY sold DESC) AS dense_rank FROM sold WHERE category_id = 3;

▶ EjecutarRestaurar la consultaBase nueva

1. La banitsa de queso y el pan blanco tienen 16 unidades vendidas cada uno. ¿Qué puesto les da RANK, y qué puesto da DENSE_RANK al bollo de mantequilla después de ellos?

RANK: 1 y 1, el bollo es el 3.º; DENSE_RANK: el bollo es el 2.ºLos dos le dan el 3.er puesto al bolloRANK: 1 y 2; DENSE_RANK: 1 y 1

Comprobar

• ROW_NUMBER da números distintos incluso cuando hay empate. Cuál de los dos va primero lo decide la segunda ordenación (aquí el nombre). Sin ella, no se promete nada.
• RANK da el mismo puesto a las filas empatadas y luego se salta: 1, 1, 3.
• DENSE_RANK da el mismo puesto y no se salta nada: 1, 1, 2.

Para «los 3 mejores productos», elige a propósito: con ROW_NUMBER salen exactamente tres, con RANK salen todos los que comparten un puesto.

La idea

Total acumulado

Con ORDER BY en OVER, la ventana de cada fila es «desde el principio hasta aquí». Cuánto hemos vendido desde el 1 de septiembre hasta cada día (sin los pedidos cancelados):

SQLbase de datos: tienda

WITH daily AS (
  SELECT o.ordered_at AS day, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.ordered_at >= '2026-09-01' AND o.status <> 'cancelled'
  GROUP BY o.ordered_at
)
SELECT day, ROUND(revenue, 2) AS revenue,
  ROUND(SUM(revenue) OVER (ORDER BY day), 2) AS running_total
FROM daily
ORDER BY day;

WITH daily AS ( SELECT o.ordered_at AS day, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.ordered_at >= '2026-09-01' AND o.status <> 'cancelled' GROUP BY o.ordered_at ) SELECT day, ROUND(revenue, 2) AS revenue, ROUND(SUM(revenue) OVER (ORDER BY day), 2) AS running_total FROM daily ORDER BY day;

▶ EjecutarRestaurar la consultaBase nueva

running_total crece fila a fila: el 3 de octubre llega a 415,30 € y el 19 de octubre es de 480,30 €. Esto se llama total acumulado (running total).

Más precisamente, para curiosos

Si dos filas tienen el mismo valor en ORDER BY, por defecto las dos reciben la suma incluyendo a la otra. Aquí cada día es una fila, así que no puede pasar. Si ordenas pedidos por fecha y un día tiene dos pedidos, añade una segunda ordenación, como ORDER BY ordered_at, id, o escribe explícitamente ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Pruébalo

Cuántos clientes tuvimos

Muestra para cada cliente el nombre, la fecha de registro y cuántos clientes tenía la tienda en esa fecha, incluyéndolo a él (columna customers_so_far).

SQLbase de datos: tienda

SELECT name, joined_at FROM customers
ORDER BY joined_at;

SELECT name, joined_at FROM customers ORDER BY joined_at;

Comprobar▶ Ejecutar

La idea

LAG: la fila anterior

LAG(column) da el valor de la fila anterior de la ventana. ¿Cuántos días pasan entre los pedidos de Maria?

SQLbase de datos: tienda

SELECT id, ordered_at,
  LAG(ordered_at) OVER (ORDER BY ordered_at) AS previous,
  julianday(ordered_at) - julianday(LAG(ordered_at) OVER (ORDER BY ordered_at)) AS days
FROM orders
WHERE customer_id = 1;

SELECT id, ordered_at, LAG(ordered_at) OVER (ORDER BY ordered_at) AS previous, julianday(ordered_at) - julianday(LAG(ordered_at) OVER (ORDER BY ordered_at)) AS days FROM orders WHERE customer_id = 1;

▶ EjecutarRestaurar la consultaBase nueva

previous es la fecha de su pedido anterior. julianday convierte una fecha en un número de día, así que la diferencia es el número de días entre los dos pedidos. El primer pedido no tiene anterior y recibe NULL. La función contraria es LEAD: la fila siguiente.

Ejercicio

Dos tareas

1. El escaparate del sitio web muestra dos productos de cada categoría, los más caros.

Muestra para cada categoría su nombre (columna category) y sus dos productos más caros con sus precios (columnas name y price).

SQLbase de datos: tienda

Comprobar▶ Ejecutar

2. Marketing quiere saber cuál es la espera más larga de cada cliente entre dos de sus pedidos.

Muestra para cada cliente con al menos dos pedidos el nombre y la pausa más larga en días entre dos de sus pedidos consecutivos (columna longest_pause).

SQLbase de datos: tienda

Comprobar▶ Ejecutar

Test

Ponte a prueba

1. ¿Qué diferencia hay entre GROUP BY y OVER (PARTITION BY …)?

GROUP BY aplasta cada grupo en una fila, mientras que OVER conserva todas las filas y añade el resultado a cada unaOVER solo funciona con ORDER BYNinguna

2. ¿Por qué WHERE place = 1 da un error si place es ROW_NUMBER() OVER (…)?

Porque place es una palabra reservadaPorque ROW_NUMBER empieza en 0Porque las funciones de ventana se calculan después de WHERE

3. ¿Qué da SUM(revenue) OVER (ORDER BY day)?

La suma solo del día actualEl total general en cada filaLa suma desde el primer día hasta el actual: un total acumulado

4. ¿Qué devuelve LAG(ordered_at) en la primera fila?

NULL, porque no hay fila anteriorUn errorLa fecha de la misma fila

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: JOIN: dos tablas en una consulta · Subconsultas · Subconsultas correlacionadas · Un informe por pasos: WITH
• Continúa en: Flujos de eventos · Bases de datos columnares y series temporales · La panadería, etapa 6: el informe, la copia y DROP
• Ejercicios sobre el tema: Subconsultas y ventanas