Как выбрать последнюю запись с group by?

Question

[Александр Панков]

Есть таблица и в ней выдержится статистика по продажам товаров за каждый день, а конкретно хранится: товар, размер, кол-во продаж, кол-во заказов, баланс на складе, номер склада, дата

У меня есть некоторый запрос, который выводит агрегированную информацию за период, например
у нас есть инфа, что синюю кружку мы продавали 3 дня в подряд, у нас в бд есть в 3 строки (на каждый день), делаю запрос я ожидаю 1 строку где будут суммы продаж, и заказов (и тд и тп) этой кружки с каждого из складов за эти 3 дня.

Я составил запрос, но он ожидаемо не работает как мне надо, так как я "схлопываю" строки по дате (группирую по товару+размеру+складу) и суммирую столбцы, а мне нужно НЕ суммировать столбик stoks, а получить из него самую позднюю запись, те логически мне нужна агрегированная информация по продажам за 3 дня + текущие количество на складе.

я набросал пример https://sqlize.online/sql/mysql80/693a79a8d004448c...

продублирую еще часть сюда

|----|--------------|------|--------|-------|--------|--------------|------------|
| id | product_id   | size | orders | sales | stocks | warehouse_id | date       |
|----|--------------|------|--------|-------|--------|--------------|------------|
| 1  | 85           | 45   | 1      | 1     | 10     | 1            | 2023-09-01 |
| 2  | 85           | 45   | 1      | 1     | 8      | 1            | 2023-09-02 |
| 3  | 85           | 45   | 1      | 1     | 6      | 1            | 2023-09-03 |

INSERT INTO stats (product_id,size,orders,sales,stocks,warehouse_id,date) VALUES (85,'45',1,1,10,1,'2023-09-01');
INSERT INTO stats (product_id,size,orders,sales,stocks,warehouse_id,date) VALUES (85,'45',1,1,8,1,'2023-09-02');
INSERT INTO stats (product_id,size,orders,sales,stocks,warehouse_id,date) VALUES (85,'45',1,1,6,1,'2023-09-03');

select
    product_id,
    size,
    sum(orders) as orders,
    sum(stocks) as stocks,
    warehouse_id
from stats
where date(`date`) >= '2023-09-01' and date(`date`) <= '2023-09-04'
group by product_id, warehouse_id, size;

ps гуглю про оконные функции, вроде очень похоже что мне подойдут, но никак не могу составить запрос

Comments

[alexalexes]

Какую версию MySQL используете?

[Александр Панков]

alexalexes, 8

Answer 1

[alexalexes]

Как-то так:

select b.*
from (select a.*, -- атрибуты по агрегированной статистике
            -- атрибуты записей-кандидатов на последние записи
             s.id id_2,
             s.product_id product_id_2,
             s.warehouse_id warehouse_id_2, 
            -- ......
             row_number() over (partition by s.product_id, s.warehouse_id, s.date order by s.id desc) r_num -- нумеруем кандидатов, по порядку id в статистике
from (select
         product_id,
         size,
         warehouse_id,
         max(date) later_date, -- поздняя дата
         sum(orders) as orders
    from stats
  where date(`date`) >= '2023-09-01' and date(`date`) <= '2023-09-04'
   group by product_id, warehouse_id, size
)  a
left join stats s on s.date = a.later_date
                 and s.product_id = a.product_id
                 and s.warehouse_id = a.warehouse_id) b
where b.r_num = 1 -- берем первого кандидата (нужно проверить, будет ли null, если left join не присоединит запись)

Посмотрите план выполнения запроса, он скорее всего будет ужасен, так как нужно разделять эти два вида запросов, запрашивать отдельно каждый вид.

Comments

[Александр Панков]

Спасибо, а что значит разделать запросы?
На уровне бизнес логики имеется ввиду, в коде?

[alexalexes]

Да, на уровне бизнес логики.
Сначала выполнить подзапрос A.
Потом по входным параметрам дозапросить записи S (или одну запись после сортировки).

.... on s.date = a.later_date
                 and s.product_id = a.product_id
                 and s.warehouse_id = a.warehouse_id

Answer 2

[Akina]

А решение было близко... раз нужны данные с разных уровней группировки, то либо вертеть подзапросы/CTE, либо использовать оконные функции. Как по мне, второе проще.

SELECT DISTINCT
       product_id,
       size,
       SUM(orders) OVER (PARTITION BY product_id, warehouse_id, size) AS orders,
       FIRST_VALUE(stocks) OVER (PARTITION BY product_id, warehouse_id, size 
                                 ORDER BY `date` DESC) AS stocks,
       warehouse_id
FROM stats
WHERE DATE(`date`) >= '2023-09-01' 
  AND DATE(`date`) <= '2023-09-04';

fiddle

Comments

[Александр Панков]

Хм, интересно, не могу впихнуть в голову ка кто работает, особенно distinct в этом контексте, спасибо

[Akina]

Александр Панков, да элементарно.

product_id, warehouse_id, size - берутся как есть из текущей записи.

orders - считается сумма по всем записям с теми же product_id, warehouse_id, size, что и у текущей записи (как при группировке).

stocks - берётся первая запись в группе по product_id, warehouse_id, size, но т.к. сортировка DESC, то она с твоей точки зрения последняя.

Всё описанное считается для каждой строки - то есть из 3 исходных мы получим три совершенно одинаковых выходных строки. DISTINCT убирает эти дубликаты, оставляя только одну запись.