获取上周有价格更新的产品

Question

这是我的表历史:

productnr    price     changedate
1001           5         06.05.2020
1001           9         01.10.2021
1001           10        08.10.2021
1002           6         01.04.2021
1002           7         14.05.2021
1002           14        07.10.2021

我需要所有上周价格变动且新旧价格差异大于15%的产品。

期望结果:

productnr     newprice    oldprice       last_changedate    secondlast_changedate 
1002          14            7                07.10.2021      14.05.2021

使用这个SQL查询，我有上周价格变化的所有产品:

Select *
from history
where TO_CHAR(changedate, 'iw') = TO_CHAR(next_day(trunc(sysdate+2), 'MONDAY') - 14, 'iw')
and changedate > sysdate - 14

但是我不知道怎样才能达到预期的结果。

Answer 1

您可以使用解析函数查找先前的值:

如果你想比较最新的productnr和以前的:

SELECT *
FROM   (
SELECT h.*,
LEAD(price     ) OVER (PARTITION BY productnr ORDER BY changedate DESC) AS oldprice,
LEAD(changedate) OVER (PARTITION BY productnr ORDER BY changedate DESC) AS oldchangedate,
ROW_NUMBER() OVER (PARTITION BY productnr ORDER BY changedate DESC) AS rn
FROM   history h
WHERE  changedate > TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY
)
WHERE  (price < oldprice * (1 - 0.15) OR price > oldprice * (1 + 0.15))
AND    rn = 1;

或者，如果您想比较本周每productnr的第一个price与上周最后一个price:

SELECT *
FROM   (
SELECT h.*,
LEAD(price     ) OVER (PARTITION BY productnr ORDER BY changedate DESC) AS oldprice,
LEAD(changedate) OVER (PARTITION BY productnr ORDER BY changedate DESC) AS oldchangedate
FROM   history h
WHERE  changedate >= TRUNC(SYSDATE, 'IW') - INTERVAL '7' DAY
AND    changedate <  TRUNC(SYSDATE, 'IW') + INTERVAL '7' DAY
)
WHERE  (price < oldprice * (1 - 0.15) OR price > oldprice * (1 + 0.15))
AND    changedate    >= TRUNC(SYSDATE, 'IW')
AND    oldchangedate <  TRUNC(SYSDATE, 'IW');