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 |
INSERIR PARA golfe VALORES("KP1", {"jogador": "Marco", "round1":75, "round2":73}), VALORES("KP2", {"jogador": "Johan", "round1":72, "round2":68}), VALORES("KP3", {"jogador": "Chang", "round1":67, "round2":76}), VALORES("KP4", {"jogador": "Isha", "round1":74, "round2":71}), VALORES("KP5", {"jogador": "Sitaram", "round1":68, "round2":72}), VALORES("KP6", {"jogador": "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 |
COM g1 como (selecionar jogador, rodada1, rodada2 de golfe) SELECIONAR g3.player AS jogador, (g3.round1+g3.round2) AS T, ((g3.round1+g3.round2) - 144) AS ToPar, (selecionar bruto 1+CONTAGEM(*) de g1 como g2 onde (g2.round1 + g2.round2) < (g3.round1 + g3.round2))[0] AS sqlrankR2 DE g1 como g3 ORDEM BY sqlrankR2 Resultados: T ToPar jogador 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 |
SELECIONAR jogador AS jogador, (round1+round2) AS T, ((round1+round2) - 144) AS ToPar, RANK() SOBRE(ORDEM BY (round1+round2)) AS classificaçãoR2 DE golfe; T ToPar jogador classificaçãoR2 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 |
SELECIONAR jogador AS jogador, (round1+round2) AS T, ((round1+round2) - 144) AS ToPar, RANK() SOBRE(ORDEM BY (round1+round2)) AS rankR2, DENSE_RANK() SOBRE (ORDEM BY (round1+round2)) AS rankR2Dense, NÚMERO DA LINHA() OVER() rownum, ((round1+round2) - FIRST_VALUE(round1+round2) SOBRE(ORDEM BY (round1+round2))) AS golpes por trás, RANK() SOBRE(ORDEM BY (round1)) AS classificaçãoR1, LAG(jogador, 1, "Nenhum") SOBRE(ORDEM BY rodada1+round2) AS inFront DE golfe ORDEM BY classificaçãoR2 T ToPar inFront jogador classificaçãoR1 classificaçãoR2 rankR2Dense rownum golpes por trás 138 -6 "Nenhum" "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
