как вывести строки с максимальными данными для группы (mysql)

Question

цель - вывести группы товаров (product_group_name) с максимальным количеством (qty), но нужно как-то оставить только строки с максимальным значением в группе

сейчас есть код, ниже на картинке результат его работы и рамками обозначено то, что нужно в итоге оставить.

`select
 age_group,
 product_group_name,
 qty,
 max(qty) over(partition by age_group) as max_qty
 from (

select
case
when timestampdiff(year, str_to_date(c.birth_dt, '%d.%m.%Y'), current_date()) between 18 and 30 then '18-30 лет'
when timestampdiff(year, str_to_date(c.birth_dt, '%d.%m.%Y'), current_date()) between 31 and 45 then '30-45 лет'
when timestampdiff(year, str_to_date(c.birth_dt, '%d.%m.%Y'), current_date()) between 45 and 60 then '45-60 лет'
when timestampdiff(year, str_to_date(c.birth_dt, '%d.%m.%Y'), current_date()) > 60 then '60+ лет'
end as age_group,
p.product_group_name,
count(p.product_group_name) as qty

from products as p
join orders_product on p.product_id = orders_product.product_id
join orders on orders.order_id = orders_product.order_id
join customers as c on c.customer_id = orders.customer_id
group by age_group, p.product_group_name
) as t
order by age_group, qty desc`

Answer 1

WITH 
cte1 AS (
    -- запрос из текста вопроса, но без ORDER BY
    SELECT age_group,
           product_group_name,
           qty,
           MAX(qty) OVER (PARTITION BY age_group) AS max_qty
    ...
    ),
cte2 AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY age_group ORDER BY max_qty DESC) AS rn
    FROM cte1
    )
SELECT *
FROM cte2 
WHERE rn = 1
ORDER BY ...;

Возможна ситуация, когда в рамках одной группы age_group две и более записей имеют одинаковый и одновременно максимальный в группе qty. Показанный запрос вернёт одну из таких записей, причём неизвестно какую. И даже не обязательно ту же, что при предыдущем выполнении этого запроса.

Если нужна одна, но определённая, следует расширить выражение сортировки в определении окна в функции ROW_NUMBER() так, чтобы полученное выражение гарантировало уникальность.

Если же нужны все такие записи - заменить ROW_NUMBER() на RANK() или DENSE_RANK().

БЛОГ НА HUSL

как вывести строки с максимальными данными для группы (mysql)

Ответы (1 шт):