Oracle MODEL子句计算CALCAMT结果异常，请求协助排查

原始表数据（TABLE1） #

期望输出结果 #

当前使用的MODEL子句查询 #

with table1 (row_no,uniqueidentifier,effectivedate,activityid,amt,calcamt) as(
select 1,'AAA',cast('10-may-2020' as date),100,100,0 from dual union all 
select 2,'AAA',cast('10-jun-2020' as date),100,20,0 from dual union all 
select 3,'AAA',cast('17-aug-2020' as date),111,50,0 from dual union all 
select 1,'BBB',cast('9-sep-2021' as date),100,150,0 from dual union all 
select 2,'BBB',cast('11-oct-2022' as date),117,25,0 from dual union all 
select 1,'CCC',cast('5-sep-2023' as date),100,100,0 from dual union all 
select 2,'CCC',cast('2-oct-2023' as date),111,15,0 from dual union all 
select 3,'CCC',cast('5-jan-2025' as date),100,20,0 from dual
)
SELECT ROW_NO,UNIQUEIDENTIFIER,EFFECTIVEDATE,ACTIVITYID,AMT, CALCAMT
FROM TABLE1 
MODEL  
  PARTITION BY(UNIQUEIDENTIFIER,EFFECTIVEDATE)  
  DIMENSION BY (ROW_NO,ACTIVITYID,AMT)  
  MEASURES(CALCAMT)  
  RULES(    
    CALCAMT[ROW_NO,ACTIVITYID,AMT] ORDER BY ROW_NO = CASE              
      WHEN CV(ROW_NO) = 1 THEN                
        CV(AMT)              
      WHEN CV(ROW_NO) > 1 AND CV(ACTIVITYID) = 100 THEN                
        CALCAMT[CV(ROW_NO) - 1, CV(ACTIVITYID) - 1, CV(AMT) - 1] + CV(AMT)              
      WHEN CV(ROW_NO) > 1 AND CV(ACTIVITYID) = 111 THEN                
        GREATEST(0,CALCAMT[CV(ROW_NO) - 1, CV(ACTIVITYID) - 1, CV(AMT) - 1] - CV(AMT))              
      WHEN CV(ROW_NO) > 1 AND CV(ACTIVITYID) = 117 THEN                
        GREATEST(0,CALCAMT[CV(ROW_NO) - 1, CV(ACTIVITYID) - 1, CV(AMT) - 1] - 2*CV(AMT))                 
    END        
  )
ORDER BY UNIQUEIDENTIFIER,EFFECTIVEDATE

当前错误执行结果 #

计算规则 #

• ActivityID为100时：
  • 若ROW_NO=1，CALCAMT等于当前AMT
  • 若ROW_NO>1，CALCAMT为上一行CALCAMT加上当前AMT
• ActivityID为111时：CALCAMT为上一行CALCAMT减去当前AMT（结果不小于0）
• ActivityID为117时：CALCAMT为上一行CALCAMT减去2倍当前AMT（结果不小于0）
• ROW_NO=1的行ActivityID固定为100

问题排查与修正 #

错误原因 #

1. PARTITION BY错误：原语句将EFFECTIVEDATE加入分区，导致每个日期单独分区，同一UNIQUEIDENTIFIER下的多行无法在同一分区内跨行计算，每个分区仅一行数据，后续行无法找到上一行的CALCAMT值。
2. DIMENSION BY错误：原语句将ACTIVITYID和AMT加入维度，规则中引用上一行时使用CV(ACTIVITYID)-1、CV(AMT)-1是错误的——上一行的ACTIVITYID和AMT不一定是当前值减1，维度只需保留用于排序的ROW_NO即可。
3. RULES引用错误：错误修改ACTIVITYID和AMT的维度值，导致无法匹配上一行记录，返回NULL。

修正后的SQL #

with table1 (row_no,uniqueidentifier,effectivedate,activityid,amt,calcamt) as(
select 1,'AAA',cast('10-may-2020' as date),100,100,0 from dual union all 
select 2,'AAA',cast('10-jun-2020' as date),100,20,0 from dual union all 
select 3,'AAA',cast('17-aug-2020' as date),111,50,0 from dual union all 
select 1,'BBB',cast('9-sep-2021' as date),100,150,0 from dual union all 
select 2,'BBB',cast('11-oct-2022' as date),117,25,0 from dual union all 
select 1,'CCC',cast('5-sep-2023' as date),100,100,0 from dual union all 
select 2,'CCC',cast('2-oct-2023' as date),111,15,0 from dual union all 
select 3,'CCC',cast('5-jan-2025' as date),100,20,0 from dual
)
SELECT ROW_NO,UNIQUEIDENTIFIER,EFFECTIVEDATE,ACTIVITYID,AMT, CALCAMT
FROM TABLE1 
MODEL  
  PARTITION BY(UNIQUEIDENTIFIER)  -- 仅按UNIQUEIDENTIFIER分区，确保同一标识下所有行在同一分区
  DIMENSION BY (ROW_NO)           -- 仅用ROW_NO作为维度，按行号顺序计算
  MEASURES(CALCAMT, ACTIVITYID, AMT)  -- 加入ACTIVITYID和AMT，方便规则中引用当前行的值
  RULES AUTOMATIC ORDER           -- 自动按ROW_NO顺序执行规则，保证计算顺序正确
  (    
    CALCAMT[ANY] = CASE              
      WHEN CV(ROW_NO) = 1 THEN                
        AMT[CV(ROW_NO)]  -- 第一行取当前AMT
      WHEN CV(ROW_NO) > 1 AND ACTIVITYID[CV(ROW_NO)] = 100 THEN                
        CALCAMT[CV(ROW_NO) - 1] + AMT[CV(ROW_NO)]              
      WHEN CV(ROW_NO) > 1 AND ACTIVITYID[CV(ROW_NO)] = 111 THEN                
        GREATEST(0, CALCAMT[CV(ROW_NO) - 1] - AMT[CV(ROW_NO)])              
      WHEN CV(ROW_NO) > 1 AND ACTIVITYID[CV(ROW_NO)] = 117 THEN                
        GREATEST(0, CALCAMT[CV(ROW_NO) - 1] - 2 * AMT[CV(ROW_NO)])                 
    END        
  )
ORDER BY UNIQUEIDENTIFIER,EFFECTIVEDATE

修正说明 #

• PARTITION BY：仅保留UNIQUEIDENTIFIER，确保同一标识下的所有行在一个分区内，支持跨行计算。
• DIMENSION BY：仅用ROW_NO作为维度，计算严格按行号顺序进行，无需其他维度。
• MEASURES：加入ACTIVITYID和AMT，规则中可直接引用当前行的这两个值。
• RULES AUTOMATIC ORDER：自动按维度顺序执行规则，保证先计算前一行再计算后一行。
• 规则引用：直接通过ROW_NO-1引用上一行的CALCAMT，不再错误修改其他维度值，确保匹配正确。

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