Получить записи с самым высоким / самым маленьким <что угодно> в группе

Question

Получить записи с самым высоким / самым маленьким <что угодно> в группе

Как это сделать?

Прежнее название этого вопроса было "использование ранга (@Rank:= @Rank + 1) в сложном запросе с подзапросами - будет ли он работать?", Потому что я искал решение с использованием рангов, но теперь я вижу, что решение, опубликованное Биллом, намного лучше.

Оригинальный вопрос:

Я пытаюсь составить запрос, который будет принимать последнюю запись из каждой группы в определенном порядке:

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

выражение @Rank := @Rank + 1 обычно используется для ранга, но для меня это выглядит подозрительно, когда используется в 2 подзапросах, но инициализируется только один раз. Будет ли это работать так?

И, во-вторых, будет ли он работать с одним подзапросом, который оценивается несколько раз? Подобно подзапросу в предложении where (или имеющем) (иначе, как написать выше):

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

Заранее спасибо!

100

mysql subquery greatest-n-per-group rank

Источник

user684229 05 янв '12 в 20:04

3 ответа

Решение

Если вы хотите использовать более сложную логику, чем только первый результат, например, получить только второй результат или первый результат с определенным ограничением:

      select *
from (
    select
        [GroupId] , [columnName], OrderField,
        ROW_NUMBER() OVER(PARTITION BY [GroupId] ORDER BY OrderField DESC) AS row_number
    from Table_NAME(nolock)
    where [columnName]!='BadValue'
    ) a
where a.row_number = 1

1

Источник

user679334 10 фев '23 в 07:07

Или вы можете использовать order by а также limit, то есть:

ВЫБРАТЬ * ИЗ ТАБЛИЦЫ ЗАКАЗАТЬ ПО ORDERFIELD DESC LIMIT 1

-1

Источник

user4410309 06 окт '21 в 07:29

Другие вопросы по тегам mysql subquery greatest-n-per-group rank

user20860 05 янв '12 в 20:12 2012-01-05 20:12 · Accepted Answer · 2012-01-05 20:12

Итак, вы хотите получить ряд с самым высоким OrderField на группу? Я бы сделал это так:

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)

(РЕДАКТИРОВАТЬ Томасом: если в одной и той же группе есть больше записей с тем же OrderField, и вам нужна ровно одна из них, вы можете расширить условие:

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

конец редактирования.)

Другими словами, верните строку t1 для которого нет другого ряда t2 существует с тем же GroupId и больше OrderField, когда t2.* НЕДЕЙСТВИТЕЛЬНО, это означает, что левое внешнее соединение не нашло такого соответствия, и поэтому t1 имеет наибольшее значение OrderField в группе.

Нет рангов, нет подзапросов. Это должно работать быстро и оптимизировать доступ к t2 с помощью "Использование индекса", если у вас есть составной индекс на (GroupId, OrderField),

Что касается производительности, см. Мой ответ на Получение последней записи в каждой группе. Я попробовал метод подзапроса и метод соединения, используя дамп данных переполнения стека. Разница замечательна: метод соединения в моем тесте работал в 278 раз быстрее.

Важно, чтобы у вас был правильный индекс, чтобы получить лучшие результаты!

Что касается вашего метода, использующего переменную @Rank, он не будет работать так, как вы его написали, потому что значения @Rank не будут сбрасываться в ноль после того, как запрос обработает первую таблицу. Я покажу вам пример.

Я вставил некоторые фиктивные данные с дополнительным полем, которое является нулевым, за исключением строки, которая, как мы знаем, является наибольшей в группе:

select * from `Table`;

+---------+------------+------+
| GroupId | OrderField | foo  |
+---------+------------+------+
|      10 |         10 | NULL |
|      10 |         20 | NULL |
|      10 |         30 | foo  |
|      20 |         40 | NULL |
|      20 |         50 | NULL |
|      20 |         60 | foo  |
+---------+------------+------+

Мы можем показать, что ранг увеличивается до трех для первой группы и шести для второй группы, и внутренний запрос возвращает их правильно:

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

Теперь запустите запрос без условия соединения, чтобы заставить декартово произведение всех строк, и мы также извлекаем все столбцы:

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

Из вышесказанного видно, что максимальный ранг на группу является правильным, но затем @Rank продолжает увеличиваться при обработке второй производной таблицы до 7 и выше. Таким образом, ранги из второй производной таблицы никогда не будут перекрываться с рангами из первой производной таблицы.

Вам нужно добавить еще одну производную таблицу, чтобы заставить @Rank сбросить до нуля между обработками двух таблиц (и надеяться, что оптимизатор не изменит порядок, в котором он оценивает таблицы, или использовать STRAIGHT_JOIN для предотвращения этого):

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

Но оптимизация этого запроса ужасна. Он не может использовать какие-либо индексы, он создает две временные таблицы, сортирует их сложным образом и даже использует буфер объединения, потому что он также не может использовать индекс при объединении временных таблиц. Это пример вывода из 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                  |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+

Тогда как мое решение с использованием левого внешнего соединения оптимизируется намного лучше. Он не использует временную таблицу и даже отчеты "Using index" Это означает, что он может разрешить объединение, используя только индекс, не касаясь данных.

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

Вы, вероятно, будете читать в блогах людей, которые утверждают, что "объединения делают SQL медленным", но это чепуха. Плохая оптимизация делает SQL медленным.