SQL: funções de janela para BI
O curso apresenta as funções de janela do Postgres a partir de um exemplo: o percentil de XP de cada aluno, que esta aplicação calculava com uma query GROUP BY e HAVING por aluno. Ele mostra como a cláusula OVER calcula sobre várias linhas sem reduzi-las e como RANK, DENSE_RANK e ROW_NUMBER numeram empates. Também mostra como PERCENT_RANK dá o percentil de todos numa query só. Trata ainda do frame padrão nos totais acumulados, de PARTITION BY para rankings por curso e de LAG para intervalos entre eventos.
O que você vai aprender
- ✓Como OVER se diferencia de GROUP BY e mantém todas as linhas de entrada
- ✓Como RANK, DENSE_RANK e ROW_NUMBER tratam linhas empatadas
- ✓Cálculo do percentil de todos os alunos com PERCENT_RANK sobre uma CTE de totais
- ✓Como o frame RANGE padrão afeta totais acumulados, e quando escrever ROWS com desempate
- ✓Rankings por grupo com PARTITION BY e intervalos entre eventos com LAG
Uma query por usuário, só para ranqueá-los
O Enrollment::UserGamificationQuery calcula um número que esta aplicação mostra a cada aluno: leaderboard_percentile, como o XP dele se compara ao de todos os outros. Até uma correção sair, no dia em que este curso foi escrito, o código que o calculava era este:
Ler a aula completa grátis →Currículo
7 aulasUma query por usuário, só para ranqueá-los
Como a aplicação calculava o percentil do leaderboard com uma query GROUP BY por aluno, e por que usar janelas.
OVER: a cláusula que não colapsa linhas
Colocar OVER depois de uma agregação mantém todas as linhas, e as janelas rodam depois do GROUP BY.
RANK, DENSE_RANK, ROW_NUMBER: três formas de numerar um empate
Como RANK, DENSE_RANK e ROW_NUMBER numeram linhas empatadas, e que pergunta cada função de ranking responde.
PERCENT_RANK: todos os percentis numa query só
Uso de PERCENT_RANK sobre uma CTE de totais para calcular o percentil de todos os alunos numa passada.
Totais correntes, e o frame que ninguém escreve explicitamente
O frame RANGE padrão dá o mesmo total a linhas empatadas, então escreva ROWS e acrescente um desempate.
PARTITION BY e LAG: janelas por grupo, e tempo entre eventos
PARTITION BY reinicia o ranking a cada curso, e LAG lê a linha anterior para medir intervalos entre eventos.
Juntando tudo: uma query, todo estudante ranqueado
Uma query final junta CTE de totais, RANK, PERCENT_RANK e PARTITION BY num leaderboard por curso.