這是我的表歷史：
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
但我不知道如何才能達到預期的結果。
uj5u.com熱心網友回復：
您可以使用決議函式來查找以前的值：
如果你想比較最新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;
或者，如果你想第一個比較price每productnr本周最后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');
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/314327.html