Oracle 12c按月份天数分摊IMP值的高效SQL实现问询

问题背景 #

现有数据表 #

CAT_PROD    FROM_DT     TO_DT       IMP
A1          15/01/2023  07/02/2023  100
A2          13/01/2023  16/01/2023  100

期望输出结果 #

CAT_PROD    RANGE_DT    IMP_RANGE   EXPLANATION
A1          202301      70,83       There are 17 days between 15th Jan (included) and 31st Jan. 17*100/24 = 70.83. 24 is the number of days between 15th Jan and 7th Feb.
A1          202302      29,17       There are 7 days between 1st Feb (included) and 7th Feb. 7*100/24 = 29.17. 24 is the number of days between 15th Jan and 7th Feb.
A2          202301      100         There are 4 days between 13th Jan (included) and 16th Jan. 4*100/4= 100. 4 is the number of days between 13th Jan and 16th Jan.

分摊规则 #

• IMP值按其所属月份的实际天数占总区间天数的比例分摊
• RANGE_DT格式为YYYYMM，代表对应月份
• CAT_PROD与RANGE_DT的组合需唯一

现有方案性能问题 #

当前使用的SQL通过CONNECT BY递归生成月份区间，再用CROSS APPLY关联，数据量较大时性能极差：

WITH aux(cat_prod, startdate, enddate, imp) AS
  (SELECT 'A1' , DATE'2023-01-15' , DATE'2023-02-07' , 100 from dual
  
  UNION
  
  SELECT 'A2' , DATE'2023-01-13' , DATE'2023-01-16' , 100 from dual
  ),
  apply_cross as
  (select e.cat_prod,
    e.imp,
    enddate-startdate + 1 total_days_range,
    case
      when e.startdate > x.s_date
      then e.startdate
      else x.s_date
    end as start_date,
    case
      when e.enddate < x.e_date
      then e.enddate
      else x.e_date
    end as end_date
  from aux e cross apply
    (select trunc( e.startdate, 'mm') + (level-1) * interval '1' month         as s_date,
      trunc( e.startdate              + (level) * interval '1' month, 'mm') -1 as e_date
    from dual
      connect by level <= months_between( trunc( e.enddate, 'mm'),trunc( e.startdate, 'mm')) + 1
    ) x
  )
select ac.cat_prod,
  to_char(start_date, 'YYYYMM') month_id,
  round(imp*(end_date-start_date+1)/total_days_range, 2) imp_prorate
from apply_cross ac;

优化实现方案 #

改用基于集合的区间拆分，避免不必要的递归开销，大幅提升大数据量下的性能：

WITH base_data AS (
    SELECT 
        CAT_PROD,
        TO_DATE(FROM_DT, 'DD/MM/YYYY') AS FROM_DT,
        TO_DATE(TO_DT, 'DD/MM/YYYY') AS TO_DT,
        IMP,
        -- 预计算总区间天数
        TO_DT - FROM_DT + 1 AS TOTAL_DAYS
    FROM YOUR_TABLE_NAME -- 替换为实际数据表名
),
split_ranges AS (
    -- 场景1：日期区间完全在同一个月内
    SELECT 
        CAT_PROD,
        FROM_DT AS PERIOD_START,
        TO_DT AS PERIOD_END,
        IMP,
        TOTAL_DAYS,
        FROM_DT AS ORIG_FROM,
        TO_DT AS ORIG_TO
    FROM base_data
    WHERE TRUNC(FROM_DT, 'MM') = TRUNC(TO_DT, 'MM')
    
    UNION ALL
    
    -- 场景2：拆分出开始月的部分天数
    SELECT 
        CAT_PROD,
        FROM_DT AS PERIOD_START,
        LAST_DAY(FROM_DT) AS PERIOD_END,
        IMP,
        TOTAL_DAYS,
        FROM_DT AS ORIG_FROM,
        TO_DT AS ORIG_TO
    FROM base_data
    WHERE TRUNC(FROM_DT, 'MM') < TRUNC(TO_DT, 'MM')
    
    UNION ALL
    
    -- 场景3：拆分出结束月的部分天数
    SELECT 
        CAT_PROD,
        TRUNC(TO_DT, 'MM') AS PERIOD_START,
        TO_DT AS PERIOD_END,
        IMP,
        TOTAL_DAYS,
        FROM_DT AS ORIG_FROM,
        TO_DT AS ORIG_TO
    FROM base_data
    WHERE TRUNC(FROM_DT, 'MM') < TRUNC(TO_DT, 'MM')
    
    UNION ALL
    
    -- 场景4：拆分出中间的完整月份（仅当跨2个以上月份时生效）
    SELECT 
        CAT_PROD,
        ADD_MONTHS(TRUNC(FROM_DT, 'MM'), LEVEL) AS PERIOD_START,
        LAST_DAY(ADD_MONTHS(TRUNC(FROM_DT, 'MM'), LEVEL)) AS PERIOD_END,
        IMP,
        TOTAL_DAYS,
        FROM_DT AS ORIG_FROM,
        TO_DT AS ORIG_TO
    FROM base_data
    WHERE TRUNC(FROM_DT, 'MM') + INTERVAL '1' MONTH < TRUNC(TO_DT, 'MM')
    CONNECT BY LEVEL <= MONTHS_BETWEEN(TRUNC(TO_DT, 'MM'), TRUNC(FROM_DT, 'MM')) - 1
        AND PRIOR CAT_PROD = CAT_PROD
        AND PRIOR FROM_DT = FROM_DT
        AND PRIOR TO_DT = TO_DT
        AND PRIOR SYS_GUID() IS NOT NULL -- 避免生成笛卡尔积
)
SELECT 
    CAT_PROD,
    TO_CHAR(PERIOD_START, 'YYYYMM') AS RANGE_DT,
    -- 计算分摊值，按需求替换小数点为逗号
    REPLACE(ROUND(IMP * (PERIOD_END - PERIOD_START + 1) / TOTAL_DAYS, 2), '.', ',') AS IMP_RANGE,
    -- 生成符合要求的说明文本
    'There are ' || (PERIOD_END - PERIOD_START + 1) || ' days between ' || 
    TO_CHAR(PERIOD_START, 'DDth Mon') || ' (included) and ' || 
    TO_CHAR(PERIOD_END, 'DDth Mon') || '. ' || 
    (PERIOD_END - PERIOD_START + 1) || '*' || IMP || '/' || TOTAL_DAYS || ' = ' || 
    ROUND(IMP * (PERIOD_END - PERIOD_START + 1) / TOTAL_DAYS, 2) || '. ' || 
    TOTAL_DAYS || ' is the number of days between ' || 
    TO_CHAR(ORIG_FROM, 'DDth Mon') || ' and ' || TO_CHAR(ORIG_TO, 'DDth Mon') || '.' AS EXPLANATION
FROM split_ranges
ORDER BY CAT_PROD, RANGE_DT;

优化说明 #

1. 精准拆分场景：将区间拆分为4种明确场景，避免无差别递归
2. 减少重复计算：预计算总区间天数，避免多次重复运算
3. 控制递归范围：仅对跨3个及以上月份的记录生成中间月份，且通过PRIOR SYS_GUID()防止无效笛卡尔积

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