Top N на группу с несколькими столами

Основываясь на моих исследованиях, это очень распространенная проблема, которая обычно имеет довольно простое решение. Моя задача - изменить несколько запросов, чтобы получить все результаты, чтобы получить 3 лучших за группу. Сначала это было хорошо, и я использовал несколько рекомендаций и ответов с этого сайта для достижения этой цели (наиболее просматриваемые продукты). Тем не менее, я столкнулся с трудностями с моей последней "Лучшей продаваемой продукцией" из-за нескольких объединений.

В принципе, мне нужно получить все продукты по порядку по # наивысшим продажам на продукт, в котором максимальные продукты для каждого поставщика 3. У меня есть несколько таблиц, которые соединяются для создания исходного запроса, и каждый раз, когда я пытаюсь использовать переменные для генерации ранжирования, он приводит к недействительным результатам. Следующее должно помочь лучше понять проблему (я сократил ненужные поля для краткости):

Таблица продуктов

productid | vendorid | approved | active | deleted

Таблица поставщиков

vendorid | approved | active | deleted

Таблица заказов

orderid | `status` | deleted

Таблица позиций заказов

orderitemid | orderid | productid | price

Теперь мой первоначальный запрос для получения всех результатов выглядит следующим образом:

SELECT COUNT(oi.price) AS `NumSales`, 
       p.productid, 
       p.vendorid
FROM products p
INNER JOIN vendors v ON (p.vendorid = v.vendorid)
INNER JOIN orders_items oi ON (p.productid = oi.productid)
INNER JOIN orders o ON (oi.orderid = o.orderid)
WHERE (p.Approved = 1 AND p.Active = 1 AND p.Deleted = 0)
AND (v.Approved = 1 AND v.Active = 1 AND v.Deleted = 0)
AND o.`Status` = 'SETTLED'
AND o.Deleted = 0
GROUP BY oi.productid
ORDER BY COUNT(oi.price) DESC
LIMIT 100;

Наконец, (и здесь, где я в тупике), я пытаюсь изменить вышеприведенное утверждение таким образом, что я получил только 3-й продукт (по # продано) для каждого поставщика. Я бы добавил, что у меня есть до сих пор, но я смущен, чтобы сделать это, и этот вопрос уже является стеной текста. Я пробовал переменные, но получаю недопустимые результаты. Любая помощь будет принята с благодарностью.

Ответы

Ответ 1

Даже если вы укажете LIMIT 100, для этого типа запроса потребуется полное сканирование и таблица, которая будет создана, тогда каждая проверенная запись и строка пронумерованы до окончательной фильтрации для 100, которые вы хотите отобразить.

select
    vendorid, productid, NumSales
from
(
    select
        vendorid, productid, NumSales,
        @r := IF(@g=vendorid,@r+1,1) RowNum,
        @g := vendorid
    from (select @g:=null) initvars
    CROSS JOIN 
    (
        SELECT COUNT(oi.price) AS NumSales, 
               p.productid, 
               p.vendorid
        FROM products p
        INNER JOIN vendors v ON (p.vendorid = v.vendorid)
        INNER JOIN orders_items oi ON (p.productid = oi.productid)
        INNER JOIN orders o ON (oi.orderid = o.orderid)
        WHERE (p.Approved = 1 AND p.Active = 1 AND p.Deleted = 0)
        AND (v.Approved = 1 AND v.Active = 1 AND v.Deleted = 0)
        AND o.`Status` = 'SETTLED'
        AND o.Deleted = 0
        GROUP BY p.vendorid, p.productid
        ORDER BY p.vendorid, NumSales DESC
    ) T
) U
WHERE RowNum <= 3
ORDER BY NumSales DESC
LIMIT 100;

Подход здесь

  • Группируйте, чтобы получить NumSales
  • Использовать переменные для номера строк в продажах для каждого поставщика/продукта
  • Отфильтруйте нумерованный набор данных, чтобы разрешить максимум 3 на одного поставщика
  • Закажите оставшиеся номера NumSales DESC и верните только 100

Ответ 2

Мне нравится это элегантное решение, однако, когда я запускаю адаптированный, но похожий запрос на моей машине dev, я получаю возвращаемый результат без детерминированного результата. Я считаю, что это связано с тем, как оптимизатор MySql имеет дело с назначением и чтением пользовательских переменных внутри одного и того же оператора.

Из документы:

Как правило, вы никогда не должны присваивать значение переменной пользователя и читать значение в пределах одного и того же оператора. Вы можете получить ожидаемые результаты, но это не гарантируется. Порядок оценки выражений с пользовательскими переменными равен undefined и может изменяться на основе элементов, содержащихся в данном операторе; кроме того, этот порядок не гарантируется одинаковым между версиями MySQL Server.

Просто добавьте эту заметку здесь, если кто-то столкнется с этим странным поведением.

Ответ 3

Ответ, данный @RichardTheKiwi, отлично поработал и получил мне 99% пути! Я использую MySQL и получаю только первую строку каждой группы, отмеченную номером строки, а остальные строки остаются NULL. Это привело к тому, что запрос возвращал только верхний хит для каждой группы, а не первые три строки. Чтобы исправить это, мне пришлось инициализировать @r в подзапросе initvars. Я изменил,

from (select @g:=null) initvars

to

from (select @g:=null, @r:=null) initvars

Вы также можете инициализировать @r до 0, и он будет работать одинаково. И для тех, кто менее знаком с этим типом синтаксиса, дополнительный раздел читает каждую отсортированную группу, и если строка имеет тот же vendorid, что и предыдущая строка, которая отслеживается с помощью переменной @g, она увеличивает номер строки, который хранится в переменной @r. Когда этот процесс достигнет следующей группы с новым vendorid, оператор IF больше не будет считаться истинным, а переменная @r (и, следовательно, RowNum) будет reset до 1.