За последние 24 часа нас посетили 16533 программиста и 1642 робота. Сейчас ищут 1263 программиста ...

Get Top N for each group in MySQL

Тема в разделе "Решения, алгоритмы", создана пользователем artoodetoo, 25 сен 2019.

  1. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    11.099
    Симпатии:
    1.243
    Адрес:
    там-сям
    Надо пополнять раздел готовыми полезными решениями :) Вот например практичная задачка:
    В один запрос получить записи, сгруппированные по какому-то атрибуту. Причем вывести по несколько записей из каждой группы, например 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
    Вроде всё хорошо и удобно, смущает только очевидный полный перебор. У построителя запросов нет шансов на оптимизацию.
     
    miketomlin нравится это.
  2. artoodetoo

    artoodetoo Суперстар
    Команда форума Модератор

    С нами с:
    11 июн 2010
    Сообщения:
    11.099
    Симпатии:
    1.243
    Адрес:
    там-сям
    Решение 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
     
    miketomlin и MouseZver нравится это.
  3. don.bidon

    don.bidon Активный пользователь

    С нами с:
    28 мар 2021
    Сообщения:
    911
    Симпатии:
    143
    В своё время, когда занимался разработкой CMS-ки (с интернет-магазином под капотом), в требованиях у которой было приемлемое шевеление на любом железе, максимально денормализовывали структуры в БД, меня б за такие запросы в базу прибили бы )