SQL Server:十进制精度/小数位数产生奇怪的结果 [英] SQL Server : Decimal Precision/Scale are yielding strange results

问题描述

我正在为一个项目编写一些 SQL，我注意到 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.

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

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

对于上面的代码，(似乎)不太精确的查询答案给出了更精确的值作为答案.当我将被除数和除数都转换为 Decimal(38,16) 时，我得到一个小数位数为 6 的数字(将其转换为 Decimal(38,16)> 再次导致 0 填充比例).

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).

当我将被除数和除数转换为默认的小数时，没有手动设置精度或小数位数，我得到了结果小数位数中的完整 16 位数字.

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.

出于好奇，我开始使用以下查询对其进行更多试验:

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.

我发现其他一些 SO 问题指向 msdn 文档中的某个位置，其中指出在对小数进行运算期间产生的精度和小数位数是通过对除数和被除数的精度和小数位数执行一组计算来确定的，以及:

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:

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

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.

所以我尝试自己运行这些方程来确定将 Decimal(38,16) 划分为另一个 Decimal(38,16) 的输出会是什么样子，根据我的发现，我仍然应该得到一个比我更精确的数字.

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.

所以我要么做错了数学，要么这里发生了一些我遗漏的事情.如果您能提供任何见解，我将不胜感激.

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.

提前致谢...

推荐答案

文档 关于值 6 的魔力以及何时应用 max 函数有点不完整，但这是我的发现表，基于该文档.

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.

正如它所说，除法的公式是:

As it says, the formulas for division are:

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

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

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

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

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

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.

所以，这是我在电子表格中生成的内容:

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

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

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.

如果我将 same 调整因子应用于比例，那么我将获得 0、10 和 9.但是我们可以看到 (38,16) 案例的结果具有 6 的比例.所以我相信这就是文档的 max(6,... 部分实际适用的地方.所以我的 srAdjusted 的最终公式是 max(6,srInit-prOver) 现在我的最终 Adjusted 值似乎与您的结果相符.

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.

当然，如果我们查阅decimal<的文档/code>，我们可以看到默认的精度和小数位数，如果不指定，都是(18,0)，所以这里是行当您没有指定精度和比例时:

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:

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

这篇关于SQL Server:十进制精度/小数位数产生奇怪的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！