如何在Oracle SQL Select脚本中实现递归并拆分交易日期为每日记录

需求说明 #

现有交易表存储了每笔交易的日期区间和金额，需要将每笔交易拆分为每日一条记录，生成新表便于查询，同时实现SQL递归逻辑。

假设原表结构 #

假设原表名为deals，字段如下（根据示例数据适配）：

• deal_no：交易编号（数字类型）
• start_date：交易起始日期（字符串格式DD.MM.RR，如20.01.22）
• end_date：交易结束日期（字符串格式DD.MM.RR）
• deal_value：交易金额（带逗号的字符串，如10,000）

实现方案：递归CTE（公用表表达式） #

使用Oracle的递归CTE来生成日期序列，拆分每笔交易的日期区间：

WITH recursive_dates AS (
    -- 锚点成员：获取每笔交易的起始日期记录
    SELECT 
        deal_no,
        TO_DATE(start_date, 'DD.MM.RR') AS deal_date,
        TO_DATE(end_date, 'DD.MM.RR') AS end_date,
        TO_NUMBER(REPLACE(deal_value, ',', '')) AS deal_value
    FROM deals
    
    UNION ALL
    
    -- 递归成员：逐天生成日期，直到超过结束日期
    SELECT 
        deal_no,
        deal_date + 1 AS deal_date,
        end_date,
        deal_value
    FROM recursive_dates
    WHERE deal_date + 1 <= end_date
)
-- 生成拆分后的结果（可直接用于创建新表）
SELECT 
    deal_no AS "Deal No.",
    TO_CHAR(deal_date, 'DD.MM.RR') AS "Date",
    TO_CHAR(deal_value, 'FM999,999') AS "Value"
FROM recursive_dates
ORDER BY deal_no, deal_date;

创建新表 #

如果需要直接生成新表，将上述查询替换为CREATE TABLE ... AS SELECT：

CREATE TABLE daily_deals AS
SELECT 
    deal_no,
    deal_date,
    deal_value
FROM recursive_dates;

递归逻辑说明 #

递归CTE由两部分组成，通过UNION ALL连接：

1. 锚点成员：返回初始数据集，即每笔交易的起始日期对应的记录，作为递归的起点。
2. 递归成员：引用自身（recursive_dates），将上一步生成的日期加1天，直到生成的日期超过交易的结束日期时，递归自动终止。

注意事项 #

• 如果原表的日期字段已经是DATE类型，无需使用TO_DATE转换。
• 如果金额字段已经是NUMBER类型，无需REPLACE和TO_NUMBER处理。
• 日期格式中的RR用于处理两位年份，Oracle会自动映射到对应世纪（如22对应2022），若需明确四位年份，可改用YYYY格式。

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