将跨天时间区间数据拆分插入tbl_bifurcation表的SQL查询需求

需求说明 #

• 源表：consumer_occurrence_restoration_times，包含consumer_id、start_time、end_time字段
• 目标表：tbl_bifurcation，需将源表数据插入，同时完成以下处理：
  • 把跨天的时间区间拆分为按天独立的行
  • 按规则设置isdaydiff字段：
    • 未跨天的时间区间，isdaydiff为0
    • 跨天拆分后，若拆分出的区间end_time为次日00:00:00，isdaydiff为0；其余拆分场景（含跨天主场景）isdaydiff为1

解决方案SQL #

WITH date_range AS (
    SELECT 
        consumer_id,
        start_time,
        end_time,
        -- 生成当前时间区间覆盖的所有日期起点
        generate_series(
            date_trunc('day', start_time),
            date_trunc('day', end_time),
            interval '1 day'
        ) AS day_start
    FROM consumer_occurrence_restoration_times
)
INSERT INTO tbl_bifurcation (consumer_id, start_time, end_time, isdaydiff)
SELECT 
    consumer_id,
    -- 确定拆分后每行的开始时间
    CASE 
        WHEN day_start = date_trunc('day', start_time) THEN start_time
        ELSE day_start
    END AS split_start,
    -- 确定拆分后每行的结束时间
    CASE 
        WHEN day_start = date_trunc('day', end_time) THEN end_time
        ELSE day_start + interval '1 day'
    END AS split_end,
    -- 计算isdaydiff字段值
    CASE
        -- 未跨天的场景
        WHEN date_trunc('day', start_time) = date_trunc('day', end_time) THEN 0
        -- 跨天拆分后结束时间为次日0点的场景
        WHEN split_end = day_start + interval '1 day' THEN 0
        -- 其余跨天相关场景
        ELSE 1
    END AS isdaydiff
FROM date_range;

逻辑拆解 #

1. 生成日期范围：通过generate_series生成每条记录时间区间覆盖的所有日期，为跨天拆分提供基础
2. 拆分时间区间：
   • 区间第一天的开始时间沿用原start_time，后续天数的开始时间设为当天0点
   • 区间最后一天的结束时间沿用原end_time，前面天数的结束时间设为次日0点
3. isdaydiff规则落地：
   • 优先判断未跨天场景，直接赋值0
   • 针对跨天拆分出的"当日0点到次日0点"区间，赋值0
   • 其他所有跨天相关场景统一赋值1

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