Utilice la analogía del golf cuando explique a los ejecutivos.
Utiliza una analogía automovilística para todos los demás. - Confucio.
El propósito de las funciones de ventana es traducir los requisitos de los informes empresariales de forma declarativa y eficaz a SQL, de modo que el rendimiento de las consultas y la eficiencia del desarrollador/analista empresarial mejoren drásticamente. He visto informes y cuadros de mando del mundo real pasar de horas a minutos, de minutos a segundos después de utilizar funciones de ventana. El tamaño de las consultas disminuye de 40 páginas a unas pocas. En los años 90, la base de datos Redbrick comprendió realmente el caso de uso empresarial y creó una nueva capa de funcionalidad para realizar informes empresariales que incluían clasificaciones, totales de ejecución, cálculo de comisiones e inventario basados en subgrupos, posiciones, etc. Estos han estado en SQL estándar en 2003. Cada capa de BI (como Tableau, Looker, Cognos) explota esta funcionalidad.
Introducción a las funciones de ventana
Imagina que tienes las puntuaciones de seis golfistas a lo largo de dos rondas. Ahora, necesitas crear la tabla de clasificación y clasificarlos. Clasifíquelos utilizando SQL.
| Jugador | Ronda 1 | Ronda2 |
| Marco | 75 | 73 |
| Johan | 72 | 68 |
| Chang | 67 | 76 |
| Isha | 74 | 71 |
| Sitaram | 68 | 72 |
| Bingjie | 71 | 67 |
Inserte los datos en Couchbase.
|
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO golf VALUES("KP1", {"player": "Marco", "round1":75, "round2":73}), VALUES("KP2", {"player": "Johan", "round1":72, "round2":68}), VALUES("KP3", {"player": "Chang", "round1":67, "round2":76}), VALUES("KP4", {"player": "Isha", "round1":74, "round2":71}), VALUES("KP5", {"player": "Sitaram", "round1":68, "round2":72}), VALUES("KP6", {"player": "Bingjie", "round1":71, "round2":67}); |
SIN funciones de ventana (estado actual - Couchbase 6.0)
Para escribir la consulta sin utilizar funciones de ventana, se necesita una subconsulta para calcular la clasificación de cada jugador. Esta subconsulta tiene que escanear todos los datos resultando la peor complejidad algorítmica de O(N^2), lo que aumenta drásticamente el tiempo de ejecución y el rendimiento.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
WITH g1 as (select player, round1, round2 from golf) SELECT g3.player AS player, (g3.round1+g3.round2) AS T, ((g3.round1+g3.round2) - 144) AS ToPar, (select raw 1+COUNT(*) from g1 as g2 where (g2.round1 + g2.round2) < (g3.round1 + g3.round2))[0] AS sqlrankR2 FROM g1 as g3 ORDER BY sqlrankR2 Results: T ToPar player sqlrankR2 138 -6 "Bingjie" 1 140 -4 "Johan" 2 140 -4 "Sitaram" 2 143 -1 "Chang" 4 145 1 "Isha" 5 148 4 "Marco" 6 |
Con funciones de ventana en Mad-Hatter (próxima versión)
Esta consulta devuelve jugador, total después de dos rondas (T), cómo de la puntuación es sobre / bajo par (ToPar) y luego clasifica basándose en las puntuaciones de las dos primeras rondas. Esta es la NUEVA funcionalidad de Mad-Hatter. La complejidad temporal de esto es O(N), lo que significa que el tiempo de ejecución sólo aumentará linealmente.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT player AS player, (round1+round2) AS T, ((round1+round2) - 144) AS ToPar, RANK() OVER(ORDER BY (round1+round2)) AS rankR2 FROM golf; T ToPar player rankR2 138 -6 "Bingjie" 1 140 -4 "Johan" 2 140 -4 "Sitaram" 2 143 -1 "Chang" 4 145 1 "Isha" 5 148 4 "Marco" 6 |
Observaciones:
- La consulta expresa los requisitos de forma sencilla y clara.
- El rendimiento de esta consulta en un escenario real es mucho mejor. Tenemos previsto medir.
- Cuando los requisitos de clasificación dependen de varios documentos, la consulta se vuelve bastante compleja de escribir, optimizar y ejecutar.
- Todo ello afecta al coste total de propiedad.
Ahora, vamos a crear un cuadro de mandos ampliado.
Mostrar añadir denso rango, número de fila, que está por delante, y el número de golpes detrás del líder. Todas cosas muy comunes en una resituación de reporte. Estás viendo la nueva función de ventana cada vez que ves la cláusula OVER(). La consulta de abajo tiene seis funciones ventana.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SELECT player AS player, (round1+round2) AS T, ((round1+round2) - 144) AS ToPar, RANK() OVER(ORDER BY (round1+round2)) AS rankR2, DENSE_RANK() OVER (ORDER BY (round1+round2)) AS rankR2Dense, ROW_NUMBER() OVER() rownum, ((round1+round2) - FIRST_VALUE(round1+round2) OVER(ORDER BY (round1+round2))) AS strokesbehind, RANK() OVER(ORDER BY (round1)) AS rankR1, LAG(player, 1, "None") OVER(ORDER BY round1+round2) AS inFront FROM golf ORDER BY rankR2 T ToPar inFront player rankR1 rankR2 rankR2Dense rownum strokesbehind 138 -6 "None" "Bingjie" 3 1 1 3 0 140 -4 "Johan" "Sitaram" 2 2 2 2 2 140 -4 "Bingjie" "Johan" 4 2 2 4 2 143 -1 "Sitaram" "Chang" 1 4 3 1 5 145 1 "Chang" "Isha" 5 5 4 5 7 148 4 "Isha" "Marco" 6 6 5 6 10 |
Como has visto antes, haciendo esta consulta con seis funciones de ventana mediante método de subconsulta será una consulta de mayor esfuerzo, costosa y propensa a errores.
Además de convertir los agregados incorporados (COUNT, SUM, AVG, etc) en funciones ventana, Sitaram ha añadido las siguientes funciones ventana. La sintaxis y la semántica de cada una de ellas están bien definidas en la norma y bien descritas en los artículos de la sección de referencia que figura a continuación.
RANGO()
DENSE_RANK()
PERCENT_RANK()
CUME_DIST()
NTILE()
RATIO_TO_REPORT()
ROW_NUMBER()
LAG()
PRIMER_VALOR()
ÚLTIMO_VALOR()
NTH_VALUE()
LEAD()
Referencias:
- Probablemente la función SQL más interesante: Funciones de ventana. https://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/
- Una ventana al mundo de las funciones analíticas. https://blogs.oracle.com/oraclemagazine/a-window-into-the-world-of-analytic-functions
- Referencia Oracle: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174
