除以零误差中遇到雪花查询

Question

我有以下雪花查询，我得到一个除零错误…你能帮我一下吗?

with cte1 as 
(select * from "coe.cup"
where typeofcare ='AM'
and status ='DONE'
and review ='false'
and date (assigneddate)>='2021-04-01'), cte2 as(
select cast(completed as date) completeddate ,iscode
,iff(iscode=1,datediff(minute,assigneddate,coded),0) codeddatetime
,iff(iscode=0,datediff(minute,assigneddate,qaed),0) qaeddatetime
,datediff(minute,assigneddate,completed) overall  from 
(select *,iff(qaed='1900-01-01 00:00:00.0000000',1,0) iscode from cte1)a )
select completeddate 
,sum(iff(iscode=1,1,0)) noofvisitbillscoded
,sum(iff(iscode=1,0,1)) noofvisitbillscodedandqaed
,count(1) totalvisitbillscompleted
,cast(sum(codeddatetime)/sum(iff(iscode=1,1,0)) as float)/60 averagetimeforcodedvisitbills
,cast(sum(qaeddatetime)/sum(iff(iscode=1,0,1)) as float)/60  averagetimeforcodedandqaedvisitbills
,cast(sum(overall)/count(1) as float)/60 overallaveragetime
from cte2
group by completeddate

Answer 1

另一个选择是使用DIV0函数，例如:

DIV0(sum(codeddatetime),sum(iff(iscode=1,1,0))

更多信息在这里:https://docs.snowflake.com/en/sql-reference/functions/div0.html

Answer 2

当涉及除法时，除数可以用NULLIFZERO处理:

NULLIFZERO ()

如果参数的值为0，则返回NULL;否则，返回参数。

第二个模式sum(iff(iscode=1,1,0))是一个条件和，模拟过滤后的COUNT。它可以进一步simplfiedCOUNT_IF

COUNT_IF ()

返回满足条件的记录数量。

总结:

,cast(sum(codeddatetime)/sum(iff(iscode=1,1,0)) as float)/60 
=>
,SUM(codeddatetime)/NULLIFZERO(COUNT_IF(iscode=1))/60 
=> if iscode is boolean column then:
,SUM(codeddatetime)/NULLIFZERO(COUNT_IF(iscode))/60 

Answer 3

我认为这是由于/sum(iff(iscode=1,1,0))，其中这可能有时返回0。

处理除零的一种方法是使用NULLIF

NULLIF( <expr1> , <expr2> )

如果expr1等于expr2，则返回NULL，否则返回expr1。

因此，在代码中，例如sum(iff(iscode=1,1,0))，您可以将其替换为:

NULLIF(sum(iff(iscode = 1, 1, 0)), 0)

，这应该返回NULL，而不是导致除零。