Azure SQL仅查询当日数据时如何正确应用LAG函数

SQL的执行顺序中，WHERE子句筛选早于窗口函数计算。直接在外层查询加当日日期过滤，窗口函数能处理的数据集仅包含当日的单条记录，没有历史数据行作为计算样本，LAG自然无法返回前N天的价格。

方案1：CTE预加载计算所需数据范围 #

先把窗口计算需要的最近30天（含当日）全量数据取出来，在这个子集上完成LAG偏移计算，最后再筛选当日的结果行即可。

WITH product_recent_30d AS (
    SELECT
        name,
        address,
        value,
        date
    FROM products
    -- 预加载前30天到当日的所有数据，给窗口函数留足计算样本
    WHERE date >= DATEADD(DAY, -30, CAST(GETDATE() AS DATE))
      AND date <= CAST(GETDATE() AS DATE)
)
SELECT
    value AS current_price,
    date,
    name,
    address,
    LAG(value, 1) OVER (PARTITION BY name, address ORDER BY date) AS day_1_price,
    LAG(value, 2) OVER (PARTITION BY name, address ORDER BY date) AS day_2_price,
    LAG(value, 3) OVER (PARTITION BY name, address ORDER BY date) AS day_3_price,
    -- 按相同规则补全LAG偏移量到30，即可得到前30天所有价格字段
    LAG(value, 30) OVER (PARTITION BY name, address ORDER BY date) AS day_30_price
FROM product_recent_30d
-- 计算完成后再筛选当日结果，不影响窗口函数取值
WHERE date = CAST(GETDATE() AS DATE);

方案2：条件聚合（推荐，适配日期不连续场景） #

如果表中存在日期断档（比如某一天没有录入对应商品的价格），LAG按行偏移的逻辑会取错非对应自然日的价格。这种场景用条件聚合按日期差值精准匹配更稳妥，最终天然只返回聚合后的当日维度结果，不需要二次筛选。

SELECT
    CAST(GETDATE() AS DATE) AS date,
    name,
    address,
    MAX(CASE WHEN date = CAST(GETDATE() AS DATE) THEN value END) AS current_price,
    MAX(CASE WHEN date = DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) THEN value END) AS day_1_price,
    MAX(CASE WHEN date = DATEADD(DAY, -2, CAST(GETDATE() AS DATE)) THEN value END) AS day_2_price,
    MAX(CASE WHEN date = DATEADD(DAY, -3, CAST(GETDATE() AS DATE)) THEN value END) AS day_3_price,
    -- 按相同规则补全到-30天即可
    MAX(CASE WHEN date = DATEADD(DAY, -30, CAST(GETDATE() AS DATE)) THEN value END) AS day_30_price
FROM products
WHERE date >= DATEADD(DAY, -30, CAST(GETDATE() AS DATE))
  AND date <= CAST(GETDATE() AS DATE)
GROUP BY name, address;

• 两种方案都只会返回当日的结果行，不会输出历史日期的冗余记录
• 条件聚合写法中，若某一天没有对应价格，字段会返回NULL，可以根据业务需要用ISNULL替换为默认值
• 注意date字段如果带时分秒，需要统一转为DATE类型做匹配，避免时间部分不一致导致匹配失败

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