Obter registros com maior / menor <tudo> por grupo

Como fazer isso

título @Former desta pergunta era "using rank (@Rank: = @Rank + 1) em consultas complexas com subconsultas - funcionará? "porque eu estava procurando uma solução usando classificações, mas agora vejo que a solução postada por Bill é muito melhor.

Pergunta original:

Estou tentando compor uma consulta que levaria o último registro de cada grupo, dada uma 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 classificação, mas para mim parece suspeito quando usado em duas subconsultas, mas inicializado apenas uma vez. Funcionará assim?

E segundo, ele funcionará com uma subconsulta avaliada várias vezes? Como a subconsulta na cláusula where (ou having) (outra maneira de 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

 TMS25 de mar de 2012 12:56

questionAnswers(1)

QuestionSolution

Então, você deseja obter a linha com o mais altoOrderField 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)

(EDIT by Tomas: Se houver mais registros com o mesmo OrderField no mesmo grupo e você precisar exatamente de um deles, convém 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

fim de edição.)

Em outras palavras, retorne a linhat1 para o qual nenhuma outra linhat2 existe com o mesmoGroupId e uma maiorOrderField. Quandot2.* é NULL, significa que a junção externa esquerda não encontrou essa correspondência e, portanto,t1 tem o maior valor deOrderField no grupo

Nenhuma classificação, nenhuma subconsulta. Isso deve ser executado rapidamente e otimizar o acesso ao T2 com "Usando índice" se você tiver um índice composto em(GroupId, OrderField).

Quanto ao desempenho, veja minha resposta para Recuperando o último registro em cada grupo. Tentei um método de subconsulta e o método join usando o despejo de dados Stack Overflow. A diferença é notável: o método join foi executado 278 vezes mais rápido no meu test

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

Quanto 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 depois que a consulta tiver processado a primeira tabela. Vou 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 classificação 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 junção, para forçar um produto cartesiano de todas as linhas, e também buscaremos 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 exposto acima que a classificação máxima por grupo está correta, mas o @Rank continua a aumentar à medida que processa a segunda tabela derivada, para 7 ou mais. Portanto, as classificações da segunda tabela derivada nunca se sobrepõem às classificações da primeira tabela derivad

Você teria que adicionar outra tabela derivada para forçar o @Rank a zerar entre o processamento das duas tabelas (e espero que o otimizador não altere a ordem em que avalia as tabelas ou use STRAIGHT_JOIN para impedir 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 desta 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 junção porque não pode usar um índice ao ingressar em tabelas temporárias. Este é um exemplo de saída deEXPLAIN:

+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | <derived4> | system | NULL          | NULL | NULL    | NULL |    1 | Using temporary; Using filesort |
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL | NULL    | NULL |    2 |                                 |
|  1 | PRIMARY     | <derived5> | 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     | <derived3> | 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 junção externa esquerda otimiza muito melhor. Ele não usa tabela temporária e até mesmo reporta"Using index", o que significa que pode resolver a junção usando apenas o índice, sem tocar nos dado

+----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+
| 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 declarações em seus blogs que "se juntam para tornar o SQL lento", mas isso não faz sentido. Má otimização torna o SQL lento.

 TMS06 de jan de 2012 07:37
Obrigado pela excelente resposta, Bill. No entanto, e se eu usasse@Rank1 e@Rank2, um para cada subconsulta? Isso resolveria o problema? Isso seria mais rápido que a sua solução?
 Bill Karwin06 de jan de 2012 08:50
Using@Rank1 e@Rank2 não faria diferença.
 ownking29 de abr de 2014 17:24
Obrigado por essa ótima solução. Eu estava lutando muito tempo com esse problema. Para as pessoas que desejam adicionar filtros para os outros campos, por exemplo, "foo", você precisa adicioná-los à condição de junção... AND t1.foo = t2.foo para obter os resultados corretos paraWHERE ... AND foo='bar'
 Andriy M05 de jan de 2012 22:20
Isso pode ser bastante útil (também para o OP), mas, infelizmente, não responde a nenhuma das duas pergunta
 TMS06 de jan de 2012 00:53
Obrigado, Bill, é uma boa ideia como evitar as fileiras, mas ... a adesão não seria lenta? A junção (sem a limitação da cláusula where) seria de tamanho muito maior do que nas minhas consultas. De qualquer forma, obrigado pela ideia! Mas eu também seria interessante na pergunta original, ou seja, se as fileiras funcionassem dessa maneir

yourAnswerToTheQuestion