在Snowflake视图中将日期范围拆分为月度新记录

需求说明 #

将每条记录按其日期范围覆盖的月份拆分，生成对应月度记录，同时输出年份、月份及该记录在当月的天数。

输入输出示例 #

输入

ID  StartDate   EndDate         
ABC 12/14/2020  1/14/2021           
XYZ 12/13/2020  12/23/2020          
DEF 12/3/2020   2/3/2021            

输出

ID  StartDate   EndDate     YEAR    MONTH   No. Of Days
ABC 12/14/2020  12/31/2020  2020    12      18
ABC 1/1/2021    1/14/2021   2021    1       14
XYZ 12/13/2020  12/23/2020  2020    12      11
DEF 12/3/2020   12/31/2020  2020    12      29
DEF 1/1/2021    1/31/2021   2021    1       31
DEF 2/1/2021    2/3/2021    2021    2       3

解决方案SQL #

假设原表名为your_table，字段ID、StartDate、EndDate为日期类型（若为字符串需用TO_DATE(字段名, 'MM/DD/YYYY')转换），创建视图的SQL如下：

CREATE OR REPLACE VIEW monthly_split_view AS
WITH date_range AS (
    SELECT 
        ID,
        StartDate,
        EndDate,
        -- 生成每条记录覆盖的每个月份的第一天
        DATEADD(MONTH, seq4(), DATE_TRUNC('MONTH', StartDate)) AS month_start
    FROM your_table
    -- 生成足够的序列覆盖最大日期范围，此处设为100个月，可按需调整
    , TABLE(GENERATOR(ROWCOUNT => 100))
    -- 过滤掉超出EndDate所在月份的序列
    WHERE month_start <= DATE_TRUNC('MONTH', EndDate)
)
SELECT 
    ID,
    -- 取原记录开始日期与当月第一天的较大值作为当月实际开始日期
    GREATEST(StartDate, month_start) AS StartDate,
    -- 取原记录结束日期与当月最后一天的较小值作为当月实际结束日期
    LEAST(EndDate, DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start))) AS EndDate,
    -- 提取年份
    YEAR(month_start) AS YEAR,
    -- 提取月份
    MONTH(month_start) AS MONTH,
    -- 计算当月天数（包含首尾日期）
    DATEDIFF(DAY, GREATEST(StartDate, month_start), LEAST(EndDate, DATEADD(DAY, -1, DATEADD(MONTH, 1, month_start)))) + 1 AS "No. Of Days"
FROM date_range
ORDER BY ID, YEAR, MONTH;

代码说明 #

1. CTE date_range：通过GENERATOR生成序列，为每条记录生成其日期范围覆盖的所有月份的第一天，直到EndDate所在的月份。
2. 实际起止日期计算：用GREATEST和LEAST函数确保每个月度记录的起止日期在原记录的日期范围内，同时贴合当月的首尾。
3. 天数计算：通过DATEDIFF计算两个日期的天数差后加1，确保包含首尾日期。

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