sql - 利润(计算)基于历史成本价 -- Postgres

Question

根据交易时的成本价计算利润时出现问题

下面是场景的重现

create table price_history(id int,dated date,product_id int,cost_price int);
insert into price_history(id,dated,product_id,cost_price) 
values 
  (6, to_date('2016-10-01','YYYY-MM-DD'),1,200),
  (7, to_date('2016-10-02','YYYY-MM-DD'),2,250),
  (8, to_date('2016-10-03','YYYY-MM-DD'),1,280),
  (9, to_date('2016-10-05','YYYY-MM-DD'),1,300);
commit;

create table sales_trans(id int,dated date,product_id int, sales_price int);
insert into sales_trans(id,dated,product_id,sales_price) 
values 
  (1, to_date('2016-10-01','YYYY-MM-DD'),1,220),
  (2, to_date('2016-10-03','YYYY-MM-DD'),1,250),
  (3, to_date('2016-10-04','YYYY-MM-DD'),1,300),
  (4, to_date('2016-10-06','YYYY-MM-DD'),1,330);

提交；

PRICE_HISTORY

ID | DATE       | PRODUCT ID| COST_PRICE
6  | 10/1/2016  | 1         | 200
7  | 10/2/2016  | 2         | 250
8  | 10/3/2016  | 1         | 280
9  | 10/5/2016  | 1         | 300

销售交易

ID | DATE       | PRODUCT ID| SALES_PRICE
1  | 10/1/2016  | 1         | 220
2  | 10/3/2016  | 1         | 250
3  | 10/4/2016  | 1         | 300
4  | 10/6/2016  | 1         | 330

我想要如下输出:

DATE        | PRODUCT ID| SALES_PRICE |  COST_PRICE
10/1/2016   | 1         | 220         | 200
10/3/2016   | 1         | 250         | 280
10/4/2016   | 1         | 300         | 280
10/6/2016   | 1         | 330         | 300

Answer 1

对联接表使用distinct on:

select distinct on(t.id, t.dated, t.product_id, sales_price)
    t.id, t.dated, t.product_id, sales_price, 
    cost_price, h.dated as cost_date
from sales_trans t
left join price_history h
on t.product_id = h.product_id and t.dated >= h.dated
order by 1, 2, 3, 4, 6 desc;

 id |   dated    | product_id | sales_price | cost_price | cost_date  
----+------------+------------+-------------+------------+------------
  1 | 2016-10-01 |          1 |         220 |        200 | 2016-10-01
  2 | 2016-10-03 |          1 |         250 |        280 | 2016-10-03
  3 | 2016-10-04 |          1 |         300 |        280 | 2016-10-03
  4 | 2016-10-06 |          1 |         330 |        300 | 2016-10-05
(4 rows)