如何通过SQL获取商品历史价格与价格变动？rank() over实现方法咨询

方法一：使用RANK() OVER窗口函数实现 #

先给每个商品的所有价格记录按日期倒序排名（最新价格排第1，前一次价格排第2），再通过自关联匹配对应数据：

-- 给每个商品的价格记录按日期降序排名
WITH ranked_prices AS (
    SELECT 
        item,
        Cost,
        CostDate,
        RANK() OVER (PARTITION BY item ORDER BY CostDate DESC) AS price_rank
    FROM price_change.`question 3 data`
)
-- 关联获取当前价格与前一次价格，并计算变动
SELECT 
    curr.item AS Item,
    curr.Cost AS CurrentCost,
    curr.CostDate AS CurrentCostDate,
    prev.Cost AS PreviousCost,
    prev.CostDate AS PreviousCostDate,
    curr.Cost - prev.Cost AS PriceChangeAmount,
    ROUND(((curr.Cost - prev.Cost)/prev.Cost)*100, 2) AS PriceChangePercent
FROM ranked_prices curr
LEFT JOIN ranked_prices prev 
    ON curr.item = prev.item 
    AND curr.price_rank = 1 
    AND prev.price_rank = 2
WHERE curr.price_rank = 1; -- 仅保留每个商品的最新价格记录

方法二：使用LAG()窗口函数更简洁实现 #

LAG()函数可直接获取同商品分组内上一条记录的数据，无需额外排名关联，代码更精简：

SELECT 
    item AS Item,
    Cost AS CurrentCost,
    CostDate AS CurrentCostDate,
    -- 直接获取前一次价格与日期
    LAG(Cost) OVER (PARTITION BY item ORDER BY CostDate) AS PreviousCost,
    LAG(CostDate) OVER (PARTITION BY item ORDER BY CostDate) AS PreviousCostDate,
    -- 计算价格变动额与变动百分比
    Cost - LAG(Cost) OVER (PARTITION BY item ORDER BY CostDate) AS PriceChangeAmount,
    ROUND(((Cost - LAG(Cost) OVER (PARTITION BY item ORDER BY CostDate))/LAG(Cost) OVER (PARTITION BY item ORDER BY CostDate))*100, 2) AS PriceChangePercent
FROM price_change.`question 3 data`
-- 过滤出每个商品的最新价格记录（部分SQL引擎支持QUALIFY，不支持可改用子查询+ROW_NUMBER()）
QUALIFY ROW_NUMBER() OVER (PARTITION BY item ORDER BY CostDate DESC) = 1;

补充说明 #

• 若商品仅存在一条价格记录，历史价格及变动字段会返回NULL，无需额外处理
• 价格变动百分比的小数位数、计算逻辑可根据需求调整
• 若使用的SQL引擎不支持QUALIFY，可将ROW_NUMBER()的逻辑放入子查询中过滤最新记录

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