如何在DB2中编写SQL实现按日期区间拆分积分数据为每日一行

实现逻辑 #

通过递归CTE生成每个规则区间内的连续日期，再根据日期对应的星期几匹配对应的积分值，最终输出单日期对应单积分的结构化结果。

实现代码 #

假设遗留积分规则表名为INTEGRAL_RULE，查询语句如下：

WITH DATE_RANGE (GEN_DATE, START_DATE, END_DATE, MON_POINT, TUE_POINT, WED_POINT, THU_POINT, FRI_POINT) AS (
    -- 递归初始节点：取每条规则的起始日期作为首个生成日期
    SELECT 
        DATE(TO_DATE("起始日期", 'DD-MM-YYYY')) AS GEN_DATE,
        DATE(TO_DATE("起始日期", 'DD-MM-YYYY')) AS START_DATE,
        DATE(TO_DATE("结束日期", 'DD-MM-YYYY')) AS END_DATE,
        "周一积分", "周二积分", "周三积分", "周四积分", "周五积分"
    FROM INTEGRAL_RULE
    UNION ALL
    -- 递归迭代：每次加1天，直到超出当前规则的结束日期
    SELECT 
        GEN_DATE + 1 DAY,
        START_DATE,
        END_DATE,
        MON_POINT, TUE_POINT, WED_POINT, THU_POINT, FRI_POINT
    FROM DATE_RANGE
    WHERE GEN_DATE + 1 DAY <= END_DATE
)
SELECT 
    TO_CHAR(GEN_DATE, 'DD-MM-YYYY') AS "日期",
    CASE DAYOFWEEK(GEN_DATE)
        WHEN 2 THEN MON_POINT  -- 周一对应DAYOFWEEK返回值2
        WHEN 3 THEN TUE_POINT  -- 周二对应DAYOFWEEK返回值3
        WHEN 4 THEN WED_POINT  -- 周三对应DAYOFWEEK返回值4
        WHEN 5 THEN THU_POINT  -- 周四对应DAYOFWEEK返回值5
        WHEN 6 THEN FRI_POINT  -- 周五对应DAYOFWEEK返回值6
        ELSE 0 -- 周末积分默认返回0，可根据实际需求调整
    END AS "积分"
FROM DATE_RANGE
-- 过滤掉0积分的行，和示例输出一致，不需要可删除该行
WHERE "积分" != 0
ORDER BY GEN_DATE ASC;

注意事项 #

• 若你的表中起始日期、结束日期本身为DATE类型，可删除TO_DATE转换逻辑直接使用字段值
• DB2中DAYOFWEEK函数返回值规则为：1=周日、2=周一、3=周二、4=周三、5=周四、6=周五、7=周六，和需求中的周积分字段完全匹配
• 若规则时间跨度超过100天，可在查询前执行SET MAX_RECURSION 1000（数值可根据最大跨度调整）避免递归深度不足报错

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