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

Question

[Олег Курлык]

Здравствуйте.
Описание: метод 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).

Comments

[Олег Курлык]

ChairfaceChippendale, Спасибо за ответ. Я читал про ONLY_FULL_GROUP_BY
Я добавил в описание пример, возможно станет яснее, что я имел ввиду

[Akina]

Проблема в том, что GROUP BY выбирает первый элемент с group_id, то есть, если у нас 4 товара, которые связаны между собой по group_id, GROUP BY выберет первый элемент.

Не-а. Не "первый". А тот, который подвернулся под руку, фактически случайный из возможных.

А мне нужно, чтобы выбирался не первый элемент, а по условию для поля quantity: если quantity > 0.

В показанном примере все три записи группы group_id=00000123 соответствуют этому условию. Почему выбрана запись именно id=2?

из группировки выбрался то один товар, который попадает под фильтр: (quantity >= minimum)

А если под этот фильтр подпадает более одной записи - которую выводить? А если ни одна не соответствует, и их всего более чем одна- опять какую выводить?

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

[Олег Курлык]

Согласен, сейчас перепишу вопрос, спасибо

Answer 1

[Immortal_pony]

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 и пишем её данные

Answer 2

[Akina]

По показанным данным и тому, что удалось выудить из описания и комментариев, мне кажется, что достаточно для каждой группы вернуть одну запись с максимальным в группе значением 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 в подзапросе.