重叠日期排除：查询人员记录中超过24小时的时间间隔问题

嘿，我懂你这个问题的棘手之处——本来算前后记录的时间差好像不难，但加上时间段重叠的情况，直接按原始顺序对比肯定会出问题，对吧？我来给你捋捋怎么解决：

核心思路是先把同一个Person_id下所有重叠或连续的时间段合并成一个完整的时间段，然后再计算合并后相邻时间段之间的间隔，这样就能准确找到间隔超过24小时的情况了。

具体步骤与代码示例 #

这里以PostgreSQL为例，其他数据库（比如SQL Server、MySQL）只需要做少量语法调整：

-- 第一步：给每个Person_id的记录标记分组，区分重叠/连续的时间段
WITH grouped_records AS (
    SELECT 
        Person_id,
        Store_id,
        Startdate,
        Enddate,
        -- 生成分组ID：当前记录的Startdate <= 上一条的Enddate时，和上一条同组；否则新建分组
        SUM(CASE WHEN Startdate <= LAG(Enddate) OVER (PARTITION BY Person_id ORDER BY Startdate) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY Person_id ORDER BY Startdate) AS group_id
    FROM your_table_name -- 替换成你的表名
),
-- 第二步：合并每个分组的时间段，得到完整的连续时间段
merged_periods AS (
    SELECT 
        Person_id,
        MIN(Startdate) AS merged_start, -- 分组内最早的开始时间
        MAX(Enddate) AS merged_end      -- 分组内最晚的结束时间
    FROM grouped_records
    GROUP BY Person_id, group_id
)
-- 第三步：计算合并后相邻时间段的间隔，筛选超过24小时的记录
SELECT 
    mp.Person_id,
    mp.merged_end AS previous_period_end,
    next_mp.merged_start AS next_period_start,
    (next_mp.merged_start - mp.merged_end) AS interval_between_periods
FROM merged_periods mp
JOIN merged_periods next_mp 
    ON mp.Person_id = next_mp.Person_id 
    AND next_mp.group_id = mp.group_id + 1
WHERE (next_mp.merged_start - mp.merged_end) > INTERVAL '24 hours';

针对不同数据库的调整 #

• SQL Server：把最后的WHERE条件改成用DATEDIFF函数判断：

  WHERE DATEDIFF(HOUR, mp.merged_end, next_mp.merged_start) > 24;
  

• MySQL：时间差计算可以用TIMESTAMPDIFF(HOUR, mp.merged_end, next_mp.merged_start) > 24，同时窗口函数的语法和PostgreSQL基本一致。

逻辑解释 #

1. 分组标记：用LAG窗口函数拿到当前记录的上一条记录的结束时间，判断当前记录是否和上一条重叠/连续，用SUM生成分组ID，把所有重叠的记录归为同一组。
2. 合并时间段：对每个分组取最早的开始时间和最晚的结束时间，得到一个没有重叠的完整时间段。
3. 计算间隔：把合并后的时间段和下一个时间段关联，计算两者的时间差，筛选出超过24小时的结果。

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