Комментарии 56
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;
Делал на Гринпламе, но по идее и на Постгресе должно работать
з.ы. Увы, не могу комментить чаще чем раз в час :) — так что расширенный ответ сильно позже
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
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
Но, конечно, вариант более громоздкий.
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;
Сортировка опциональна, исключительно ради наглядности.
Только другие значения вытаскивать будет тяжеловато, и логику работы тяжело разглядеть.
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;
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;
ОШИБКА: скопировать окно "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
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грегатор
Впрочем, тема была раскрыта достаточно полно. Буду писать следующую статью с объяснением, как решать эту задачу и разбором предложенных вариантов.
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
Вначале вычисляем группы (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),
Вот исходя из того, что с одной датой может быть много строчек, мне больше нравится вариант с ROWS, он будет переключать уровень стоимости на новый при каждой встреченной 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)),
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;
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
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
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
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;
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;
SQL: задача на поиск последней цены