Как сформировать таблицу со столбцом «Выручка» с учетом актуальной цены на продукт?

Question

[EkZha]

Здравствуйте! Имеется две таблицы:

1) "Цены" (prices) со столбцами "наименование продукта", "цена", "дата вступления цены в силу" (у одного и то же продукта с течением времени цена менялась)
2) "Продажи"(sales) со столбцами "наименование продукта", "дата продажи", "количество проданных единиц товара"
Нужно составить запрос на создание таблицы со столбцами "наименование продукта", "дата продажи", "выручка", где выручка считается как произведение количества на актуальную на данную дату цену.

Как написать условие так, чтобы для продукта учитывалась цена, указанная на ближайшую в меньшую дату сторону? В результате моего запроса расчет для некоторых записей производятся по неправильным ценам: CREATE TABLE revenue AS SELECT sales.product, sales.date, sales.amount * prices.price AS revenue FROM sales INNER JOIN prices ON sales.product = prices.product WHERE sales.date >= prices.date_start group by sales.date;

Подскажите, в какую сторону думать.

Comments

[Максим Y]

Посмотрите функции LEAD/LAG.
www.postgresqltutorial.com/postgresql-lead-function

[EkZha]

Спасибо!

[Максим Y]

EkZha, с помощью LEAD добавляете столбец "дата вступления в силу следующей цены", таким образом получаете выборку с ценами, их датами начала действия и конца действия. Дальше свой лог продаж джойните с полученной выборкой, где "дата начала действия цены" <= дата продажи > "дата конца действия цены".

[EkZha]

Максим Sh, спасибо за подробное описание. Сделала всё, как Вы сказали, с учетом наличия нулевых значений в столбце "дата вступления в силу следующей цены"

CREATE VIEW prices_extra AS SELECT product, price, date_start, LEAD(date_start) OVER(PARTITION BY product ORDER BY date_start) next_date FROM prices;

CREATE TABLE revenue AS SELECT sales.product, sales.date, prices_extra.price * sales.amount AS revenue FROM sales INNER JOIN 
prices_extra ON sales.product = prices_extra.product WHERE 
CASE WHEN prices_extra.next_date IS NOT NULL 
	THEN prices_extra.date_start <= sales.date AND sales.date < prices_extra.next_date
ELSE sales.date > prices_extra.date_start END;

[Максим Y]

EkZha, ну отлично получилось.
Вместо VIEW можно джойнить с подзапросом:

... FROM sales 
INNER JOIN (SELECT product,
                   price,
                   date_start, 
                   LEAD(date_start) OVER(PARTITION BY product ORDER BY date_start) next_date 
              FROM prices) prices_extra 
     ON sales.product = prices_extra.product ...

А WHERE можно упростить, подставляя вместо отсутствующих значений дату из будущего:

WHERE prices_extra.date_start <= sales.date AND sales.date < COALESCE(prices_extra.next_date, to_date('20990101', 'YYYYMMDD'))

[EkZha]

Максим Sh, спасибо за помощь и советы !

Answer 1

[d-stream]

Ну для начала стоило бы вырвать руки аффтору именно такой структуры данных...
Потом погрустить и извращаться-извращаться с кучей оговорок...

Например можно взять и получить цену позже искомой даты как:

select top 1
цена
from Цены
where дата вступления цены в силу<=искомая дата and товар=товар
order by дата вступления цены в силу desc

как минимум приведенный выше кусок напрашивается как вложенный запрос или как кусок cte ну или как фрагмент join

Comments

[Максим Y]

Как select top 1 применить в задаче заполнения таблицы? Инлайн запрос?

[d-stream]

Максим Sh, как я и писал:

как минимум приведенный выше кусок напрашивается как вложенный запрос или как кусок cte ну или как фрагмент join

[EkZha]

Спасибо вам, направление ясно, буду додумывать

[d-stream]

EkZha, только вот архитектурно исходные жутко кривые. К примеру вообще никак не отслеживается например продажа по произвольной цене.

Нормальный вариант - в конкретной продаже должна фигурировать конкретная цена продажи (отражение реального факта), а цена, которая могла бы быть/должна была бы быть - условно-справочно для будущих отчетов по отклонениям фактических продаж от "плановых" и т.п.