Как упростить запрос с группировкой, максимальным значением и JOIN в MySQL?

Question

[Максим Федоров]

Написал рабочий запрос, но что-то мне подсказывает, что он не в порядке...
Работает исправно, но многим не понравился двойной джойн сразу и в основном и вложенных запросах + группировка
Может чего не понимаю?

Дано
Есть 3 таблицы
- товары products
- акции discounts

упрощенная для понимания стуктура акций

- связь акций и товаров «многие ко многим» в таблице products_discounts

Задача
Выбрать для списка товаров акцию для каждого товара, с наибольшим приоритетом, которая включена и действующая (интервалы дат)

Сделал работающий запрос
Айдишники товаров и дату вставил для удобства сразу в запрос

Более читабельный запрос в картинке

SELECT
  pd.product_id,
  d.name,
  d.discount_percent,
  d.discount_amount
FROM s_products_discount pd
LEFT JOIN s_discounts d ON pd.discount_id = d.id
LEFT JOIN (SELECT pd.product_id, MAX(d.priority) as max_priority
           FROM s_products_discount pd
           LEFT JOIN s_discounts d ON pd.discount_id = d.id
           WHERE d.visible = 1
           AND d.date_from <= '2018-05-05'
           AND d.date_to >= '2018-05-05'
           GROUP BY pd.product_id) pp ON pd.product_id = pp.product_id
WHERE 1
AND d.priority = pp.max_priority
AND pd.product_id IN ('41987', '41988', '41989', '41990', '41991', '41992', '41993', '41994')

Если своими словами, то во вложенном запросе получаю табличку, в которой все товары и и приоритетная акция, попадающая под условия

Comments

[JimmDiGreez]

Честно говоря не владею навыками написания таких запросов без проверок. Но мне, почему то, сразу подумалось на having. Врядли работает, но мб поинспектировать как-то эту возможно получится.

select pd.product_id, d.name, d.discount_percent, d.discount_amount 
from s_products_discount pd
inner join s_discounts d 
on (pd.discount_id = d.id 
  and d.visible = 1 
  and d.date_from <= '2018-05-05' 
  and d.date_to >= '2018-05-05')
where pd.product_id 
in ('41987', '41988', '41989', '41990', 
  '41991', '41992', '41993', '41994')
having max(d.priority);

[Максим Федоров]

JimmDiGreez, оу, спасибо — пойдем смотреть и пробовать

Answer 1

[Nekonyash]

Попробуйте следующее:

SELECT
  pd.product_id,
  d.name,
  d.discount_percent,
  d.discount_amount
FROM s_products_discount pd
JOIN s_discounts d ON pd.discount_id = d.id
WHERE 1
AND pd.product_id IN ('41987', '41988', '41989', '41990', '41991', '41992', '41993', '41994') # проверять список ид ДО более сложной проверки
AND d.priority = (
  SELECT MAX(sd.priority)
  FROM s_products_discount spd
  JOIN s_discounts sd on spd.discount_id = sd.id
  WHERE spd.product_id = pd.product_id
  AND sd.visible = 1
  AND sd.date_from <= '2018-05-05'
  AND sd.date_to >= '2018-05-05'
)

У меня только возник вопрос... А зачем везде LEFT JOIN? Разве в текущей задаче нужен именно он, а не просто join? Такое чувство, что left пишется просто по привычке.
В чем состояла оптимизация - вы строите временную таблицу, где к каждому продукту ищется максимальный доступный приоритет. Если представить, что у нас пол миллиона продуктов, вы хотим выбрать только десяток - все равно будет построена таблица по всем продуктам. Я же переместила подзапрос в where, где будет сравниваться приоритет для конкретного продукта. Судя по моим маленьким тестам, даже на малом количестве данных запрос не проигрывает, на большом количестве данных разница должна быть заметнее.

Comments

[Максим Федоров]

Да, сейчас посмотрел на свой JOIN — и правда...

Запрос попробую, правда вчера на встрече с заказчиком поменялись бизнес требования и все переделывать нужно :)

Спасибо БОЛЬШОЕ!

Answer 2

[Руслан .]

В подзапросе таблица s_products_discount не нужна, можно вот так попробовать:

SELECT pd.product_id,
       d.name,
       d.discount_percent,
       d.discount_amount
  FROM s_products_discount pd
  LEFT JOIN s_discounts     d
         ON d.id = pd.discount_id 
  LEFT JOIN (SELECT MAX(d.priority) as max_priority
               FROM s_discounts dd
              WHERE dd.id         = pd.discount_id
                AND dd.visible    = 1
                AND dd.date_from <= '2018-05-05'
                AND dd.date_to   >= '2018-05-05') pp 
         ON pp.max_priority = d.priority
 WHERE pd.product_id IN ('41987', '41988', '41989', '41990', '41991', '41992', '41993', '41994')

Comments

[Максим Федоров]

так весь смысл в этом джойне и есть — им мы делаем сопоставление id товара и приоритет подходящей для него акции)

[Руслан .]

Так мы его и не теряем, мы от внешней таблицы товаров идем в таблицу дисконтов по идентификатору дисконта.

[Максим Федоров]

Руслан, алиас pd. в подзапрсое не видит и ругается, что нет такого поля pd.discount_id

[Руслан .]

Сорри, не все алиасы правильно указал:
Еще немного переписал:

SELECT pd.product_id,
d.name,
d.discount_percent,
d.discount_amount
FROM s_products_discount pd
LEFT JOIN s_discounts d
ON d.id = pd.discount_id
WHERE pd.product_id IN ('41987', '41988', '41989', '41990', '41991', '41992', '41993', '41994')
AND d.priority = (SELECT MAX(dd.priority)
FROM s_discounts dd
WHERE dd.id = pd.discount_id
AND dd.visible = 1
AND dd.date_from <= '2018-05-05'
AND dd.date_to >= '2018-05-05')

[Максим Федоров]

Руслан, нет, не получилось — вывело все

[Руслан .]

Странно по продукту 41987 есть 4 записи с дисконтами, но вывелось 3 возможно фильтр не правильно работает из-за LEFT JOIN, а если так (вывел еще одно поле d.priority, чтобы понять какой приоритет отобрался):

SELECT pd.product_id,
d.name,
d.discount_percent,
d.discount_amount
d.priority
FROM s_products_discount pd
INNER JOIN s_discounts d
ON d.id = pd.discount_id
AND d.priority = (SELECT MAX(dd.priority)
FROM s_discounts dd
WHERE dd.id = pd.discount_id
AND dd.visible = 1
AND dd.date_from <= '2018-05-05'
AND dd.date_to >= '2018-05-05')
WHERE pd.product_id IN ('41987', '41988', '41989', '41990', '41991', '41992', '41993', '41994')

[Максим Федоров]

Руслан,

Странно по продукту 41987 есть 4 записи с дисконтами, но вывелось 3 возможно фильтр не правильно работает

Неее. все верно работает, я же не показал таблицу дисконта, он там отключен или дата не подходит

[Руслан .]

Точно!
Нужно одинаковый фильтр на обе таблицы дисконта вешать, тогда сойдется:

SELECT pd.product_id,
       d.name,
       d.discount_percent,
       d.discount_amount
       d.priority
FROM s_products_discount pd
INNER JOIN s_discounts d
        ON d.id          = pd.discount_id
       AND dd.visible    = 1
       AND dd.date_from <= '2018-05-05'
       AND dd.date_to   >= '2018-05-05'
       AND d.priority = (SELECT MAX(dd.priority)
                           FROM s_discounts dd
                          WHERE dd.id         = pd.discount_id
                            AND dd.visible    = 1
                            AND dd.date_from <= '2018-05-05'
                            AND dd.date_to   >= '2018-05-05')
WHERE pd.product_id IN ('41987', '41988', '41989', '41990', '41991', '41992', '41993', '41994')

[Максим Федоров]

Руслан, в обоих последних случаях выводятся все записи, то есть со всеми приоритетами
:):):):)
точнее не все, а с учетом фильтра, но сам факт — не самая приоритетная

[Руслан .]

Странно, возможно max в подзапросе без group by как то не так работает, но можно вообще без max :) :

SELECT pd.product_id,
       d.name,
       d.discount_percent,
       d.discount_amount
FROM s_products_discount pd
INNER JOIN s_discounts d
        ON d.id         = pd.discount_id
       AND d.visible    = 1
       AND d.date_from <= '2018-05-05'
       AND d.date_to   >= '2018-05-05'
WHERE pd.product_id IN ('41987', '41988', '41989', '41990', '41991', '41992', '41993', '41994')
  AND NOT EXISTS (SELECT 1
                    FROM s_discounts dd
                   WHERE dd.id         = pd.discount_id
                     AND dd.priority   > d.priority
                     AND dd.visible    = 1
                     AND dd.date_from <= '2018-05-05'
                     AND dd.date_to   >= '2018-05-05')