Como retornar o último registro por grupo ?

Boa Noite Pessoal,

Eu tinha outros planos para a próxima postagem, mas ultimamente nos fóruns da Microsoft e nas comunidades do Orkut, estou vendo uma dúvida com uma recorrência enorme. A dúvida muda um pouco o enunciado cada vez que ela aparece, mas a essência é basicamente a mesma. "Quero selecionar 1 registro de cada Codigo", "Select – Data mais recente", "Buscar mais de um campo de tabela agrupando somente por um campo", etc. Esse problema na verdade é uma versão mais específica do "TOP N" que descrevi em um dos meus Webcast e cuja a solução está disponível no blog (Dicas e Truques sobre consultas complexas no SQL Server).

Vou descrever melhor o problema e sua solução através de um exemplo. O script abaixo, cria uma tabela com alguns registros de lancamentos de conta corrente.

— Cria a tabela de clientes
CREATE TABLE Clientes (
    IDCliente INT, NomeCliente VARCHAR(50))

— Popula a tabela de Clientes
INSERT INTO Clientes VALUES (1, ‘Bianca’)
INSERT INTO Clientes VALUES (2, ‘Herbert’)
INSERT INTO Clientes VALUES (3, ‘Paloma’)
INSERT INTO Clientes VALUES (4, ‘Marcos’)
INSERT INTO Clientes VALUES (5, ‘Jonas’)
INSERT INTO Clientes VALUES (6, ‘Tatiana’)

— Cria a tabela de saldos
CREATE TABLE Lancamentos (
    IDCliente INT, Data SMALLDATETIME,
    Valor SMALLMONEY, Tipo CHAR(1))

— Popula a tabela de Saldos
INSERT INTO Lancamentos VALUES (1, ‘20090109’,15.00,‘C’)
INSERT INTO Lancamentos VALUES (1, ‘20090110’,25.00,‘C’)
INSERT INTO Lancamentos VALUES (1, ‘20090111’,10.00,‘D’)
INSERT INTO Lancamentos VALUES (2, ‘20090110’,15.00,‘D’)
INSERT INTO Lancamentos VALUES (2, ‘20090111’,26.00,‘D’)
INSERT INTO Lancamentos VALUES (3, ‘20090111’,19.00,‘C’)
INSERT INTO Lancamentos VALUES (4, ‘20090108’,42.00,‘D’)
INSERT INTO Lancamentos VALUES (4, ‘20090109’,33.00,‘C’)
INSERT INTO Lancamentos VALUES (4, ‘20090110’,18.00,‘C’)
INSERT INTO Lancamentos VALUES (4, ‘20090111’,11.00,‘D’)
INSERT INTO Lancamentos VALUES (5, ‘20090109’,23.00,‘C’)
INSERT INTO Lancamentos VALUES (5, ‘20090110’,55.00,‘C’)

O objetivo normalmente é muito simples. Para o script em questão é necessário obter o último lançamento para cada cliente. O último lançamento é identificado tendo-se por base a data mais recente. Já vi alguns exemplos incorretos, mas que esboçam uma primeira tentativa:

SELECT * FROM Lancamentos
WHERE Data = (SELECT MAX(Data) FROM Saldos)

SELECT IDCliente, MAX(Data) As UltimaData, Valor, Tipo
FROM Lancamentos
GROUP BY IDCliente, Valor, Tipo

SELECT IDCliente, MAX(Data) As UltimaData, Valor, Tipo
FROM Lancamentos
GROUP BY IDCliente

SELECT IDCliente, MAX(Data) As UltimaData, MAX(Valor) As UltimoValor,
MAX(Tipo) As UltimoTipo FROM Lancamentos
GROUP BY IDCliente

O primeiro SELECT está errado porque ele apenas retornará todos os lançamentos que ocorreram na última data e isso não necessariamente significa retornar o último lançamento para cada cliente, visto que podem haver clientes que tiveram lançamentos na última data (que é o caso do cliente 5).

O segundo SELECT está incorreto, porque a idéia é demonstrar o último lançamento por cliente. Da forma como a cláusula GROUP BY está formulada, será retornado o último lançamento pode cliente, por valor e por tipo. Como não há combinações cliente, valor e tipo repetidas, na prática essa cláusula não terá nenhum efeito e os 12 lançamentos serão retornados.

O terceiro SELECT até faz algum sentido, mas a cláusula GROUP BY exige que todos os campos que não estejam em uma função de agregação ou não sejam uma constante sejam contemplados. Assim, como Valor e Tipo não são utilizados por nenhuma função de agregação e nem representam constantes, essa instrução irá provocar um erro de sintaxe.

O último SELECT é uma tentativa de eliminar as restrições do terceiro SELECT, mas também não retorna os resultados corretamente. O MAX irá retornar o maior valor para uma determinada coluna e não necessariamente a maior data está associada ao maior valor. Considerando que existem apenas dois tipos de lançamento (C para crédito e D para débito), os clientes que possuírem débito terão o MAX(Tipo) igual a D e não necessariamente o último lançamento é um débito.

A utilização do MAX certamente faz parte da resposta, mas ela sozinha não é suficiente. O detalhe para encontrar o caminho da resposta está em perceber que o MAX é útil para encontrar a última data e com ele podemos encontrar a última data para cada cliente representando quando ocorreu seu último lançamento.

SELECT IDCliente, MAX(Data) As UltimaData
FROM Saldos GROUP BY IDCliente

O retorno da consulta é explicitado na tabela abaixo:

IDCliente	UltimaData
1	2009-01-11 00:00:00
2	2009-01-11 00:00:00
3	2009-01-11 00:00:00
4	2009-01-11 00:00:00
5	2009-01-10 00:00:00

Uma vez que tenhamos o ID do cliente e a data do último lançamento (UltimaData), basta realizar um JOIN entre esse resultado e a tabela de lançamentos. Combinando esse resultado com a tabela de lançamentos através das colunas IDCliente e UltimaData com IDCliente e Data poderemos obter todos os detalhes do último lançamento.

— Utilização de Subqueries (Derived Table)
SELECT L1.* FROM Lancamentos As L1
INNER JOIN (
    SELECT IDCliente, MAX(Data) As UltimaData
    FROM Lancamentos GROUP BY IDCliente) As L2
        ON L1.IDCliente = L2.IDCliente AND L1.Data = L2.UltimaData
ORDER BY L1.IDCliente

— Utilização de CTEs (2005 e superiores)
;WITH L2 As (
    SELECT IDCliente, MAX(Data) As UltimaData
    FROM Lancamentos GROUP BY IDCliente)

SELECT L1.* FROM Lancamentos As L1
INNER JOIN L2 ON L1.IDCliente = L2.IDCliente AND L1.Data = L2.UltimaData
ORDER BY L1.IDCliente

Ambas as construções retornam os últimos lançamentos de cada cliente na tabela de lançamentos

IDCliente	Data	Valor	Tipo
1	2009-01-11 00:00:00	10,00	D
2	2009-01-11 00:00:00	26,00	D
3	2009-01-11 00:00:00	19,00	C
4	2009-01-11 00:00:00	11,00	D
5	2009-01-10 00:00:00	55,00	C

Caso seja necessário exibir o nome do cliente, basta combinar essa consulta com a tabela de clientes. Podemos perceber que o cliente 6 (Tatiana) não teve nenhum lançamento e por isso é interessante utilizar o LEFT OUTER JOIN. Mostrarei a sintaxe utilizando-me do recursos de CTEs disponível apenas para o SQL Server 2005 e posteriores. É possível resolver utilizando somente subqueries desde que o script seja adaptado.

— Definir a data do último lançamento por cliente
;WITH UL1 As (
    SELECT IDCliente, MAX(Data) As UltimaData
    FROM Lancamentos GROUP BY IDCliente),

— Recuperar todos os dados do último lançamento por cliente
UL2 As (
    SELECT L.* FROM Lancamentos As L INNER JOIN UL1
    ON L.IDCliente = UL1.IDCliente AND L.Data = UL1.UltimaData)

— Combinar os registros do último lançamento com a tabela de clientes
SELECT NomeCliente, Data, Valor, Tipo
FROM Clientes
LEFT OUTER JOIN UL2 ON Clientes.IDCliente = UL2.IDCliente

O comando retorna exatamente como o esperado (incluindo clientes que não possuam lançamentos):

NomeCliente	Data	Valor	Tipo
Bianca	2009-01-11 00:00:00	10,00	D
Herbert	2009-01-11 00:00:00	26,00	D
Paloma	2009-01-11 00:00:00	19,00	C
Marcos	2009-01-11 00:00:00	11,00	D
Jonas	2009-01-10 00:00:00	55,00	C
Tatiana	NULL	NULL	NULL

Para que a consulta funcione perfeitamente é necessário um pré-requisito que embora esteja presente na maioria das vezes em que esse tipo de dúvida aparece ele não é obrigatório. No exemplo em questão, estamos pressupondo que nunca haverá "empate", ou seja, nunca dois registros serão eleitos como o último lançamento. Se colocarmos mais de um lançamento por dia teremos situações de empate e o resultado pode ser "negocialmente" incorreto.

— Insere um lancamento para o cliente 5
INSERT INTO Lancamentos VALUES (5, ‘20090110’,65.00,‘D’)

— Definir a data do último lançamento por cliente
;WITH UL1 As (
    SELECT IDCliente, MAX(Data) As UltimaData
    FROM Lancamentos GROUP BY IDCliente),

— Recuperar todos os dados do último lançamento por cliente
UL2 As (
    SELECT L.* FROM Lancamentos As L INNER JOIN UL1
    ON L.IDCliente = UL1.IDCliente AND L.Data = UL1.UltimaData)

— Combinar os registros do último lançamento com a tabela de clientes
SELECT NomeCliente, Data, Valor, Tipo
FROM Clientes
LEFT OUTER JOIN UL2 ON Clientes.IDCliente = UL2.IDCliente

O resultado inclui um lançamento para cada cliente, mas no caso do cliente 5 (Jonas) há dois lançamentos

NomeCliente	Data	Valor	Tipo
Bianca	2009-01-11 00:00:00	10,00	D
Herbert	2009-01-11 00:00:00	26,00	D
Paloma	2009-01-11 00:00:00	19,00	C
Marcos	2009-01-11 00:00:00	11,00	D
Jonas	2009-01-10 00:00:00	55,00	C
Jonas	2009-01-10 00:00:00	65,00	D
Tatiana	NULL	NULL	NULL

O cliente Jonas apareceu duas vezes por que o critério de último lançamento informa que o último lançamento é aquele cuja a data seja a maior. No caso do Jonas, há dois lançamentos com a maior data (10/01/2009). A premissa de que não haveria empate não foi obedecida. Se houvesse um terceiro registro no dia 10/01/2009, o problema se repetiria. Para eliminá-lo é necessário estipular um critério de desempate (Tie Breaker). É possível estipular algumas regras adicionais como o maior valor ou ainda optar-se pelo crédito no caso de haver empates relacionados a data do último lançamento. Essas regras adicionais diminuem ainda mais a ocorrência de empates, mas nada impediria que Jonas fizesse outro lançamento cujo valor fosse igual a R$ 65,00 e cujo tipo fosse crédito. Nesse caso o empate permaneceria.

O melhor Tiebreaker possível é a própria chave primária da tabela. Normalmente tabelas de lançamentos possui um identificador (ou sequencial). O script abaixo adiciona esse sequencial e o promove a chave primária da tabela.

— Adicionar uma coluna de identificação
ALTER TABLE Lancamentos ADD IDLancamento INT IDENTITY(1,1) NOT NULL

— Promove a coluna IDLancamento à chave primária
ALTER TABLE Lancamentos ADD CONSTRAINT PK_Lancamento PRIMARY KEY (IDLancamento)

Como a chave primária é única, não existe a possibilidade dela "empatar" e podemos utilizá-la como critério de desempate. A consulta abaixo, utiliza o critério de maior data como último lançamento, mas em caso de empate, a chave primária que for maior será utilizada para desempatar.

— Definir a data do último lançamento por cliente
;WITH UL1 As (
    SELECT IDCliente, MAX(Data) As UltimaData
    FROM Lancamentos GROUP BY IDCliente),

— Recuperar o maior IdLancamento da maior por cliente
UL2 As (
    SELECT L.IDCliente, L.Data, MAX(IDLancamento) As UltimoIDLancamento
    FROM Lancamentos As L
        INNER JOIN UL1 ON L.IDCliente = UL1.IDCliente AND
            L.Data = UL1.UltimaData
    GROUP BY L.IDCliente, L.Data),

— Recuperar todos os dados do último lançamento por cliente
UL3 As (
    SELECT L.* FROM Lancamentos As L INNER JOIN UL2
    ON L.IDCliente = UL2.IDCliente AND L.Data = UL2.Data

— No caso de empate, vale o maior IDLancamento
    AND L.IDLancamento = UL2.UltimoIDLancamento)

— Combinar os registros do último lançamento com a tabela de clientes
SELECT NomeCliente, Data, Valor, Tipo
FROM Clientes
LEFT OUTER JOIN UL3 ON Clientes.IDCliente = UL3.IDCliente

Como o cliente Jonas tem dois registros em sua maior data (10/01/2009), a chave primária serviu como critério de desempate retornando o registro com o maior IDLancamento (no caso o lançamento a débito de R$ 65,00).

NomeCliente	Data	Valor	Tipo
Bianca	2009-01-11 00:00:00	10,00	D
Herbert	2009-01-11 00:00:00	26,00	D
Paloma	2009-01-11 00:00:00	19,00	C
Marcos	2009-01-11 00:00:00	11,00	D
Jonas	2009-01-10 00:00:00	65,00	D
Tatiana	NULL	NULL	NULL

Se considerarmos que o IDLancamento é sequencial e que o maior ID sempre expressará o último lançamento, seria melhor utilizar o MAX(IDLancamento) ao invés do MAX(Data). Nesse caso, o IDLancamento que já é um ótimo Tiebreaker, também retornaria o ID do último lançamento.

— Definir a data do último lançamento por cliente
;WITH UL1 As (
    SELECT IDCliente, MAX(IDLancamento) As UltimoLancamento
    FROM Lancamentos GROUP BY IDCliente),

— Recuperar todos os dados do último lançamento por cliente
UL2 As (
    SELECT L.* FROM Lancamentos As L INNER JOIN UL1
    ON L.IDCliente = UL1.IDCliente AND L.IDLancamento = UL1.UltimoLancamento)

— Combinar os registros do último lançamento com a tabela de clientes
SELECT NomeCliente, Data, Valor, Tipo
FROM Clientes
LEFT OUTER JOIN UL2 ON Clientes.IDCliente = UL2.IDCliente

O resultado foi o mesmo que o apresentado anteriormente

NomeCliente	Data	Valor	Tipo
Bianca	2009-01-11 00:00:00	10,00	D
Herbert	2009-01-11 00:00:00	26,00	D
Paloma	2009-01-11 00:00:00	19,00	C
Marcos	2009-01-11 00:00:00	11,00	D
Jonas	2009-01-10 00:00:00	65,00	D
Tatiana	NULL	NULL	NULL

A consulta ficou bem mais simples e embora tenha descartado a necessidade de um tiebreaker, ela confiou na premissa de que o último IDLancamento sempre expressará o lançamento mais recente. Essa premissa é verdadeira na maioria dos casos, mas tenha em mente que lançamentos retroativos (ou atrasados) podem fazer com que ela não seja mais verdadeira e nesse caso a solução anterior seria a mais correta.

Todas essas considerações e scripts mostram como realizar consultas do tipo "o último registro pelo campo ?", mas os novos recursos do Transact SQL a partir do SQL Server 2005 fazem com que tais scripts sejam desnecessários. Os operadores CROSS APPLY e OUTER APPLY são capazes de resolver esse problema de forma mais efetiva.

— Retorna o nome do cliente e seu último lançamento
— Considera apenas os clientes que possuam lançamentos
— As questões de TieBreaker estão devidamente tratadas
SELECT NomeCliente, Data, Valor, Tipo
FROM Clientes As C
CROSS APPLY (
    SELECT TOP 1 Data, Valor, Tipo
    FROM Lancamentos As L WHERE C.IDCliente = L.IDCliente
    ORDER BY Data DESC, IDLancamento DESC) As UltimoLancamento

O resultado esperado é bem próximo, com exceção da cliente Tatiana que por não possuir nenhum lançamento não apareceu no resultado

NomeCliente	Data	Valor	Tipo
Bianca	2009-01-11 00:00:00	10,00	D
Herbert	2009-01-11 00:00:00	26,00	D
Paloma	2009-01-11 00:00:00	19,00	C
Marcos	2009-01-11 00:00:00	11,00	D
Jonas	2009-01-10 00:00:00	65,00	D

O operador CROSS APPLY tem uma familiaridade muito próximo com o INNER JOIN (embora não sejam a mesma coisa) e por isso não considerou clientes sem lançamentos. A solução é utilizar o operador OUTER APPLY (que possui uma familiaridade com OUTER JOINs).

— Retorna o nome do cliente e seu último lançamento
— Considera apenas os clientes que possuam lançamentos
— As questões de TieBreaker estão devidamente tratadas
SELECT NomeCliente, Data, Valor, Tipo
FROM Clientes As C
OUTER APPLY (
    SELECT TOP 1 Data, Valor, Tipo
    FROM Lancamentos As L WHERE C.IDCliente = L.IDCliente
    ORDER BY Data DESC, IDLancamento DESC) As UltimoLancamento

NomeCliente	Data	Valor	Tipo
Bianca	2009-01-11 00:00:00	10,00	D
Herbert	2009-01-11 00:00:00	26,00	D
Paloma	2009-01-11 00:00:00	19,00	C
Marcos	2009-01-11 00:00:00	11,00	D
Jonas	2009-01-10 00:00:00	65,00	D
Tatiana	NULL	NULL	NULL

O interessante desses novos operadores é que eles ajudam a resolver outros problemas que seriam muito mais trabalhosos de fazer. Se ao invés do último lançamento fosse necessário os dois últimos lançamentos, as abordagens anteriores que se baseavam no MAX seriam invalidadas enquanto que com os operadores CROSS APPLY e OUTER APPLY bastaria utilizar um TOP 2 ao invés de TOP 1. Por essas questões é que considerar a utilização de novos recursos é algo que deve ser observado.

O objetivo desse posts foi levar esclarescimentos a uma dúvida que nos últimos dias tem se tornado tão freqüente. Espero que os que a possuam e que leram esse post não tenham mais dificuldades na elaboração desse tipo de consulta.

[ ]s,

Gustavo

Publicidade