SQL Server：使用数字文字进行计算

发布于 2024-07-26 03:41:05 字数 651 浏览 2 评论 0原文

我用浮点计算做了一些测试，以尽量减少精度损失。我偶然发现了一个现象，我想在这里展示并希望得到解释。

当我编写

print 1.0 / (1.0 / 60.0)

结果时，

60.0024000960

当我编写相同的公式并显式转换为 float 时，

print cast(1.0 as float) / (cast(1.0 as float) / cast(60.0 as float))

结果为

到目前为止，我认为带小数位的数字文字会自动视为 float具有适当精度的值。转换为 real 显示的结果与转换为 float 相同。

是否有一些关于 SQL Server 如何计算数字文字的文档？
这些文字是什么数据类型？
我真的必须将它们转换为 float 以获得更好的精度吗（这对我来说听起来很讽刺:)？
有没有比用强制转换弄乱我的公式更简单的方法？

原文

I did some testing with floating point calculations to minimize the precision loss. I stumbled across a phenomen I want to show here and hopefully get an explanation.

When I write

print 1.0 / (1.0 / 60.0)

the result is

60.0024000960

When I write the same formula and do explicit casting to float

print cast(1.0 as float) / (cast(1.0 as float) / cast(60.0 as float))

the result is

Until now I thought that numeric literals with decimal places are automatically treated as float values with the appropriate precision. Casting to real shows the same result as casting to float.

Is there some documentation on how SQL Server evaluates numeric literals?
Of what datatype are those literals?
Do I really have to cast them to float get better precision (which sounds like irony to me :)?
Is there an easier way than cluttering my formulas with casts?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

同展鸳鸯锦 2024-08-02 03:41:05

SQL Server 使用尽可能小的数据类型。

当您运行此脚本时，

SELECT SQL_VARIANT_PROPERTY(1.0, 'BaseType')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Precision')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Scale')
SELECT SQL_VARIANT_PROPERTY(1.0, 'TotalBytes')

您将看到 SQL Server 隐式使用 NUMERIC(2, 1) 数据类型。
除以 60.0 将结果转换为 NUMERIC(8, 6)。
最终计算将结果转换为 NUMERIC(17, 10)。

编辑

摘自 SQL Server 联机丛书数据类型转换

在 Transact-SQL 语句中，常量
自动带有小数点
转换为数值数据值，
使用最小精度和小数位数
必要的。例如，常数
12.345 转换为精度为 5 的数值
规模为 3。

SQL Server uses the smallest possible datatype.

When you run this script

SELECT SQL_VARIANT_PROPERTY(1.0, 'BaseType')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Precision')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Scale')
SELECT SQL_VARIANT_PROPERTY(1.0, 'TotalBytes')

you'll see that SQL Server implicitly used a NUMERIC(2, 1) datatype.
The division by 60.0 converts the result to NUMERIC(8, 6).
The final calculation converts the result to NUMERIC(17, 10).

Edit

Taken from SQL Server Books Online Data Type Conversion

In Transact-SQL statements, a constant
with a decimal point is automatically
converted into a numeric data value,
using the minimum precision and scale
necessary. For example, the constant
12.345 is converted into a numeric value with a precision of 5 and a
scale of 3.

回复收藏 0 原文

゛清羽墨安 2024-08-02 03:41:05

是的，您经常需要将它们投射到浮动以获得更好的精度。我的看法：

为了在计算之前更精确地转换小数

回复收藏 0 原文

廻憶裏菂餘溫 2024-08-02 03:41:05

我认为应该了解一下幕后发生的事情，以便以后类似案件时参考。

带小数点（不包括科学记数法）的文字数值表示 Decimal 数据类型，它存储为尽可能小的 Decimal 类型。与 Lieven Keersmaekers 的引述相同：
https://msdn.microsoft.com/en -us/library/ms191530%28SQL.90%29.aspx#_decimal

在 Transact-SQL 语句中，带小数点的常量是
自动转换为数值数据值，使用最小值
必要的精度和规模。例如，常数 12.345 是
转换为精度为 5、小数位数为 3 的数值。

小数点右侧的尾随零指定小数位数。小数点左侧的前导零将被忽略。

一些示例：

1.0  -> Decimal(2,1)
60.0 -> Decimal(3,1)
1.00 -> Decimal(3,2)
01.0 -> Decimal (2,1)

另一点需要考虑的是数据类型优先级。
当运算符组合两个不同数据类型的表达式时，数据类型优先级规则指定优先级较低的数据类型转换为优先级较高的数据类型。
还要考虑的另一点是，如果我们对 Decimal 类型进行算术运算，则生成的 Decimal 类型（即精度和小数位数）取决于操作数和运算本身。文档精度、比例和长度对此进行了描述。

因此，括号中的表达式的一部分

( 1.0 / 60.0 ) is evaluated to 0.016666 and the resulting type is Decimal (8,6)

使用上述有关十进制表达式的精度和小数位数的规则。此外，还使用银行四舍五入或四舍五入到偶数。重要的是要注意 Decimal 和 float 类型使用不同的舍入。
如果我们继续表达式

1.0 / 0.016666 is evaluated to 60.002400096 and the resulting type is Decimal (17,10)

，则部分差异是由于十进制类型与浮点类型使用不同的舍入造成的。

根据上述规则，只需在括号内使用一次强制转换就足够了。所有其他文字将根据数据类型优先级规则提升为浮点数。

1.0 / (1.0 / cast(60.0 as float))

还有一件重要的事情。即使这个浮点表达式也不能计算出精确的结果。只是为了让前端（SSMS 或其他）将值四舍五入到（我猜）精度 6 位，然后截断尾随零。所以 ie 1.000001 变成 1。

很简单，不是吗？

I think it should be understood what is going on behind the scenes for future reference in similar cases.

Literal numerical values with decimal point excluding scientific notation represent Decimal data type which is stored as smallest possible Decimal type. Same quote as Lieven Keersmaekers's from:
https://msdn.microsoft.com/en-us/library/ms191530%28SQL.90%29.aspx#_decimal

In Transact-SQL statements, a constant with a decimal point is
automatically converted into a numeric data value, using the minimum
precision and scale necessary. For example, the constant 12.345 is
converted into a numeric value with a precision of 5 and a scale of 3.

The trailing zeros on the right of decimal point specify scale. The leading zeros left of decimal point are ignored.

Some examples:

1.0  -> Decimal(2,1)
60.0 -> Decimal(3,1)
1.00 -> Decimal(3,2)
01.0 -> Decimal (2,1)

Another point to consider is Data Type precedence.
When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.
And yet another point to consider is if we do arithmetic operations on Decimal types that the resulting Decimal type, i.e. precision and scale depend on both operands and operation itself. This is described in document Precision, Scale, and Length.

So, part of your expression in parenthesis

( 1.0 / 60.0 ) is evaluated to 0.016666 and the resulting type is Decimal (8,6)

using above rules about Precision and scale of Decimal expressions. In addition the banker's rounding or rounding to even is used. It is important to note different rounding for Decimal and float type are used.
If we continue the expression

1.0 / 0.016666 is evaluated to 60.002400096 and the resulting type is Decimal (17,10)

So the part of the discrepancy is due to different rounding being used for decimal types than for float.

In accordance to the above rules it would be sufficient to use just one cast inside parenthesis. Every other literal will be promoted to float in accordance with Data Type Precedence rules.

1.0 / (1.0 / cast(60.0 as float))

And one more IMPORTANT thing. Even this float expression does not calculate exact result. It is just so that the front end (SSMS or whatever) rounds the value to (I guess) precision 6 digits and then truncates trailing zeros. So i.e. 1.000001 becomes 1.

Simple, isn't it?

回复收藏 0 原文