如何在Snowflake中动态生成两个指定日期间的无间隙连续日期

之前网上流传的基于固定行数表+row_number()生成序列的方法，本质是提前预设一个最大日期间隔上限，一旦某条记录的日期间隔超过预设值就会生成不全，无法适配间隔长度波动大的场景。下面两种动态实现方案不需要硬编码最大间隔，可自动适配任意长度的日期间差：

方案1：原生GENERATE_SERIES实现（推荐，性能最优） #

Snowflake已原生支持GENERATE_SERIES序列生成函数，配合LATERAL关联可以针对每一条数据的start_date、end_date动态生成长度匹配的日期序列，不需要提前构建辅助数字表，也没有行数上限限制。

SELECT
    src.location_id,
    src.subscription_id,
    DATEADD(day, seq.value, src.start_date) AS continuous_date
FROM your_subscription_table src
LEFT JOIN LATERAL GENERATE_SERIES(
    0, -- 序列起始偏移量，对应start_date本身
    DATEDIFF(day, src.start_date, src.end_date) -- 序列结束偏移量，对应end_date
) seq
;

如果需要按周、按月等其他粒度生成连续日期，只需要把DATEADD和DATEDIFF里的day单位替换成对应粒度（week/month/year）即可，逻辑完全一致。

方案2：递归CTE实现（兼容低版本Snowflake） #

如果使用的Snowflake版本暂不支持GENERATE_SERIES，可以用递归CTE实现同等动态效果，逻辑是从每条记录的start_date开始逐次累加时间粒度，直到达到end_date为止。

WITH RECURSIVE cte_date_seq AS (
    -- 锚点：加载所有原始记录，以start_date作为序列起点
    SELECT
        location_id,
        subscription_id,
        start_date AS continuous_date,
        end_date
    FROM your_subscription_table
    UNION ALL
    -- 递归：每次日期+1天，直到等于end_date时终止
    SELECT
        location_id,
        subscription_id,
        DATEADD(day, 1, continuous_date) AS continuous_date,
        end_date
    FROM cte_date_seq
    WHERE continuous_date < end_date
)
SELECT location_id, subscription_id, continuous_date
FROM cte_date_seq
-- 根据业务最大日期间隔调整递归迭代上限，Snowflake最高支持100万次迭代
OPTION (MAX_RECURSION_ITERATIONS = 10000)
;

方案对比 #

• 优先选择GENERATE_SERIES方案：执行性能更高，不需要调整会话参数，代码简洁易维护，不管是间隔几天的短周期还是间隔数年的长周期都能稳定输出无间隙日期
• 两种方案都完全脱离了固定行数表的限制，不会出现因预设行数不足导致的日期缺失问题，可自动适配所有不同长度的日期间隔场景

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