如何在Snowflake中基于多表起始日期生成年度月度日期序列？

原代码的问题 #

你写的递归CTE无法正常运行的核心原因是终止条件错误：WHERE dt < DATEADD('year', 1, dt)这个条件对任何日期都成立（给日期加1年必然大于原日期），导致递归无限执行，最终触发Snowflake的递归深度限制报错。

解决方案一：修正递归CTE的终止逻辑 #

需要在递归CTE中保留每个表的原始起始日期，用它来判断是否停止递归：

WITH RECURSIVE dates_cte AS (
    -- 初始层：保留原始起始日期用于终止判断
    SELECT 
        SF_LOWDATE::DATE AS dt,
        SF_LOWDATE::DATE AS original_start,
        REPLACE(REPLACE(object_name, '[', ''), ']', '') AS object_name
    FROM table_sf_lowdates
    UNION ALL
    -- 递归层：每月递增日期
    SELECT 
        DATEADD('month', 1, dt) AS dt,
        original_start,
        object_name
    FROM dates_cte
    -- 终止条件：当前日期未超过原始起始日期加1年
    WHERE dt < DATEADD('year', 1, original_start)
)
SELECT dt, object_name 
FROM dates_cte 
ORDER BY object_name, dt;

解决方案二：用GENERATOR函数实现（更高效） #

Snowflake的GENERATOR函数可以直接生成固定数量的行，结合横向连接给每个表的起始日期生成12个月的序列，性能比递归CTE更优：

SELECT 
    DATEADD('month', seq4(), t.SF_LOWDATE::DATE) AS dt,
    REPLACE(REPLACE(t.object_name, '[', ''), ']', '') AS object_name
FROM table_sf_lowdates t
-- 生成0到11的序列，对应起始日期及之后11个月（共12个日期）
JOIN TABLE(GENERATOR(ROWCOUNT => 12)) g
-- 确保生成的日期不超过起始日期加1年
WHERE DATEADD('month', seq4(), t.SF_LOWDATE::DATE) < DATEADD('year', 1, t.SF_LOWDATE::DATE)
ORDER BY object_name, dt;

两种方案说明 #

• 递归CTE逻辑直观，适合理解递归原理，但数据量较大时性能略逊。
• GENERATOR函数是集合式操作，避免了递归的开销，处理大量表时更高效。

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