mysql - remix - lary over sola

Obtener registros con el menor/menor<cualquier cosa> por grupo

Question

¿Como hacer eso?

El título anterior de esta pregunta era " usar rango (@Rank: = @Rank + 1) en una consulta compleja 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:

Intento redactar 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

Expression @Rank := @Rank + 1 se usa normalmente para rango, pero para mí parece sospechoso cuando se usa en 2 subconsultas, pero se inicializa solo una vez. ¿Funcionará de esta manera?

Y segundo, ¿funcionará con una subconsulta que se evalúa varias veces? Como subconsulta en where (o having) cláusula (otra manera 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 por adelantado!

Answer 1

Entonces, ¿desea obtener la fila con el campo OrderField más 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)

( EDIT por Tomas: si hay más registros con el mismo OrderField 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 editar)

En otras palabras, devuelva la fila t1 para la que no existe otra fila t2 con el mismo GroupId y un campo de orden mayor. Cuando t2.* Es NULL, significa que la unión externa izquierda no 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 "Usar índice" si tiene un índice compuesto (GroupId, OrderField) .

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

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

En cuanto a su método utilizando 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. Te mostraré un ejemplo.

Inserté algunos datos ficticios, con un campo adicional que es nulo, excepto en la fila que sabemos que es la más grande 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 a seis para el segundo grupo, y la consulta interna los devuelve 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 ejecute la consulta sin condición de unión, para forzar un producto cartesiano de todas las filas, y también recuperamos 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 a partir de lo anterior que el rango máximo por grupo es correcto, pero luego el @Rank continúa aumentando a medida que procesa la segunda tabla derivada, a 7 y a mayor. Entonces, los rangos de la segunda tabla derivada nunca se superpondrán con los rangos de la primera tabla derivada.

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 que evalúa las tablas, o bien use STRAIGHT_JOIN para evitar eso):

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 clasifica de la manera difícil e incluso utiliza un búfer de unión porque tampoco puede usar un índice al unir tablas temporales. 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 que usa la combinación externa izquierda optimiza mucho mejor. No utiliza ninguna tabla temporal e incluso informa "Using index" 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 leas a las personas que hacen afirmaciones en sus blogs que "se unen hacen que SQL sea lento", pero eso no tiene sentido. La mala optimización hace que SQL sea lento.