Funciones de Ventana e Índices: Cada Fila con su Contexto
Otra vez PostgreSQL de verdad, compilado a WebAssembly, corriendo enteramente en tu navegador. Esta vez cada venta va a mantener su fila propia mientras le agregás un ranking, la venta anterior o un total que se acumula — y después vas a crear una tabla de 50.000 filas para ver con tus propios ojos cómo un índice cambia el plan real que arma Postgres.
Bienvenido
Este taller es la práctica de laboratorio de funciones de ventana (OVER, PARTITION BY, RANK/DENSE_RANK, LAG/LEAD) e índices con EXPLAIN en PostgreSQL. No vuelve a explicar la teoría desde cero —asume que ya viste la guía— y en cambio te da un PostgreSQL real para escribir cada consulta, viendo exactamente qué columna calcula cada función de ventana y qué plan de ejecución elige Postgres antes y después de crear un índice.
Si todavía no viste la teoría, repasa primero Funciones de Ventana, Índices y EXPLAIN antes de continuar. Este taller usa dos tablas: una chica (ventas, 14 filas) para las funciones de ventana, y una grande (ventas_grande, 50.000 filas generadas en el momento) para que un índice tenga algo real que acelerar.
Esta página corre su propia base de datos, independiente de cualquier otro taller que hayas abierto antes. Existe solo mientras esta pestaña siga abierta: si recargas la página, la cierras, o navegas a otra guía, se pierde todo y hay que empezar de nuevo desde "Prepara tu base de datos". Si te equivocás y querés reiniciar sin recargar, usa el botón "Reiniciar base de datos" de abajo.
✍️ Lo escribís vos
Cada bloque parte vacío. El enunciado te dice qué tenés que lograr — el código SQL lo escribís de cero.
🩻 Planes reales
Los planes de EXPLAIN que vas a ver salen de Postgres de verdad decidiendo sobre tus propios datos — no son ilustrativos como en la guía teórica.
🎲 Datos al azar en la tabla grande
ventas_grande se genera con valores aleatorios: tu tabla no va a tener las mismas filas que la de un compañero, pero el comportamiento de EXPLAIN que vas a observar es el mismo para todos.
Zona de pruebas libres
Este bloque queda disponible durante toda la guía. Usalo en cualquier momento para probar una idea propia, revisar el estado de una tabla con SELECT * FROM tabla;, o experimentar con algo que se te ocurra a mitad de un ejercicio — sin desordenar los bloques guiados.
Prepara tu base de datos
Este bloque no es un ejercicio — ya viene resuelto. Crea una tabla, ventas, con 14 filas de ejemplo. Ejecútalo tal cual, una sola vez, antes de seguir.
Crea ventas con columnas vendedor, categoria, monto y fecha: 4 vendedores (Marisol, Iván, Daniela, Esteban) con ventas en fechas distintas, para que LAG/LEAD y los totales acumulados tengan un orden real sobre el cual avanzar. Hay un empate a propósito (Bs 640, entre Iván y Daniela) para que RANK() y DENSE_RANK() tengan algo interesante que resolver.
El resultado final debería mostrarte 14 filas. Si contás menos o más, usa "🔄 Reiniciar base de datos" y vuelve a ejecutar este bloque — el resto del taller (hasta la sección de índices) asume exactamente estos datos.
1. ROW_NUMBER y PARTITION BY
Numerar cada fila sin perder ninguna — y reiniciar esa numeración por grupo cuando hace falta.
Escribe un SELECT sobre ventas que traiga vendedor, categoria, monto y una columna posicion con ROW_NUMBER() OVER (ORDER BY monto DESC).
Deberían salir 14 filas, numeradas de 1 a 14 según el monto — ni una fila de menos ni una de más. A diferencia de GROUP BY, una función de ventana nunca colapsa nada.
Ahora agrega PARTITION BY vendedor dentro del OVER, para que la numeración reinicie en 1 en cada vendedor. Trae vendedor, monto y posicion, ordenado por vendedor, posicion.
Siguen siendo 14 filas en total, pero ahora la columna posicion vuelve a empezar en 1 con cada vendedor: Marisol y Iván llegan hasta 4, Daniela y Esteban hasta 3.
1.3 — Antes de ejecutar, predice: ¿cuántas filas devuelve SELECT vendedor, COUNT(*) FROM ventas GROUP BY vendedor? ¿Y cuántas devuelve SELECT vendedor, ROW_NUMBER() OVER (PARTITION BY vendedor ORDER BY monto DESC) FROM ventas? Escribe las dos consultas en este mismo editor, una debajo de otra, para comprobarlo.
2. RANK y DENSE_RANK
Dos formas distintas de resolver un empate al numerar filas.
Filtra ventas por categoria = 'Electrónica' y trae vendedor, monto, RANK() OVER (ORDER BY monto DESC) AS posicion_rank y DENSE_RANK() OVER (ORDER BY monto DESC) AS posicion_dense, ordenado por monto DESC.
Deberían salir 5 filas de Electrónica. Iván y Daniela empatan en Bs 640 y comparten la posición 2 en las dos columnas. Después del empate, posicion_rank salta directo a 4 (Esteban, Bs 560) mientras que posicion_dense sigue de corrido en 3 — esa es toda la diferencia entre las dos funciones.
Repite la misma idea pero sobre toda la tabla ventas (sin el WHERE), trayendo también categoria. El mismo empate en Bs 640 sigue ahí, ahora en medio de las 14 filas.
3. LAG y LEAD
Mirar la fila anterior o siguiente de la misma partición, sin escribir ningún JOIN.
Trae vendedor, fecha, monto y LAG(monto) OVER (PARTITION BY vendedor ORDER BY fecha) AS venta_anterior, ordenado por vendedor, fecha.
Fíjate en venta_anterior: la primera fila de cada vendedor (según fecha) no tiene ninguna venta antes en su propia partición, así que Postgres devuelve NULL ahí — no un error ni un 0.
Agrega también LEAD(monto) OVER (PARTITION BY vendedor ORDER BY fecha) AS venta_siguiente a la misma consulta, para ver hacia atrás y hacia adelante en la misma fila.
Ahora cada vendedor tiene un NULL en venta_anterior en su primera fila, y un NULL en venta_siguiente en su última — el resto de las filas trae ambos valores.
4. Totales acumulados con SUM() OVER
Un SUM() que en vez de sumar toda la partición de una, va sumando fila a fila según el ORDER BY.
Trae vendedor, fecha, monto y SUM(monto) OVER (PARTITION BY vendedor ORDER BY fecha) AS acumulado, ordenado por vendedor, fecha.
En cada vendedor, acumulado empieza igual al primer monto y va sumando de a una venta. El último valor de cada vendedor coincide con el total real de sus ventas — por ejemplo, el de Marisol termina en Bs 1485 (320 + 890 + 95 + 180).
Ahora quita el PARTITION BY: trae fecha, vendedor, monto y SUM(monto) OVER (ORDER BY fecha) AS acumulado_general, ordenado por fecha. Sin partición, toda la tabla es una única ventana.
Esta vez el acumulado no reinicia nunca: crece de venta en venta a lo largo de toda la tabla, sin importar quién la vendió, hasta terminar en Bs 4840 — la suma total de las 14 ventas.
5. Índices y EXPLAIN
Con 14 filas, Postgres siempre va a elegir recorrer la tabla entera — es más rápido que buscar un atajo. Para ver un índice cambiar algo de verdad, hace falta una tabla grande.
Este bloque crea ventas_grande y la llena con 50.000 filas usando generate_series, con vendedor, categoría, monto y fecha aleatorios. Puede tardar unos segundos — es una tabla de verdad, no una simulación.
El COUNT(*) final debería mostrarte 50000. El ANALYZE del final no es cosmético: sin él, Postgres todavía no tiene estadísticas actualizadas de la tabla nueva, y los planes de EXPLAIN de abajo saldrían con estimaciones menos precisas.
Escribe EXPLAIN SELECT * FROM ventas_grande WHERE vendedor = 'Marisol'; — todavía sin ningún índice sobre vendedor.
El plan debería mostrar Seq Scan on ventas_grande: Postgres recorre la tabla entera para encontrar las filas de Marisol. Fíjate en rows — con 20 vendedores repartidos parejo, debería rondar el 5% de 50.000 (unas 2.500 filas), aunque tu número exacto va a variar un poco porque los datos se generaron al azar.
Crea un índice sobre esa columna: CREATE INDEX idx_ventas_grande_vendedor ON ventas_grande(vendedor);, y después ANALYZE ventas_grande; para que Postgres actualice sus estadísticas con el índice nuevo.
Vuelve a ejecutar exactamente el mismo EXPLAIN del ejercicio 5.1.
El plan cambió a Bitmap Heap Scan, apoyado en un Bitmap Index Scan sobre tu índice nuevo. Fíjate que rows sigue siendo prácticamente el mismo número que en el ejercicio 5.1 — Postgres sigue esperando encontrar la misma cantidad de filas. Lo que bajó fue el cost: ahora tiene un atajo para llegar a ellas en vez de recorrer la tabla entera.
🔍 ¿Por qué "Bitmap Heap Scan" y no "Index Scan"? Cuando el filtro selecciona una porción moderada de la tabla (ni una sola fila, ni casi todas), Postgres arma primero un mapa de bits con las posiciones que coinciden (Bitmap Index Scan) y después va a buscarlas de forma ordenada. El "Index Scan" puro y simple —fila por fila del índice— lo vas a ver más abajo, en el Reto final, con un filtro mucho más selectivo.
Repite el mismo EXPLAIN, pero esta vez con EXPLAIN ANALYZE en vez de EXPLAIN solo.
Además de todo lo de antes, ahora aparecen líneas nuevas: actual time=... con la cantidad real de filas encontradas, y al final Planning Time y Execution Time en milisegundos. Esta consulta se ejecutó de verdad — EXPLAIN solo la había estimado.
Escribe EXPLAIN SELECT * FROM ventas_grande WHERE categoria = 'Electrónica'; — filtrando por una columna que no tiene su propio índice.
El índice de vendedor no sirve para nada acá: el plan vuelve a mostrar Seq Scan. Cada índice cubre solo la columna para la que se creó — filtrar por categoria necesitaría su propio índice.
Reto final: ranking por grupo y un segundo índice
Sin reiniciar la base de datos, resuelve estos tres desafíos combinando funciones de ventana e índices.
- 1. Sobre
ventas(la tabla chica), encuentra la venta más alta de cada vendedor: usaRANK() OVER (PARTITION BY vendedor ORDER BY monto DESC)en una subconsulta, y filtra afuera solo las filas con posición 1. Debería darte 4 filas, una por vendedor. - 2. Sobre
ventas_grande, crea un índice sobrecategoria(idx_ventas_grande_categoria), correANALYZE, y repite elEXPLAINdel ejercicio 5.5 para confirmar que el plan ya no es un Seq Scan simple. - 3. Bonus: sin crear ningún índice nuevo, ejecuta
EXPLAIN SELECT * FROM ventas_grande WHERE id = 25000;— la tabla ya tiene un índice automático sobreidpor ser su clave primaria. Compará el tipo de plan con el de los pasos anteriores.
El punto 3 debería mostrarte Index Scan using ventas_grande_pkey con rows=1 — a diferencia del Bitmap Heap Scan de la sección anterior, acá el filtro es tan selectivo (una sola fila entre 50.000) que Postgres va directo al índice de la clave primaria, sin pasar por el mapa de bits intermedio.
Repaso
Ocho preguntas cortas sobre lo que acabas de observar en vivo. Tu progreso se guarda automáticamente en este navegador.
En el ejercicio 1.1 numeraste las 14 filas de ventas con ROW_NUMBER() OVER (ORDER BY monto DESC). Si en cambio hubieras usado GROUP BY vendedor con COUNT(*), ¿qué diferencia real habría en el resultado?
PARTITION BY reduce la cantidad de filas del resultado, igual que GROUP BY.
Creaste un índice sobre vendedor en ventas_grande y volviste a correr EXPLAIN sobre el mismo WHERE vendedor = 'Marisol'. El plan cambió de Seq Scan a Bitmap Heap Scan. ¿Qué NO cambió entre ambos planes?
En el ejercicio 5.5 filtraste ventas_grande por categoria, aun teniendo ya el índice sobre vendedor creado. El plan volvió a mostrar Seq Scan. ¿Por qué?
En el ejercicio 5.4 corriste EXPLAIN ANALYZE sobre la misma consulta ya optimizada con índice. ¿Qué información nueva aparece ahí que EXPLAIN (sin ANALYZE) no mostraba?
Clasifica cada función de ventana según qué tipo de cálculo hace.
Sobre el ejercicio 2.1: Iván y Daniela empataron en Bs 640 dentro de Electrónica, compartiendo la posición 2. El siguiente en la lista es Esteban, con Bs 560.
1. RANK() le asigna a Esteban la posición .
2. DENSE_RANK() en cambio le asigna a Esteban la posición .
En el ejercicio 3.1, la primera venta de cada vendedor (según fecha) mostró NULL en la columna venta_anterior. ¿Por qué?
Une cada elemento con lo que hace dentro de una función de ventana.
Cheat Sheet del taller
| Forma | Para qué sirve |
|---|---|
funcion() OVER (PARTITION BY col ORDER BY col2) | Sintaxis general de una función de ventana. |
ROW_NUMBER() | Numera 1, 2, 3... sin importar los empates. |
RANK() / DENSE_RANK() | Numeran con hueco tras un empate, o sin hueco. |
LAG(columna) / LEAD(columna) | Valor de la fila anterior o siguiente en 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. |
Seq Scan → Bitmap Heap Scan / Index Scan | Recorrido completo vs. uso de un índice para llegar directo. |
¿Sabías que...?
🎲 Tus datos, distintos a los de un compañero
ventas_grande se llenó con random() — cada estudiante generó una tabla con vendedores, montos y fechas distintos. Los números exactos de cost y rows que viste no van a coincidir con los de nadie más, pero el comportamiento (Seq Scan antes, Bitmap Heap Scan después) sí es el mismo para todos.
🧮 ANALYZE, sin EXPLAIN, también existe solo
El comando ANALYZE que corriste después de cada CREATE INDEX es, en realidad, un comando aparte de EXPLAIN: actualiza las estadísticas internas de la tabla. Postgres también lo corre automáticamente de fondo (autovacuum), pero en una tabla recién creada conviene forzarlo a mano.
🔑 Toda clave primaria ya trae su propio índice
Por eso el ejercicio bonus del Reto final (WHERE id = 25000) mostró un Index Scan sin que crearas ningún índice nuevo: PRIMARY KEY crea automáticamente un índice único apenas se declara la tabla.