Oracle 查询：选择列中数据的最后更改

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

产品_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

这很棘手，因为你的价格会上下波动。您可以使用lag(ignore nulls)。首先，找出价格变化的时间。然后获取之前的价格。所以：

select cr.*,
       (case when prev_current_price <> current_price
             then prev_current_price  -- use the previous price when it changes
             else  -- lag to the previous change
                  lag( (case when prev_current_price <> current_price then prev_current_price
                        end) ignore nulls
                      ) over (partition by cr.product_id
                              order by cr.price_date
                             )
        end) as prev_price
from (select cr.*,
             lag(cr.current_price) over (partition by cr.product_id order by cr.price_date) as prev_current_price
      from comp_results cr
     ) cr;