Agora bora mergulhar nos exemplos práticos pra ver como tudo isso funciona de verdade nas tarefas do dia a dia!
Exemplo: cálculo do ranking de vendas por região
Imagina que a gente tem uma tabela sales com dados de vendas em várias regiões. Precisamos descobrir o ranking das vendas pra cada região.
| id | region | sales_amount |
|---|---|---|
| 1 | North | 5000 |
| 2 | North | 3000 |
| 3 | North | 7000 |
| 4 | South | 2000 |
| 5 | South | 4000 |
| 6 | East | 8000 |
| 7 | East | 6000 |
Missão: achar o ranking (RANK) das vendas pra cada região
SELECT
region,
sales_amount,
RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM
sales;
Resultado:
| region | sales_amount | sales_rank |
|---|---|---|
| North | 7000 | 1 |
| North | 5000 | 2 |
| North | 3000 | 3 |
| South | 4000 | 1 |
| South | 2000 | 2 |
| East | 8000 | 1 |
| East | 6000 | 2 |
Repara que a gente usou PARTITION BY region pra calcular o ranking separado pra cada região. Se não tivesse PARTITION BY, o ranking ia ser global pra tabela toda.
Exemplo: cálculo da soma acumulada de receita
Agora bora trabalhar com a tabela transactions pra calcular a soma acumulada de receita pra cada cliente.
| id | customer_id | purchase_date | amount |
|---|---|---|---|
| 1 | 101 | 2023-01-01 | 100 |
| 2 | 101 | 2023-01-03 | 50 |
| 3 | 102 | 2023-01-02 | 200 |
| 4 | 101 | 2023-01-05 | 150 |
| 5 | 102 | 2023-01-04 | 100 |
Missão: calcular a soma acumulada pra cada cliente
SELECT
customer_id,
purchase_date,
amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS cumulative_sum
FROM
transactions;
Resultado:
| customer_id | purchase_date | amount | cumulative_sum |
|---|---|---|---|
| 101 | 2023-01-01 | 100 | 100 |
| 101 | 2023-01-03 | 50 | 150 |
| 101 | 2023-01-05 | 150 | 300 |
| 102 | 2023-01-02 | 200 | 200 |
| 102 | 2023-01-04 | 100 | 300 |
Aqui o pulo do gato é usar ORDER BY purchase_date dentro do OVER(), pra soma acumulada sair na ordem cronológica certinha.
Exemplo: divisão dos dados em quantis
Imagina que a gente tem uma tabela students com nomes e notas das provas. Queremos dividir os alunos em 4 grupos de acordo com as notas.
| id | name | test_score |
|---|---|---|
| 1 | Alice | 85 |
| 2 | Bob | 95 |
| 3 | Charlie | 75 |
| 4 | Diana | 88 |
| 5 | Edward | 65 |
| 6 | Fiona | 70 |
Missão: dividir os estudantes em 4 grupos usando NTILE()
SELECT
name,
test_score,
NTILE(4) OVER (ORDER BY test_score DESC) AS quartile
FROM
students;
O resultado da query vai ser assim:
| name | test_score | quartile |
|---|---|---|
| Bob | 95 | 1 |
| Diana | 88 | 1 |
| Alice | 85 | 2 |
| Charlie | 75 | 3 |
| Fiona | 70 | 3 |
| Edward | 65 | 4 |
NTILE(4) divide os dados em 4 grupos. Os alunos com as notas mais altas ficam no primeiro grupo, os com as mais baixas no último.
Exemplo: análise de dados temporais
Na tabela site_visits ficam os dados de visitas ao site por dia. Precisamos calcular a diferença de visitas entre os dias pra cada site.
| site_id | visit_date | visits |
|---|---|---|
| 1 | 2023-01-01 | 100 |
| 1 | 2023-01-02 | 120 |
| 1 | 2023-01-03 | 110 |
| 2 | 2023-01-01 | 50 |
| 2 | 2023-01-02 | 60 |
| 2 | 2023-01-03 | 70 |
Missão — calcular a diferença de visitas entre os dias
SELECT
site_id,
visit_date,
visits,
visits - LAG(visits) OVER (PARTITION BY site_id ORDER BY visit_date) AS visit_diff
FROM
site_visits;
Resultado:
| site_id | visit_date | visits | visit_diff |
|---|---|---|---|
| 1 | 2023-01-01 | 100 | NULL |
| 1 | 2023-01-02 | 120 | 20 |
| 1 | 2023-01-03 | 110 | -10 |
| 2 | 2023-01-01 | 50 | NULL |
| 2 | 2023-01-02 | 60 | 10 |
| 2 | 2023-01-03 | 70 | 10 |
A função LAG() deixa pegar o valor da linha anterior. Se não tiver linha anterior, o resultado é NULL. Mais sobre ela tu vai ver nas próximas aulas :P
GO TO FULL VERSION