Как вывести по 5 максимальных значений в строках из каждой категории?

Question

[Сергей Кореневский]

Отчёт для формы для голосования кино.
Нужно вывести по каждой номинации 5 фильмов с максимальным количеством балов.
Каждая строка таблицы это 1 голос: колонки nomin_id, user_id, film_id

SELECT count(user_id ) countUser , nomin_id,  film_id 
FROM votes
GROUP BY  film_id , nomin_id
ORDER BY countUser DESC
LIMIT 5;

По идее надо этот запрос таблицы votes вложить в другой запрос таблицы votes и проверять есть ли совпадения двух таблиц WHERE EXISTS().
Но всего в таблице 25 000 строк запрос выполняется 30 секунд, а потом крах.

Comments

[asmelnik]

Вот вроде похожая ситуация.
Попробуйте творчески адаптировать.

https://ru.stackoverflow.com/questions/775900/%D0%...

Answer 1

[alexalexes]

select A.*
       from
       (
       select B.*,
              row_number() over (partition by B.nomin_id order by B.countUser desc) rwnm -- партишином задаем область счета строк, а сортировкой - порядок счета
              from
              (
              SELECT count(user_id) countUser, nomin_id,  film_id
              FROM votes
              GROUP BY  film_id , nomin_id
              ) B
       ) A
where rwnm <= 5 -- берем по этому количеству от каждого локального счета

См. оконную функцию row_number для MySQL 8+ версия, либо MariaDB. Но учтите, что MySQL 5 не поддерживает оконки, в ней свои костыли есть в виде использования переменных с @.

Но всего в таблице 25 000 строк запрос выполняется 30 секунд, а потом крах.

Индексы на каждый из атрибутов user_id, nomin_id, film_id нужно создать, тогда будет пару сотен миллисекунд выполняться.

Comments

[Сергей Кореневский]

Спасибо, очень круто. буду теперь учить как работает Partionon

[Сергей Кореневский]

Подскажите, ключи первичные или уникальные?

[alexalexes]

Причем здесь свойства ключей?
Про какие поля таблицы вы говорите?

[Сергей Кореневский]

alexalexes, Извените, ошибся, не ключи а индексы. Но я не понял что это значит индексы, там есть PK, UQ, AI
что из индексами является не понял

[alexalexes]

PK - это первичный ключ, позволяет однозначно идентифицировать запись в таблице, сам по себе несет ограничение уникальности, и на это поле можно ссылаться из других таблиц (там эта ссылка называется внешним ключом).
UQ - уникальность поля, выставляет ограничение на колонку, при которой вставка одинакового значения невозможна.
AI - автоинкремент поля. Позволяет автоматически вставлять новое значение поля (+1 от ранее вставленного) при вставке, не упоминая названия поля в операторе insert. Это нестандартный для классического SQL объект, обычно, в других СУБД используют триггер+последовательность (AI в MySQL - это как раз 2 в 1).

Но PK, UQ, AI - это не то, что нужно для индексов.
Тебе нужно создать на каждое поле по индексу, без флагов UQ - выставлять уникальность поля не нужно, иначе ты не вставишь новую запись с одинаковым значением этого поля.

[Сергей Кореневский]

alexalexes, Но у меня там всё равно по трём полям одинаковых значений быть не может.
Я думал что индексы это и есть PK и UQ. Но по Вашим намёкам я понял что это совсем разные вещи.
Спасибо , я поизучаю индексы.

[alexalexes]

Это отдельный вид объектов в СУБД, даже память под них выделяется существенная, когда они работают. Учите базу не по интерфейсу phpmyadmin, а по документации.

Answer 2

[Rsa97]

То же самое, что у alexalexes, только в более линейном виде.

WITH
  `counts` AS (
    SELECT `film_id`, `nomin_id`, COUNT(*) AS `votes_count`, 
      FROM `votes`
      GROUP BY `nomin_id`, `film_id`
  ).
  `places` AS (
    SELECT `film_id`, `nomin_id`, `votes_count`,
           ROW_NUMBER() OVER `win` AS `place`
      FROM `counts`
      WINDOW `win` AS (
        PARTITION BY `nomin_id`
        ORDER BY `votes_count` DESC
      )
  )
SELECT `votes_count`, `nomin_id`, `film_id`, `place`
  FROM `places`
  WHERE `place` <= 5

Comments

[Сергей Кореневский]

Спасибо, Это какой то магический код. Искал в яндексе что значит WINDOW вместо этого предлагает скачать прогу под Windows.

[Rsa97]

Сергей Кореневский,
https://dev.mysql.com/doc/refman/8.0/en/window-fun...

[Сергей Кореневский]

Rsa97, этого нет даже в Рунете. Значит мне повезло что Вы и alexalexes решили мне помочь.

[alexalexes]

CTE и window function есть почти во всех нелокальных СУБД, и в русских источниках они описаны как общие табличные выражения и оконные функции.
У вас как раз задачка самая показательная под демонстрацию возможностей этих методов.