SQL Server和Excel中的不同的计算 [英] Different calculation in SQL Server and Excel
问题描述
DECLARE @ Var01 float
SET @ Var01 = 1164.83 *（1 - 3.3387306 * LOG（0.00459418151829729）+ 1.426559 * POWER（LOG（0.00459418151829729），2））/（1 - 3.4680733 * LOG（0.00459418151829729）+ 1.8779192 * POWER（LOG（0.00459418151829729），2） - 0.21223784 * POWER（LOG（0.00459418151829729 ），3） - 0.0035814371 * POWER（LOG（0.00459418151829729），4） - 0.90903163 * POWER（10，-4）* POWER（LOG（0.00459418151829729），5）） - 459.67
结果是： 214.630185149416
然后我试图与excel进行比较，公式如下：
= 1164.83 *（1 - 3.3387306 * LN 0.00459418151829729）+ 1.426559 *（LN（0.00459418151829729））^ 2）/（1 - 3.4680733 * LN（0.00459418151829729）+ 1.8779192 *（LN（0.00459418151829729））^ 2 -0.21223784 *（LN（0.00459418151829729））^ 3 - 0.0035814371 * LN（0.00459418151829729））^ 4 - 0.90903163 * 10 ^ -4 *（LN（0.00459418151829729））^ 5）-45 9.67
结果是： 211.981432072480
问题是，哪一个是正确的？任何想法？计算不一样？
这些评论推测这是roundoff错误，SQL Server更可靠，因为它使用比Excel更精确的浮点数。这是错误的相对误差约为1％。执行一个简短的计算时，你不会得到1％的相对误差，同时进行10 -13 ％的舍入误差，除非你减去几乎相等的大数字。
我建议分解计算，看看SQL Server和Excel是否同意这些作品，看看它们在哪里分歧。这将会奏效这就像跨越一个程序，而不是说最终的结果不是所期望的。您可以进行二进制搜索以快速查找问题，但OP没有提供任何其他信息。
正在执行的计算是
没有任何取消巨大的数字可能会导致相对较大的错误。所以，我试图解决
查看分母中的错误会导致这种计算错误。有一点微积分，我可以检查常数中的拼写错误。解决方案是x = -0.410829。这几乎就是分母的最后一个词。所以，答案并不是这些环境之一在简单的浮点计算中产生1％的相对误差，就是说这个词在分母中被丢弃。最后一个术语是唯一一个像POWER（10，-4）这样的东西。这可能是使用整数运算而不是浮点进行的，因此它的计算结果为0而不是0.0001？是的，显然这就是SQL Server的作用。在整数运算中就像1/2 = 0。如果你想要一个十进制输出，你必须给它一个十进制输入。将10转换为十进制，将其更改为POWER（10.0，-4），使用0.0001，或对整个系数使用适当的科学记数法。
This is my formula. I put it in a SQL Server stored procedure:
DECLARE @Var01 float
SET @Var01 = 1164.83 * (1 - 3.3387306 * LOG(0.00459418151829729) + 1.426559 * POWER(LOG(0.00459418151829729),2)) / (1 - 3.4680733 * LOG(0.00459418151829729) + 1.8779192 * POWER(LOG(0.00459418151829729), 2) - 0.21223784 * POWER(LOG(0.00459418151829729), 3) - 0.0035814371 * POWER(LOG(0.00459418151829729), 4) - 0.90903163 * POWER(10, -4) * POWER(LOG(0.00459418151829729), 5)) - 459.67
The result is: 214.630185149416
Then I'm trying to compare to excel, the formula as below:
=1164.83 * (1 - 3.3387306 * LN(0.00459418151829729) + 1.426559 * (LN(0.00459418151829729)) ^ 2) / (1 - 3.4680733 * LN(0.00459418151829729) + 1.8779192 * (LN(0.00459418151829729)) ^ 2 - 0.21223784 * (LN(0.00459418151829729)) ^ 3 - 0.0035814371 * (LN(0.00459418151829729)) ^ 4 - 0.90903163 * 10 ^ -4 * (LN(0.00459418151829729)) ^ 5) - 459.67
The result is: 211.981432072480
The question is, which one is correct? Any Idea? What the calculation is different?
The comments have speculated that this is roundoff error, and that SQL Server is more reliable because it uses more precise floats than Excel. This is wrong. The relative error is about 1%. You do not get relative errors of 1% when you perform a short computation while making roundoff errors of 10-13% unless you are subtracting nearly equal large numbers.
I suggested breaking down the computation to see if SQL Server and Excel agree on the pieces, to see where they diverged. This would have worked. It's like stepping through a program instead of just saying that the end result is not what was desired. You can do a binary search to find the problem rapidly, but the OP didn't provide any additional information.
The computation being performed is
There isn't any cancelation of huge numbers that might cause a large relative error. So, I tried to solve
to see what error in the denominator would result in this miscalculation. With a little calculus, I could check for typos in the constants. The solution is x=-0.410829. That's almost exactly the last term in the denominator. So, the answer isn't that one of these environments produces 1% relative errors in simple floating point calculations, it's that a term was dropped in the denominator. This would have been obvious from breaking the calculation into pieces.
The last term is the only one with something like POWER(10,-4). Could it be that this is carried out using integer arithmetic instead of floating point, so that it evaluates to 0 instead of 0.0001? Yes, apparently that's what SQL Server does. It's like 1/2=0 in integer arithmetic. If you want a decimal output you have to give it a decimal input. Cast the 10 to decimal, change it to POWER(10.0,-4), use 0.0001, or use proper scientific notation for the whole coefficient.
这篇关于SQL Server和Excel中的不同的计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
