Funciones de Ventana, Índices y EXPLAIN
GROUP BY resume filas en un grupo. Pero a veces querés lo contrario: mantener cada fila individual y agregarle una columna calculada — un ranking, un total acumulado, el valor de la fila anterior. Para eso existen las funciones de ventana. Y una vez que tus tablas crecen de verdad, necesitás saber si Postgres está buscando fila por fila o usando un atajo: para eso están los índices y EXPLAIN.
👁 — vistas · Ing. Roy Carrasco, Facultad de Ingeniería de Sistemas · UAB
Scrollea para empezar — a la izquierda vas a ver la consulta y su resultado armarse solos, mientras leés a la derecha.
-- todavía no escribimos nada
Resumir sin perder el detalle
GROUP BY te da un total por categoría, pero colapsa las filas — ya no ves cada venta individual. ¿Y si querés un ranking de cada venta, o un total acumulado fila a fila, sin perder ni una sola fila de la tabla?
Las diez ventas, ordenadas
Arrancamos simple: todas las ventas de la tabla ventas, ordenadas de mayor a menor monto. Diez filas, ninguna función de ventana todavía.
Una columna nueva, ninguna fila perdida
ROW_NUMBER() OVER (ORDER BY monto DESC) numera cada fila según ese orden. OVER es lo que convierte a ROW_NUMBER en una función de ventana: le dice sobre qué conjunto de filas "mirar". Seguimos con las mismas diez filas — solo se agregó una columna.
Reiniciar la numeración por grupo
PARTITION BY vendedor divide la tabla en ventanas separadas, una por vendedor, y ROW_NUMBER() vuelve a contar desde 1 en cada una. A diferencia de GROUP BY, esto no reduce las filas: solo cambia cómo se numeran.
Dos formas distintas de manejar un empate
Entre las ventas de Electrónica hay un empate en Bs 640. RANK() le da el mismo puesto a los dos empatados y salta el siguiente número (1, 2, 2, 4). DENSE_RANK() también empata, pero no deja huecos (1, 2, 2, 3).
Mirar la fila anterior sin un JOIN
LAG(monto) OVER (PARTITION BY vendedor ORDER BY fecha) trae el monto de la venta anterior de ese mismo vendedor. La primera venta de cada uno no tiene anterior — por eso da NULL. LEAD() hace lo mismo pero mirando hacia adelante.
SUM() que se acumula fila a fila
SUM(monto) OVER (PARTITION BY vendedor ORDER BY fecha) ya no suma toda la partición de una — con ORDER BY adentro del OVER, cada fila suma solo hasta sí misma: un total acumulado real, venta tras venta.
Practicá lo que viste
Antes de seguir a índices, confirmá que OVER, PARTITION BY y las funciones de ranking quedaron claras.
Una tabla ventas tiene 10 filas. Le agregás ROW_NUMBER() OVER (ORDER BY monto DESC) AS posicion al SELECT. ¿Cuántas filas devuelve ahora?
Sobre ROW_NUMBER() OVER (PARTITION BY vendedor ORDER BY monto DESC).
1. La cláusula que abre una función de ventana, indicando sobre qué conjunto de filas opera, es .
2. Para que la numeración reinicie en cada vendedor, en vez de seguir de corrido en toda la tabla, se usa dentro del OVER.
Clasifica cada función de ventana según qué tipo de cálculo hace.
-- todavía no escribimos nada
Una tabla que ya no es chiquita
La tabla ventas en producción no tiene 10 filas: tiene 200.000. Buscar las ventas de un vendedor puntual obliga a Postgres a revisar fila por fila, una por una, si no le damos una forma más rápida de encontrarlas.
Ver el plan antes de correr la consulta
EXPLAIN antepuesto a un SELECT no ejecuta la consulta de verdad: le pregunta al planificador de Postgres cómo piensa resolverla, y con qué costo estimado. Acá dice Seq Scan: va a revisar la tabla entera, fila por fila.
Construir un atajo hacia los datos
CREATE INDEX arma una estructura aparte (un árbol B-tree, ordenado) sobre la columna vendedor, que le permite a Postgres saltar directo a las filas que buscás en vez de mirarlas todas.
De Seq Scan a Index Scan
La misma consulta de antes, ahora con el índice creado. El plan cambió a Index Scan, y el costo estimado bajó de miles a cientos. Fijate que la cantidad de filas esperadas (rows) es la misma que antes — lo que cambió es cómo las busca, no cuántas espera encontrar.
El plan más los tiempos reales
EXPLAIN ANALYZE sí ejecuta la consulta de verdad, y le suma al plan los tiempos reales y la cantidad de filas que realmente encontró. Fijate que el estimado (14.238) y el real (14.251) casi nunca coinciden exactamente — y está bien que sea así.
Un índice no acelera todo
El índice que creamos es sobre vendedor. Si ahora filtramos por categoria, ese índice no sirve de nada — Postgres vuelve a un Seq Scan. Cada índice cubre solo las columnas para las que se creó.
Practicá lo que viste
Emparejá cada concepto con lo que hace, y confirmá por qué un índice no es magia universal.
Une cada término con su descripción.
Creaste un índice sobre vendedor, pero WHERE categoria = 'Electrónica' sigue mostrando Seq Scan en el plan. ¿Por qué?
Ejercicios de razonamiento
En estas preguntas no se te pide recordar sintaxis: se te da un escenario, y debés razonar cuál sería la respuesta correcta antes de comprobarla.
Necesitás mostrar cada venta individual junto con el total acumulado de ventas de su vendedor hasta esa fecha, sin resumir ni perder ninguna fila.
¿Qué usarías para resolver esto?
Querés el top 3 de ventas por categoría, pero si hay empate justo en el 3er lugar, necesitás incluir a todos los empatados, no cortar a la mitad.
¿RANK(), DENSE_RANK() o ROW_NUMBER() encaja mejor acá?
Un compañero de equipo propone crear un índice sobre cada columna de la tabla ventas, "para que todas las consultas sean instantáneas".
Completá la explicación que le darías:
Un índice acelera las consultas que esa columna en el WHERE, pero cada índice de más hace que INSERT, UPDATE y DELETE queden más , porque Postgres también tiene que mantenerlo actualizado.
Actividades de repaso
Un repaso integrador de todo el módulo. Cada actividad se corrige al instante; tu progreso se guarda automáticamente en este navegador.
¿Qué le pasa al número de filas del resultado cuando agregás una función de ventana (OVER) a un SELECT?
PARTITION BY hace lo mismo que GROUP BY: agrupa filas y las colapsa en un resultado por grupo.
¿Cuál es la diferencia entre RANK() y DENSE_RANK() ante un empate?
¿Qué diferencia real hay entre EXPLAIN y EXPLAIN ANALYZE?
Después de crear un índice sobre una columna, ¿qué le pasa exactamente a los datos de la tabla?
Cheat Sheet
| Forma | Para qué sirve |
|---|---|
funcion() OVER (PARTITION BY col ORDER BY col2) | Sintaxis general de una función de ventana. |
ROW_NUMBER() | Numera las filas 1, 2, 3... sin importar los empates. |
RANK() | Numera con huecos si hay empate (1, 1, 3). |
DENSE_RANK() | Numera sin huecos aunque haya empate (1, 1, 2). |
LAG(columna) | Valor de la fila anterior dentro de la partición. |
LEAD(columna) | Valor de la fila siguiente dentro de la partición. |
SUM(columna) OVER (...ORDER BY...) | Total acumulado (running total), fila a fila. |
CREATE INDEX nombre ON tabla(columna); | Crea un índice B-tree sobre esa columna. |
EXPLAIN consulta; | Muestra el plan estimado, sin ejecutar la consulta. |
EXPLAIN ANALYZE consulta; | Ejecuta la consulta de verdad y suma los tiempos reales. |
¿Sabías que...?
📜 Llegaron después que GROUP BY
GROUP BY es parte del estándar SQL desde 1992 (SQL-92). Las funciones de ventana recién se incorporaron al estándar en SQL:2003, más de una década después — Postgres las soporta desde su versión 8.4 (2009).
🎯 EXPLAIN nunca adivina al azar
El costo estimado que muestra EXPLAIN sale de estadísticas reales que Postgres guarda sobre cada tabla (cuántas filas tiene, cómo se distribuyen los valores). El comando ANALYZE (sin EXPLAIN) es justamente el que actualiza esas estadísticas.
🌳 B-tree no es el único tipo de índice
B-tree es el índice por defecto de PostgreSQL y el más común, pero no el único: existen también GIN (para arrays y JSON) y GiST (para datos geométricos o búsqueda de texto completo), cada uno pensado para un tipo de dato distinto.