为什么Clng在这些场景下工作不一样，在SQL Server中能重现吗?(不是银行家的四舍五入) [英] Why Does Clng Work Differently In These Scenarios And Can It Be Reproduced In SQL Server? (Not Banker's Rounding)

问题描述

执行以下语句会导致 Access SQL:

CLNG((CCUR(1.225)/1)*100) = 123

转换，Decimal > Currency > Double > Double > Long

如果我删除了 CCUR 转换函数:

CLNG(((1.225)/1)*100) = 122

这里的转换是，Decimal > Double > Double > Long

这两者有什么区别?

这扩展到代码和访问 SQL 之间的不同

在访问 SQL 中

 clng((CCUR(1.015)/1)*100)/100 = 1.01(四舍五入错误)

在访问 VBA

 clng((CCUR(1.015)/1)*100)/100 = 1.02(此处适当四舍五入)

Microsoft 解释说 CLng 函数使用 银行家的舍入，此处.

<块引用>

当小数部分正好是 0.5 时，CInt 和 CLng 总是将其四舍五入到最接近的偶数.例如，0.5 舍入为 0，1.5 舍入为 2.CInt 和 CLng 与 Fix 和 Int 函数不同，后者截断而不是舍入数字的小数部分.此外，Fix 和 Int 始终返回与传入的类型相同的值.

看一个类似的问题和随后的回答HERE，说明后面的位计算有变化场景，基于它的计算方式，但我不确定数据类型如何影响它.

我错过了什么，为什么要这样计算?如何在 SQL Server 中以可预测的方式重现这种行为?

编辑

经过一番挖掘，我相信这确实是舍入点问题的结果.在 SQL Server 中，如果浮点数超出 15 位最大精度，它会将浮点数四舍五入到最接近的整数.尽管 Double 等同于 TSQL 中的 Float(53)，但访问似乎以某种方式保持更多.

解决方案

结果的差异是两个不同问题的组合:Jet/ACE 与 VBA 表达式评估和十进制数的二进制浮点表示.

第一个是 Jet/ACE 表达式引擎隐式地将小数转换为 Decimal，而 VBA 将它们转换为 Double.这很容易演示(注意 Eval() 函数使用 Jet/ACE db 引擎计算表达式):

?Typename(1.015), eval("typename(1.015)")双十进制

第二个问题是浮点运算.这有点难以证明，因为 VBA 总是舍入其输出，但使用另一种语言(在本例中为 Python)时问题更明显:

<预><代码>>>>从十进制导入十进制>>>十进制(1.015)十进制('1.0149999999999999023003738329862244427204132080078125')

VBA 中的 Double 类型使用浮点运算，而 Decimal 类型使用整数运算(它在后台存储小数点的位置).

结果是银行家的四舍五入或传统的四舍五入是一个红鲱鱼.决定因素是数字的二进制浮点表示是略大于还是小于十进制表示.

<小时>

要了解它在原始问题中的工作原理，请参阅以下 VBA:

?Eval("typename((CCUR(1.225)/1))"), Eval("typename(((1.225)/1))")双十进制?Eval("typename(CCUR(1.225))"), Eval("typename(1.225)")货币十进制

和 Python:

<预><代码>>>>十进制 (1.225)十进制('1.22500000000000088817841970012523233890533447265625')

<小时>

我还应该指出，您在第二个示例中对转换为 Double 的假设是不正确的.数据类型保持Decimal，直到最终转换为Long.前两个函数之间的区别在于，在 Jet/ACE 中将 Decimal 乘以 Currency 类型得到 Double. 这对我来说似乎有点奇怪，但代码证明了这一点:

?eval("TypeName(1.225)"), eval("TypeName(1.225)")十进制 十进制?eval("TypeName(CCUR(1.225))"), eval("TypeName((1.225))")货币十进制?eval("TypeName(CCUR(1.225)/1)"), eval("TypeName((1.225)/1)")双十进制?eval("TypeName((CCUR(1.225)/1)*100)"), eval("TypeName(((1.225)/1)*100)")双十进制?eval("TypeName(CLNG((CCUR(1.225)/1)*100))"), eval("TypeName(CLNG(((1.225)/1)*100))")龙龙

所以两种情况的转换实际上是:

Decimal > Currency > Double > Double > Long (就像你正确假设)；和

Decimal > Decimal > Decimal > Decimal > Long (更正你的初始假设).

<小时>

要在下面的评论中回答您的问题，Eval() 使用与 Jet/ACE 相同的表达式引擎，因此它在功能上等同于在 Access 查询中输入相同的公式.为了进一步证明，我提出以下内容:

SELECTTypeName(1.225) 为 A1，TypeName(CCUR(1.225)) 为 A2，TypeName(CCUR(1.225)/1) 为 A3，TypeName((CCUR(1.225)/1)*100) as A4,TypeName(CLNG((CCUR(1.225)/1)*100)) as A5

SELECTTypeName(1.225) 为 B1，TypeName((1.225)) 作为 B2，TypeName((1.225)/1) 作为 B3，TypeName(((1.225)/1)*100) 作为 B4，TypeName(CLNG(((1.225)/1)*100)) as B5

Executing the following statement results in Access SQL:

CLNG((CCUR(1.225)/1)*100) = 123

The Conversion Goes, Decimal > Currency > Double > Double > Long

If I remove the CCUR conversion function:

CLNG(((1.225)/1)*100) = 122

The Conversion here goes , Decimal > Double > Double > Long

What is the difference between these two?

This extends to being different between Code And Access SQL

In Access SQL

 clng((CCUR(1.015)/1)*100)/100 = 1.01 (Wrong Rounding)

In Access VBA

 clng((CCUR(1.015)/1)*100)/100 = 1.02 (Appropriate Rounding Here)

Microsoft explain that the CLng function uses Banker's Rounding, here.

When the fractional part is exactly 0.5, CInt and CLng always round it to the nearest even number. For example, 0.5 rounds to 0, and 1.5 rounds to 2. CInt and CLng differ from the Fix and Int functions, which truncate, rather than round, the fractional part of a number. Also, Fix and Int always return a value of the same type as is passed in.

Looking at a similar question and the subsequent answer HERE, it explains that there are changes to the bit calculation behind the scenes, based on how it is calculated, but I'm not sure how the data type effects it.

What am I missing, and why is it calculating this way? How could I reproduce this behavior predictably in SQL Server?

EDIT

After some digging I believe that this is truly the result of a rounding point issue. In SQL server it will round floats to the nearest whole number if it is outside of the 15 digit max of precision. Access seems to hold more somehow, even though a Double is equivalent to a Float(53) in TSQL.

解决方案

The difference in results is a combination of two different issues: Jet/ACE vs VBA expression evaluation and binary floating point representation of decimal numbers.

The first is that the Jet/ACE expression engine implicitly converts fractional numbers to Decimal while VBA converts them to Double. This can be easily demonstrated (note the Eval() function evaluates an expression using the Jet/ACE db engine):

?Typename(1.015), eval("typename(1.015)")
Double        Decimal

The second issue is that of floating point arithmetic. This is somewhat more difficult to demonstrate because VBA always rounds its output, but the issue is more obvious using another language (Python, in this case):

>>> from decimal import Decimal
>>> Decimal(1.015)
Decimal('1.0149999999999999023003738329862244427204132080078125')

The Double type in VBA uses floating-point arithmetic, while the Decimal type uses integer arithmetic (it stores the position of the decimal point behind the scenes).

The upshot to this is that Banker's rounding or traditional rounding is a red herring. The determining factor is whether the binary floating point representation of the number is slightly greater or less than its decimal representation.

---

To see how this works in your original question see the following VBA:

?Eval("typename((CCUR(1.225)/1))"), Eval("typename(((1.225)/1))")
Double        Decimal
?Eval("typename(CCUR(1.225))"), Eval("typename(1.225)") 
Currency      Decimal

And Python:

>>> Decimal(1.225)
Decimal('1.225000000000000088817841970012523233890533447265625')

---

I should also point out that your assumption of the conversion to Double in your second example is incorrect. The data type remains Decimal until the final conversion to Long. The difference between the first two functions is that multiplying a Decimal by a Currency type in Jet/ACE results in a Double. This seems like somewhat odd behavior to me, but the code bears it out:

?eval("TypeName(1.225)"), eval("TypeName(1.225)")
Decimal       Decimal

?eval("TypeName(CCUR(1.225))"), eval("TypeName((1.225))")
Currency      Decimal

?eval("TypeName(CCUR(1.225)/1)"), eval("TypeName((1.225)/1)")
Double        Decimal

?eval("TypeName((CCUR(1.225)/1)*100)"), eval("TypeName(((1.225)/1)*100)")
Double        Decimal

?eval("TypeName(CLNG((CCUR(1.225)/1)*100))"), eval("TypeName(CLNG(((1.225)/1)*100))")
Long          Long

So the conversion in the two cases is actually:

Decimal > Currency > Double > Double > Long (as you correctly assumed); and

Decimal > Decimal > Decimal > Decimal > Long (correcting your initial assumption).

---

To answer your question in the comment below, Eval() uses the same expression engine as Jet/ACE, so it is functionally equivalent to entering the same formula in an Access query. For further proof, I present the following:

SELECT
TypeName(1.225) as A1,
TypeName(CCUR(1.225)) as A2, 
TypeName(CCUR(1.225)/1) as A3,
TypeName((CCUR(1.225)/1)*100) as A4, 
TypeName(CLNG((CCUR(1.225)/1)*100)) as A5

SELECT
TypeName(1.225) as B1,
TypeName((1.225)) as B2,
TypeName((1.225)/1) as B3,
TypeName(((1.225)/1)*100) as B4,
TypeName(CLNG(((1.225)/1)*100)) as B5

这篇关于为什么Clng在这些场景下工作不一样，在SQL Server中能重现吗?(不是银行家的四舍五入)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！