SQL Server如何获取商品前一日价格？现有查询未达预期求解决

问题分析 #

你当前使用LAG()函数的查询，只是按行顺序取同商品的上一条记录价格，但不会判断上一条记录的日期是否为当前日期的前一天。比如lux的2022-02-25记录，原查询会返回2022-02-23的价格，但实际上2022-02-24没有该商品的记录，此时应该显示NULL，这就是原查询不符合预期的原因。

解决方案 #

以下几种方法都可以实现需求：

方法1：LEFT JOIN关联前一日记录 #

通过自连接，明确匹配商品名相同且日期为前一天的记录：

SELECT 
    pd.productname,
    pd.productdate,
    pd.price,
    prev.price AS previousdayprice
FROM [dbo].[productdetails] pd
LEFT JOIN [dbo].[productdetails] prev
    ON pd.productname = prev.productname
    AND DATEADD(day, -1, pd.productdate) = prev.productdate
ORDER BY pd.productname, pd.productdate;

方法2：LAG()结合CASE判断日期连续性 #

保留LAG的逻辑，但增加日期判断，只有当上一条记录的日期是当前日期的前一天时，才返回价格，否则返回NULL：

SELECT 
    *,
    CASE 
        WHEN DATEADD(day, -1, productdate) = LAG(productdate) OVER (PARTITION BY productname ORDER BY productdate)
        THEN LAG(price) OVER (PARTITION BY productname ORDER BY productdate)
        ELSE NULL
    END AS previousdayprice
FROM [dbo].[productdetails]
ORDER BY productname, productdate;

方法3：OUTER APPLY查询前一日数据 #

使用OUTER APPLY为每一行单独查询对应商品前一天的价格，无记录时返回NULL：

SELECT 
    pd.*,
    prev.price AS previousdayprice
FROM [dbo].[productdetails] pd
OUTER APPLY (
    SELECT price 
    FROM [dbo].[productdetails]
    WHERE productname = pd.productname
      AND productdate = DATEADD(day, -1, pd.productdate)
) prev
ORDER BY pd.productname, pd.productdate;

以上三种方法执行后，都会得到符合你预期的结果：当商品对应前一日无记录时，previousdayprice字段显示NULL。

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