使用Lag函数计算回溯日期对应数值的技术问题咨询

适用场景 #

针对时序数据按维度分组后，取指定天数前的对应指标值的需求，例如同品前N天销售额、用户前N次消费金额等场景均可使用。

通用实现代码（SQL环境，兼容MySQL 8.0+、PostgreSQL、Hive、Spark SQL等支持窗口函数的数据库） #

SELECT
    日期字段,
    分组维度字段,
    当日指标值,
    -- LAG参数依次为：目标取值字段、回溯天数、无匹配值时的默认返回值
    LAG(当日指标值, 回溯天数N, 0) OVER (
        PARTITION BY 分组维度字段 -- 按业务维度分组，不同分组独立计算偏移
        ORDER BY 日期字段 ASC -- 必须按时间升序排序，确保取到的是历史值
    ) AS 回溯N日指标值
FROM 业务数据表
ORDER BY 分组维度字段, 日期字段;

如果需要动态适配不同记录的不同回溯天数，可先关联维度表获取每条记录对应的偏移天数，将LAG的第二个参数替换为对应偏移天数字段即可。

核心注意事项 #

• OVER子句必须配置ORDER BY 日期字段 ASC规则，无排序的LAG返回值无业务逻辑意义，会出现随机偏移
• 若业务日期存在缺失（例如节假日无交易记录），LAG默认按行偏移而非自然日期偏移，会出现偏移错位问题。这种场景需要先生成连续的完整日期序列，填充缺失日期的指标值为0或空值后，再调用LAG函数
• PARTITION BY的分组维度要严格匹配业务需求，例如需要分产品取历史值就按产品ID分组，避免漏加分组导致跨主体取数
• 若回溯天数大于对应分组内的历史数据条数，会返回你设置的默认值，未设置默认值时返回NULL，后续业务计算需要提前做空值兼容处理
• 部分低版本数据库不支持LAG的第二个参数传入动态字段，仅支持固定数值，使用前需要先确认对应数据库的版本支持能力

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