Get Top N for each group in MySQL

Надо пополнять раздел готовыми полезными решениями Вот например практичная задачка:
В один запрос получить записи, сгруппированные по какому-то атрибуту. Причем вывести по несколько записей из каждой группы, например 3 самые новые или 5 у которых самый большой рейтинг.

Решение 1 (источник):
Объединяем таблицу саму с собой и считаем сколько записей оказалось выше (то есть вычисляем место в рейтинге).
Объединение с рейтингом делается так, чтобы в условии объединения участвовало
a. равенство по атрибуту, который нам нужен для группировки И
b. неравенство по другому признаку— оно задаст порядок в рейтинге.
Сгруппируем и наложим фильтр HAVING COUNT(*) < N
https://www.db-fiddle.com/f/XLjyrL2mVmpSK1S4zFJtP/0

Код (SQL):

1. SELECT m1.`month`, m1.`ip_address`, m1.`hits`
2. FROM my_data_per_month m1
3. LEFT OUTER JOIN my_data_per_month m2
4.   ON (m1.`month` = m2.`month` AND m1.`hits` < m2.`hits`)
5. GROUP BY m1.`month`, m1.`ip_address`
6. HAVING COUNT(*) < 3
7. ORDER BY m1.`month` ASC, m1.`hits` DESC

Недостаток метода: если внутри группы есть записи с одинаковым атрибутом сортировки (hits), то в группу попадут более N записей. Очевидно если сортировка по id, то проблемы нет.

Решение 2 (источник):
Комбинация GROUP_CONCAT(...ORDER BY...) и FIND_IN_SET()
Снова группировка и объединение таблица с собой, точнее с производной формой от той же таблицы, чтобы ранжировать список. Потом отсекаем те позиции, которые стоят слишком далеко от начала списка.
http://sqlfiddle.com/#!9/b66be/1

Код (SQL):

1. SELECT yourtable.*
2. FROM  yourtable INNER JOIN (
3.   SELECT  id, GROUP_CONCAT(YEAR ORDER BY rate DESC) grouped_year
4.   FROM  yourtable
5.   GROUP BY id) group_max
6.   ON yourtable.id = group_max.id
7.   AND FIND_IN_SET(YEAR, grouped_year) <=5
8. ORDER BY
9.   yourtable.id, yourtable.YEAR DESC

Также одинаковый рейтинг может испортить картину. Можно попробовать решить через DISTINCT. Также GROUP_CONCAT имеет ограничения по длинне списка.

Решение 3 (источник):
Использовать переменные чтобы создать колонку с рейтингом внутри группы. Затем оформить это как подзапрос и отфильтровать по рейтингу.
https://www.db-fiddle.com/f/6h9oqKvBpzNVidg9K669af/0

Код (SQL):

1. SELECT city, country, population, country_rank
2. FROM (
3.   SELECT city, country, population,
4.   @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
5.   @current_country := country
6.   FROM (SELECT @country_rank := 0, @current_country := '') x, cities
7.   ORDER BY country, population DESC
8. ) ranked
9. WHERE country_rank <= 2

Вроде всё хорошо и удобно, смущает только очевидный полный перебор. У построителя запросов нет шансов на оптимизацию.

 

Решение 4. С 8й версии MySQL получил window (or windowing) functions. И вообще виндоу фанкшенс теперь в стандарте SQL.
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

Теперь можно выбирать top N per group через них (источник):
https://www.db-fiddle.com/f/ju2hCYw4duWsP7WJT1EaN6/0

Код (SQL):

1. SELECT a.`month`, a.`ip_address`, a.`hits`
2. FROM (
3.   SELECT
4.     `month`, `ip_address`, `hits`,
5.     ROW_NUMBER() OVER (PARTITION BY `month` ORDER BY hits DESC) `ranked_order`
6.   FROM my_data_per_month
7. ) a
8. WHERE a.`ranked_order` <= 3