За последние 24 часа нас посетили 8732 программиста и 485 роботов. Сейчас ищут 255 программистов ...

Grouping By Top N in MySQL

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

Метки:
  1. artoodetoo

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

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