SQL Server：十进制精度/比例正在产生奇怪的结果

I was working on a bit of SQL for a project, and I noticed some seemingly strange behavior in SQL Server, with regard to what the answer looks like when dividing with decimals.

我正在为一个项目开发一些SQL,我注意到在SQL Server中有一些看似奇怪的行为,关于用小数除法时的答案是什么样的。

Here are some examples which illustrate the behavior I'm seeing:

以下是一些说明我所看到的行为的例子:

DECLARE @Ratio Decimal(38,16)
SET @Ratio = CAST(210 as Decimal(38,16))/CAST(222 as Decimal(38,16));

select @Ratio -- Results in 0.9459450000000000

DECLARE @Ratio Decimal(38,16)
SET @Ratio = CAST(210 as Decimal)/CAST(222 as Decimal);

select @Ratio -- Results in 0.9459459459459459

For the code above, the answer for the query which is (seemingly) less precise gives a more precise value as the answer. When I cast both the dividend and the divisor as Decimal(38,16), I get a number with a scale of 6 (casting it to a Decimal(38,16) again results in the 0's padding the scale).

对于上面的代码,查询的答案(看似不太精确)给出了更精确的值作为答案。当我将被除数和除数都转换为十进制(38,16)时,我得到一个等级为6的数字(将其转换为十进制(38,16)再次导致0的填充比例)。

When I cast the dividend and divisor to just a default Decimal, with no precision or scale set manually, I get the full 16 digits in the scale of my result.

当我将被除数和除数转换为默认的十进制,没有手动设置精度或比例时,我得到结果比例的完整16位数。

Out of curiosity, I began experimenting more with it, using these queries:

出于好奇,我开始尝试使用这些查询:

select CAST(210 as Decimal(38,16))/CAST(222 as Decimal(38,16)) --0.945945
select CAST(210 as Decimal(28,16))/CAST(222 as Decimal(28,16)) --0.9459459459
select CAST(210 as Decimal(29,16))/CAST(222 as Decimal(29,16)) --0.945945945

As you can see, as I increase the precision, the scale of the answer appears to decrease. I can't see a correlation between the scale of the result vs the scale or precision of the dividend and divisor.

如您所见,随着我提高精度,答案的比例似乎会降低。我看不出结果的规模与被除数和除数的比例或精度之间的相关性。

I found some other SO questions pointing to a place in the msdn documentation which states that the resulting precision and scale during an operation on a decimal is determined by performing a set of calculations on the precision and scale of the divisor and dividend, and that:

我发现了一些指向msdn文档中某个位置的其他SO问题,这些问题表明在小数运算期间产生的精度和比例是通过对除数和被除数的精度和比例执行一组计算来确定的,并且:

The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

结果精度和标度的绝对最大值为38.当结果精度大于38时,相应的标度会减小,以防止结果的整数部分被截断。

So I tried running through those equations myself to determine what the output of dividing a Decimal(38,16) into another Decimal(38,16) would look like, and according to what I found, I still should have gotten back a more precise number than I did.

所以我尝试自己运行这些方程来确定将十进制(38,16)除以另一个十进制(38,16)的输出看起来是什么样的,并且根据我发现的结果,我仍然应该得到更精确的回归数量比我多。

So I'm either doing the math wrong, or there's something else going on here that I'm missing. I'd greatly appreciate any insight that any of you has to offer.

所以我要么做错了数学,要么在这里发生了一些我不知道的事情。我非常感谢任何人提供的任何见解。

Thanks in advance...

提前致谢...

1 个解决方案

#1

The documentation is a little incomplete as to the magic of the value 6 and when to apply the max function, but here's a table of my findings, based on that documentation.

关于值6的魔力以及何时应用max函数,文档有点不完整,但这里有一个基于该文档的我的发现表。

As it says, the formulas for division are:

正如它所说,分工的公式是:

Result precision = p1 - s1 + s2 + max(6, s1 + p2 + 1), Result scale = max(6, s1 + p2 + 1)

结果精度= p1 - s1 + s2 + max(6,s1 + p2 + 1),结果比例= max(6,s1 + p2 + 1)

And, as you yourself highlight, we then have the footnote:

而且,正如您自己强调的那样,我们有脚注:

The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

结果精度和标度的绝对最大值为38.当结果精度大于38时,相应的标度会减小,以防止结果的整数部分被截断。

So, here's what I produced in my spreadsheet:

那么,这就是我在电子表格中制​​作的内容:

p1 s1 p2 s2 prInit srInit prOver prAdjusted srAdjusted
38 16 38 16 93     55     55     38         6
28 16 28 16 73     45     35     38         10
29 16 29 16 75     46     37     38         9

So, I'm using pr and sr to indicate the precision and scale of the result. The prInit and srInit formulas are exactly the forumlas from the documentation. As we can see, in all 3 cases, the precision of the result is vastly larger than 38 and so the footnote applies. prOver is just max(0,prInit - 38) - how much we have to adjust the precision by if the footnote applies. prAdjusted is just prInit - prOver. We can see in all three cases that the final precision of the result is 38.

所以,我使用pr和sr来表示结果的精度和规模。 prInit和srInit公式正是文档中的论坛。我们可以看到,在所有3种情况下,结果的精确度远远大于38,因此脚注适用。 prOver只是max(0,prInit - 38) - 如果脚注适用,我们必须调整精度。 prAdjusted只是prInit - prOver。我们可以在所有三种情况下看到结果的最终精度为38。

If I apply the same adjustment factor to the scales then I would obtain results of 0, 10 and 9. But we can see that your result for the (38,16) case has a scale of 6. So I believe that that is where the max(6,... part of the documentation actually applies. So my final formula for srAdjusted is max(6,srInit-prOver) and now my final Adjusted values appear to match your results.

如果我将相同的调整因子应用于比例,那么我将获得0,10和9的结果。但是我们可以看到(38,16)案例的结果具有6的等级。所以我相信那就是最大值(6,...文档的一部分实际应用。所以我的srAdjusted的最终公式是max(6,srInit-prOver),现在我的最终调整值看起来与你的结果相符。

---

And, of course, if we consult the documentation for decimal, we can see that the default precision and scale, if you do not specify them, are (18,0), so here's the row for when you didn't specify precision and scale:

当然,如果我们查阅十进制的文档,我们可以看到默认的精度和标度,如果你没有指定它们,是(18,0),所以这里是你没有指定精度和规模:

p1 s1 p2 s2 prInit srInit prOver prAdjusted srAdjusted
18 0  18 0  37     19     0      37         19