Use a analogia do golfe ao explicar para os executivos.
Use uma analogia com um carro para todos os outros. - Confúcio.
O objetivo das funções de janela é traduzir os requisitos de relatórios comerciais de forma declarativa e eficaz para o SQL, de modo que o desempenho da consulta e a eficiência do desenvolvedor/analista de negócios melhorem drasticamente. Já vi relatórios e painéis do mundo real passarem de horas para minutos e de minutos para segundos depois de usar as funções de janela. O tamanho da consulta diminuiu de 40 páginas para algumas páginas. Nos anos 90, o banco de dados Redbrick realmente entendeu o caso de uso comercial e criou uma nova camada de funcionalidade para fazer relatórios comerciais que incluíam classificação, execução de totais, cálculo de comissões e inventário com base em subgrupos, posições etc. Esses recursos foram incluídos no padrão SQL em 2003. Todas as camadas de BI (como Tableau, Looker, Cognos) exploram essa funcionalidade.
Introdução às funções de janela
Imagine que você tenha as pontuações de seis jogadores de golfe em duas rodadas. Agora, você precisa criar a tabela de classificação e classificá-los. Classifique-os usando SQL.
| Jogador | Rodada1 | Rodada2 |
| Marco | 75 | 73 |
| Johan | 72 | 68 |
| Chang | 67 | 76 |
| Isha | 74 | 71 |
| Sitaram | 68 | 72 |
| Bingjie | 71 | 67 |
Insira os dados no 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}); |
SEM funções de janela (estado atual - Couchbase 6.0)
Para escrever a consulta sem o uso de funções de janela, você precisa de uma subconsulta para calcular a classificação de cada jogador. Essa subconsulta precisa examinar todos os dados resultando na pior complexidade algorítmica de O(N^2), o que aumenta drasticamente o tempo de execução e a taxa de transferência.
|
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 |
Com funções de janela no Mad-Hatter (versão futura)
Essa consulta retorna o jogador, o total após duas rodadas (T), o quanto da pontuação está acima/abaixo do par (ToPar) e, em seguida classificações com base nas pontuações das duas primeiras rodadas. Essa é a NOVA funcionalidade do Mad-Hatter. A complexidade de tempo disso é O(N), o que significa que o tempo de execução só aumentará linearmente.
|
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 |
Observações:
- A consulta expressa os requisitos de forma simples e clara.
- O desempenho dessa consulta em um cenário do mundo real é muito melhor. Planejamos medir.
- Quando os requisitos de classificação dependem de vários documentos, a consulta se torna bastante complexa - para escrever, otimizar e executar.
- Tudo isso afeta o TCO em geral.
Agora, vamos criar um painel expandido.
Mostre a classificação densa, o número da linha, quem está na frente e o número de tacadas atrás do líder. Todas essas informações são muito comuns em uma situação de relatório. Você está vendo a nova função de janela sempre que vê a cláusula OVER(). A consulta abaixo tem seis funções de janela.
|
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 você viu anteriormente, fazer essa consulta com seis funções de janela usando será uma consulta mais trabalhosa, cara e propensa a erros.
Além de transformar os agregados internos (COUNT, SUM, AVG etc.) em funções de janela, o Sitaram adicionou as seguintes funções de janela. A sintaxe e a semântica de cada uma delas estão bem definidas no padrão e bem descritas nos artigos da seção de referência abaixo.
RANK()
DENSE_RANK()
PERCENT_RANK()
CUME_DIST()
NTILE()
RATIO_TO_REPORT()
ROW_NUMBER()
LAG()
FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()
LEAD()
Referências:
- Provavelmente o recurso mais legal do SQL: Funções de janela. https://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/
- Uma janela para o mundo das funções analíticas. https://blogs.oracle.com/oraclemagazine/a-window-into-the-world-of-analytic-functions
- Referência Oracle: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174
