Lección 7.1 · OVER: calcular sin agrupar
Funciones de ventana
El resumen junto a cada fila: totales, rankings y acumulados.
OVER: calcular sin agrupar
El resumen junto a cada fila.
Lo que dice la voz
La gerencia quiere saber cuánto aporta cada vendedor a la venta de su propia sucursal, en porcentaje, para comparar equipos de distinto tamaño. Muchas preguntas son así: comparan una fila con el total de su grupo: qué parte de la venta del mes fue esta boleta, cuánto se aleja este precio del promedio de su categoría, qué peso tiene cada vendedor en su sucursal. Group by responde con una fila por grupo y pierde el detalle, y una subconsulta sirve, pero se alarga. Este módulo trata de las funciones de ventana, que calculan un resumen y lo dejan junto a cada fila, sin juntar nada. Hoy vas a aprender over y partition by. En la siete punto dos vienen los rankings, y en la siete punto tres, casos prácticos.
Lo que dice la voz
Partamos por lo conocido. Avg del precio, sin más, da una sola fila, el promedio del catálogo. ¿Y para verlo al lado de cada producto? En el curso básico usaste una subconsulta de un valor dentro del select, y funciona: cada fila trae su nombre, su precio y el mismo promedio. El problema aparece cuando quieres el promedio de su categoría en vez del de todo. La subconsulta tiene que ser correlacionada, como en la lección dos punto uno, y la consulta se alarga. Las ventanas resuelven las dos cosas con la misma idea.
Lo que dice la voz
Es la misma pregunta, pero ahora avg va seguido de la palabra over y un par de paréntesis vacíos, sin subconsulta y sin group by. ¿Cuántas filas crees que salen, y qué trae la columna promedio?
Lo que dice la voz
Salen los catorce productos, con el mismo promedio en todos, veintisiete mil ochocientos cuarenta y siete con decimales. Over, que significa sobre, convierte la función de resumen en una función de ventana. En vez de juntar las filas en una, calcula el promedio y lo pone en cada fila. A esto le llamamos ventana: el conjunto de filas que la función mira. Con los paréntesis vacíos, la ventana son todas las filas del resultado.
Lo que dice la voz
Como la ventana deja el promedio en la misma fila, ¿puedes hacer cuentas con él? Claro. Aquí se le resta al precio el promedio redondeado, y el resultado dice cuánto se aleja cada producto del promedio del catálogo. El monitor está noventa y dos mil pesos por encima, y la carpeta, casi veintiséis mil por debajo. Fíjate en que el order by usa el alias diferencia. Puede, porque el orden se aplica cuando la ventana ya está calculada.
Lo que dice la voz
Un caso típico de reporte. La CTE, del módulo tres, calcula el total de cada boleta de septiembre. Afuera, una suma con over y los paréntesis vacíos junta las cincuenta y cuatro boletas del mes. Da cinco millones seiscientos veintitrés mil pesos. La última columna divide cada total por esa suma: la participación de la boleta en el mes. El cien escrito con decimal evita la división entera, como en el módulo cinco. Ahora un detalle importante. El limit muestra seis filas. ¿Cuántas sumó la ventana? Las cincuenta y cuatro, porque el limit se aplica al final.
Lo que dice la voz
Ahora el promedio de cada categoría. Dentro del over se escribe partition by, que significa partir por, y la columna que forma los grupos. Ahora la ventana de cada fila mira solo las de su misma categoría. Oficina promedia nueve mil novecientos noventa; tecnología, treinta y seis mil novecientos noventa; hogar, cuarenta y ocho mil trescientos veintitrés. Mira la pizarra. ¿Está cara o barata? Casi en el promedio del catálogo, pero muy por encima del de su categoría. Es la misma respuesta que daba la correlacionada de la dos punto uno. Y siguen saliendo catorce filas.
Lo que dice la voz
Volvamos a las boletas de septiembre. Si parto por la sucursal, la suma de la ventana pasa a ser la venta de la sucursal de cada boleta en el mes. Así, cada boleta se compara con las de su propia tienda. ¿Pesa igual una boleta de Antofagasta que una de Santiago del mismo monto? No: la de Antofagasta pesa más dentro de su sucursal, porque Antofagasta vende menos. Y en una consulta caben varias ventanas, una sin partición y otra con partición, cada una con su over.
Lo que dice la voz
Group by y las ventanas se pueden usar juntos, porque la ventana se calcula después de agrupar. Aquí el group by deja tres filas, una por sucursal, con sus ventas. Esa expresión rara, una suma dentro de otra suma con over, se lee de adentro hacia afuera. El sum de adentro es la venta de cada sucursal, y el de afuera, con over, suma esas tres filas. El resultado es la participación, y Santiago vende el cuarenta y nueve por ciento de la tienda. En la seis punto tres lo hacías con una subconsulta del total. Esta forma no repite la consulta.
Lo que dice la voz
Se quiere contar los productos de cada categoría con una ventana partida por categoría, pero la consulta también agrupa por categoría. El catálogo tiene catorce productos. ¿Qué crees que dice cada categoría?
Lo que dice la voz
Uno, en las tres. Es un error que no avisa. El group by ya dejó una fila por categoría, y la ventana cuenta esas filas: en cada partición hay una sola. Si hubo group by, la ventana nunca ve las filas originales: ve lo que quedó. ¿Cómo cuentas productos, entonces? O agrupas con count sin over, o usas la ventana sin group by. Mezclarlas por costumbre da cifras que parecen razonables y están mal.
En qué momento se calcula
FROM y JOIN, después WHERE, después GROUP BY y HAVINGORDER BY y LIMITSELECT y en el ORDER BY, no en WHERE ni en HAVING| nombre | id_categoria | precio |
|---|---|---|
| Silla de escritorio | 3 | 89990 |
| Lámpara de escritorio | 3 | 19990 |
| Repisa de madera | 3 | 34990 |
| nombre | precio | promedio |
|---|---|---|
| Silla de escritorio | 89990 | 48323 |
| Lámpara de escritorio | 19990 | 48323 |
| Repisa de madera | 34990 | 48323 |
Lo que dice la voz
Todo lo anterior se explica con el orden de ejecución que conoces desde el curso básico, con un paso nuevo. Primero se arman las filas con from y los join; el where las filtra; el group by las agrupa y el having filtra los grupos. Las ventanas se calculan recién ahí, sobre las filas que sobrevivieron. Después viene el order by, y al final el limit. De esto sale que un where cambia lo que suma la ventana, y que, si hubo group by, la ventana ve los grupos. Y una ventana no puede ir en el where ni en el having, porque cuando esas cláusulas corren, todavía no existe.
Lo que dice la voz
Queremos los productos más caros que el promedio de su categoría. La primera idea es poner la ventana en el where, y el motor responde misuse of window function avg: mal uso de una función de ventana. No es una rareza de SQLite: MySQL tampoco lo permite. ¿Y si le doy un alias en el select y lo uso en el where? Tampoco sirve: el error cambia a misuse of aliased window function, pero es el mismo. En el having pasa igual.
Lo que dice la voz
La solución es separar los pasos. La CTE calcula la ventana y le da un nombre a la columna, prom. Afuera, esa columna ya es un valor como cualquier otro, y el where la puede usar. Salen cuatro: la calculadora y la pizarra en oficina, el monitor en tecnología y la silla en hogar. Una subconsulta en el from hace lo mismo. Esta idea, calcular la ventana adentro y filtrar afuera, la vas a usar en todo el módulo, empezando por los rankings de la próxima lección.
Lo que dice la voz
En el order by sí se puede, porque se ejecuta después de las ventanas, aunque la ventana no aparezca en el select. Sirve cuando el orden depende de un cálculo que el lector no necesita ver. Por ejemplo, compras pide revisar los tres productos más caros en comparación con su propia categoría, y la lista lleva solo el nombre y el precio. Esta consulta ordena por cuánto supera cada precio al promedio de su categoría, sin mostrar ese promedio. Salen el monitor, la silla y la pizarra. ¿Y si la diferencia sí se va a mostrar? Yo la calculo en el select y ordeno por su alias. Fuera del select, el order by es el único lugar donde una ventana es válida.
¿GROUP BY, subconsulta o ventana?
GROUP BY: quieres una fila por grupoCOUNT(DISTINCT ...) OVER no existe en SQLite, MySQL ni SQL Server| nombre | id_categoria | precio |
|---|---|---|
| Silla de escritorio | 3 | 89990 |
| Lámpara de escritorio | 3 | 19990 |
| Repisa de madera | 3 | 34990 |
| nombre | id_categoria | precio | prom |
|---|---|---|---|
| Silla de escritorio | 3 | 89990 | 48323 |
| Lámpara de escritorio | 3 | 19990 | 48323 |
| Repisa de madera | 3 | 34990 | 48323 |
| MySQL | Las ventanas existen desde MySQL 8.0; en 5.7 no. La forma es la misma: AVG(precio) OVER (PARTITION BY id_categoria) No admite OVER con COUNT(DISTINCT ...). |
|---|---|
| SQL Server | La misma forma: AVG(precio) OVER (PARTITION BY id_categoria). No admite OVER con COUNT(DISTINCT ...). |
Lo que dice la voz
¿Cómo eliges? Si el reporte tiene una fila por grupo, como las ventas por sucursal, es un group by. Si necesitas cada fila con el resumen de su grupo al lado, una ventana. La subconsulta sigue siendo válida, y la vas a ver en consultas antiguas, porque MySQL recién tiene ventanas desde la versión ocho; en una base con MySQL cinco punto siete no existen. Y un límite: count distinct no se puede usar con over, ni en SQLite, ni en MySQL, ni en SQL Server. Si lo intentas, SQLite responde distinct is not supported for window functions.
Lo que te llevas de esta lección
AVG(x) OVER () pone el resumen de todas las filas en cada filaOVER (PARTITION BY col) lo calcula dentro de su grupoWHERE, GROUP BY y HAVING, y antes de ORDER BY y LIMITWHERE afueraLo que dice la voz
Una función de resumen seguida de over con paréntesis vacíos calcula sobre todas las filas y deja el resultado en cada una, y con partition by, el cálculo se hace dentro del grupo de cada fila. No se junta ninguna fila, como en group by. La ventana se calcula después del where, del group by y del having, así que ve lo que ellos dejaron, y antes del order by y del limit. Por eso va en el select o en el order by, y para filtrar por ella la calculas en una CTE y filtras afuera. En la próxima lección vienen los rankings: numerar las filas y quedarte con las primeras de cada grupo.
El peso de cada vendedor en su sucursal
La gerencia quiere saber cuánto aporta cada vendedor a las ventas de su propia sucursal, en porcentaje, para comparar equipos de distinto tamaño.