如何用SQL按时间顺序合并满足连续日期规则的记录？

你的问题出在分组逻辑上：之前的方法仅判断单条记录与前后记录的日期差是否在-1到1之间，没有区分独立的连续日期链，导致所有时序记录被归为同一组。正确的思路是通过识别连续的日期关联关系，为每个独立的连续链生成唯一分组ID，再按分组聚合。

实现步骤 #

• 1. 清理日期字段：处理带->的特殊格式、非日期值，转为标准日期或NULL
• 2. 标记连续分组：用窗口函数判断当前记录是否与前一条记录构成连续（当前start = 前一条stop + 1天），生成唯一分组ID
• 3. 聚合分组结果：按分组ID合并，取每组最早的开始日期和最晚的结束日期，同时保留无效日期的记录

具体SQL实现（MySQL版本） #

WITH cleaned_dates AS (
    SELECT 
        id,
        -- 清理started字段，提取有效日期
        CASE 
            WHEN started LIKE '%->%' THEN TRIM(REPLACE(started, '->', ''))
            WHEN started NOT REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' THEN NULL
            ELSE started
        END AS clean_started,
        -- 清理stop字段，提取有效日期
        CASE 
            WHEN stop LIKE '%->%' THEN TRIM(REPLACE(stop, '->', ''))
            WHEN stop NOT REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' THEN NULL
            ELSE stop
        END AS clean_stop
    FROM date_table
),
grouped_records AS (
    SELECT 
        *,
        -- 生成分组ID：当前记录与前一条连续则分组ID不变，否则递增
        SUM(
            CASE 
                WHEN LAG(clean_stop) OVER (ORDER BY clean_started) IS NULL THEN 1
                WHEN DATEDIFF(clean_started, LAG(clean_stop) OVER (ORDER BY clean_started)) = 1 THEN 0
                ELSE 1
            END
        ) OVER (ORDER BY clean_started) AS group_id
    FROM cleaned_dates
    WHERE clean_started IS NOT NULL AND clean_stop IS NOT NULL
)
-- 合并连续分组，同时保留无效日期记录
SELECT 
    ROW_NUMBER() OVER (ORDER BY MIN(clean_started)) AS new_id,
    MIN(clean_started) AS started,
    MAX(clean_stop) AS stop
FROM grouped_records
GROUP BY group_id
UNION ALL
SELECT 
    ROW_NUMBER() OVER (ORDER BY id) + (SELECT COUNT(DISTINCT group_id) FROM grouped_records) AS new_id,
    started,
    stop
FROM date_table
WHERE started = 'Some-Date' OR stop IS NULL
ORDER BY new_id;

逻辑说明 #

• cleaned_dates：统一日期格式，过滤掉无法识别的日期值，确保后续日期计算准确。
• grouped_records：使用LAG()获取前一条记录的结束日期，通过SUM()累积生成分组ID——只有当当前记录与前一条不连续时，分组ID才会递增，这样同一连续日期链的记录会被分配同一个ID。
• 最后聚合分组时，取每组的最早开始和最晚结束日期；用UNION ALL保留那些不符合日期格式的特殊记录，保证结果完整性。

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