Oracle Query: 在列中选择数据的最后更改

Question

我有一个包含以下各列的表：

产品ID定价日期当前价格最后定价日期

我正在尝试编写一个查询，以显示产品的旧价格（Last_Price），然后将其更改为当前价格，这样我的结果如下所示

PRODUCT_ID  PRICE_DATE  PRODUCT_PRICE LAST_PRICE
BlueLotion  24/08/2018   £10.00        £7.50
BlueLotion  23/08/2018   £10.00        £7.50
BlueLotion  22/08/2018   £10.00        £7.50
BlueLotion  21/08/2018   £7.50         £6.50 
BlueLotion  20/08/2018   £7.50         £6.50 
BlueLotion  19/08/2018   £7.50         £6.50
BlueLotion  17/08/2018   £6.50         £7.50
BlueLotion  16/08/2018   £6.50         £7.50
BlueLotion  13/08/2018   £6.50         £7.50
BlueLotion  12/08/2018   £7.50          NULL
BlueLotion  11/08/2018   £7.50          NULL
BlueLotion  10/08/2018   £7.50          NULL

有效地选择更改之前数据的价值。一些需要测试的资源-您可以使用脚本快速创建表：

create table COMP_RESULTS (product_id varchar2(20), price_date date, product_price number);

insert into comp_results values ('BlueLotion','24 AUG 2018','10');
insert into comp_results values ('BlueLotion','23 AUG 2018','10');
insert into comp_results values ('BlueLotion','22 AUG 2018','10');
insert into comp_results values ('BlueLotion','21 AUG 2018','7.5');
insert into comp_results values ('BlueLotion','20 AUG 2018','7.5');
insert into comp_results values ('BlueLotion','19 AUG 2018','7.5');
insert into comp_results values ('BlueLotion','18 AUG 2018','6.5');
insert into comp_results values ('BlueLotion','17 AUG 2018','6.5');
insert into comp_results values ('BlueLotion','16 AUG 2018','6.5');
insert into comp_results values ('BlueLotion','15 AUG 2018','7.5');
insert into comp_results values ('BlueLotion','14 AUG 2018','7.5');
insert into comp_results values ('BlueLotion','13 AUG 2018','7.5');

对不起，每个人，我能补充一下其他并发症吗？我还需要在结果表中添加一列，以显示Last_Date_With_Prev_Price。因此，最终结果集为

PRODUCT_ID  PRICE_DATE  PRICE  LAST_PRICE  DATE_WITH_PREV_RATE
BlueLotion  24/08/2018  £10.00  £7.50       21/08/2018
BlueLotion  23/08/2018  £10.00  £7.50       21/08/2018
BlueLotion  22/08/2018  £10.00  £7.50       21/08/2018
BlueLotion  21/08/2018  £7.50   £6.50       17/08/2018
BlueLotion  20/08/2018  £7.50   £6.50       17/08/2018
BlueLotion  19/08/2018  £7.50   £6.50       17/08/2018
BlueLotion  17/08/2018  £6.50   £7.50       12/08/2018
BlueLotion  16/08/2018  £6.50   £7.50       12/08/2018
BlueLotion  13/08/2018  £6.50   £7.50       12/08/2018
BlueLotion  12/08/2018  £7.50   NULL        NULL
BlueLotion  11/08/2018  £7.50   NULL        NULL
BlueLotion  10/08/2018  £7.50   NULL        NULL

Answer 1

一个简单的方法是在select子句中使用子查询：

select
  product_id,
  price_date,
  product_price,
  (
    select
      max(before.product_price) keep (dense_rank last order by before.price_date)
    from comp_results before
    where before.product_id = comp_results.product_id
      and before.price_date < comp_results.price_date
      and before.product_price <> comp_results.product_price
  ) as last_price
from comp_results
order by product_id, price_date desc;