sql-server - SQL Server 数字舍入问题

Question

这是 [ TSQL number rounding issue 的后续问题。这是相同的代码:

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

当你执行代码时，你将得到:

5745
5746

但是，当您在函数中将数据类型从 float 更改为 real 时，如下所示:

IF OBJECT_ID(N'dbo.rounding_testing') IS NOT NULL
    DROP FUNCTION dbo.rounding_testing;
GO
CREATE FUNCTION dbo.rounding_testing
(
    @value REAL,
    @digit INT
)
RETURNS REAL
BEGIN
    DECLARE
        @factor REAL,
        @result REAL;
    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);

执行时你会得到这个:

5746
5746

针对该问题下的两个答案，我又做了一些测试，发现自己还是不太清楚。首先我想说我已经阅读了有关浮点和实型类型以及数字和十进制类型的msdn文档。我现在知道 SQL Server 如何在内部存储它们。对于float 和 real 类型，使用 IEEE 754 标准。对于小数和数字类型，请参阅 How does SQL Server store decimal type values internally? 。我想知道在 float 情况下哪个确切步骤导致了精度损失。所以我创建了一个像这样的表:

USE tempdb;
GO

IF OBJECT_ID('dbo.mytable') IS NOT NULL
    DROP TABLE dbo.mytable;
CREATE TABLE dbo.mytable
(
    a NUMERIC(5, 4),
    b FLOAT,
    c FLOAT,
    d FLOAT,
    e FLOAT,
    f REAL,
    g REAL,
    h REAL,
    i REAL
);
GO

然后我手动将中间数据插入到该表中。

INSERT INTO dbo.mytable
VALUES(
    5.7456,
    CAST(5.7456 AS FLOAT),
    CAST(POWER(10, 3) AS FLOAT),
    CAST(CAST(5.7456 AS FLOAT) * CAST(POWER(10, 3) AS FLOAT) AS FLOAT),
    CAST(CAST(5.7456 AS FLOAT) * CAST(POWER(10, 3) AS FLOAT) + 0.4 AS FLOAT),
    CAST(5.7456 AS REAL),
    CAST(POWER(10, 3) AS REAL),
    CAST(CAST(5.7456 AS REAL) * CAST(POWER(10, 3) AS REAL) AS REAL),
    CAST(CAST(5.7456 AS REAL) * CAST(POWER(10, 3) AS REAL) + 0.4 AS REAL));

之后，我使用DBCC PAGE 来调查我插入的行。以下是该行的原始数据:

0000000000000000:   10003900 0170e000 002497ff 907efb16 40000000  ..9..pà..$ÿ.~û.@...
0000000000000014:   0000408f 40999999 999971b6 40ffffff ffff71b6  ..@.@.....q¶@ÿÿÿÿÿq¶
0000000000000028:   40f5dbb7 4000007a 44cd8cb3 450090b3 45090000  @õÛ·@..zDͳE..³E  ..
000000000000003C:   00                                            .      

这是原始数据的解释:

Column Stuff inserted                                                          Hex (little endian)     Interpretation
------ ----------------------------------------------------------------------- ----------------------- --------------                                                                                  
a      5.7456                                                                  01 70 E0 00 00          Decimal 57456, the decimal point position is stored in catalog view                                                                    
b      CAST(5.7456 AS FLOAT)                                                   24 97 FF 90 7E FB 16 40 IEEE 754 double precision format, 5.7456                                                                             
c      CAST(POWER(10, 3) AS FLOAT)                                             00 00 00 00 00 40 8F 40 IEEE 754 double precision format, 1000                                                                           
d      CAST(CAST(5.7456 AS FLOAT) * CAST(POWER(10, 3) AS FLOAT) AS FLOAT)      99 99 99 99 99 71 B6 40 IEEE 754 double precision format, 5745.6                                                                             
e      CAST(CAST(5.7456 AS FLOAT) * CAST(POWER(10, 3) AS FLOAT) + 0.4 AS FLOAT)FF FF FF FF FF 71 B6 40 IEEE 754 double precision format, 5746                                                                             
f      CAST(5.7456 AS REAL)                                                    F5 DB B7 40             IEEE 754 single precision format, 5.7456                                                                 
g      CAST(POWER(10, 3) AS REAL)                                              00 00 7A 44             IEEE 754 single precision format, 1000                                                                   
h      CAST(CAST(5.7456 AS REAL) * CAST(POWER(10, 3) AS REAL) AS REAL)         CD 8C B3 45             IEEE 754 single precision format, 5745.6                            
i      CAST(CAST(5.7456 AS REAL) * CAST(POWER(10, 3) AS REAL) + 0.4 AS REAL))  00 90 B3 45             IEEE 754 single precision format, 5746                            

从十六进制解释来看，在我看来，任何步骤都没有精度损失，无论它是float还是real。那么精度损失到底来自哪里呢？

Answer 1

最接近 5.7456 的实数(单精度)值为十六进制 40b7dbf5，十进制为 5.745600223541259765625。

最接近 5.7456 的浮点( double )值是十六进制 4016fb7e90ff9724，十进制为 5.745599999999999596411726088263094425201416015625。

(使用我的 floating-point converter 来验证这些:输入 5.7456 并选中“ double ”和“单精度”框，然后选择“十进制”和“原始十六进制”输出框。)

您可以看到 double 值小于 5.7456，这是问题的根源(即为什么您得到 5745 作为答案)。

计算结果 5.7456 * 1000 单精度为 5745.60009765625， double 为 5745.5999999999994543031789362430572509765625。

0.4 在单精度中为 0.4000000059604644775390625，在 double 中为 0.40000000000000002220446049250313080847263336181640625。

5.7456 * 1000 + 0.4 单精度为 5746， double 为 5745.9999999999990905052982270717620849609375。

(我使用 C 程序来进行这些计算。)

因此，差异是由于值的转换方式和计算结果以两种精度舍入的方式组合造成的。

(你说“从十六进制解释来看，在我看来，任何步骤都没有精度损失”......我不知道你的意思。)