Funciones de ventana

Diagrama de SQL: tablas unidas con JOIN, filtrado SELECT y agregacion GROUP BY. — SQL: consultas, joins, agregacion y optimizacion de consultas.

Mapa conceptual

SQL
- Básico
  - SELECT
  - WHERE
  - ORDER BY
- JOINs
  - INNER
  - LEFT
  - FULL
- Agregación
  - GROUP BY
  - HAVING
- Avanzado
  - Subconsultas
  - CTEs
  - Ventana
- Rendimiento
  - EXPLAIN
  - Índices

Qué es una ventana

Una función de ventana opera sobre un conjunto de filas relacionadas con la fila actual: su ventana. A diferencia de una agregación normal, no reduce el número de filas: devuelve un valor por fila, calculado mirando a las demás. La cláusula OVER define esa ventana.

La cláusula OVER

OVER contiene tres piezas opcionales: PARTITION BY para dividir las filas en grupos independientes, ORDER BY para ordenarlas dentro de la partición y un marco (frame) para acotar qué porción del orden entra en el cálculo. Sin nada dentro de OVER, la ventana es toda la tabla.

PARTITION BY frente a GROUP BY

Ambos reparten filas en grupos, pero GROUP BY colapsa cada grupo a una fila y PARTITION BY mantiene todas. Por eso la ventana permite ver, para cada pedido, el total de su cliente junto a su propio importe: detalle y resumen lado a lado.

Funciones de ranking

ROW NUMBER asigna un entero único 1, 2, 3 por fila según el orden.
RANK empata en el mismo número y salta los siguientes.
DENSE RANK empata sin saltar: tras un empate viene el siguiente consecutivo.
Estas elegen, por ejemplo, el primer pedido de cada cliente o el top tres por categoría.

Acumulados y promedios móviles

SUM o AVG sobre una ventana ordenada fabrica series acumuladas: ventas acumuladas mes a mes, saldo corriente. Limitando el marco a un número de filas previas y actuales se obtienen medias móviles que suavizan fluctuaciones.

Desplazamientos: LAG y LEAD

LAG mira el valor de la fila anterior y LEAD el de la siguiente, dentro de la partición ordenada. Son la forma natural de calcular variaciones respecto al periodo previo: crecimiento intermensual, diferencia con la venta anterior, detección de huecos.

Primer y último

FIRST VALUE y LAST VALUE devuelven el extremo de la ventana ordenada; NTH VALUE, el enésimo. Útiles para comparar cada fila con la mayor o la inicial de su grupo sin una subconsulta aparte.

Elige la función de ventana que responde tu pregunta (ranking, acumulado, desplazamiento).
Define PARTITION BY para el nivel de independencia deseado.
Ordena con ORDER BY lo que la función deba leer en secuencia.
Ajusta el marco si solo quieres una ventana deslizante.
Filtra el resultado en una consulta externa, porque WHERE no ve las ventanas.

Por qué no se filtran en WHERE

Las ventanas se calculan después del WHERE y del GROUP BY, en la fase de selección. Por eso no puedes filtrar por un ROW NUMBER en el mismo WHERE: hay que envolver la consulta y filtrar fuera, o usar una CTE que exponga la columna de ventana.

Orden estable y empates

Si el ORDER BY de una ventana no desempata del todo, ROW NUMBER puede dar números distintos en cada ejecución entre filas empatadas. Para resultados reproducibles, añade una clave única al final del orden.

Ventanas y rendimiento

Ordenar grandes particiones cuesta; las ventanas obligan a ordenar por partición. Con índice adecuado sobre las columnas de partición y orden, el motor evita ordenar de nuevo. Ventanas gigantes sobre toda la tabla son el peor caso.

Cada función de ventana traduce una necesidad analítica común.
Función	Para qué sirve	Ejemplo de pregunta
RANK	poner en orden con empates	¿qué puesto ocupa este alumno?
SUM OVER	acumular ordenado	¿ventas acumuladas hasta hoy?
LAG	valor de la fila previa	¿crecí respecto al mes pasado?
ROW NUMBER	numerar sin saltos	¿cuál es el primer pedido del cliente?

Quieres el importe del pedido inmediatamente anterior del mismo cliente, fila a fila. ¿Qué usas?

Frame por defecto

Cuando escribes una agregación de ventana con ORDER BY y sin marco explícito, el marco por defecto va desde el inicio de la partición hasta la fila actual: por eso SUM con ORDER BY da un acumulado, no una media de todo el grupo. Entender el default evita sorpresas.

Comparar con el grupo

Restar a un valor su promedio de partición mide cuánto se desvía de su grupo: ventas de un día menos la media del mes. Es la base de normalizaciones y de detectar anomalías dentro de categorías.

Errores frecuentes

Filtrar por una columna de ventana en el WHERE de la misma consulta.
Olvidar el ORDER BY cuando la función lo necesita, obteniendo resultados arbitrarios.
No desempatar el orden y esperar un ROW NUMBER estable entre ejecuciones.

Una función de ventana reduce el número de filas del resultado, igual que GROUP BY.

Cuándo conviene

Para top N por categoría, acumulados, porcentajes dentro del grupo y comparaciones secuenciales, la ventana es más limpia y rápida que auto-uniones o subconsultas correlacionadas. Es la herramienta nativa del SQL analítico moderno.

Piezas de una ventana

Divide la ventana en grupos

PARTITION BY

Da el orden dentro del grupo

ORDER BY

Numero único sin empates

ROW NUMBER

Empata y salta

RANK

Valor de la fila anterior

LAG

Va de inicio a fila actual

Frame por defecto

Toca una tarjeta para ver la respuesta.

Ordena el modo de construir un ranking por categoría:

Arrastra cada ficha a su categoría (o tócala y luego toca la categoría). También puedes usar el teclado.

Use The Index, LukeVentanas, ordenación y su coste de ejecución.

Video complementario

Recurso audiovisual para reforzar los conceptos.

Escribe una consulta que muestre cada venta junto a la venta acumulada del mes para su producto. ¿Qué va en PARTITION BY y qué en ORDER BY dentro del OVER?

Tu texto se guarda sólo en este dispositivo.

SQLZooEjercicios interactivos de SQL en línea.

Funciones de ventana

Herramientas de la lección

Sobre este contenido

Ir a