SQLite 同Name分组下按日期序更新Previous Date列的实现方法

核心实现逻辑 #

• 需求本质是逐行计算：匹配相同Name的分组内，取时间早于当前行的最近一条Date作为Previous Date，无更早记录时该字段置为NULL
• 可根据实际使用的SQLite版本，选择窗口函数实现（推荐，性能更好）或兼容子查询实现

推荐实现（SQLite 3.25.0及以上版本） #

从3.25.0版本开始SQLite支持标准窗口函数，可直接用内置的LAG()函数快速取分组内上一行的值，避免复杂的嵌套关联计算：

WITH ordered_calc AS (
    SELECT
        rowid,
        -- 按Name分组，按Date升序排序，取紧邻上一行的Date值
        LAG("Date", 1, NULL) OVER (
            PARTITION BY Names
            ORDER BY "Date" ASC
        ) AS target_prev_date
    FROM 你的表名
)
UPDATE 你的表名
SET "Previous Date" = ordered_calc.target_prev_date
FROM ordered_calc
WHERE 你的表名.rowid = ordered_calc.rowid;

实现逻辑完全匹配需求规则：

• PARTITION BY Names：按姓名拆分独立计算分组，和需求的同Name匹配逻辑一致
• ORDER BY "Date" ASC：同组内按时间正序排列，保证取到的是时间上紧邻的前序记录
• LAG()的第三个参数为NULL，正好对应每个Name分组最早一条记录无更早日期时，Previous Date置空的规则

低版本兼容实现（SQLite 3.25.0之前版本） #

如果使用的是不支持窗口函数的老版本SQLite，可通过关联子查询找到同Name下小于当前Date的最大日期，作为前序日期更新：

UPDATE 你的表名
SET "Previous Date" = (
    SELECT MAX(t2."Date")
    FROM 你的表名 t2
    WHERE
        t2.Names = 你的表名.Names
        AND t2."Date" < 你的表名."Date"
)

效果验证 #

针对需求中提到的两类典型场景，两种实现的计算结果完全匹配规则：

场景1：同Name乱序存储的日期序列（原始数据：3月5日、3月29日、3月17日、3月10日），排序后为3月5日 → 3月10日 → 3月17日 → 3月29日，对应Previous Date依次为NULL、3月5日、3月10日、3月17日
场景2：多Name混合存储的序列：第一条John（2020/1/1）→ 第二条John（其他日期）→ 第三条非John的独立记录，对应Previous Date依次为NULL、2020/1/1、NULL

注意事项 #

• 如果表中存在同Name、同Date的重复行，两种写法都会给同日期的记录计算相同的前序日期，如需精确拆分排序顺序，可在窗口函数的ORDER BY规则中追加表主键、rowid等唯一字段打破平局
• 如果是无rowid的特殊表（WITHOUT ROWID类型），窗口函数关联时需要替换为表的真实主键字段，不能直接使用rowid匹配

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