本文介绍使用 mysql 8.0+ 窗口函数 lag() 实现新记录自动关联“上一条有效记录”的日期和数值，无需更新旧数据，适用于审计型、时序追踪类表结构。
本文介绍使用 mysql 8.0+ 窗口函数 lag() 实现新记录自动关联“上一条有效记录”的日期和数值，无需更新旧数据，适用于审计型、时序追踪类表结构。
在构建具有时间序列特性的业务表（如客户 SKU 的周期性采集值）时，常需在插入新记录的同时，自动填充该客户 + SKU 组合下「上一次采集」的日期（PreviousDate）和数值（PreviousValueCaptured）。关键约束在于：必须保留所有原始记录（不可用 ON DUPLICATE KEY UPDATE 覆盖），且关联逻辑需按 CustomerID 和 SKUCode 分组、按 Date 升序严格排序。
MySQL 8.0 引入的窗口函数为此类需求提供了简洁、高效、原子化的解决方案。核心是 LAG() 函数——它可访问当前行之前指定偏移量的行值，配合 PARTITION BY 和 ORDER BY 实现分组内有序回溯。
✅ 正确实现方式如下（假设源数据来自 source_table，目标表为 CS_data）：
INSERT INTO CS_data (
Date,
CustomerID,
SKUCode,
NewValueCaptured,
PreviousDate,
PreviousValueCaptured
)
SELECT
Date,
CustomerID,
SKUCode,
ValueCaptured AS NewValueCaptured,
COALESCE(
LAG(Date) OVER (PARTITION BY CustomerID, SKUCode ORDER BY Date),
NULL -- 首条记录不强制填自身日期，保持 NULL 更符合语义（如示例中首行 PreviousDate = NULL）
) AS PreviousDate,
COALESCE(
LAG(ValueCaptured) OVER (PARTITION BY CustomerID, SKUCode ORDER BY Date),
NULL
) AS PreviousValueCaptured
FROM source_table;
? 关键说明：
-
PARTITION BY CustomerID, SKUCode：确保每个客户 × SKU 组合独立计算“上一条记录”，避免跨客户/跨 SKU 错误关联； -
ORDER BY Date：严格按时间升序排列，保证LAG()取到的是逻辑上“最近的前一次”； -
COALESCE(..., NULL)：显式将首条记录的LAG()结果（为NULL）保持为NULL，与示例数据完全一致（而非误用Date或ValueCaptured填充）； -
无需主键冲突处理：本方案为纯
INSERT SELECT，每行独立插入，天然满足“保留所有记录”的要求； - 性能友好：窗口函数在查询执行阶段一次性完成计算，无子查询嵌套或自连接开销。
⚠️ 注意事项：
- 仅适用于 MySQL 8.0.2 及以上版本（低版本需改用相关联子查询或变量模拟，复杂且不可靠）；
- 源表
Date字段必须为DATE或DATETIME类型，并确保数据无歧义（如避免同客户同 SKU 同日多条记录未定义顺序——此时应追加ORDER BY Date, id明确稳定性）； - 若源数据存在重复
(CustomerID, SKUCode, Date)，建议先去重或增加唯一标识列用于二级排序，防止LAG()行为不确定。
通过此方法，您可精准复现示例中的结果表结构：每条新记录都携带其所属客户 SKU 组内严格意义上的前序采集快照，为后续趋势分析、变化检测、增量校验等场景奠定可靠数据基础。
