Умная группировка

Question

Есть такая вот табличка:

Product 
Id, group_id, name, price, quantity, date
1, 1, Футболка (красная), 150, 100, 2023-10-21 00:00:00
2, 1, Футболка (синяя), 150, 50, 2023-10-20 00:00:00
3, 1, Футболка (белая), 300, 90, 2023-10-21 00:00:00
4, 1, Футболка (зеленая), 90, 100, 2023-10-21 00:00:00
5, 1, Футболка (оранжевая), 110, 21, 2023-10-05 00:00:00
6, 2, шорты (xl), 700, 100, 2023-10-21 00:00:00
7, 2, шорты (s), 500, 100, 2023-10-21 00:00:00
8, 2, шорты (m), 1000, 50, 2023-10-21 00:00:00
9, 2, шорты (xxl), 400, 10, 2023-10-21 00:00:00
10, 2, шорты (xxxl), 350, 1, 2023-10-21 00:00:00

Мне необходимо будет сортировать несколькими вариантами:

наименьшая цена в группе для сортировки по цене
максимальное кол-во в группе для сортировки по кол-ву

При одинаковых ценах/количествах показывать самый новый товар.

Использую PostgreSQL 14.8

Answer 1

Я не совсем понял, что хотите получить в итоге: все элементы отсортированные внутри групп по возрастанию цены и по убыванию количества? тогда вот решение: sqlfiddle

Или же хотите получить самый дешевый товар и товар, которого больше всего по количеству в одном запросе? тогда тут нужно уточнить, что тут получаем несколько товаров в топе, если хотим получить один, показ самого нового не помогает избавиться от недетерминированной сортировки, есть несколько товаров с одной датой внесения в базу. Вот решение для такого варианта, но тут берем все самые дешевые товары в группе и все наибольшие по остатку sqlfiddle.com

Отдельно добавлю код решения: 1 вариант

with a as(
select 
  p.id
  , p.group_id
  , p.name
  , p.price
  , p.quantity
  , p.date
  , row_number() over (partition by p.group_id order by p.price, p.date desc) as min_price
  , row_number() over (partition by p.group_id order by p.quantity desc, p.date desc) as max_count
from product p
)
select 
  p.id
  , p.group_id
  , p.name
  , p.price
  , p.quantity
  , p.date
from a as p
order by p.group_id, p.min_price, p.max_count

второй вариант

with a as(
select 
  p.id
  , p.group_id
  , p.name
  , p.price
  , p.quantity
  , p.date
  , min(p.price) over (partition by p.group_id) as min_price
  , max(p.quantity) over (partition by p.group_id) as max_count
from product p
)
select 
  p.id
  , p.group_id
  , p.name
  , p.price
  , p.quantity
  , p.date
  , 'min_price_in_group' as reason_sort
from a as p
where p.price = p.min_price
union all
select 
  p.id
  , p.group_id
  , p.name
  , p.price
  , p.quantity
  , p.date
  , 'max_quantity_in_group' as reason_sort
from a as p
where p.quantity = p.max_count
order by group_id

БЛОГ НА HUSL

Умная группировка

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