Открыть список
Как стать автором
Обновить

Комментарии 55

Переделал - первый раз не так понял вопрос
select stock_id, prod_id, start_date, kind, price1, cost1, bonus1, (
select price1
from (
select *, row_number() over (partition by stock_id, prod_id order by start_date desc) as rn
from price
where kind='R'
) a
where rn=1 and price.prod_id=a.prod_id and price.stock_id=a.stock_id) as price1x
from price;

Расширенная версия
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)), 
last_price (stock_id, prod_id, price1x, cost1x, bonus1x) as (
select stock_id, prod_id, price1, cost1, bonus1
       from (
             select stock_id, prod_id, price1, cost1, bonus1, row_number() over (partition by stock_id, prod_id order by start_date desc) as rn
             from price
             where kind='R'
       ) a
       where a.rn = 1
)
select p.stock_id, p.prod_id, start_date, kind, price1, cost1, bonus1, price1x, cost1x, bonus1x
from price p
join last_price l
       on p.stock_id = l.stock_id and p.prod_id=l.prod_id;


Делал на Гринпламе, но по идее и на Постгресе должно работать
з.ы. Увы, не могу комментить чаще чем раз в час :) — так что расширенный ответ сильно позже
Увы, Вы не заметили такого подвоха, что R-строчек может быть несколько, и данные нужно брать из последней. Сравните Ваш результат с эталонным в статье. Видите?
Точно, спасибо!
Неожиданно стало интересно :)
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)),
real_price(stock_id, prod_id, price1x, cost1x, bonus1x, start_p, end_p) as (
       select
             stock_id, prod_id, price1, cost1, bonus1,
             start_date as start_p,
             lead(
                    (start_date-interval'1 days')::date,
                    1,
                    (select max(start_date) from price p2 where p.stock_id=p2.stock_id and p.prod_id = p2.prod_id)
             ) over (partition by stock_id, prod_id order by start_date asc) as end_p
       from price p
       where kind='R')
select p.*, r.price1x, r.cost1x, r.bonus1x
from price p join real_price r
       on p.stock_id = r.stock_id and p.prod_id = r.prod_id and p.start_date between r.start_p and r.end_p
order by p.stock_id, p.prod_id, p.start_date;

Да, так поинтереснее будет. Теперь работает верно, причём в расширенной версии условия.

Не проще ли завести промежуточную таблицу и собирать в ней историю изменений?


А потом просто брать фильтр по полю "действует на дату". Тем более что это данные источника, их всяко надо обработать

ну можно так искать любые данные из последней строки
Заголовок спойлера
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 p.*, x.price1 price1x
from price p
left join (
select stock_id, prod_id, price1, row_number() over (partition by stock_id, prod_id order by start_date desc) x
from price 
where kind = 'R') x on x.x = 1 and x.stock_id = p.stock_id and x.prod_id = p.prod_id

Можно. Для этого даже JOIN не нужен, достаточно оконной функции first_value() over (...). Но Вы недостаточно внимательно условие прочитали, строк с реальной ценой может быть несколько…
Тогда как-то так
Если надо последняя реальная цена на дату, то тогда что-то из разряда outer apply из TSQL (в Postreg чуть иначе но тоже есть и работает)
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 p.*, z.price1 price1x
from price p
 LEFT JOIN LATERAL (
select stock_id, prod_id, price1
from price  x
where kind = 'R' and x.stock_id = p.stock_id and x.prod_id = p.prod_id and x.start_date <= p.start_date
    order by start_date desc limit 1) z on true

либо тоже через row_number()
       select *
       from (
select p.*, x.price1 price1x, row_number() over (partition by p.prod_id, p.stock_id, p.start_date order by x.start_date desc) x
from price p
 LEFT JOIN (
select stock_id, prod_id, start_date, price1
from price 
where kind = 'R') x on x.stock_id = p.stock_id and x.prod_id = p.prod_id and x.start_date <= p.start_date
           ) n
           where n.x = 1

Да, так уже работает. Первое работающее решение есть!
Можете пояснить, зачем использовали в row_number() over (… start_date ...)? И как собираетесь вытаскивать остальные поля из нужной строчки?
в partition надо разделить на отдельные строки из основной таблицы, поэтому туда надо ещё и kind добавить, вместе в роли сборного идентификатора строки. Остальные поля добавляются в left join (...) x, таблица соединяется со всеми строками себя же из x с датой меньшеравно исходной, и уже оконная оставляет последнюю дату.
Но, конечно, вариант более громоздкий.
Как-то так можно вытащить любое из нужных полей
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
	*
,	((array_agg(row_to_json(p)) FILTER(WHERE kind = 'R') OVER(PARTITION BY stock_id, prod_id ORDER BY start_date DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING))[1] ->> 'price1')::numeric price1x
FROM
	price p
ORDER BY
	stock_id, prod_id, start_date;

Сортировка опциональна, исключительно ради наглядности.
Хитро придумано, но зато в один проход. И ведь работает!
Только другие значения вытаскивать будет тяжеловато, и логику работы тяжело разглядеть.
Если их конечное число — норм, достаточно вытащить всю строку в переменную — можно даже в LATERAL, тогда вроде даже лишних узлов не возникнет.
Расширенная версия с дополнительными полями
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
	*
FROM
	(
		SELECT
			*
		,	(array_agg(row_to_json(p)) FILTER(WHERE kind = 'R') OVER(PARTITION BY stock_id, prod_id ORDER BY start_date DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING))[1] rjs
		FROM
			price p
	) T
,	LATERAL (
		SELECT
			price1 price1x
		,	start_date start_datex
		FROM
			json_to_record(rjs) T(price1 numeric, start_date date)
	) L
ORDER BY
	stock_id, prod_id, start_date;

Да, работает. JSON — мощное колдунство. Интересно, на Oracle или MSSQL что-то подобное работать будет?
Можно и без JSON, но чуток копипасты
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
	*
,	(array_agg(price1)     FILTER(WHERE kind = 'R') OVER(w ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING))[1] price1x
,	(array_agg(start_date) FILTER(WHERE kind = 'R') OVER(w ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING))[1] start_datex
FROM
	price p
WINDOW
	w AS (PARTITION BY stock_id, prod_id ORDER BY start_date DESC)
ORDER BY
	stock_id, prod_id, start_date;

Супер! Это получается использование FILTER в оконной функции. Для компактности можно ещё определение окна отдельно сделать.
Оно как раз тут отдельно, иначе копипаста была бы еще жирнее. А если речь про frame_clause, то так не получается:

ОШИБКА:  скопировать окно "w", имеющее предложение рамки, нельзя
А вот так?
(array_agg(start_date) FILTER(WHERE kind = 'R') OVER w)[1]
...
WINDOW
	w AS (PARTITION BY stock_id, prod_id ORDER BY start_date DESC
              ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

Забавно… Никогда бы не подумал, что OVER(w) и OVER w имеют разное поведение.

Тогда получается так
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
	*
,	(array_agg(price1)     FILTER(WHERE kind = 'R') OVER w)[1] price1x
,	(array_agg(start_date) FILTER(WHERE kind = 'R') OVER w)[1] start_datex
FROM
	price p
WINDOW
	w AS (PARTITION BY stock_id, prod_id ORDER BY start_date DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
ORDER BY
	stock_id, prod_id, start_date;

Вот теперь работает.

Разное поведение, потому что разные вещи выражает. OVER w — это запуск оконной функции в окне w, которое можно определить отдельно. А если OVER (w), то это уже выражение, которое может быть задано в том числе с использованием существующего окна w. В документации достаточно хорошо видна разница: postgrespro.ru/docs/postgresql/12/sql-expressions#SYNTAX-WINDOW-FUNCTIONS
Согласен — они даже для парсера по-разному называются внутри WindowDef: name и refname. Век живи — век учись!
Если уметь считать max для Json, то вместо конкатенации строк можно использовать его. Хотя, Postgre Json считает строкой.

Запрос
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 *,
substring(max(case when kind = 'R' then start_date::varchar || price1::varchar end)
		  over(partition by stock_id, prod_id order by start_date rows unbounded preceding), 11, 100)::numeric
From price
order by 1, 2, 3

Работает. Хотя преобразования чисел и дат в строку и обратно — это не очень надёжное допущение, вдруг формат даты в настройках базы изменён — и приплыли.
Можно задать кастомній формат. Если нужно железобетонное решение в один прогон, то тогда:
1. создать тип struct(date, numeric)
2. написать aгрегатор

Да хотя бы явно преобразовывать дату в строку по маске, чтобы знать сколько потом откусывать. Тут предложили ещё аналогичный вариант с преобразованием в JSON. Для постгреса так, наверное, даже проще.
Есть еще вариант с массивом. Дата в нумерик норм кастуется, таймстемп тоже. Ну а максимум на массиве, вроде как, работает «правильно».
Да, можно. И подобное решение уже приводили. В JSON зато можно положить все значения, а не только какое-то одно.
Вроде затихло, видимо желающие отписались, а остальным некогда или стесняются.

Впрочем, тема была раскрыта достаточно полно. Буду писать следующую статью с объяснением, как решать эту задачу и разбором предложенных вариантов.
Каждый день заглядываю, не появилось ли продолжение :)
Да, пора бы… (:
Вот короткое решение под DB2 SQL

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 *, last_value(case when kind = 'R' then price1 end, 'IGNORE NULLS') over(order by STOCK_ID, PROD_ID, START_DATE) from price

Наверное, к сожалению PostgreSQL так не умеет…
Тогда немного сложнее

Вначале вычисляем группы (s) и далее первая величина в каждой группе
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,first_value(price1) over(partition by s) from (
select *, sum(case when kind = 'R' then 1 else 0 end) over(order by STOCK_ID, PROD_ID, START_DATE) s from price) a

О! Я бы назвал такую реализацию классической. Сперва делаем группы по уровням стоимости, потом оконной функцией вытаскиваем значения.

Но в реализации есть ошибка. Надо или явно указать порядок по kind, чтобы первой строкой по каждой дате всегда была R, или в определении рамки указать явно ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, чтобы s увеличивалась только на R-строках, а не на родственных. По умолчанию используется режим RANGE. Попробуйте запустить запрос с ROWS… и без на таких данных:
(1,1,'2000-01-05','P', 90.0, 0, 0),
(1,1,'2000-01-06','P', 80.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),

Абсолютно с вами согласен. Лично мне нравится добавить kind в сортировку. Я хотел лишь показать вариант решения и понимаю что нюансы могут быть. Кстати, как быть если R не на первом месте цены? Сетить price1x в 0?
Так как такое поведение не определено, то и неважно. Главное, чтобы при этом запрос что-то выбирал, а не падал с ошибками.

Вот исходя из того, что с одной датой может быть много строчек, мне больше нравится вариант с ROWS, он будет переключать уровень стоимости на новый при каждой встреченной R-строке. Алгоритмы СУБД часто не перемешивают уже упорядоченные в наборе записи, сохраняя оригинальный порядок, так что цены будут расставлены наиболее ожидаемым образом.
Заголовок спойлера
create sequence aaa;
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)),
q_price as (
select *, case when kind = 'R' then nextval('aaa') else currval('aaa') end as rn
from price),
r_price as (
select *, dense_rank() over (partition by stock_id, prod_id, rn order by stock_id, prod_id, start_date) as dr
from q_price)
select *, lag(price1, dr::int-1) over (partition by stock_id, prod_id, rn order by stock_id, prod_id, start_date) as price1x from r_price p order by stock_id, prod_id, start_date;

Интересная идея с использованием последовательности, мне такое в голову не приходило. И ведь работает! Из недостатков укажу, что требуются права на создание последовательности.
намудрил с названиями, лень переписывать
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 al.*,p.price1X
from (select p.stock_id,p.prod_id,p.start_date,max(r.start_date2) as start_date2
from price p
left join (select stock_id as stock_id2, prod_id as prod_id2, start_date as start_date2 from price pr where pr.kind='R') r
on p.stock_id=r.stock_id2 and p.prod_id=r.prod_id2 and p.start_date>=r.start_date2
Group by p.stock_id,p.prod_id,p.start_date
Order by p.stock_id,p.prod_id,p.start_date asc) r2
left join (select stock_id as stock_id2, prod_id as prod_id2, start_date as start_date2, price1 as price1X from price pr where pr.kind='R') p
on r2.stock_id=p.stock_id2 and r2.prod_id=p.prod_id2 and r2.start_date2=p.start_date2
left join (select * from price) al
on r2.stock_id=al.stock_id and r2.prod_id=al.prod_id and r2.start_date=al.start_date
Работает!
из последней 'R'-строки проморгал
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 r2.price1 as price1x ,p.*
from (select p.stock_id,p.prod_id,p.start_date,p.price1,max(r.start_date2) as start_date2
from price p
left join (select stock_id as stock_id2, prod_id as prod_id2, start_date as start_date2 from price pr where pr.kind='R') r
on p.stock_id=r.stock_id2 and p.prod_id=r.prod_id2 and p.start_date>=r.start_date2
Group by p.stock_id,p.prod_id,p.start_date,p.price1
Order by p.stock_id,p.prod_id,p.start_date asc) r2
left join (select * from price pr where pr.kind='R') p
on r2.stock_id=p.stock_id and r2.prod_id=p.prod_id and r2.start_date2=p.start_date
Нет такого программиста 1с которому не приходилось решать такие задачи. Первый пакет запроса выборка по фильтру задачи, второй пакет запроса внутренние соединение первого пакета и второго (товар=товар, склад =склад, дата1запроса >=дата2запроса)
Анекдот в тему. Встречаются два девопса. Один другому:
— Мне бы CI и деплой на сервер настроить надо…
— Давай я тебе объясню, как это сделать!
— Объяснить я и сам могу. Мне бы сделать…
sql
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
    gp.stock_id,
    gp.prod_id,
    gp.start_date,
    gp.kind,
    gp.price1,
    gp.cost1,
    gp.bonus1,
    gp.price1x
from (
    select
        p.stock_id,
        p.prod_id,
        p.start_date,
        p.kind,
        p.price1,
        p.cost1,
        p.bonus1,
        rp.price1 as price1x,
        row_number() over (partition by p.stock_id, p.prod_id, p.start_date order by rp.start_date desc) as num
    from price as p left join price as rp
        on p.stock_id = rp.stock_id
           and p.prod_id = rp.prod_id
           and rp.kind = 'R'
           and p.start_date >= rp.start_date
) as gp
where gp.num = 1
order by gp.stock_id, gp.prod_id, gp.start_date

Работает!
Не изящное решение, но всё же
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))
, price_hist as 
(select stock_id, prod_id, start_date, kind, coalesce(lead(start_date) over (partition by stock_id, prod_id order by start_date), to_date('5999-12-31', 'YYYY-MM-DD')) end_date
from price p
where kind = 'R')

select 
	stock_id, prod_id, start_date, kind, price1, cost1, bonus1, 
	first_value(price1) over(partition by stock_id, prod_id, check_date order by start_date) check_date
from
	(select 
		stock_id, prod_id, p.start_date, p.kind, price1, cost1, bonus1, end_date,
		first_value(end_date) over(partition by stock_id, prod_id, p.start_date order by end_date) check_date
	from 
		price p left join
		price_hist
			using(stock_id, prod_id)
	where end_date > p.start_date) t
where check_date=end_date

Работает. Действительно, можно было обойтись чем-то одним из двух — или оконными функциями, или JOIN-ом. Потому и несколько тяжеловесно.
Такого варианта вроде пока еще не было
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 DISTINCT ON(X)
	*
FROM
	price X
JOIN
	price Y
		ON (Y.stock_id, Y.prod_id, Y.kind) = (X.stock_id, X.prod_id, 'R') AND
		Y.start_date <= X.start_date
ORDER BY
	X, Y.start_date DESC;

или вот так
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 DISTINCT ON(X)
	*
FROM
	price X
JOIN
	price Y
		USING(stock_id, prod_id)
WHERE
	Y.kind = 'R' AND
	Y.start_date <= X.start_date
ORDER BY
	X, Y.start_date DESC;

Да, такого не было. И почти работает! DISTINCT даёт себя знать, дубликаты строк пропадают. Не знаю, нужны ли они будут или нет, но пропадают… А так красиво, конечно.
Если строки являются полными дубликатами, то тут большой вопрос к бизнес-сценариям…

Если нам так дороги дубликаты
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
	Z.*
,	Y.price1 price1x
,	Y.start_date start_datex
FROM
	price Z
LEFT JOIN (
		SELECT DISTINCT ON(X)
			X
		,	Y.*
		FROM
			price X
		JOIN
			price Y
				USING(stock_id, prod_id)
		WHERE
			Y.kind = 'R' AND
			Y.start_date <= X.start_date
		ORDER BY
			X, Y.start_date DESC
	) Y
		ON Z = Y.X;

Вариант
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 t.*,
       case range_num
         when 0
           then null
         else first_value(price1) over (partition by stock_id, prod_id, range_num order by start_date)
       end as pricex
  from (select p.*,
               sum(case kind
                     when 'R'
                       then 1
                     else 0
                   end) over (partition by stock_id, prod_id order by start_date) as range_num
          from price p) t
 order by t.stock_id, t.prod_id, t.start_date;

Спасибо за вариант, он почти работающий. На такую ошибку я уже указывал в комментариях выше, нужно указать ROWS… в определение окна, где sum().
Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

Информация

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

Блог на Хабре