Oracle查询:在列中选择数据的最后更改 [英] Oracle Query: Picking the last change in data in a column

问题描述

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

I have a table that has the following columns:

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

Product_ID Pricing_Date Current_Price Last_Pricing_Date

我正在尝试编写一个查询，以显示产品的旧价格(Last_Price)，然后将其更改为当前价格，以使结果看起来像下面的

I am trying to write a query that shows the old price (Last_Price) of the product before it was changed to the current price so that my results look like the below

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

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

Effectively picking what the value of the data was before the change. Some resources to test - you can quickly create a table using script:

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 因此，最终结果集将是

I'm sorry everyone, can I add one other complication. I need to also bring a column into the results table that shows the Last_Date_With_Prev_Price The final results set would therefore be

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

推荐答案

一种直接的方法是select子句中的子查询:

A straight-forward approach to this is a subquery in the select clause:

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;

Rextester演示: http://rextester.com/HTBXE60602

Rextester demo: http://rextester.com/HTBXE60602

这篇关于Oracle查询:在列中选择数据的最后更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！