ROW_NUMBER() do MySQL Explicado (MySQL 8.0): Ranqueamento, Consultas Top-N e Desduplicação

1. Introdução

A versão 8.0 do MySQL introduziu muitas funcionalidades novas, e uma das mais notáveis é o suporte a funções de janela. Neste artigo, focaremos em uma das funções mais usadas: ROW_NUMBER().

A função ROW_NUMBER() oferece recursos poderosos para análise e geração de relatórios, facilitando a ordenação e classificação de dados com base em condições específicas. Este artigo explica tudo, desde o uso básico e exemplos práticos até abordagens alternativas para versões mais antigas do MySQL.

Público‑alvo

• Usuários iniciantes a intermediários com conhecimento básico de SQL
• Engenheiros e analistas de dados que processam e analisam dados usando MySQL
• Qualquer pessoa que esteja considerando migrar para a versão mais recente do MySQL

Benefícios do ROW_NUMBER()

Esta função permite atribuir um número único a cada linha com base em condições específicas. Por exemplo, você pode escrever consultas como “criar um ranking em ordem decrescente de vendas” ou “extrair e organizar dados duplicados” de forma concisa.

Em versões mais antigas, era comum escrever consultas complexas usando variáveis definidas pelo usuário. Com ROW_NUMBER(), seu SQL fica mais simples e legível.

Neste artigo, usaremos exemplos concretos de consultas e os explicaremos de maneira amigável para iniciantes. Na próxima seção, analisaremos mais de perto a sintaxe básica e o comportamento desta função.

2. O que é a função ROW_NUMBER()?

A função ROW_NUMBER(), adicionada recentemente no MySQL 8.0, é um tipo de função de janela que atribui números sequenciais às linhas. Ela pode numerar linhas por uma ordem específica e/ou dentro de cada grupo, o que é extremamente útil para análise de dados e geração de relatórios. Aqui explicaremos a sintaxe básica em detalhes com exemplos práticos.

Sintaxe básica do ROW_NUMBER()

Primeiro, o formato básico do ROW_NUMBER() é o seguinte.

SELECT
    column_name,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS row_num
FROM
    table_name;

Significado de cada elemento

• ROW_NUMBER() : Atribui um número sequencial a cada linha.
• OVER : Palavra‑chave usada para definir a janela de uma função de janela.
• PARTITION BY : Agrupa os dados pela coluna especificada. Opcional. Se omitido, a numeração é aplicada a todas as linhas.
• ORDER BY : Define a ordenação usada para atribuir os números, ou seja, o critério de classificação.

Exemplo básico

Por exemplo, suponha que você tenha uma tabela chamada “sales” com os seguintes dados.

Para atribuir números sequenciais dentro de cada departamento em ordem decrescente de vendas, use a consulta a seguir.

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

Resultado

A partir deste resultado, você pode ver que os rankings por vendas dentro de cada departamento são exibidos.

Como usar PARTITION BY

No exemplo acima, os dados são agrupados pela coluna “department”. Isso cria uma sequência separada para cada departamento.

Se você omitir PARTITION BY, a numeração será atribuída a todas as linhas como uma única sequência.

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Resultado

Características e cuidados do ROW_NUMBER()

• Numeração única : Mesmo que os valores sejam iguais, os números atribuídos são únicos.
• Tratamento de NULLs : Se o ORDER BY incluir NULLs, eles aparecerão primeiro na ordem ascendente e por último na ordem descendente.
• Impacto de desempenho : Para conjuntos de dados grandes, o ORDER BY pode ser custoso, portanto, índices adequados são importantes.

3. Casos de uso práticos

Aqui estão cenários práticos usando a função ROW_NUMBER() do MySQL. Esta função é útil em muitos casos reais, como ranking de dados e tratamento de duplicatas.

3-1. Ranking dentro de cada grupo

Por exemplo, considere o caso em que você deseja “classificar funcionários por vendas dentro de cada departamento” usando dados de vendas. Use o seguinte conjunto de dados como exemplo.

Exemplo de consulta: Classificação de vendas por departamento

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Resultado:

Dessa forma, cada departamento recebe sua própria sequência em ordem decrescente de vendas, facilitando a geração de classificações.

3-2. Extrair as N principais linhas

Em seguida, vamos examinar um caso em que você deseja “extrair os 3 principais funcionários por vendas dentro de cada departamento.”

Exemplo de consulta: Extrair N principais linhas

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

Resultado:

Este exemplo recupera apenas as 3 principais linhas por vendas dentro de cada departamento. Como você pode ver, ROW_NUMBER() é adequado não apenas para classificação, mas também para filtrar resultados principais.

3-3. Encontrar e remover dados duplicados

Os bancos de dados às vezes contêm registros duplicados. Em tais casos, você também pode manipulá-los facilmente usando ROW_NUMBER().

Exemplo de consulta: Detectar duplicatas

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

Esta consulta detecta duplicatas quando múltiplos registros existem para o mesmo nome de funcionário.

Exemplo de consulta: Excluir duplicatas

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

Resumo

ROW_NUMBER() é útil em uma variedade de cenários, como:

1. Classificação dentro de cada grupo
2. Extrair N principais linhas
3. Detectar e excluir duplicatas

Isso torna o processamento e a análise de dados complexos mais simples e eficientes.

4. Comparação com Outras Funções de Janela

No MySQL 8.0, além de ROW_NUMBER(), há funções de janela como RANK() e DENSE_RANK() que podem ser usadas para classificações e cálculos de posição. Embora tenham papéis semelhantes, seu comportamento e resultados diferem. Aqui, compararemos cada função e explicaremos quando usá-las.

4-1. Função RANK()

A função RANK() atribui classificações, dando a mesma classificação para valores iguais e pulando o próximo número de classificação.

Sintaxe básica

SELECT
    column_name,
    RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

Exemplo

Usando os seguintes dados, calcule as classificações de vendas.

Exemplo de consulta: Usando RANK()

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

Resultado:

Pontos principais:

• A e B com o mesmo valor de vendas (800) são tratados como classificação “1”.
• A próxima classificação “2” é pulada, então C se torna classificação “3”.

4-2. Função DENSE_RANK()

A função DENSE_RANK() também atribui a mesma classificação para valores iguais, mas não pula o próximo número de classificação.

Sintaxe básica

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS dense_rank
FROM
    table_name;

Exemplo

Usando os mesmos dados de acima, experimente a função DENSE_RANK().

Exemplo de consulta: Usando DENSE_RANK()

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

Resultado:

Pontos principais:

• A e B com o mesmo valor de vendas (800) são ambos tratados como classificação “1”.
• Ao contrário de RANK(), a próxima classificação começa em “2”, preservando a continuidade das classificações.

4-3. Como ROW_NUMBER() difere

A função ROW_NUMBER() difere das outras duas ao atribuir um número único mesmo quando os valores são iguais.

Exemplo

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Resultado:

Pontos-chave:

• Mesmo que os valores sejam iguais, cada linha recebe um número único, portanto não há classificações duplicadas.
• Isso é útil quando você precisa de controle estrito de ordenação ou de unicidade por linha.

4-4. Resumo rápido de casos de uso

Resumo

ROW_NUMBER(), RANK(), e DENSE_RANK() devem ser usados adequadamente dependendo do cenário.

1. ROW_NUMBER() é a melhor opção quando você precisa de números únicos por linha.
2. RANK() é útil quando você deseja que empates compartilhem a mesma classificação e quer enfatizar lacunas nas classificações.
3. DENSE_RANK() é adequado quando você quer classificações contínuas sem lacunas.

5. Alternativas para versões do MySQL abaixo de 8.0

Em versões anteriores ao MySQL 8.0, ROW_NUMBER() e outras funções de janela não são suportadas. No entanto, você pode obter um comportamento semelhante usando variáveis definidas pelo usuário. Esta seção explica alternativas práticas para versões do MySQL abaixo de 8.0.

5-1. Numeração sequencial usando variáveis definidas pelo usuário

No MySQL 5.7 e anteriores, você pode usar variáveis definidas pelo usuário para atribuir números sequenciais por linha. Vamos analisar o exemplo a seguir.

Exemplo: Classificação de vendas por departamento

Dados de exemplo:

Consulta:

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

Resultado:

5-2. Extraindo as N linhas superiores

Para recuperar as N linhas superiores, você pode usar variáveis definidas pelo usuário de maneira semelhante.

Consulta:

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

Resultado:

Esta consulta atribui classificações por departamento e, em seguida, extrai apenas as linhas dentro das 3 primeiras.

5-3. Detectando e excluindo duplicatas

Você também pode lidar com dados duplicados usando variáveis definidas pelo usuário.

Exemplo de consulta: Detectar duplicatas

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

Exemplo de consulta: Excluir duplicatas

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. Avisos ao usar variáveis definidas pelo usuário

1. Dependência de sessão * Variáveis definidas pelo usuário são válidas apenas dentro da sessão atual. Elas não podem ser reutilizadas em consultas ou sessões diferentes.

2. Dependência da ordem de processamento * Variáveis definidas pelo usuário dependem da ordem de execução, portanto, definir ORDER BY corretamente é fundamental.

3. Legibilidade e manutenção do SQL * As consultas podem se tornar complexas, portanto, no MySQL 8.0 e posteriores, recomenda-se usar funções de janela.

Resumo

Nas versões do MySQL abaixo de 8.0, você pode usar variáveis definidas pelo usuário para implementar numeração sequencial e classificação em vez de funções de janela. No entanto, como as consultas tendem a ficar mais complexas, é melhor considerar a migração para uma versão mais recente sempre que possível.

6. Avisos e Melhores Práticas

A função ROW_NUMBER() do MySQL e as alternativas baseadas em variáveis são muito convenientes, mas há pontos importantes a serem lembrados para executá‑las com precisão e eficiência. Esta seção explica avisos práticos e melhores práticas para otimização de desempenho.

6-1. Considerações de desempenho

1. Custo do ORDER BY

ROW_NUMBER() é sempre usado com ORDER BY. Como requer ordenação, o tempo de processamento pode aumentar significativamente para grandes conjuntos de dados.

Mitigação:

• Use índices: Adicione índices às colunas usadas no ORDER BY para acelerar a ordenação.
• Use LIMIT: Recupere apenas o número de linhas que realmente precisa para reduzir a quantidade de dados processados.

Exemplo:

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. Aumento do uso de memória e I/O de disco

As funções de janela são processadas usando tabelas temporárias e memória. À medida que o volume de dados cresce, o consumo de memória e o I/O de disco podem aumentar.

Mitigação:

• Divida consultas: Quebre o processamento em consultas menores e extraia os dados passo a passo para reduzir a carga.
• Use tabelas temporárias: Armazene os dados extraídos em uma tabela temporária e execute a agregação a partir dela para distribuir a carga de trabalho.

6-2. Dicas de otimização de consultas

1. Verifique o plano de execução

No MySQL, você pode usar EXPLAIN para verificar o plano de execução da consulta. Isso ajuda a confirmar se os índices estão sendo usados corretamente.

Exemplo:

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Saída de exemplo:

Se você vir Using index, isso indica que o índice está sendo usado adequadamente.

2. Otimize índices

Certifique‑se de adicionar índices às colunas usadas em ORDER BY e WHERE. Preste atenção especial ao seguinte.

• Índices de coluna única: Bons para condições de ordenação simples
• Índices compostos: Eficazes quando múltiplas colunas estão envolvidas nas condições

Exemplo:

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. Use processamento em lote

Em vez de processar um grande conjunto de dados de uma só vez, você pode reduzir a carga processando os dados em lotes.

Exemplo:

SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 1000;

6-3. Mantendo a consistência dos dados

1. Atualizações e recálculo

Quando linhas são inseridas ou excluídas, a numeração pode mudar. Crie um mecanismo para recalcular os números conforme necessário.

Exemplo:

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Usar uma view ajuda a manter as classificações atualizadas com base nos dados mais recentes.

6-4. Exemplo de consulta com boas práticas

Abaixo está um exemplo de boas práticas que consideram desempenho e manutenibilidade.

Exemplo: Extrair as N primeiras linhas

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

Esta estrutura usa uma expressão de tabela comum (CTE) para melhorar a legibilidade e a reutilização.

Resumo

Ao usar ROW_NUMBER() ou suas alternativas, tenha em mente os seguintes pontos:

1. Melhore a velocidade através da otimização de índices.
2. Identifique gargalos verificando o plano de execução.
3. Planeje atualizações de dados e mantenha a consistência.
4. Use processamento em lote e CTEs para distribuir a carga.

Aplicar essas melhores práticas permitirá um processamento eficiente para análise e geração de relatórios em grande escala.

7. Conclusão

Neste artigo, focamos na função ROW_NUMBER() do MySQL, explicando tudo desde o uso básico e exemplos práticos até alternativas para versões mais antigas, além de ressalvas e boas práticas. Nesta seção, recapitularemos os pontos principais e resumiremos as lições práticas.

7-1. Por que o ROW_NUMBER() é útil

A função ROW_NUMBER() é especialmente conveniente para análise de dados e geração de relatórios das seguintes maneiras:

1. Numeração sequencial dentro de grupos: Crie facilmente rankings de vendas por departamento ou rankings baseados em categorias.
2. Extração das N primeiras linhas: Filtre e extraia dados de forma eficiente com base em condições específicas.
3. Detecção e exclusão de duplicatas: Útil para limpeza e organização de dados.

Como simplifica consultas complexas, melhora significativamente a legibilidade e a manutenção do SQL.

7-2. Comparação com outras funções de janela

Em comparação com funções de janela como RANK() e DENSE_RANK(), ROW_NUMBER() difere ao atribuir um número único mesmo para valores idênticos.

Escolhendo a função correta:
Selecionar a melhor função para seu propósito permite um processamento de dados eficiente.

7-3. Lidando com versões mais antigas do MySQL

Para ambientes abaixo do MySQL 8.0, também apresentamos abordagens usando variáveis definidas pelo usuário. No entanto, você deve considerar estas ressalvas:

• Legibilidade reduzida devido a SQL mais complexo
• A otimização de consultas pode ser mais difícil em alguns casos
• Pode ser necessário tratamento adicional para manter a consistência dos dados

Se possível, considere fortemente migrar para o MySQL 8.0 ou superior e usar funções de janela.

7-4. Pontos-chave para otimização de desempenho

1. Use índices: Adicione índices às colunas usadas em ORDER BY para melhorar a velocidade.
2. Verifique planos de execução: Valide o desempenho antecipadamente com EXPLAIN.
3. Adote processamento em lote: Processar grandes conjuntos de dados em blocos menores para distribuir a carga.
4. Use visualizações e CTEs: Melhore a reutilização e simplifique consultas complexas.

Aplicando essas técnicas, você pode alcançar um processamento de dados eficiente e estável.

7-5. Notas finais

ROW_NUMBER() é uma ferramenta poderosa que pode melhorar significativamente a eficiência da análise de dados.
Neste artigo, abordamos tudo, desde a sintaxe básica e exemplos práticos até ressalvas e alternativas.

Incentivamos você a executar as consultas por conta própria enquanto acompanha este artigo. Melhorar suas habilidades em SQL ajudará a enfrentar análises e relatórios de dados mais complexos com confiança.

Apêndice: Recursos de referência

• Documentação oficial: MySQL Window Functions
• Ambiente SQL online: SQL Fiddle (uma ferramenta que permite executar e testar SQL online)