Group by. Как отфильтровать запрос?

Здравствуйте.
Описание: метод getProducts отдает список товаров. Есть определенные товары, которые ранее содержали опции, необходимо было разделить их по опциям: один товар - одна опция. Чтобы как-то их связать, так как теперь это разные сущности, я использовал поле group_id. Таким образом, товары были связаны между собой.
Метод getProducts должен отдавать товары, группируя их по group_id. В результате отдается один товар, который выбран с помощью sql GROUP BY group_id. Проблема в том, что GROUP BY выбирает первый элемент с group_id, то есть, если у нас 4 товара, которые связаны между собой по group_id, GROUP BY выберет первый элемент. А мне нужно, чтобы выбирался не первый элемент, а по условию для поля quantity: если quantity > 0.
Да, я могу вписать с помощью WHERE quantity > 0, и он выберет нужный товар, но мне необходимо, чтобы метод getProducts отдавал все товары, даже с quantity > 0, НО из списка GROUP BY выбирался ПОДХОДЯЩИЙ товар под условие.

То есть, если кратко: как отфильтровать товары, которые группируются с помощью GROUP BY. Я читал про HAVING, но, как я понял, он работает с MAX,SUM,COUNT и т.д

Пример список товаров:
product_id       name                quantity       minimum       group_id
1            Роза 35см                2              25           0000123
2            Роза 50см                67             25           0000123
3            Роза 80см                15             25           0000123
4            Тюльпан                  12             1            0000654
5            Кактус                    1             10           0000436
6            Ромашка                   1             5            0000756

После запроса, я хочу получить следующую выборку:
product_id       name                quantity       minimum       group_id
2            Роза 50см                  67             25          0000123
4            Тюльпан                    12             1           0000654
5            Кактус                      1             10          0000436
6            Ромашка                     1             5           0000756

Необходимо, чтобы товары сгруппировались по group_id и из группировки выбрался то один товар, который попадает под фильтр: (quantity >= minimum), но при этом, вывелись товары Кактус и Ромашка (хоть у них и quantity < minimum).
  • Вопрос задан
  • 70 просмотров
Пригласить эксперта
Ответы на вопрос 2
Immortal_pony
@Immortal_pony Куратор тега MySQL
SELECT 
    ## product_id
    CASE 
        WHEN COUNT(`products`.`product_id`)=1 THEN ANY_VALUE(`products`.`product_id`)
        ELSE (
            SELECT 
                `product_id` 
            FROM 
                `products` AS `product_id_sub` 
            WHERE 1
                AND `product_id_sub`.`group_id`=`products`.`group_id`
                AND `product_id_sub`.`quantity` > `product_id_sub`.`minimum`
            ORDER BY 
                `product_id_sub`.`minimum` DESC
            LIMIT 1
        )
    END AS 'product_id',
    
    ## name
    CASE 
        WHEN COUNT(`products`.`product_id`)=1 THEN ANY_VALUE(`products`.`name`)
        ELSE (
            SELECT 
                `name` 
            FROM 
                `products` AS `name_sub` 
            WHERE 1
                AND `name_sub`.`group_id`=`products`.`group_id`
                AND `name_sub`.`quantity` > `name_sub`.`minimum`
            ORDER BY 
                `name_sub`.`minimum` DESC
            LIMIT 1
        )
    END AS 'name',
    
    ## quantity       
    CASE 
        WHEN COUNT(`products`.`product_id`)=1 THEN ANY_VALUE(`products`.`quantity`)
        ELSE (
            SELECT 
                `quantity` 
            FROM 
                `products` AS `quantity_sub` 
            WHERE 1
                AND `quantity_sub`.`group_id`=`products`.`group_id`
                AND `quantity_sub`.`quantity` > `quantity_sub`.`minimum`
            ORDER BY 
                `quantity_sub`.`minimum` DESC
            LIMIT 1
        )
    END AS 'quantity',
    
    ## minimum       
    CASE 
        WHEN COUNT(`products`.`product_id`)=1 THEN ANY_VALUE(`products`.`minimum`)
        ELSE (
            SELECT 
                `minimum` 
            FROM 
                `products` AS `minimum_sub` 
            WHERE 1
                AND `minimum_sub`.`group_id`=`products`.`group_id`
                AND `minimum_sub`.`quantity` > `minimum_sub`.`minimum`
            ORDER BY 
                `minimum_sub`.`minimum` DESC
            LIMIT 1
        )
    END AS 'minimum',
    
    ## group_id
    `group_id`    
FROM `products`
GROUP BY `group_id`


Если устроит результат в виде JSON, то можно сократить количество подзапросов:
SELECT 
    CASE 
        WHEN COUNT(`products`.`product_id`)=1 THEN JSON_OBJECT(
            'product_id', ANY_VALUE(`products`.`product_id`),
            'name', ANY_VALUE(`products`.`name`),
            'quantity', ANY_VALUE(`products`.`quantity`),
            'minimum', ANY_VALUE(`products`.`minimum`),
            'name', `products`.`group_id`
        )
        ELSE (
            SELECT 
                JSON_OBJECT(
                    'product_id', `product_with_required_quantity`.`product_id`,
                    'name', `product_with_required_quantity`.`name`,
                    'quantity', `product_with_required_quantity`.`quantity`,
                    'minimum', `product_with_required_quantity`.`minimum`,
                    'name', `product_with_required_quantity`.`group_id`
                )
            FROM 
                `products` AS `product_with_required_quantity` 
            WHERE 1
                AND `product_with_required_quantity`.`group_id`=`products`.`group_id`
                AND `product_with_required_quantity`.`quantity` > `product_with_required_quantity`.`minimum`
            ORDER BY 
                `product_with_required_quantity`.`minimum` DESC
            LIMIT 1
        )
    END AS 'product'  
FROM `products`
GROUP BY `group_id`


PS В обоих запросах логика для не указанных чётко случаев следующая:
1. Если нет ни одного записи с quantity>minimum, то пишем NULL
2. Если больше чем одной записи с quantity>minimum, то выбираем запись с наибольшим quantity и пишем её данные
Ответ написан
Комментировать
@Akina
Сетевой и системный админ, SQL-программист.
По показанным данным и тому, что удалось выудить из описания и комментариев, мне кажется, что достаточно для каждой группы вернуть одну запись с максимальным в группе значением quantity. Это делается запросом
WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY quantity DESC) rn
    FROM tablename
)
SELECT *
FROM cte
WHERE rn = 1;


Запрос предполагает, что для всех записей группы значение minimum одинаково. Если это не так - использовать ORDER BY quantity>minimum DESC, quantity DESC.

Запрос предполагает, что в группе нет дубликатов по quantity. Если это не так - вернётся случайный из дубликатов с максимальным значением поля. Если нужен определённый из них - расширить соотв. образом ORDER BY.

Если версия MySQL старая и не поддерживает ни CTE, ни оконные функции - использовать эмуляцию ROW_NUMBER() на базе UDV в подзапросе.
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы