SQL条件行时间差计算：为幼犬如厕日志添加上次排便/排尿间隔列

嘿，这个需求我太熟了！LAG函数确实搞不定这种「找最近的符合特定条件历史记录」的场景，不过有几种靠谱的方法能实现，我给你整理一下：

方案一：窗口函数追踪最近符合条件的时间（推荐，性能优） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server等），这个方法是最高效的。我们用MAX() OVER()窗口函数来实时追踪最近一次排便/排尿的时间，再计算时间差：

SELECT 
    d.`datetime`, 
    d.`poo`, 
    d.`pee`,
    -- 计算距上次排便的时间差（单位：分钟，可换成HOUR/SECOND等）
    TIMESTAMPDIFF(MINUTE, 
        MAX(CASE WHEN d.`poo` = 1 THEN d.`datetime` END) OVER (ORDER BY d.`datetime` ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
        d.`datetime`
    ) AS `poo_diff`,
    -- 计算距上次排尿的时间差
    TIMESTAMPDIFF(MINUTE, 
        MAX(CASE WHEN d.`pee` = 1 THEN d.`datetime` END) OVER (ORDER BY d.`datetime` ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
        d.`datetime`
    ) AS `pee_diff`
FROM `diary` d 
WHERE 
    d.`user_id`=3 
    AND (d.`poo`=1 OR d.`pee`=1) 
    AND d.`datetime` >= DATE_ADD(CURDATE(), INTERVAL -7 DAY) 
    AND d.`datetime` <= CURDATE()
ORDER BY d.`datetime`;

逻辑说明： #

• MAX(CASE WHEN d.poo=1 THEN d.datetime END)会在遍历每一行时，只保留poo=1的时间戳，其他行返回NULL
• 窗口范围ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING限定为当前行之前的所有行，MAX()会自动筛选出最近的有效时间戳
• 第一次出现的排便/排尿记录，对应的_diff列会返回NULL，这符合“没有历史记录”的逻辑

方案二：自关联+子查询（兼容旧版本数据库） #

如果你的数据库不支持窗口函数，比如MySQL 5.x，用自关联子查询也能实现，逻辑更直观：

SELECT 
    d.`datetime`, 
    d.`poo`, 
    d.`pee`,
    TIMESTAMPDIFF(MINUTE, 
        -- 找到当前行之前最近的排便记录时间
        (SELECT MAX(d2.`datetime`) FROM `diary` d2 
         WHERE d2.`user_id`=d.`user_id` 
         AND d2.`datetime` < d.`datetime` 
         AND d2.`poo`=1),
        d.`datetime`
    ) AS `poo_diff`,
    TIMESTAMPDIFF(MINUTE, 
        -- 找到当前行之前最近的排尿记录时间
        (SELECT MAX(d2.`datetime`) FROM `diary` d2 
         WHERE d2.`user_id`=d.`user_id` 
         AND d2.`datetime` < d.`datetime` 
         AND d2.`pee`=1),
        d.`datetime`
    ) AS `pee_diff`
FROM `diary` d 
WHERE 
    d.`user_id`=3 
    AND (d.`poo`=1 OR d.`pee`=1) 
    AND d.`datetime` >= DATE_ADD(CURDATE(), INTERVAL -7 DAY) 
    AND d.`datetime` <= CURDATE()
ORDER BY d.`datetime`;

逻辑说明： #

• 每个行都通过子查询，在同用户、更早时间范围内，找到最大的datetime（也就是最近的符合条件的记录）
• 这个方法兼容性好，但数据量大时性能会下降，因为每行都要执行一次子查询

方案三：LAG函数+IGNORE NULLS（简洁版，需数据库支持） #

如果你的数据库支持LAG() IGNORE NULLS（比如MySQL 8.0.22+、PostgreSQL），可以用更简洁的写法：

SELECT 
    d.`datetime`, 
    d.`poo`, 
    d.`pee`,
    TIMESTAMPDIFF(MINUTE,
        LAG(CASE WHEN d.`poo`=1 THEN d.`datetime` END IGNORE NULLS) OVER (ORDER BY d.`datetime`),
        d.`datetime`
    ) AS `poo_diff`,
    TIMESTAMPDIFF(MINUTE,
        LAG(CASE WHEN d.`pee`=1 THEN d.`datetime` END IGNORE NULLS) OVER (ORDER BY d.`datetime`),
        d.`datetime`
    ) AS `pee_diff`
FROM `diary` d 
WHERE 
    d.`user_id`=3 
    AND (d.`poo`=1 OR d.`pee`=1) 
    AND d.`datetime` >= DATE_ADD(CURDATE(), INTERVAL -7 DAY) 
    AND d.`datetime` <= CURDATE()
ORDER BY d.`datetime`;

逻辑说明： #

• CASE语句把非目标行为NULL，LAG(...) IGNORE NULLS会自动跳过NULL，直接取上一个有效时间戳
• 写法最简洁，但要确认你的数据库版本支持这个特性

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