TSQL号码舍入问题 [英] TSQL number rounding issue

问题描述

我有一段代码：

  IF OBJECT_ID（N'dbo.rounding_testing'）不是NULL 
 DROP功能dbo.rounding_testing; 
 GO 
 CREATE FUNCTION dbo.rounding_testing 
（
 @value FLOAT，
 @digit INT 
）
 RETURNS FLOAT 
 BEGIN 
 DECLARE 
 @factor FLOAT，
 @result FLOAT; 
 SELECT @factor = POWER（10，@digit）; 
 
 SELECT @result = FLOOR（@value * @factor + 0.4）; 
 
 RETURN @result; 
 END; 
 GO 
 
 SELECT dbo.rounding_testing（5.7456，3）; 
 SELECT FLOOR（5.7456 * 1000 + 0.4）; 
  

结果是：

pre > 5745
5746


我期待两个5746.我试图调试该功能，并发现一些有趣的行为。下面是我在调试时在 Immediate Window 中做的一些测试。

  @factor 
 1.000000000000000e + 003 
 @result 
 5.745000000000000e + 003 
 @value 
 5.745600000000000e + 000 
 @ value * @ factor 
 5745.6 
 @ value * @ factor + 0.4 
 5746 
 floor（@ value * @ factor + 0.4）
 5745 
 floor（5746）
 5746 
  

任何人都可以帮忙解释结果吗？尤其是这三行：

pre $ factor $ 0.4 $ b $ 5746
floor（@ value * @ factor + 0.4）
5745
floor（5746）
5746

FLOOR（5.7456 * 1000 + 0.4）; 表达式中，首先计算括号之间的部分。对于常量，数据类型是基于符号推断的;为5.7456，即 decimal（5,4）; 1000是 int ;而0.4是 decimal（1,1）。 5.7456 * 1000的推断数据类型是 decimal（10,4）;对于完整表达式来说，它是 decimal（11,4）。这些都是精确的数字数据类型，所以你不会经历任何舍入;确切地说，最终的结果是5746.0000。 FLOOR 函数修剪分数并转换为 decimal（11,0），返回5746。

在用户定义的函数中，将输入参数和中间结果存储在 float 数据类型（浮点数据）中。此数据类型旨在用于近似数据，如测量数据，您从装置中读取的数据已经是近似值。我已经在高中学到了尽可能多的数字，但是把最后一个数字看作是微不足道的 - 我必须把它保留在所有的计算中，但是根据我的测量结果精确到最后的结果。舍入确保最后一位数字的不准确性不会影响最终结果。
浮点数据类型应该以相同的方式处理。

在内部，浮点数位在基数2数字系统中表示。这意味着在我们常用的base-10系统（如5.7456）中有一些数字具有精确的表示形式，但是在base-2中却是一个永无止境的小数部分。 （类似于三分之一，它可以完全以3为基数表示，具有以10为底的永不完整的小数部分：0.33333333333（等））。用于存储 float 数字的基数为2的数字是有限的，因此必须在最后切断 - 这导致它被舍入或下降了一小部分。你可以看到这个如果你运行：

$ $ p $ DECLARE @a float = 5.7456;
SELECT CAST（@a十进制（19,16））;


在这种情况下，大量的基数为2的数字后，存储的值是0.0000000000000004，小于您输入的十进制值。由于 FLOOR 函数，这个小小的差异变成了一个巨大的效果，这正是它应该做的：最近的整数。

（我见过很多人称这是一个错误，不是这样，它是有目的和有记录的行为。也不比在 DECIMAL（7,6）中存储三分之一时所获得的精度损失更好，这一点不那么明显，因为我们都长大了习惯于以10为基础工作）

I have a piece of code:

IF OBJECT_ID(N'dbo.rounding_testing') IS NOT NULL
    DROP FUNCTION dbo.rounding_testing;
GO
CREATE FUNCTION dbo.rounding_testing
(
    @value FLOAT,
    @digit INT
)
RETURNS FLOAT
BEGIN
    DECLARE
        @factor FLOAT,
        @result FLOAT;
    SELECT @factor = POWER(10, @digit);

    SELECT @result = FLOOR(@value * @factor + 0.4);

    RETURN @result;
END;
GO

SELECT dbo.rounding_testing(5.7456, 3);
SELECT FLOOR(5.7456 * 1000 + 0.4);

The results are:

5745
5746

I'm expecting two 5746. I tried to debug the function and found some interesting behavior. Below are some testing I did in the Immediate Window when debugging.

@factor
1.000000000000000e+003
@result
5.745000000000000e+003
@value
5.745600000000000e+000
@value*@factor
5745.6
@value*@factor+0.4
5746
floor(@value*@factor+0.4)
5745
floor(5746)
5746

Can anyone help to explain the result? Especially these three lines:

@value*@factor+0.4
5746
floor(@value*@factor+0.4)
5745
floor(5746)
5746

解决方案

In the expression FLOOR(5.7456 * 1000 + 0.4);, the part between parentheses is evaluated first. For constants the data types are inferred based on the notation; for 5.7456 that is decimal(5,4); 1000 is an int; and 0.4 is decimal(1,1). The inferred data type for 5.7456 * 1000 is then decimal(10,4); and for the full expression it is decimal(11,4). These are all exact numeric data types so you will not experience any rounding; the end result is 5746.0000 exactly. The FLOOR function trims the fraction and converts to decimal(11,0), returning 5746.

In the user-defined function, you store input parameters and intermediate results in float data type (floating point data). This data type is intended to be used for approximate data, such as measurements, where the data you read from the intstrument is already an approximation. I have learned in high school to read as many digits as I can, but treat the last one as insignificant - I had to keep it in all computations, but round the final result to the number of significant digits based on the accuracy of my measurements. The rounding ensures that inaccuracies in the last digits will not affect the end result. Floating point data types should be treated in the same way.

Internally, floating point digits are represented in a base-2 number system. This means that there are numbers that have an exact representation in our commonly used base-10 system (such as 5.7456), but a never ending fractional part in base-2. (Similar to how for instance one third, which can be represented exactly in base-3, has a never ending fractional part in base-10: 0.33333333333(etc)). The number of base-2 digits used for storage of a float number is finite, so it has to be cut off at the end - which results in it being rounded either up or down by a tiny fraction. You can see this if you run:

DECLARE @a float = 5.7456;
SELECT CAST(@a AS decimal(19,16));

In this case, the effect of cutting off after a lot of base-2 digits is that the value stored is 0.0000000000000004 less than the decimal value you put in. That small difference turns into a huge effect because of the FLOOR function, which does exactly what it should do: round down to the nearest integer.

(I've seen a lot of people call this an error. It is not. It is intended and documented behavior. And the precision loss here is neither worse nor better than the precision loss you get when you store a third in a DECIMAL(7,6); it is just a bit less obvious because we have all grown up being used to working in base-10)

这篇关于TSQL号码舍入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！