SQL实现跨天时间区间拆分并插入至目标表（含isdaydiff配置）

需求概述 #

需要将consumer_occurrence_restoration_times表中的时间区间数据按以下规则处理后，插入到tbl_bifurcation表：

• 跨天的区间拆分为单日记录，确保每条记录的start_time和end_time处于同一天（不超过次日00:00:00）
• isdaydiff字段规则：
  • 拆分后的中间段（非原区间首尾段）设为1
  • 原区间未跨天，或拆分后的首尾段（含end_time为次日00:00:00的记录）设为0

原表结构与数据 #

源表结构 #

CREATE TABLE consumer_occurrence_restoration_times (
    consumer_id INT,
    start_time TIMESTAMP,
    end_time TIMESTAMP
);

源表数据 #

INSERT INTO consumer_occurrence_restoration_times (consumer_id, start_time, end_time)
VALUES
    (1, '2023-09-24 20:00:00', '2023-09-25 12:00:00'),
    (2, '2023-09-24 21:00:00', '2023-09-25 13:00:00'),
    (1, '2023-09-26 20:00:00', '2023-09-28 02:00:00'),
    (3, '2023-09-25 19:00:00', '2023-09-26 10:00:00'),
    (4, '2023-09-25 21:30:00', '2023-09-27 14:00:00'),
    (5, '2023-09-25 21:30:00', '2023-09-25 22:00:00');

目标表结构 #

CREATE TABLE tbl_bifurcation (
    consumer_id INT,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    isdaydiff INT
);

实现SQL语句 #

WITH date_series AS (
    SELECT 
        consumer_id,
        start_time,
        end_time,
        -- 生成区间覆盖的所有日期的00:00:00时间点
        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 day_start != date_trunc('day', start_time) AND day_start != date_trunc('day', end_time) THEN 1
        ELSE 0
    END AS isdaydiff
FROM date_series
-- 过滤无效的时间区间（起始时间>=结束时间）
WHERE CASE 
        WHEN day_start = date_trunc('day', start_time) THEN start_time
        ELSE day_start
    END < CASE 
        WHEN day_start = date_trunc('day', end_time) THEN end_time
        ELSE day_start + interval '1 day'
    END
ORDER BY consumer_id, split_start;

逻辑说明 #

1. 日期序列生成：通过generate_series生成原时间区间覆盖的所有日期的零点时间，为拆分跨天区间做基础。
2. 区间拆分规则：
   • 原区间的起始日期：拆分后起始时间用原start_time，结束时间用次日零点（若不是原区间的结束日期）
   • 原区间的结束日期：拆分后结束时间用原end_time，起始时间用当日零点（若不是原区间的起始日期）
   • 中间日期：拆分后起始时间为当日零点，结束时间为次日零点
3. isdaydiff判断：仅当拆分记录对应的日期既不是原区间的起始日也不是结束日时，标记为1，其余情况为0。
4. 无效记录过滤：避免生成start_time >= end_time的异常记录。

执行结果 #

插入tbl_bifurcation表后的最终数据如下：

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