如何在Snowflake SQL中拆分跨天的ACTUAL_START_DATE与END_DATE区间

需求明确 #

将跨天的时间区间拆分为每日分段：

• 若ACTUAL_END_DATE > ACTUAL_START_DATE且跨多天，拆分出每日的时段记录（如2024-04-11 00:00:00到2024-04-14 12:00:00拆分为4条记录）
• 单日区间保持原样（日期差最大不超过7天）

问题根源 #

原SQL仅能处理1天跨度的场景，核心原因是未生成覆盖整个区间的连续日期序列，无法迭代处理多天分段。

正确实现方案 #

方案一：递归CTE（推荐，适配任意天数） #

利用Snowflake的递归CTE生成每日日期节点，逐个计算分段的起止时间：

-- 1. 创建测试数据（可替换为你的业务表）
CREATE OR REPLACE TEMP TABLE test_intervals (
    ID INT,
    ACTUAL_START_DATE TIMESTAMP_NTZ,
    ACTUAL_END_DATE TIMESTAMP_NTZ
);

INSERT INTO test_intervals VALUES
(1, '2024-04-11 00:00:00.000', '2024-04-14 12:00:00.000'),
(2, '2024-05-01 14:30:00', '2024-05-02 09:15:00'),
(3, '2024-06-10 08:00:00', '2024-06-10 18:00:00');

-- 2. 递归拆分日期区间
WITH recursive date_segments AS (
    -- 初始行：处理每个区间的第一天
    SELECT
        ID,
        ACTUAL_START_DATE AS segment_start,
        LEAST(DATEADD(DAY, 1, DATE_TRUNC('DAY', ACTUAL_START_DATE)) - INTERVAL '1 MILLISECOND', ACTUAL_END_DATE) AS segment_end,
        DATE_TRUNC('DAY', ACTUAL_START_DATE) AS current_day,
        DATE_TRUNC('DAY', ACTUAL_END_DATE) AS end_day
    FROM test_intervals
    UNION ALL
    -- 递归生成后续每日分段
    SELECT
        ID,
        DATEADD(DAY, 1, current_day) AS segment_start,
        LEAST(DATEADD(DAY, 1, DATEADD(DAY, 1, current_day)) - INTERVAL '1 MILLISECOND', ACTUAL_END_DATE) AS segment_end,
        DATEADD(DAY, 1, current_day) AS current_day,
        end_day
    FROM date_segments
    WHERE current_day < end_day -- 终止条件：当前天等于结束天则停止
)
SELECT
    ID,
    segment_start,
    segment_end
FROM date_segments
ORDER BY ID, segment_start;

方案二：GENERATOR函数（适合固定最大天数场景） #

利用GENERATOR生成最多7行（匹配需求中最大7天跨度），关联区间后计算每日分段：

WITH date_range AS (
    SELECT
        ID,
        ACTUAL_START_DATE,
        ACTUAL_END_DATE,
        DATE_TRUNC('DAY', ACTUAL_START_DATE) AS start_day,
        DATE_TRUNC('DAY', ACTUAL_END_DATE) AS end_day,
        DATEDIFF(DAY, start_day, end_day) + 1 AS total_days
    FROM test_intervals
),
days_generated AS (
    SELECT
        dr.ID,
        dr.ACTUAL_START_DATE,
        dr.ACTUAL_END_DATE,
        DATEADD(DAY, seq4(), dr.start_day) AS current_day
    FROM date_range dr,
         TABLE(GENERATOR(ROWCOUNT => 7)) -- 生成7行覆盖最大跨度
    WHERE seq4() <= dr.total_days - 1
)
SELECT
    ID,
    -- 计算分段开始时间：第一天用原开始时间，后续用当天0点
    CASE
        WHEN current_day = DATE_TRUNC('DAY', ACTUAL_START_DATE) THEN ACTUAL_START_DATE
        ELSE current_day
    END AS segment_start,
    -- 计算分段结束时间：最后一天用原结束时间，后续用当天最后一刻
    CASE
        WHEN current_day = DATE_TRUNC('DAY', ACTUAL_END_DATE) THEN ACTUAL_END_DATE
        ELSE DATEADD(DAY, 1, current_day) - INTERVAL '1 MILLISECOND'
    END AS segment_end
FROM days_generated
ORDER BY ID, segment_start;

代码解释 #

• 递归CTE方案：初始行处理第一天的分段，递归部分迭代生成后续每日的起止时间，直到覆盖整个区间，灵活性更强
• GENERATOR方案：提前生成固定行数的日期序列，通过关联计算分段，逻辑更直观，适合已知最大跨度的场景
• 两种方案均通过DATE_TRUNC对齐日期，用LEAST或CASE确保分段不超出原区间的起止时间

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