如何在Snowflake SQL中基于起止日期生成用户月度时间序列并自建日期表？

初始表结构 #

userid | start_dt   | end_dt
123    | 2020-01-01 | 2020-03-01
355    | 2021-04-01 | 2021-05-01

目标结果 #

date       | userid 
2020-01-01 | 123
2020-02-01 | 123
2020-03-01 | 123
2021-04-01 | 355
2021-05-01 | 355

以下是两种最优实现方案，分别适用于临时查询和长期复用场景：

方案一：动态生成月度序列（无需单独建表） #

通过Snowflake的GENERATOR函数配合日期函数，直接在查询中生成所需的月度日期，无需提前维护日期表，适合临时查询场景。

WITH user_time_ranges AS (
    SELECT 
        userid,
        start_dt,
        end_dt
    FROM your_table_name -- 替换为你的初始表名
),
monthly_sequence AS (
    SELECT
        userid,
        DATEADD(MONTH, seq4(), DATE_TRUNC('MONTH', start_dt)) AS date
    FROM user_time_ranges,
    -- 生成足够覆盖最大月份跨度的行数
    TABLE(GENERATOR(ROWCOUNT => (SELECT MAX(DATEDIFF(MONTH, start_dt, end_dt)) + 1 FROM user_time_ranges)))
    -- 过滤超出用户end_dt的日期
    WHERE DATEADD(MONTH, seq4(), DATE_TRUNC('MONTH', start_dt)) <= end_dt
)
SELECT date, userid
FROM monthly_sequence
ORDER BY userid, date;

关键说明：

• DATE_TRUNC('MONTH', start_dt) 将用户的起始日期统一转为当月1号，确保序列从月度起始开始
• seq4() 生成从0开始的连续整数序列，配合DATEADD生成每个月的1号
• GENERATOR的ROWCOUNT设为用户时间范围内的最大月份跨度+1，避免生成多余行

方案二：自建永久日期维度表（长期复用） #

如果需要频繁使用日期维度进行分析，自建一个完整的日期表是更高效的选择，可避免数据缺失问题，且支持扩展更多日期属性。

1. 创建日期维度表

CREATE OR REPLACE TABLE date_dimension (
    date DATE PRIMARY KEY,
    year NUMBER(4, 0),
    month NUMBER(2, 0),
    year_month VARCHAR(7) -- 格式为 'yyyy-mm'
);

2. 插入月度起始日期数据

生成2010-01-01至2030-12-01的所有月度1号（可根据需求调整时间范围）：

INSERT INTO date_dimension (date, year, month, year_month)
SELECT
    DATEADD(MONTH, seq4(), '2010-01-01'::DATE) AS date,
    EXTRACT(YEAR FROM DATEADD(MONTH, seq4(), '2010-01-01'::DATE)) AS year,
    EXTRACT(MONTH FROM DATEADD(MONTH, seq4(), '2010-01-01'::DATE)) AS month,
    TO_CHAR(DATEADD(MONTH, seq4(), '2010-01-01'::DATE), 'yyyy-mm') AS year_month
FROM TABLE(GENERATOR(ROWCOUNT => 252)) -- 2010至2030共252个月
WHERE DATEADD(MONTH, seq4(), '2010-01-01'::DATE) <= '2030-12-01'::DATE;

3. 关联用户表生成目标结果

WITH user_time_ranges AS (
    SELECT 
        userid,
        start_dt,
        end_dt
    FROM your_table_name -- 替换为你的初始表名
)
SELECT
    dd.date,
    utr.userid
FROM user_time_ranges utr
JOIN date_dimension dd
    ON dd.date BETWEEN DATE_TRUNC('MONTH', utr.start_dt) AND utr.end_dt
    AND dd.date = DATE_TRUNC('MONTH', dd.date) -- 确保只取月度起始日
ORDER BY utr.userid, dd.date;

关键说明：

• 日期表可根据需求扩展更多字段（如季度、星期数、是否工作日等），满足多样化分析需求
• 提前生成足够覆盖业务需求的日期范围，从根源避免数据缺失问题
• 关联时通过DATE_TRUNC确保匹配用户的月度时间区间

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