本文介绍如何利用 mysql 8.0+ 的 lag() 窗口函数，在插入新记录时自动填充“前一条同客户同 sku 记录的日期与数值”，避免更新已有行，确保历史数据完整性。
本文介绍如何利用 mysql 8.0+ 的 lag() 窗口函数，在插入新记录时自动填充“前一条同客户同 sku 记录的日期与数值”，避免更新已有行，确保历史数据完整性。
在构建时序型业务表（如客户 SKU 指标快照）时，常需保留每条原始采集记录，并同时附带「上一次同维度（CustomerID + SKUCode）的有效值」用于趋势对比或差值计算。题中目标正是：*插入新行时，自动从历史数据中查出同一 CustomerID 和 SKUCode 下、时间紧邻的前一条记录的 Date 和 NewValueCaptured，填入当前行的 PreviousDate 和 PreviousValueCaptured 字段；若无前序记录，则置为 NULL（而非当前值）——注意：示例表中首条记录的 Previous 列实际为 NULL，但答案中 COALESCE(..., Date) 会回退为当前值，需按需调整。**
关键在于不依赖 UPDATE 或 ON DUPLICATE KEY UPDATE（后者会修改旧数据，违背“保留每条记录”的前提），而是在 INSERT ... SELECT 阶段通过窗口函数一次性完成逻辑计算。
✅ 正确实现方式（MySQL 8.0+）：
INSERT INTO CS_data (
Date,
CustomerID,
SKUCode,
NewValueCaptured,
PreviousDate,
PreviousValueCaptured
)
SELECT
Date,
CustomerID,
SKUCode,
ValueCaptured AS NewValueCaptured,
-- 获取前一行的 Date：按 CustomerID+SKUCode 分组，按 Date 升序排列
LAG(Date) OVER (PARTITION BY CustomerID, SKUCode ORDER BY Date) AS PreviousDate,
-- 获取前一行的 ValueCaptured
LAG(ValueCaptured) OVER (PARTITION BY CustomerID, SKUCode ORDER BY Date) AS PreviousValueCaptured
FROM source_table;
? 说明与注意事项：
-
LAG()是标准窗口函数，返回当前行之前第 N 行（默认 N=1）的指定列值；若当前行为分组内第一行，则返回NULL—— 这恰好符合题中需求（首条记录 Previous* 为 NULL）。 -
PARTITION BY CustomerID, SKUCode确保比较范围严格限定在相同客户与 SKU 组内，避免跨客户/跨商品误关联。 -
ORDER BY Date必须明确且升序，以保证“前一条”语义的时间正确性；若存在同一天多条记录，建议补充次级排序字段（如自增 ID）消除不确定性。 -
源表
source_table必须已包含Date,CustomerID,SKUCode,ValueCaptured字段；若字段名不同，请在 SELECT 中做别名映射。 - ⚠️ 原答案中使用
COALESCE(LAG(...), Date)会导致首条记录的PreviousDate被设为自身Date（非 NULL），与示例表不符。应直接使用LAG(...)不加 COALESCE，以保持 NULL 语义。 - 此方案为纯插入（INSERT-SELECT），零副作用，完全满足“保留所有记录”和“仅填充新行 Prev 字段”的双重要求。
? 扩展建议：
如需兼容 MySQL 5.7 或更低版本（不支持窗口函数），可改用相关子查询（性能较差，大数据量慎用）：
SELECT
t1.Date,
t1.CustomerID,
t1.SKUCode,
t1.ValueCaptured,
(SELECT t2.Date
FROM source_table t2
WHERE t2.CustomerID = t1.CustomerID
AND t2.SKUCode = t1.SKUCode
AND t2.Date <p>综上，<code>LAG()</code> 是解决此类“行间时序引用”问题最简洁、高效且语义清晰的标准方案，推荐在 MySQL 8.0+ 环境中优先采用。</p>
