Oracle 19c跨多年合同价值按日历年度比例分配高效实现问询

要实现跨年度合同的价值按比例自动分配到对应日历年度，且无需手动编写每个年份的查询，可以用递归CTE+时间交集计算的方案，高效处理数十年跨度的合同数据。

核心逻辑 #

1. 用递归CTE自动生成每个合同覆盖的所有日历年度，无需硬编码年份
2. 计算每个年度内合同实际存续的时长占总合同时长的比例
3. 按比例分配合同价值后，按交易对手和年度汇总

完整SQL示例 #

假设你的合同表名为contracts，字段为counterparty(交易对手)、value(合同总价值)、start_date(起始时间，TIMESTAMP类型)、end_date(结束时间，TIMESTAMP类型)：

WITH contract_years AS (
    -- 锚点成员：获取每个合同的起始年份
    SELECT
        counterparty,
        value,
        start_date,
        end_date,
        EXTRACT(YEAR FROM start_date) AS contract_year
    FROM contracts
    UNION ALL
    -- 递归成员：逐年生成后续年份，直到超过合同结束年份
    SELECT
        counterparty,
        value,
        start_date,
        end_date,
        contract_year + 1
    FROM contract_years
    WHERE contract_year + 1 <= EXTRACT(YEAR FROM end_date)
),
allocated_values AS (
    SELECT
        counterparty,
        contract_year,
        value,
        -- 计算合同在当前年度的实际起止时间（取交集）
        GREATEST(start_date, TO_TIMESTAMP(contract_year || '-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) AS overlap_start,
        LEAST(end_date, TO_TIMESTAMP(contract_year || '-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) AS overlap_end,
        -- 计算合同总时长（转成秒数，确保精度）
        (CAST(end_date AS DATE) - CAST(start_date AS DATE)) * 86400 + 
        EXTRACT(SECOND FROM end_date) - EXTRACT(SECOND FROM start_date) +
        (EXTRACT(MINUTE FROM end_date) - EXTRACT(MINUTE FROM start_date)) * 60 +
        (EXTRACT(HOUR FROM end_date) - EXTRACT(HOUR FROM start_date)) * 3600 AS total_duration_sec
    FROM contract_years
)
SELECT
    counterparty,
    contract_year AS calendar_year,
    SUM(
        value * 
        -- 计算当前年度分配比例：年度内存续秒数 / 总合同秒数
        (
            (CAST(overlap_end AS DATE) - CAST(overlap_start AS DATE)) * 86400 + 
            EXTRACT(SECOND FROM overlap_end) - EXTRACT(SECOND FROM overlap_start) +
            (EXTRACT(MINUTE FROM overlap_end) - EXTRACT(MINUTE FROM overlap_start)) * 60 +
            (EXTRACT(HOUR FROM overlap_end) - EXTRACT(HOUR FROM overlap_start)) * 3600
        ) / total_duration_sec
    ) AS total_allocated_value
FROM allocated_values
-- 排除总时长为0的异常合同（如起止时间完全相同）
WHERE total_duration_sec > 0
GROUP BY counterparty, contract_year
ORDER BY counterparty, contract_year;

关键细节说明 #

• 递归CTE生成年度：自动遍历每个合同从起始年到结束年的所有年份，不管跨度多少年都能覆盖
• 时间交集计算：用GREATEST和LEAST取合同时间与年度时间的重叠区间，确保只计算合同在该年度内的实际存续时长
• 高精度时长计算：将TIMESTAMP转成秒数计算，避免天级计算的精度丢失，适配小时/天级的短期限合同
• 异常处理：过滤总时长为0的合同，避免除法报错

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