Obtenga registros con el <whatever> más alto / más pequeño por grupo

¿Como hacer eso

El título anterior de esta pregunta era "using 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 tome 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

Expresión@Rank := @Rank + 1 normalmente se usa para el 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 la cláusula where (o having) (otra forma de cómo 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

 TMS25 mar. 2012 12:56
más preguntas avanzadas aquí stackoverflow.com / preguntas / 9841093 /…

Respuestas a la pregunta(2)

Solución de preguntas

Así que quieres obtener la fila con la @ más alOrderField 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, puede ampliar 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 edición.)

n otras palabras, devuelva la filat1 para el que no hay otra filat2 existe con el mismoGroupId y una mayorOrderField. Cuandot2.* es NULL, significa que la combinación externa izquierda no encontró tal coincidencia y, por lo tanto,t1 tiene el mayor valor deOrderField en el grupo

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

n cuanto al rendimiento, vea mi respuesta a Recuperando el último registro en cada grupo. Intenté un método de subconsulta y el método de combinación usando el volcado de datos de desbordamiento de pila. La diferencia es notable: el método de unión se ejecutó 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 usando 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 mejor 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 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 ejecute la consulta sin condición de unión, para forzar un producto cartesiano de todas las filas, y también buscamos 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 @Rank continúa aumentando a medida que procesa la segunda tabla derivada, a 7 y más. Por lo tanto, los rangos de la segunda tabla derivada nunca se superpondrán con los rangos de la primera tabla derivada.

Debería agregar otra tabla derivada para forzar a @Rank a restablecerse 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 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 ordena de la manera difícil e incluso usa un búfer de unión porque tampoco puede usar un índice al unir tablas temporales. Este es un ejemplo de salida 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                  |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+

Mientras que mi solución usando la unión externa izquierda se optimiza mucho mejor. No utiliza una tabla temporal e incluso informa"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 personas que hacen afirmaciones en sus blogs que "las uniones hacen que SQL sea lento", pero eso no tiene sentido. La mala optimización hace que SQL sea lento.

 Andriy M05 ene. 2012 22:20
Esto puede resultar bastante útil (también para el OP), pero, lamentablemente, no responde ninguna de las dos preguntas formuladas.
 TMS06 ene. 2012 00:53
Gracias Bill, esa es una buena idea de cómo evitar los rangos, pero ... ¿la unión no sería lenta? La unión (sin la limitación de la cláusula where) sería de un tamaño mucho mayor que en mis consultas. De todos modos, gracias por la idea! Pero también sería interesante en la pregunta original, es decir, si los rangos funcionarían de esta manera.
 TMS06 ene. 2012 07:37
Gracias por la excelente respuesta, Bill. Sin embargo, ¿y si usara@Rank1 y@Rank2, uno para cada subconsulta? ¿Eso solucionaría el problema? ¿Sería eso más rápido que tu solución?
 Bill Karwin06 ene. 2012 08:50
Utilizando@Rank1 y@Rank2 no haría ninguna diferencia.
 ownking29 abr. 2014 17:24
Gracias por esa gran solución. Estuve luchando mucho tiempo con ese problema. Para las personas que desean agregar filtros para los otros campos, p. "foo" necesita agregarlos a la condición de unión... AND t1.foo = t2.foo para luego obtener los resultados correctos paraWHERE ... AND foo='bar'

Su respuesta a la pregunta