Obtener registros con mayor / menor por grupo

Question

Obtener registros con mayor / menor por grupo

¿Cómo hacer eso?

El título anterior de esta pregunta era " usando rank (@Rank := @Rank + 1) en consultas complejas con subconsultas - ¿funcionará?" porque estaba buscando una solución usando rangos, pero ahora veo que la solución publicada por Bill es mucho mejor.

Pregunta original:

Estoy tratando de componer una consulta que tomaría el último registro de cada grupo dado un orden definido:

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

La expresión @Rank := @Rank + 1 se usa normalmente para el rango, pero para mí parece sospechoso cuando se usa en 2 subconsultas, pero se inicializa solo una vez. ¿Funcionará así?

Y segundo, ¿funcionará con una subconsulta que se evalúa varias veces? Como subconsulta en la cláusula where (or having) (otra forma de escribir lo anterior):

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

Gracias de antemano!

76

mysql greatest-n-per-group rank subquery

Author: TMS, 2012-01-06

Source

1 answers

score 147 · Accepted Answer

Así que desea obtener la fila con el mayor OrderField por grupo? Lo haría de esta manera:

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: Si hay más registros con el mismo campo de orden dentro del mismo grupo y necesita exactamente uno de ellos, es posible que desee extender la condición:

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

Fin de la edición.)

En otras palabras, devuelve la fila t1 para la que no existe otra fila t2 con la misma GroupId y una mayor OrderField. Cuando t2.* es NULL, significa la unión externa izquierda no se encontró tal coincidencia, y por lo tanto t1 tiene el mayor valor de OrderField en el grupo.

Sin rangos, sin subconsultas. Esto debería ejecutarse rápidamente y optimizar el acceso a t2 con "Using index" si tiene un índice compuesto en (GroupId, OrderField).

En cuanto a rendimiento, ver mi respuesta a Recuperar el último registro en cada grupo. Probé un método de subconsulta y el método join usando el volcado de datos de desbordamiento de pila. La diferencia es notable: el método de unión funcionó 278 veces más rápido en mi prueba.

¡Es importante que tengas el índice correcto para obtener los mejores resultados!

Con respecto a su método que usa la variable @Rank, no funcionará como lo ha escrito, porque los valores de @Rank no se restablecerán a cero después de que la consulta haya procesado la primera tabla. Les mostraré un ejemplo.

Inserté algunos datos ficticios, con un campo adicional que es nulo excepto en la fila que sabemos que es la mayor 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 demostrar que el rango aumenta a tres para el primer grupo y seis para el segundo grupo, y la consulta interna devuelve estos correctamente:

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 |
+---------+---------+

Ahora ejecutamos la consulta sin condición de unión, para forzar un producto cartesiano de todas las filas, y también obtenemos todas las columnas:

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 de lo anterior que el rango máximo por grupo es correcto, pero luego el rango @continúa aumentando a medida que procesa la segunda tabla derivada, a 7 y más arriba. Así que los rangos de la segunda tabla derivada nunca se superpondrán con los rangos de la primera tabla derivada en absoluto.

Tendría que agregar otra tabla derivada para forzar a @Rank a restablecer a cero entre el procesamiento de las dos tablas (y esperar que el optimizador no cambie el orden en el que evalúa las tablas, o bien use STRAIGHT_JOIN para evitarlo):

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 |
+---------+------------+------+------+

Pero la optimización de esta consulta es terrible. No puede usar ningún índice, crea dos tablas temporales, las ordena de la manera difícil, e incluso usa un búfer de unión porque no puede usar un indexar al unirse a tablas temporales tampoco. Este es un ejemplo de salida de EXPLAIN:

+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+
| 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                  |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+

Mientras que mi solución usando la unión externa izquierda optimiza mucho mejor. No utiliza ninguna tabla temporal e incluso reporta "Using index" lo que significa que puede resolver la unión usando solo el índice, sin tocar los datos.

+----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+
| 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 |
+----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+

Probablemente leerá a la gente que hace afirmaciones en sus blogs que "se une a hacer que SQL sea lento", pero eso no tiene sentido. Una mala optimización hace que SQL sea lento.