Oracle除数等于零

Question

我如何在不计算两次分母求和的情况下，围绕这个查询包装一个解码器来处理零因子？我不想返回thedate和四舍五入百分比以外的任何内容。

SELECT thedate, ROUND (100*
  SUM( case when TRUNC(ACTIVITY_END_DATE) <= thedate
     AND TRUNC(ACTIVITY_END_DATE) >= add_months( trunc(thedate,'mm'), -12) 
     AND trunc(ACTIVITY_END_DATE) <= trunc(ACTIVITY_NEED_DATE) 
     AND SYSDATE  >=  trunc(thedate,'mm') then 1 else 0 end )  
  /
  SUM( case when TRUNC(ACTIVITY_END_DATE) <= thedate 
     AND TRUNC(ACTIVITY_END_DATE) >= add_months( trunc(thedate,'mm'), -12) 
     AND SYSDATE  >=  trunc(thedate,'mm') then 1 else 0 end ) ) as OTR12 
       FROM TEST
       cross join (  select add_months(last_day(SYSDATE), level-7) as thedate 
       from dual connect by level <= 12  )  
       GROUP BY thedate 
       ORDER BY thedate 

看起来在解码的时候，我必须做两次分母求和

DECODE(denominator_summation,0,NULL, numerator_summation / denominator_summation)

Answer 1

使用子查询怎么样？

select thedate, (case when denom <> 0 then round(100*num/denom) end)
from (SELECT thedate,
             SUM( case when TRUNC(ACTIVITY_END_DATE) <= thedate
                            AND TRUNC(ACTIVITY_END_DATE) >= add_months( trunc(thedate,'mm'), -12) 
                            AND trunc(ACTIVITY_END_DATE) <= trunc(ACTIVITY_NEED_DATE) 
                            AND SYSDATE  >=  trunc(thedate,'mm') then 1 else 0
                  end )   as num,
             SUM( case when TRUNC(ACTIVITY_END_DATE) <= thedate 
                        AND TRUNC(ACTIVITY_END_DATE) >= add_months( trunc(thedate,'mm'), -12) 
                        AND SYSDATE  >=  trunc(thedate,'mm') then 1 else 0 end ) ) as OTR12 
        FROM TEST cross join
             (  select add_months(last_day(SYSDATE), level-7) as thedate 
                from dual connect by level <= 12  )  
        GROUP BY thedate 
     ) t
order by thedate

Answer 2

使用having子句：

having 
SUM( case when TRUNC(ACTIVITY_END_DATE) <= thedate 
 AND TRUNC(ACTIVITY_END_DATE) >= add_months( trunc(thedate,'mm'), -12) 
 AND SYSDATE  >=  trunc(thedate,'mm') then 1 else 0 end ) ) as OTR12 
   FROM TEST
   cross join (  select add_months(last_day(SYSDATE), level-7) as thedate 
   from dual connect by level <= 12  )
> 0

Answer 3

SELECT 
  thedate, 
  ROUND (
    100 * SUM( 
       case when TRUNC(ACTIVITY_END_DATE) <= thedate
       AND TRUNC(ACTIVITY_END_DATE) >= add_months( trunc(thedate,'mm'), -12) 
       AND trunc(ACTIVITY_END_DATE) <= trunc(ACTIVITY_NEED_DATE) 
       AND SYSDATE  >=  trunc(thedate,'mm') then 1 else 0 end 
    ) / nullif(SUM( 
       case when TRUNC(ACTIVITY_END_DATE) <= thedate 
       AND TRUNC(ACTIVITY_END_DATE) >= add_months( trunc(thedate,'mm'), -12) 
       AND SYSDATE  >=  trunc(thedate,'mm') then 1 else 0 end 
    ), 0) 
  ) as OTR12 
FROM 
  TEST
  cross join (  
     select add_months(last_day(SYSDATE), level-7) as thedate 
     from dual connect by level <= 12  
  )  
GROUP BY thedate 
ORDER BY thedate