误差怎么这么大,分组后再求比率.

Question

一个表[table]有5个字段:种类,单号,不良,良品,不良率(=不良/(不良+良品)),记录有1000多条.在查询分析器中运行.
select avg(isnull(不良率,0)) from [table]
返回:0.30142110468092032select isnull(sum(isnull(不良,0)),0)/(
isnull(sum(isnull(不良,0)),0)+
isnull(sum(isnull(良品,0)),0))
from [table]
返回:0.370535select 种类,
isnull(sum(isnull(不良,0)),0) as 不良,
isnull(sum(isnull(良品,0)),0) as 良品,
isnull(avg(isnull(不良率,0)),0) as 不良率
into #t
from [table]
group by 种类 order by 种类
select avg(isnull(不良率,0)) from #t
drop table #t
返回:0.36492959136333694为什么这三个值相差那么大呢?理论上应该是一样大的吧.
请高手指点,非常谢谢!!

Answer 1

select avg(isnull(不良率,0)) from [table]
返回:0.30142110468092032select isnull(sum(isnull(不良,0)),0)/(
isnull(sum(isnull(不良,0)),0)+
isnull(sum(isnull(良品,0)),0))
from [table]
返回:0.370535这两种算法结果差异的原因在于方法1中不良率作为计算列,每行的不良率值都是四舍五入的,所以1000多行被舍掉的值累加起来数值会不小.
而方法2中每行在计算的时候完全没有四舍五入,所求的和是最精确的.
----------------------------------------------------
方法3由于进行了分组,聚合函数avg()可能受到NULL值的影响.

Answer 2

jacket008(§优国优民§) ( ) 
 
1、3都是错误的计算方法
--------------------------------------------
不同意此看法,如果认为方法1是错误的,那么avg()函数就不该存在.
  
 

Answer 3

多谢大家哈!!
这么多回复.
to: hellowork(一两清风)
方法1中不良率并不是计算列,而是用存储过程算出来的,公式是:
case 不良+良品 when 0 then 0 else 不良/(不良+良品) end.

Answer 4

case 不良+良品 when 0 then 0 else 不良/(不良+良品) end
==========================================
如果不良和良品都是int
不良/(不良+良品)就等于0

Answer 5

to yesyesyes():'不良率'的数据类型是numeric(20,17).

Answer 6

还有就是方法3中都使用了isnull()函数,聚合函数avg()还会受到NULL值的影响吗?

Answer 7

declare @ta table(id int identity,good decimal(5,2),bad decimal(5,2),ratio as bad/good)
insert @ta
select 10,5 union all
select 10,5 union all
select 10,5 union all
select 10,5 union all
select 10,5 select sum(good) as good,sum(bad) as bad, avg(ratio)as [avg],sum(bad)/sum(good) as [bad/good]
from @ta
GO/*结果(能整除时,使用avg()函数的结果与使用sum(bad)/sum(good)的值相等)
good   bad   avg           bad/good
-------------------------------------
50     25    .500000       .500000
*/declare @tb table(id int identity,good decimal(5,2),bad decimal(5,2),ratio as bad/good)
insert @tb
select 12,5 union all
select 13,6 union all
select 14,7 union all
select 15,8select sum(good) as good,sum(bad) as bad, avg(ratio)as [avg],sum(bad)/sum(good) as [bad/good]
from @tb
GO/*结果(不能整除时,使用avg()函数的结果与使用sum(bad)/sum(good)的值不等)
good   bad   avg           bad/good
-------------------------------------
54     26    .47788461     .481481
*/

Answer 8

declare @tc table(id int identity,good decimal(5,2),bad decimal(5,2),ratio as bad/good)
insert @tc
select 10,5 union all
select 10,5 union all
select 10,5 union all
select 10,5 union all
select 10,NULL    
select avg(bad) as [avg],sum(bad)/count(good) as [sum/count],avg(isnull(bad,0)) as [avgISNULL0]
from @tc/*结果(NULL值对聚合函数的影响)
avg     sum/count   avgISNULL0
--------------------------------------
5.0000  4.0000      4.0000
*/

Answer 9

谢谢hellowork(一两清风)!那您举的
declare @tb table(id int identity,good decimal(5,2),bad decimal(5,2),ratio as bad/good)
insert @tb
select 12,5 union all
select 13,6 union all
select 14,7 union all
select 15,8select sum(good) as good,sum(bad) as bad, avg(ratio)as [avg],sum(bad)/sum(good) as [bad/good]
from @tb
GO/*结果(不能整除时,使用avg()函数的结果与使用sum(bad)/sum(good)的值不等)
good   bad   avg           bad/good
-------------------------------------
54     26    .47788461     .481481
*/这个例子中,算不良率,取哪个值比较好呢?.47788461     还是  .481481??

Answer 10

这个不好说,具体要看楼主的实际需求,使用avg(ratio)会丧失些精确度,使用sum(bad)/sum(good)精度会高些.而且实际应用中还要看NULL值怎么处理合适.

Answer 11

to hellowork(一两清风):不良为null值时,就作为0处理啊.

Answer 12

如果将null作为0处理,则avg()在计算平均值时就把该行算作一行,否则avg()在计算平均值时分母就会少一行(使平均值变大).楼主可以运行一下我最后一个例子table @tc,体会一下.

Answer 13

那就采用sum(bad)/sum(good)这种方法吧.精度更高的.

Answer 14

如果将null作为0处理,则avg()在计算平均值时就把该行算作一行.
我们这里要的就是这种效果吧.