Ограничить результаты до N строк на группу

У меня есть решение, которое работает великолепно и не требует сложных запросов. Используйте что-то вроде:

SUBSTRING_INDEX(GROUP_CONCAT(col_name ORDER BY desired_col_order_name), ',', N)

Это позволяет получить нужные вам значения.

Например, чтобы получить топ-1 для каждой группы, вы можете использовать следующий запрос:

SELECT 
    *
FROM
    yourtable
WHERE
    id IN (
        SELECT 
            SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY rate DESC), ',', 1) AS id
        FROM
            yourtable
        GROUP BY year
    )
ORDER BY rate DESC;

Этот запрос сначала собирает все id, отсортированные по rate в убывающем порядке для каждой группы по year, и затем извлекает только первый id (топ-1) из каждой группы. Внешний запрос получает все записи с этими id, отсортированные по rate.

Ваш запрос почти идентичен предложенному. Единственное изменение заключается в добавлении следующей строки:

row_number() over (partition by id order by rate DESC)

Это добавляет нумерацию строк в пределах каждой группы идентификаторов (id), сортируя результаты по убыванию значения rate. Таким образом, вы сможете выбрать только первые 5 записей с наивысшими значениями rate для каждого id в подзапросе.

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

SELECT year, id, rate
FROM (
  SELECT
    year, id, rate, row_number() over (partition by id order by rate DESC) AS row_num
  FROM h
  WHERE year BETWEEN 2000 AND 2009
  AND id IN (SELECT rid FROM table2)
  GROUP BY id, year
  ORDER BY id, rate DESC
) as subquery
WHERE row_num <= 5

Обратите внимание, что я также добавил псевдоним AS row_num для лучшей читаемости. Теперь запрос будет правильно возвращать по 5 записей с наибольшими значениями rate для каждого id из таблицы h за указанный диапазон лет.

Для решения данной задачи вам потребуется использовать серию подзапросов, чтобы сначала присвоить ранги значениям, затем ограничить их, а после выполнить сумму с группировкой. Вот пример SQL-запроса:

@Rnk:=0;
@N:=2;

SELECT
  c.id,
  SUM(c.val)
FROM (
  SELECT
    b.id,
    b.bal
  FROM (
    SELECT   
      IF(@last_id = id, @Rnk + 1, 1) AS Rnk,
      a.id,
      a.val,
      @last_id = id
    FROM (   
      SELECT 
        id,
        val 
      FROM list
      ORDER BY id, val DESC
    ) AS a
  ) AS b
  WHERE b.Rnk < @N
) AS c
GROUP BY c.id;

В этом запросе выполняются следующие шаги:

1. Вложенный подзапрос (aliased as a) извлекает значения из таблицы list, сортируя их по id и val в порядке убывания.
2. Второй подзапрос (aliased as b) присваивает каждому элементу ранг Rnk в зависимости от его id. Для этого используется переменная @last_id, которая помогает отслеживать изменение id.
3. Внешний запрос (aliased as c) фильтрует результаты так, чтобы оставить только те строки, где ранг меньше установленного значения @N.
4. Наконец, выполняется группировка по id и суммирование значений val.

Пожалуйста, учтите, что использование переменных в SQL может зависеть от конкретной системы управления базами данных (СУБД), и описанный выше код может потребовать некоторых корректировок для работы в вашей среде.

Попробуйте следующее:

SELECT h.year, h.id, h.rate 
FROM (SELECT h.year, h.id, h.rate, IF(@lastid = (@lastid:=h.id), @index:=@index+1, @index:=0) indx 
      FROM (SELECT h.year, h.id, h.rate 
            FROM h
            WHERE h.year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2)
            GROUP BY id, h.year
            ORDER BY id, rate DESC
            ) h, (SELECT @lastid:='', @index:=0) AS a
    ) h 
WHERE h.indx <= 5;

Это запрос SQL выполняет выборку данных из таблицы h, в которой находятся записи за период с 2000 по 2009 годы. Он сначала фильтрует записи, чтобы оставить только те, которые имеют идентификаторы из table2, а затем группирует их по идентификатору и году. Для каждой группы он присваивает индекс в зависимости от идентификатора и сортирует записи по убыванию ставки (rate). В конце запроса выбираются только первые 5 записей для каждого идентификатора.

Убедитесь, что у вас правильно настроены переменные @lastid и @index перед выполнением этого запроса в вашей SQL-сессии.

Ваше решение действительно выглядит элегантным и быстрым! Давайте разберем его подробнее.

В данном SQL-запросе мы выполняем несколько шагов, чтобы получить желаемый результат. Мы выбираем данные за определенный диапазон лет (с 2000 по 2009) и группируем их по идентификатору id. Используем GROUP_CONCAT для объединения строк, которые мы затем обрабатываем с помощью SUBSTRING_INDEX, чтобы получить только первые 5 значений, сортируя по rate в порядке убывания.

Вот краткое объяснение ключевых частей запроса:

1. GROUP_CONCAT и ORDER BY: Мы объединяем значения id и year в одну строку и упорядочиваем их по rate, отбирая только самые высокие 5 значений для каждого id.

2. SUBSTRING_INDEX: Мы используем эту функцию, чтобы получить строку из объединенных значений, и затем выполняем проверку на наличие текущей записи в этой строке, обеспечивая тем самым, что мы получаем только уникальные записи.

3. LEFT JOIN: Это позволяет нам получить все соответствующие записи из основной таблицы h, сохраняя при этом результаты, полученные из временной таблицы h_temp.

Как вы правильно заметили, данный запрос адаптируем для других целей, изменяя условия сортировки или диапазон лет. Если у вас есть конкретные вопросы по изменениям или как использовать этот запрос в других сценариях, не стесняйтесь спрашивать!