如何编写SQL查询上周调价且新旧价差超15%的商品数据

现有History价格调整历史表，表结构及测试数据如下：

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：上一次调价日期

预期返回结果示例：

productnr     newprice    oldprice       last_changedate    secondlast_changedate 
1002          14            7                07.10.2021      14.05.2021

已实现查询所有上周调价商品的逻辑：

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

使用窗口函数LAG获取同商品上一次的调价记录，无需自关联即可完成需求，SQL如下：

WITH price_history_rank AS (
    SELECT 
        productnr,
        price AS newprice,
        LAG(price, 1) OVER(PARTITION BY productnr ORDER BY changedate) AS oldprice,
        changedate AS last_changedate,
        LAG(changedate, 1) OVER(PARTITION BY productnr ORDER BY changedate) AS secondlast_changedate,
        ROW_NUMBER() OVER(PARTITION BY productnr ORDER BY changedate DESC) AS rn
    FROM history
)
SELECT 
    productnr,
    newprice,
    oldprice,
    last_changedate,
    secondlast_changedate
FROM price_history_rank
WHERE 
    rn = 1 -- 仅保留每个商品最新的调价记录
    -- 沿用原有上周调价过滤逻辑
    AND TO_CHAR(last_changedate, 'iw') = TO_CHAR(next_day(trunc(sysdate+2), 'MONDAY') - 14, 'iw')
    AND last_changedate > sysdate - 14
    -- 价格差比例超过15%，若仅需统计涨价超过15%去掉ABS()即可
    AND ABS((newprice - oldprice) * 1.0 / oldprice) > 0.15;

逻辑说明 #

• 先用CTE对每个商品的调价记录按日期排序，通过LAG函数直接拿到上一次调价的价格和日期，比自关联写法性能更高、逻辑更简洁
• 过滤时先取每个商品最新的调价记录，再匹配上周调价的条件，最后过滤价格差比例即可

内容的提问来源于stack exchange，提问作者TimLer