Nesta semana encarei um teste de SQL em que me pediram para desenvolver uma query, a princípio simples, mas que deu um nó na minha cabeça ao tentar resolver.
O que me ajudou a chegar a uma solução, depois de alguma pesquisa, foi numerar as linhas, no sentido de atribuir valores inteiros aos registros retornados por um select.
Como eu não tinha o costume de utilizar os recursos de numeração de linhas do SQL, resolvi estudar para melhorar meu conhecimento nesse assunto.
O objetivo deste artigo é dividir com vocês o resultado desse estudo.
[22/11/2024 Uma revisão foi publicada em Medium]
--
Abaixo vou colocar códigos SQL e indicar compatibilidade com PostgreSQL, Oracle e SQLite. Quando não indicar, é porque funciona nos três.
"Porque esses três?"
Os dois primeiros são os que mais utilizo - e mais gosto - atualmente.
O terceiro é muito utilizado em instalações iniciais de alguns frameworks, como o Django, e, apesar de muitas limitações, tem uma boa compatibilidade com padrões SQL.
Obs.: Os códigos estarão o mais simples possível. Não os tome como recomendação de forma de trabalho.
Montando tabela para testes
Primeiro vamos criar uma tabela para trabalhar os exemplos.
CREATE TABLE venda (
regiao VARCHAR(1)
, pessoa VARCHAR(10)
, quantidade INTEGER
);
(PostgreSQL e SQLite)
CREATE TABLE venda (
regiao VARCHAR2(1)
, pessoa VARCHAR2(10)
, quantidade INTEGER
);
(Oracle)
A ideia dessa tabela "venda" é guardar a "quantidade" que cada "pessoa" vendeu em cada "região".
Agora vamos inserir os seguintes dados:
regiao | pessoa | quantidade |
A | alice | 20 |
A | maria | 10 |
A | pedro | 10 |
A | sofia | 15 |
B | alice | 15 |
B | maria | 15 |
B | pedro | 15 |
B | sofia | 30 |
Segue o comando SQL para inserir todos esses dados de uma vez:
INSERT INTO venda
(regiao, pessoa, quantidade)
VALUES
('A', 'alice', 20)
, ('A', 'sofia', 15)
, ('A', 'maria', 10)
, ('A', 'pedro', 10)
, ('B', 'alice', 15)
, ('B', 'sofia', 30)
, ('B', 'maria', 15)
, ('B', 'pedro', 15);
(PostgreSQL, SQLite e Oracle recente)
INSERT INTO venda
(regiao, pessoa, quantidade)
SELECT 'A', 'sofia', 15 FROM DUAL
UNION SELECT 'A', 'maria', 10 FROM DUAL
UNION SELECT 'A', 'pedro', 10 FROM DUAL
UNION SELECT 'B', 'alice', 15 FROM DUAL
UNION SELECT 'B', 'sofia', 30 FROM DUAL
UNION SELECT 'B', 'maria', 15 FROM DUAL
UNION SELECT 'B', 'pedro', 15 FROM DUAL;
(Oracle antigo)
Numeração de linhas
Agora vamos partir para as numerações das linhas propriamente ditas.
"As numerações?! Tem mais de uma?"
Sim! Aqui vou falar de três.
Mas antes, vou falar do que é comum entre as três.
No padrão SQL, as três numerações que vou abordar são adicionadas ao select seguindo o padrão abaixo, onde vou utilizar a palavra chave "NUMERAÇÃO", que depois será substituída por três diferentes palavras chaves.
NUMERAÇÃO() OVER (PARTITION BY campo1, campo2... ORDER BY campo3 DESC, campo4...) AS numeracao
"Onde isso entra no select?"
Isso é uma das colunas do resultado da consulta. Faz sentido, é claro! Se queremos que cada linha tenha uma numeração, essa numeração deve aparecer em uma coluna.
Entendendo cada parte do padrão acima apresentado:
- "AS numeracao" é opcional, mas, é recomendado.
- Nesse contexto, o "AS" é utilizado para indicar um nome para a coluna que apresentará a numeração.
- O "ORDER BY campo3 DESC, campo4...", pelo padrão SQL, é obrigatório e indica quais campos serão utilizados como regra de ordenação das linhas. De acordo com a ordenação indicada, a numeração da primeira linha recebe o valor 1 (um) e as demais linhas, seus devidos valores, seguindo regras específicas do incremento utilizado, até ser atribuído valor de numeração à todas as linhas.
- Na prática, dependendo do gerenciador de banco de dados utilizado, é possível não informar a ordenação a ser indicada.
- Em PostgreSQL e SQLite, basta não indicar o "ORDER BY ..."
- Em Oracle, basta indicar "ORDER BY NULL", para indicar que nenhuma ordenação será aplicada.
- Como no uso mais comum do "ORDER BY", que é definir a ordenação dos dados retornados pela query, o "DESC" deve ser utilizado apenas quando se desejar que ordenação seja decrescente pelo valor do campo que precede o "DESC".
- "PARTITION BY campo1, campo2..." é opcional e indica qual ou quais campos particionam a numeração.
- Caso não seja indicada uma partição, a numeração da primeira linha recebe o valor 1 (um) e as demais linhas, seus devidos valores.
- Caso seja indicada uma partição, por um ou mais campos, para cada valor do conjunto dos campos a numeração funcionará de modo independente. A numeração da primeira linha de cada partição recebe o valor 1 (um) e as demais linhas da partição, seus devidos valores.
- Na tabela de testes "venda", montada anteriormente, caso fosse indicado "PARTITION BY regiao", a numeração começaria com o valor 1 (um) duas vezes, já que o campo região apresenta dois diferentes valores, "A" e "B".
- Nos locais onde aparecem referências à campos das tabelas, como "campo1", também podem ser informadas funções, cujos resultados serão utilizados da mesma forma que seriam os valores diretos dos campos. Por exemplo, "LOWER(campo2)", para particionar ou ordenar pelo valor do "campo2" convertido para minúsculas.
Tendo previamente entendidas essas partes do padrão apresentado, vamos agora às numerações.
Numerar linha - ROW_NUMBER
Essa é a numeração mais simples.
Depois de atribuir o valor 1 (um) à uma linha, observando a ordenação, definida por "ORDER BY", e a partição, definida por "PARTITION BY", cada linha da sequência, dentro da partição, recebe um valor de numeração uma unidade maior que o da linha anterior.
Exemplo 1
- A coluna "numero_geral" é uma numeração geral para todas as linhas, de acordo com a ordenação por região e pessoa.
- A coluna "numero_regiao" é uma numeração particionada por região, seguindo, dentro de cada partição, a ordenação por pessoa.
SELECT
v.*
, ROW_NUMBER() OVER (ORDER BY regiao, pessoa) AS numero_geral
, ROW_NUMBER() OVER (PARTITION BY regiao ORDER BY pessoa) AS numero_regiao
FROM venda v
ORDER BY regiao, pessoa;
regiao | pessoa | quantidade | numero_geral | numero_regiao |
A | alice | 30 | 1 | 1 |
A | maria | 20 | 2 | 2 |
A | pedro | 5 | 3 | 3 |
A | sofia | 15 | 4 | 4 |
B | alice | 15 | 5 | 1 |
B | maria | 10 | 6 | 2 |
B | pedro | 15 | 7 | 3 |
B | sofia | 30 | 8 | 4 |
Exemplo 2
- A coluna "ordem_quant" é uma numeração geral, seguindo a ordenação por quantidade decrescente.
- Ranqueia as linhas pela quantidade decrescente de venda. Na primeira linha esta a maior venda e na última a menor.
SELECT
v.*
, ROW_NUMBER() OVER (ORDER BY quantidade DESC) AS ordem_quant
FROM venda v
ORDER BY quantidade DESC;
regiao | pessoa | quantidade | ordem_quant |
A | alice | 30 | 1 |
B | sofia | 30 | 2 |
A | maria | 20 | 3 |
B | pedro | 15 | 4 |
A | sofia | 15 | 5 |
B | alice | 15 | 6 |
B | maria | 10 | 7 |
A | pedro | 5 | 8 |
Ranquear - RANK
O Exemplo 2 acima ranqueia as linhas pela quantidade, porém não trata o fato de haver "empate" no valor utilizado para o ranqueamento.
Não há um motivo razoável para colocar a venda de Sofia na região "B" em segundo lugar, já que vendeu a mesma quantidade que Alice na região "A". Esse problema se repete no empate triplo entre as linhas 4, 5 e 6.
Para que os empatados recebam o mesmo número de posição devemos utilizar a numeração "RANK".
Exemplo 3
- A coluna "ordem_quant" é uma numeração geral, seguindo a ordenação por quantidade decrescente e considerando empates.
- Segue a lógica de que os empates não alteram os ranqueamentos das linhas não empatadas.
- Fazendo um paralelo com os esportes olímpicos: Caso os dois melhores atletas em um esporte empatem, ambos recebem medalhas de ouro e ficam juntos no lugar mais alto do pódio. O terceiro melhor atleta recebe medalha de bronze e fica no posto mais baixo do pódio, reservado ao terceiro lugar.
SELECT
v.*
, RANK() OVER (ORDER BY quantidade DESC) AS ordem_quant
FROM venda v
ORDER BY quantidade DESC;
regiao | pessoa | quantidade | ordem_quant |
A | alice | 30 | 1 |
B | sofia | 30 | 1 |
A | maria | 20 | 3 |
B | pedro | 15 | 4 |
A | sofia | 15 | 4 |
B | alice | 15 | 4 |
B | maria | 10 | 7 |
A | pedro | 5 | 8 |
Ao contrário do Exemplo 2, o "RANK", no Exemplo 3, deixa claro que há duas linhas empatadas em primeiro lugar e 3 linhas empatadas em quarto lugar. Além disso, mantém as numerações 3, 7 e 8 para as linhas que efetivamente estão em terceiro, sétimo e oitavo lugar.
Ranquear densamente - DENSE_RANK
O Exemplo 3
acima gera numeração com "saltos, ou "vazios". Entre a menor e a maior numeração não aparecem todos os números. No caso, não aparecem 2, 5 e 6.
Voltando ao paralelo com os esportes: O local no pódio reservado para o segundo colocado ficou vazio.
Para não haver esses "saltos" na numeração devemos utilizar o "DENSE_RANK".
Exemplo 4
- A coluna "ordem_quant" é uma numeração geral, seguindo a ordenação por quantidade decrescente e considerando empates e não permitindo intervalos na numeração.
- Voltando novamente a um paralelo com o esporte: Seria como se o posicionamento dos atletas significasse, "ter o enésimo melhor tempo", e não "ser o enésimo melhor atleta".
SELECT
v.*
, DENSE_RANK() OVER (ORDER BY quantidade DESC) AS ordem_quant
FROM venda v
ORDER BY quantidade DESC;
regiao | pessoa | quantidade | ordem_quant |
A | alice | 30 | 1 |
B | sofia | 30 | 1 |
A | maria | 20 | 2 |
B | pedro | 15 | 3 |
A | sofia | 15 | 3 |
B | alice | 15 | 3 |
B | maria | 10 | 4 |
A | pedro | 5 | 5 |
Neste Exemplo 4, o "DENSE_RANK" deixa claro que duas linhas têm a maior quantidade vendida, uma tem a segunda maior, três têm a terceira maior, e assim por diante. Também fica claro que há cinco diferentes quantidades vendidas.
Tudo junto ao mesmo tempo
Vamos ver agora os casos dos exemplos 2 a 4 juntos, mantendo as respectivas palavras chave de numeração como nomes das colunas:
SELECT
v.*
, ROW_NUMBER() OVER (ORDER BY quantidade DESC) AS row_number
, RANK() OVER (ORDER BY quantidade DESC) AS rank
, DENSE_RANK() OVER (ORDER BY quantidade DESC) AS dense_rank
FROM venda v
ORDER BY quantidade DESC;
regiao | pessoa | quantidade | row_number | rank | dense_rank |
A | alice | 30 | 1 | 1 | 1 |
B | sofia | 30 | 2 | 1 | 1 |
A | maria | 20 | 3 | 3 | 2 |
B | pedro | 15 | 4 | 4 | 3 |
A | sofia | 15 | 5 | 4 | 3 |
B | alice | 15 | 6 | 4 | 3 |
B | maria | 10 | 7 | 7 | 4 |
A | pedro | 5 | 8 | 8 | 5 |
Importante
O uso das funções "ROW_NUMBER", "RANK" e "DENSE_RANK" em tabelas muito grandes pode impactar o desempenho da consulta, especialmente sem índices adequados nos campos usados em "ORDER BY" e "PARTITION BY".
Exemplo um pouco mais complexo
Vamos complicar um pouco as coisas. Digamos que tenhamos recebido o seguinte pedido: Liste os vendedores com os dois maiores totais de vendas em todas as regiões, indicando nome, venda total, menor e maior venda.
Antes de olhar o código abaixo, pense um pouco em como resolveria.
...
Pensou?
Agora compare e sua solução à minha. Não necessariamente uma é melhor que outra. O que importa, a princípio, é entregar os dados pedidos.
WITH agrupado AS
(
SELECT
v.pessoa
, SUM(v.quantidade) total
, MIN(v.quantidade) menor
, MAX(v.quantidade) maior
FROM venda v
GROUP BY
v.pessoa
)
, ranqueado AS
(
SELECT
a.*
, DENSE_RANK() OVER (ORDER BY total DESC) AS classificacao
FROM agrupado a
)
SELECT
r.pessoa
, r.classificacao
, r.total
, r.menor
, r.maior
FROM ranqueado r
WHERE r.classificacao <= 2
ORDER BY
r.classificacao;
pessoa | classificacao | total | menor | maior |
sofia | 1 | 45 | 15 | 30 |
alice | 1 | 45 | 15 | 30 |
maria | 2 | 30 | 10 | 20 |
O teste que "deu um nó na minha cabeça"
Talvez você esteja curioso, deste a primeira linha deste artigo, para saber qual pedido de query que "deu um nó na minha cabeça".
Se sim, aí vai:
Dada uma tabela "presenca", que rastreia a presença dos funcionários por data, escreva uma consulta para encontrar todos os funcionários que estiveram ausentes por três ou mais dias consecutivos.
Campos da tabela "presenca": "func_id", "dia", "status" (valores: "presente", "ausente")
Retorno esperado: "func_id", "dia_inicial", "dia_final", "num_dias_consecutivos"
WITH do_grupo_id AS
(
SELECT
p.*
, ROW_NUMBER() OVER (PARTITION BY p.func_id ORDER BY p.dia)
- ROW_NUMBER() OVER (PARTITION BY p.func_id, p.status ORDER BY p.dia) AS grupo_id
FROM presenca p
)
, grp_count AS
(
SELECT
func_id
, grupo_id
, MIN(dia) dia_inicial
, MAX(dia) dia_final
, COUNT(1) dias
FROM do_grupo_id
WHERE status = 'a'
GROUP BY
func_id
, grupo_id
)
SELECT
func_id
, dia_inicial
, dia_final
, dias AS num_dias_consecutivos
FROM grp_count
WHERE dias >= 3
ORDER BY
func_id
, dia_inicial;
Se você estiver com dificuldade de entender justamente a parte onde as numerações "salvaram o dia" na solução acima, calma. Vou explicar.
No primeiro select da query, acima, o "row_number" é utilizado para criar um identificador de subgrupo para linhas consecutivas que têm algo "em comum" em uma sequência.
No caso, o que as linhas têm "em comum" é o fato de serem "ausências" (ou "presenças") consecutivas em uma sequência de dias.
Para criar esse identificador de subgrupo é utilizado o "truque" de subtrair de um "row_number" de uma partição mais genérica, um "row_number" de uma partição mais específica. Sendo a especificidade, justamente o campo que dá às linhas o algo "em comum" que estamos procurando.
Na query acima, os dois "row_number" estão ordenados pelo "dia", que é a sequência analisada.
Na partição dos dois "row_number" consta o "func_id", pois a análise pedida é por funcionário.
Na partição do "row_number" mais específico é acrescentado o campo "status", que é justamente a informação do que há "em comum" entre as linhas consequentes, "presente" ou "ausente".
O campo da especificidade, junto ao identificador de subgrupo criado, formam um agrupamento, que pode ser utilizado para diversos processamentos.
Tendo esse recurso montado, os próximos passos da query apresentada acima são mais fáceis de serem compreendidos.
"Como esse truque funciona?"
Se a lógica do "truque" ainda não ficou clara, vamos explorar um exemplo visual para simplificar.
Hipoteticamente construí uma base de dados assim: Uma moeda foi lançada várias vezes e eu fui anotando o resultado, cara ("A") ou coroa ("O"). A sequência de lançamentos foi anotada em uma linha.
Como criar um identificador de subgrupo para as sequências de resultados iguais dos lançamentos?
O "truque" é utilizar duas vezes o "row_number, como demonstrado nas linhas da tabela abaixo, onde:
- a primeira linha exemplifica o conjunto de dados gerado: Os resultados dos lançamentos.
- a segunda linha exemplifica o "row_number" mais genérico, que simplesmente numera todos os dados.
- a terceira linha exemplifica o "row_number" mais específico. No caso a partição é o resultado, que pode ser "A" ou "O". Todos os resultados cara, "A", são numerados a partir de 1 (um). Da mesma forma todos os resultados coroa, "B".
- a quarta linha é igual a segunda menos a terceira, gerando com isso o identificador de subgrupo que estávamos procurando.
Dados na sequência | A | A | A | O | O | O | O | A | A | A | A | A | O | O | A | A | O | O | O | O | O | O |
row_number genérico | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 |
row_number específico | 1 | 2 | 3 | 1 | 2 | 3 | 4 | 4 | 5 | 6 | 7 | 8 | 5 | 6 | 9 | 10 | 7 | 8 | 9 | 10 | 11 | 12 |
genérico – específico = identificador de subgrupo |
0 | 0 | 0 | 3 | 3 | 3 | 3 | 4 | 4 | 4 | 4 | 4 | 8 | 8 | 6 | 6 | 10 | 10 | 10 | 10 | 10 | 10 |
Por fim, uma visualização gráfica desses valores.
--
Espero que tenham se divertido aprendendo ou relembrando algo que pode ser útil para vocês.
Nenhum comentário:
Postar um comentário