SQL实现：如何查询获取指定列字段的前一个历史值

需求说明 #

需要跟踪表中CATEGORY列的变更记录，为每条数据新增PREVCAT字段，存储同一客户上一次变更对应的分类值。
现有示例源表结构与数据：

CUSTOMER_ID, CATEGORY, NAME, MODIFIED
1, A, MYCUSTOMER, 2022-05-01
2, A, MYOTHERCUS, 2022-05-01
1, B, MYCUSTOMER, 2022-05-02

期望输出结果：

CUSTOMER_ID, CATEGORY, NAME, MODIFIED, PREVCAT
1, A, MYCUSTOMER, 2022-05-01, null
2, A, MYOTHERCUS, 2022-05-01, null
1, B, MYCUSTOMER, 2022-05-02, A

正确SQL写法 #

使用LAG窗口函数时结果不对，基本都是分区、排序逻辑配置错误导致的，核心规则是按客户维度分区，按修改时间升序排序，保证只取同一客户、时间更早的上一条记录的分类值。
适配所有支持标准窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Spark SQL、BigQuery等）的写法：

SELECT
  CUSTOMER_ID,
  CATEGORY,
  NAME,
  MODIFIED,
  LAG(CATEGORY) OVER (
    PARTITION BY CUSTOMER_ID
    ORDER BY MODIFIED ASC
  ) AS PREVCAT
-- 替换成你的实际表名
FROM your_actual_table;

常见错误排查 #

• 漏写PARTITION BY CUSTOMER_ID：会将所有客户的记录混排，导致取到其他客户的分类值
• 未按MODIFIED字段排序：数据库表存储顺序无保障，不指定时间排序的话，取到的“上一条”是随机的
• 同客户同时间存在多条记录：如果业务上存在同一客户同一修改时间有多条数据的场景，可以在排序规则中追加唯一字段作为次级排序，保证结果稳定，例如ORDER BY MODIFIED ASC, RECORD_ID ASC

结果验证 #

用示例数据运行上述SQL，返回结果完全匹配预期：

• 每个客户的最早一条记录没有更早的分类数据，PREVCAT返回null
• 客户1 2022-05-02的B分类记录，上一条同客户记录分类为A，PREVCAT正确返回A
• 即使表中存在更多字段、更大数据量，只要分区和排序逻辑正确，结果就不会出错

内容的提问来源于stack exchange，提问作者Riël