Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
Здравствуйте! В эфире снова Радио 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 или на сдачу тестов по сертификации...
