Как стать автором
Обновить
233.42
Postgres Professional
Разработчик СУБД Postgres Pro

SQL: задача на поиск последней цены

Время на прочтение 3 мин
Количество просмотров 9.3K

Здравствуйте! В эфире снова Радио SQL.

Давненько не выходили в эфир, но тут братья-гуманоиды из соседнего Малого МакГеланового облака подкинули задачку. Сходу в один присест задачка не решилась, пришлось подумать. Значит и в Западном рукаве Галактики тоже могут найтись желающие поломать мозг об задачку. Сейчас изложу условие, а ответ следующим посланием уйдёт.

Условие такое. Есть набор данных с ценами на товары (prod_id) на складах (stock_id). Причём цены бывают настоящие (R=Real), а бывают рекламные (P=Promo). Для каждой цены есть дата начала действия. Нужно к каждой строчке набора вытащить реальную цену, которая является последней по дате настоящей ценой (price1) с типом 'R' на этот товар на соответствующем складе.

Вот начало запроса с тестовыми данными в виде CTE, на которых можно потренироваться:

with price(stock_id, prod_id, start_date, kind, price1, cost1, bonus1) as (
values (1,1,to_date('2000-01-01','YYYY-MM-DD'),'R',100.0,32.12,6.49),
       (1,1,'2000-01-02','P', 80.0, 0,   0),
       (1,1,'2000-01-03','P', 70.0, 0,   0),
       (1,1,'2000-01-04','R',110.0,33.48,6.19),
       (1,1,'2000-01-05','P', 90.0, 0,   0),
       (1,1,'2000-01-06','R',120.0,41.22,6.19),
       (1,1,'2000-01-07','P', 80.0, 0,   0),
       (1,1,'2000-01-08','P', 90.0, 0,   0),
       (1,1,'2000-01-09','R', 93.0,36.87,6.49),
       (1,1,'2000-01-10','R', 94.0,36.85,6.99),
       (1,2,'2000-01-01','R',101.0,52.06,9.00),
       (1,2,'2000-01-02','P', 81.0, 0,   0),
       (1,2,'2000-01-03','P', 71.0, 0,   0),
       (1,3,'2000-01-04','R',111.0,64.96,4.50),
       (1,3,'2000-01-05','P', 92.0, 0,   0),
       (1,3,'2000-01-06','R',122.0,66.83,4.60),
       (1,3,'2000-01-07','P', 82.0, 0,   0),
       (1,3,'2000-01-08','P', 92.0, 0,   0))
select ...

Должно получиться что-то вида:

 stock_id | prod_id | start_date | kind | price1 | cost1 | bonus1 | price1x 
----------+---------+------------+------+--------+-------+--------+---------
        1 |       1 | 2000-01-01 | R    |  100.0 | 32.12 |   6.49 |   100.0
        1 |       1 | 2000-01-02 | P    |   80.0 |     0 |      0 |   100.0
        1 |       1 | 2000-01-03 | P    |   70.0 |     0 |      0 |   100.0
        1 |       1 | 2000-01-04 | R    |  110.0 | 33.48 |   6.19 |   110.0
        1 |       1 | 2000-01-05 | P    |   90.0 |     0 |      0 |   110.0
        1 |       1 | 2000-01-06 | R    |  120.0 | 41.22 |   6.19 |   120.0
        1 |       1 | 2000-01-07 | P    |   80.0 |     0 |      0 |   120.0
        1 |       1 | 2000-01-08 | P    |   90.0 |     0 |      0 |   120.0
        ...

Особенности же тут вот в чём. Я не зря радировал выше «источник данных», потому что не таблица тут у нас, а вьюха, собранная из самых разных и зачастую совершенно неожиданных источников, откуда всякие промо-цены и берутся. То есть primary key для строчек не только нету, но и даже суррогатный-то на лету не так сразу получишь, так как никаких CTID (или там ROWID) в помине нету... Второй нюанс — это тут я оставил только колонки price1, cost1 и bonus1, а в настоящем источнике данных много всяких характеристик нужно было вытащить из последней 'R'-строки, так как на рекламных строках эти данные отсутствуют. И не спрашивайте, почему так — бизнесу виднее. Считайте расширенным условием задачи — выбрать все эти поля из последней R-записи.

Что ж, прошу в каменты с идеями и вариантами решений! Через мифический человеко-месяц будет подведение итогов и разбор решения. Решения в комментариях будут проверяться на PostgreSQL, так что можно пользоваться (но не злоупотреблять!) платформозависимыми особенностями. Большая просьба SQL-код убирать под спойлеры, чтобы не светить его тем, кто хотел бы попробовать свои силы в решении. Для не имеющих постгреса под руками, есть масса возможностей запустить запрос онлайн, так что за отмазку не засчитывается. Да и поставить работающий постгрес из штатных репозиториев ОС под любым не слишком экзотическим Linux можно буквально в одну команду.

P.S. Так как опубликовано в корпоративном блоге Постгрес Про, то будем пользоваться корпоративными привилегиями. Для стимуляции активности за самое интересное решение выдам какие-нибудь плюшки от лица компании, промокод на PGConf или на сдачу тестов по сертификации...

P.P.S. Разбор решения тут: https://habr.com/ru/company/postgrespro/blog/557300/

Теги:
Хабы:
+10
Комментарии 56
Комментарии Комментарии 56

Публикации

Информация

Сайт
www.postgrespro.ru
Дата регистрации
Дата основания
Численность
201–500 человек
Местоположение
Россия
Представитель
Иван Панченко