Snowflake：基于指定工作日动态给起始日期累加计数天数

表结构 #

需求 #

计算END_DATE列：从ORIGINAL_EXPDATE开始，往后累加NO_RATES个符合条件的日期——仅当日期对应的星期列（如周一对应MO_COUNT）值为TRUE时，该日期才被计入。

示例 #

ORIGINAL_EXPDATE = 2024-02-05（周一），NO_RATES = 2，仅MO_COUNT和FR_COUNT为TRUE：

• 符合条件的日期为2月9日（周五）、2月12日（周一）
• 最终END_DATE为2024-02-12

测试数据 #

WITH test_data AS (
    SELECT *
    FROM (VALUES 
        (1, '2024-02-05'::DATE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, 2), -- 预期END_DATE: 2024-02-12
        (2, '2024-02-05'::DATE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, 2), -- 预期END_DATE: 2024-02-07
        (3, '2024-02-05'::DATE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, 2) -- 预期END_DATE: 2024-02-17
    ) AS t(id, original_expdate, mo_count, tu_count, we_count, th_count, fr_count, sa_count, su_count, no_rates)
)
SELECT * FROM test_data;

测试案例说明 #

• ID=1：仅计入周一和周五，累加2个符合条件的日期后，END_DATE为2024-02-12
• ID=2：周一至周六均计入，2月6日（周二）、7日（周三）为前两个符合条件的日期，END_DATE为2024-02-07
• ID=3：仅计入周六，累加2个符合条件的日期后，END_DATE为2024-02-17

解决方案 #

由于性能要求不高，可以通过生成后续日期序列，筛选符合条件的日期后取第NO_RATES个：

WITH test_data AS (
    SELECT *
    FROM (VALUES 
        (1, '2024-02-05'::DATE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, 2),
        (2, '2024-02-05'::DATE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, 2),
        (3, '2024-02-05'::DATE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, 2)
    ) AS t(id, original_expdate, mo_count, tu_count, we_count, th_count, fr_count, sa_count, su_count, no_rates)
),
date_candidates AS (
    SELECT 
        td.*,
        DATEADD(DAY, seq.index, td.original_expdate) AS candidate_date,
        -- 判断当前日期是否符合计数规则
        CASE DAYOFWEEKISO(candidate_date)
            WHEN 1 THEN mo_count -- DAYOFWEEKISO返回1=周一，7=周日
            WHEN 2 THEN tu_count
            WHEN 3 THEN we_count
            WHEN 4 THEN th_count
            WHEN 5 THEN fr_count
            WHEN 6 THEN sa_count
            WHEN 7 THEN su_count
        END AS is_eligible
    FROM test_data td
    -- 生成原始日期后90天的序列，可根据实际需求调整天数
    LATERAL FLATTEN(INPUT => SEQUENCE(1, 90)) seq
),
ranked_dates AS (
    SELECT 
        *,
        -- 对符合条件的日期按顺序编号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY candidate_date) AS rank_num
    FROM date_candidates
    WHERE is_eligible = TRUE
)
SELECT 
    id,
    original_expdate,
    mo_count,
    tu_count,
    we_count,
    th_count,
    fr_count,
    sa_count,
    su_count,
    no_rates,
    candidate_date AS end_date
FROM ranked_dates
WHERE rank_num = no_rates
ORDER BY id;

逻辑说明 #

1. 用SEQUENCE生成原始日期之后的90天序列，覆盖绝大多数累加场景
2. 通过DAYOFWEEKISO获取日期对应的星期，匹配对应的*_COUNT列判断是否符合计数条件
3. 对每个ID下的符合条件日期按时间排序并编号，取编号等于NO_RATES的日期作为END_DATE

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