Como Recuperar o Último Registro em Cada Grupo no SQL

Recuperar o último registro de cada grupo em um banco de dados é uma necessidade comum, mas que pode ser desafiadora dependendo do sistema de gerenciamento de banco de dados (SGDB) utilizado. Este artigo explora várias formas de resolver esse problema em diferentes SGDBs, como MySQL, PostgreSQL, SQL Server e Oracle, utilizando abordagens modernas e eficientes.

1. Entendendo o Problema

O objetivo é obter o último registro (ou o registro mais recente) de cada grupo baseado em uma ou mais colunas. Por exemplo, em uma tabela de vendas, você pode querer encontrar a venda mais recente de cada cliente.

Exemplo de Tabela:

Resultado esperado: Queremos o último registro de cada cliente com base na coluna data_venda:

2. Soluções Gerais

2.1 Usando ROW_NUMBER()

O uso da função ROW_NUMBER() é uma abordagem moderna e eficiente suportada por muitos SGDBs.

Exemplo (SQL Server, PostgreSQL, Oracle):

sqlCopiar códigoWITH cte AS (
    SELECT 
        id, 
        cliente_id, 
        data_venda, 
        valor,
        ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY data_venda DESC) AS row_num
    FROM vendas
)
SELECT id, cliente_id, data_venda, valor
FROM cte
WHERE row_num = 1;

Como Funciona:

1. A função ROW_NUMBER()atribui um número a cada linha dentro de cada grupo (PARTITION BY cliente_id),ordenando por data_vendaem ordem decrescente.
2. No passo final,filtramos apenas os registros onde row_num=1.

2.2 Usando DISTINCT ON

PostgreSQL oferece a funcionalidade DISTINCT ON,que é extremamente eficiente para este tipo de problema.

Exemplo (PostgreSQL):

sqlCopiar códigoSELECT DISTINCT ON (cliente_id) id,cliente_id,data_venda,valor
FROM vendas
ORDER BY cliente_id,data_venda DESC;

Como Funciona:

1. DISTINCT ONretorna apenas o primeiro registro encontrado para cada grupo definido pela cláusula (cliente_id).
2. A cláusula ORDER BYdetermina qual registro será considerado o “primeiro”para cada grupo.

2.3 Usando Subconsultas

Subconsultas correlacionadas funcionam em qualquer SGDB,incluindo MySQL.

Exemplo (MySQL,SQL Server,Oracle):

sqlCopiar códigoSELECT id,cliente_id,data_venda,valor
FROM vendas v1
WHERE data_venda=(SELECT MAX(data_venda)
FROM vendas v2
WHERE v1.cliente_id=v2.cliente_id);

Como Funciona:

1. Para cada registro da tabela vendas,a subconsulta encontra a data máxima (MAX(data_venda)) para o mesmo cliente_id.
2. Apenas os registros com a maior data_vendasão retornados.

Desvantagens:

• Pode ser menos performático em tabelas grandes,já que a subconsulta é executada para cada linha.

2.4 Usando Funções de Agregação

Combinar GROUP BYcom funções de agregação pode ser uma abordagem simples.

Exemplo (MySQL,PostgreSQL,SQL Server):

sqlCopiar códigoSELECT v1.id,v1.cliente_id,v1.data_venda,v1.valor
FROM vendas v1
JOIN (SELECT cliente_id,MAX(data_venda) AS max_data
FROM vendas
GROUP BY cliente_id) v2
ON v1.cliente_id=v2.cliente_id AND v1.data_venda=v2.max_data;

Como Funciona:

1. O subquery encontra a data máxima (MAX(data_venda) para cada cliente.
2. O JOINcombina a tabela original com os resultados agregados para trazer os registros completos.

VAI GOSTAR:O que é normalização de banco de dados?–Descubra tudo sobre a normalização de banco de dados,incluindo as explicações detalhadas da 5NF e 2NF com exemplos práticos. Aprenda como melhorar a organização e a eficiência dos seus dados e otimize seus sistemas de forma profissional.

3. Abordagens Específicas por SGDB

3.1 MySQL (Antes do 8.0)

MySQL antes da versão 8.0 não suportava funções analíticas como ROW_NUMBER(). Nesse caso,o uso de subconsultas ou agregações é necessário.

Exemplo:

sqlCopiar códigoSELECT v1.*
FROM vendas v1
INNER JOIN (SELECT cliente_id,MAX(data_venda) AS max_data
FROM vendas
GROUP BY cliente_id) v2
ON v1.cliente_id=v2.cliente_id AND v1.data_venda=v2.max_data;

3.2 MySQL 8.0 ou Superior

Com suporte a ROW_NUMBER():

sqlCopiar códigoWITH cte AS (SELECT id,cliente_id,data_venda,valor,ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY data_venda DESC) AS row_num
FROM vendas)
SELECT id,cliente_id,data_venda,valor
FROM cte
WHERE row_num=1;

3.3 PostgreSQL

Usar DISTINCT ONé geralmente a abordagem mais eficiente:

sqlCopiar códigoSELECT DISTINCT ON (cliente_id) id,cliente_id,data_venda,valor
FROM vendas
ORDER BY cliente_id,data_venda DESC;

3.4 SQL Server

Usando ROW_NUMBER():

sqlCopiar códigoWITH cte AS (SELECT id,cliente_id,data_venda,valor,ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY data_venda DESC) AS row_num
FROM vendas)
SELECT id,cliente_id,data_venda,valor
FROM cte
WHERE row_num=1;

3.5 Oracle

Com funções analíticas:

SELECT id,cliente_id,data_venda,valor
FROM (
SELECT 
id,cliente_id,data_venda,valor,
ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY data_venda DESC) AS row_num
FROM vendas
)
WHERE row_num=1;

4. Desempenho e Otimizações

4.1 Índices

• Adicionar um índice nas colunas usadas para particionamento e ordenação (cliente_id,data_venda) pode melhorar significativamente o desempenho.

4.2 Tamanho da Tabela

• Em tabelas muito grandes,preferir abordagens que minimizam leituras redundantes,como DISTINCT ON(PostgreSQL) ou ROW_NUMBER().

4.3 Benchmarking

• Execute benchmarks em seu ambiente para comparar o desempenho de diferentes abordagens,especialmente se a tabela tiver milhões de registros.

5. Conclusão

Recuperar o último registro de cada grupo pode ser feito de várias formas,dependendo do SGDB e das necessidades específicas do sistema. Funções analíticas,como ROW_NUMBER(),são geralmente as mais eficientes e flexíveis,mas alternativas como DISTINCT ONe subconsultas também são úteis em cenários específicos. Entender as capacidades do SGDB escolhido e ajustar a abordagem para o contexto é essencial para garantir consultas rápidas e eficientes.

Referências Sobre o Tema

Aqui estão algumas referências confiáveis e úteis para aprofundar seu conhecimento sobre como recuperar o último registro em cada grupo em SQL:

Documentação Oficial

1. MySQL:
   • Documentação sobre Funções Analíticas:
     MySQL ROW_NUMBER()
   • Documentação sobre Funções Agregadas:
     MySQL Aggregate Functions
2. PostgreSQL:
   • Documentação do DISTINCT ON:
     PostgreSQL DISTINCT Clause
   • Documentação sobre Funções de Janela:
     PostgreSQL Window Functions
3. SQL Server:
   • Documentação do ROW_NUMBER():
     ROW_NUMBER() –SQL Server
   • Documentação sobre Agregação com GROUP BY:
     GROUP BY –SQL Server
4. Oracle:
   • Documentação sobre Funções Analíticas:
     Oracle Analytics Functions

Livros

Para quem gosta de se aprofundar com leitura,livros oferecem uma visão mais técnica e estruturada.

• SQL em 10 Minutos por dia,Sams Teach Yourself by Ben Forta
• SQL Queries for Mere Mortals by John L. Viescas and Michael J. Hernandez
• Aprendendo SQL by Alan Beaulieu (O’Reilly)
• Alto desempenho em SQL by Baron Schwartz (O’Reilly)

Ambientes Práticos

A prática é essencial para dominar SQL. Aqui estão algumas ferramentas para experimentar e executar consultas.

• DB Fiddle (Editor SQL online): https://www.db-fiddle.com/
• SQLzoo (Treinamento interativo): https://sqlzoo.net/
• Mode Analytics SQL Playground: https://mode.com/sql-tutorial/
• Kaggle SQL Practice: https://www.kaggle.com/learn/advanced-sql

VAI GOSTAR:

• Como Otimizar o MySQL com o Comando SET GLOBAL
• Como Otimizar a Performance do PostgreSQL
• SQL Server:DISTINCT e GROUP BY entenda as diferenças
• SQL:Diferenças entre UNION e UNION ALL
• Os Principais Comandos SQL que Todo Desenvolvedor Deve Conhecer
• O Problema N+1 Queries em SQL:Entenda e Resolva