Obter registros com maior / menor por grupo

Como fazer isso?

O antigo título desta pergunta foi ” usando rank (@Rank: = @Rank + 1) em consultas complexas com subconsultas – funcionará? ” Porque eu estava procurando solução usando ranks, mas agora vejo que a solução postada por Bill é muito melhor.

Pergunta original:

Eu estou tentando compor uma consulta que levaria o último registro de cada grupo, dada alguma ordem definida:

SET @Rank=0; select s.* from (select GroupId, max(Rank) AS MaxRank from (select GroupId, @Rank := @Rank + 1 AS Rank from Table order by OrderField ) as t group by GroupId) as t join ( select *, @Rank := @Rank + 1 AS Rank from Table order by OrderField ) as s on t.GroupId = s.GroupId and t.MaxRank = s.Rank order by OrderField 

Expression @Rank := @Rank + 1 é normalmente usado para rank, mas para mim parece suspeito quando usado em 2 subqueries, mas inicializado apenas uma vez. Funcionará desta maneira?

Em segundo lugar, funcionará com uma subconsulta avaliada várias vezes? Como subconsulta em onde (ou tendo) cláusula (outra maneira como escrever o acima):

 SET @Rank=0; select Table.*, @Rank := @Rank + 1 AS Rank from Table having Rank = (select max(Rank) AS MaxRank from (select GroupId, @Rank := @Rank + 1 AS Rank from Table as t0 order by OrderField ) as t where t.GroupId = table.GroupId ) order by OrderField 

Desde já, obrigado!

Então você quer obter a linha com o maior OrderField por grupo? Eu faria assim:

 SELECT t1.* FROM `Table` AS t1 LEFT OUTER JOIN `Table` AS t2 ON t1.GroupId = t2.GroupId AND t1.OrderField < t2.OrderField WHERE t2.GroupId IS NULL ORDER BY t1.OrderField; // not needed! (note by Tomas) 

( EDITAR por Tomas: Se houver mais registros com o mesmo OrderField dentro do mesmo grupo e você precisar exatamente de um deles, você pode querer estender a condição:

 SELECT t1.* FROM `Table` AS t1 LEFT OUTER JOIN `Table` AS t2 ON t1.GroupId = t2.GroupId AND (t1.OrderField < t2.OrderField OR (t1.OrderField = t2.OrderField AND t1.Id < t2.Id)) WHERE t2.GroupId IS NULL 

final da edição.)

Em outras palavras, retorne a linha t1 para a qual nenhuma outra linha t2 existe com o mesmo GroupId e um OrderField maior. Quando t2.* É NULL, significa que a junit externa esquerda não encontrou essa correspondência e, portanto, t1 tem o maior valor de OrderField no grupo.

Sem classificações, sem subconsultas. Isso deve ser executado rapidamente e otimizar o access a t2 com "Usando o índice" se você tiver um índice composto em (GroupId, OrderField) .


Em relação ao desempenho, veja minha resposta em Recuperando o último registro em cada grupo . Eu tentei um método de subconsulta e o método de associação usando o despejo de dados de estouro de pilha. A diferença é notável: o método de junit foi executado 278 vezes mais rápido no meu teste.

É importante que você tenha o índice certo para obter os melhores resultados!

Em relação ao seu método usando a variável @Rank, ele não funcionará como você o escreveu, porque os valores de @Rank não serão redefinidos para zero após a consulta ter processado a primeira tabela. Eu vou te mostrar um exemplo.

Inseri alguns dados fictícios, com um campo extra que é nulo, exceto na linha que sabemos ser a maior por grupo:

 select * from `Table`; +---------+------------+------+ | GroupId | OrderField | foo | +---------+------------+------+ | 10 | 10 | NULL | | 10 | 20 | NULL | | 10 | 30 | foo | | 20 | 40 | NULL | | 20 | 50 | NULL | | 20 | 60 | foo | +---------+------------+------+ 

Podemos mostrar que a sorting aumenta para três para o primeiro grupo e seis para o segundo grupo, e a consulta interna os retorna corretamente:

 select GroupId, max(Rank) AS MaxRank from ( select GroupId, @Rank := @Rank + 1 AS Rank from `Table` order by OrderField) as t group by GroupId +---------+---------+ | GroupId | MaxRank | +---------+---------+ | 10 | 3 | | 20 | 6 | +---------+---------+ 

Agora execute a consulta sem condição de junit, para forçar um produto cartesiano de todas as linhas, e também buscamos todas as colunas:

 select s.*, t.* from (select GroupId, max(Rank) AS MaxRank from (select GroupId, @Rank := @Rank + 1 AS Rank from `Table` order by OrderField ) as t group by GroupId) as t join ( select *, @Rank := @Rank + 1 AS Rank from `Table` order by OrderField ) as s -- on t.GroupId = s.GroupId and t.MaxRank = s.Rank order by OrderField; +---------+---------+---------+------------+------+------+ | GroupId | MaxRank | GroupId | OrderField | foo | Rank | +---------+---------+---------+------------+------+------+ | 10 | 3 | 10 | 10 | NULL | 7 | | 20 | 6 | 10 | 10 | NULL | 7 | | 10 | 3 | 10 | 20 | NULL | 8 | | 20 | 6 | 10 | 20 | NULL | 8 | | 20 | 6 | 10 | 30 | foo | 9 | | 10 | 3 | 10 | 30 | foo | 9 | | 10 | 3 | 20 | 40 | NULL | 10 | | 20 | 6 | 20 | 40 | NULL | 10 | | 10 | 3 | 20 | 50 | NULL | 11 | | 20 | 6 | 20 | 50 | NULL | 11 | | 20 | 6 | 20 | 60 | foo | 12 | | 10 | 3 | 20 | 60 | foo | 12 | +---------+---------+---------+------------+------+------+ 

Podemos ver pelo acima que a sorting máxima por grupo está correta, mas então o @Rank continua a aumentar à medida que processa a segunda tabela derivada, para 7 e em maior. Portanto, os ranks da segunda tabela derivada nunca se sobrepõem aos ranks da primeira tabela derivada.

Você teria que adicionar outra tabela derivada para forçar o @Rank a zerar entre o processamento das duas tabelas (e esperar que o otimizador não altere a ordem na qual ele avalia as tabelas, ou use STRAIGHT_JOIN para evitar isso):

 select s.* from (select GroupId, max(Rank) AS MaxRank from (select GroupId, @Rank := @Rank + 1 AS Rank from `Table` order by OrderField ) as t group by GroupId) as t join (select @Rank := 0) r -- RESET @Rank TO ZERO HERE join ( select *, @Rank := @Rank + 1 AS Rank from `Table` order by OrderField ) as s on t.GroupId = s.GroupId and t.MaxRank = s.Rank order by OrderField; +---------+------------+------+------+ | GroupId | OrderField | foo | Rank | +---------+------------+------+------+ | 10 | 30 | foo | 3 | | 20 | 60 | foo | 6 | +---------+------------+------+------+ 

Mas a otimização dessa consulta é terrível. Ele não pode usar nenhum índice, cria duas tabelas temporárias, classifica-as da maneira mais difícil e até usa um buffer de junit porque não pode usar um índice ao ingressar em tabelas temporárias. Este é um exemplo de saída do EXPLAIN :

 +----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+ | 1 | PRIMARY |  | system | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort | | 1 | PRIMARY |  | ALL | NULL | NULL | NULL | NULL | 2 | | | 1 | PRIMARY |  | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using join buffer | | 5 | DERIVED | Table | ALL | NULL | NULL | NULL | NULL | 6 | Using filesort | | 4 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | DERIVED |  | ALL | NULL | NULL | NULL | NULL | 6 | Using temporary; Using filesort | | 3 | DERIVED | Table | ALL | NULL | NULL | NULL | NULL | 6 | Using filesort | +----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+ 

Considerando que minha solução usando a junit externa esquerda otimiza muito melhor. Ele não usa tabela temporária e até mesmo reporta "Using index" que significa que ele pode resolver a junit usando apenas o índice, sem tocar nos dados.

 +----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 6 | Using filesort | | 1 | SIMPLE | t2 | ref | GroupId | GroupId | 5 | test.t1.GroupId | 1 | Using where; Using index | +----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+ 

Você provavelmente lerá pessoas fazendo afirmações em seus blogs que "se juntam à lentidão do SQL", mas isso não faz sentido. A otimização deficiente torna o SQL lento.