Oracle查询使用条件来获取特定日期
Oracle查询使用条件来获取特定日期
提问于 2018-09-26 20:16:12
我有一个价格表，如下所示：
PRODUCT_ID PRICE_DATE 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 我想要一些帮助写一个查询，拉为每个记录的最后价格日期。例如，在数据行1中，BlueLotion的最后价格为7.5，该价格最后一次出现在2018年8月21日
因此，结果集将如下所示：
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 为了帮助理解这个例子，请参考下面的脚本来构建一个表。
create table COMP_RESULTS (product_id varchar2(20), price_date date, product_price number, last_price number);
insert into comp_results values ('BlueLotion',DATE '24 AUG 2018','10','7.5');
insert into comp_results values ('BlueLotion',DATE '23 AUG 2018','10','7.5');
insert into comp_results values ('BlueLotion',DATE '22 AUG 2018','10','7.5');
insert into comp_results values ('BlueLotion',DATE '21 AUG 2018','7.5','6.5');
insert into comp_results values ('BlueLotion',DATE '20 AUG 2018','7.5','6.5');
insert into comp_results values ('BlueLotion',DATE '19 AUG 2018','7.5','6.5');
insert into comp_results values ('BlueLotion',DATE '18 AUG 2018','6.5','7.5');
insert into comp_results values ('BlueLotion',DATE '17 AUG 2018','6.5','7.5');
insert into comp_results values ('BlueLotion',DATE '15 AUG 2018','6.5','7.5');
insert into comp_results values ('BlueLotion',DATE '14 AUG 2018','7.5',NULL);
insert into comp_results values ('BlueLotion',DATE '13 AUG 2018','7.5',NULL);
insert into comp_results values ('BlueLotion',DATE '12 AUG 2018','7.5',NULL);注意:表中有不同的产品，而不仅仅是BlueLotion。
回答 2
Stack Overflow用户
发布于 2018-09-26 20:46:02
您可以尝试使用LAG函数：https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=a545cab41ec5858e4f54f9220eb8c4f4
select comp_results.*,lastchangeddate from comp_results
inner join
(select product_id,prev_price,min(case when product_price<>prev_price then price_Date end) as lastchangeddate
from
(
select product_id,price_Date, product_price,last_price,
LAG(product_price, 1, 0) OVER (ORDER BY price_Date desc) as prev_price
from comp_results)a where prev_price<>0 group by product_id,prev_price
)b
on comp_results.product_id =b.product_id and
comp_results.product_price=b.prev_price
order by price_Date descStack Overflow用户
发布于 2018-09-26 20:20:55
您可以使用相关子查询：
SELECT cr.*,
(SELECT c1.PRICE_DATE
FROM COMP_RESULTS cr1
WHERE cr1.PRODUCT_ID = cr.PRODUCT_ID AND
cr1.PRICE = cr.LAST_PRICE AND cr1.PRICE_DATE < cr.PRICE_DATE
ORDER BY c1.PRICE_DATE DESC
FETCH FIRST 1 ROWS ONLY
) AS DATE_WITH_PREV_RATE
FROM COMP_RESULTS cr;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52517568
复制相关文章
相似问题
腾讯云开发者
