PostgreSQL如何查找相同ID对应valid_from与valid_to的日期不连续记录

实现思路 #

使用PostgreSQL内置窗口函数LEAD()按id分组后拉取同id下相邻行的生效时间，直接比对当前行失效时间与下一行生效时间是否相等即可定位不连续的异常记录。

前置说明 #

默认你的表中valid_from、valid_to字段为timestamp/timestamptz时间类型，若字段为字符串存储，需要先通过to_timestamp(valid_from, 'dd.mm.yyyy hh24:mi')转换为时间类型再执行查询。

可直接运行的SQL #

WITH adjacent_record AS (
    SELECT
        id,
        valid_from,
        valid_to,
        -- 按id分组、生效时间升序排序，拉取下一条记录的生效时间
        LEAD(valid_from) OVER (PARTITION BY id ORDER BY valid_from ASC) AS next_valid_from
    -- 替换为你实际的业务表名
    FROM your_business_table
)
SELECT
    id AS 记录id,
    valid_from AS 当前记录生效时间,
    valid_to AS 当前记录失效时间,
    next_valid_from AS 下一条记录生效时间,
    (next_valid_from - valid_to) AS 间隔时长
FROM adjacent_record
WHERE
    -- 过滤掉每组最后一条无后续记录的行
    next_valid_from IS NOT NULL
    -- 筛选失效时间与下一条生效时间不相等的异常行
    AND valid_to != next_valid_from;

运行效果说明 #

你提供的示例数据执行该SQL后，只会返回第一条Name_of_record1相关的记录，显示间隔为38天左右，和你预期的异常点匹配。

如果业务允许毫秒级的时间误差，可以把最后的判断条件调整为next_valid_from - valid_to > INTERVAL '1 second'，可根据你的业务容忍度自定义间隔阈值。

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