sql - LOG 和 EXP 函数中的舍入问题

Question

我正在尝试执行累积乘法。我正在尝试两种方法来做到这一点

样本数据:

DECLARE @TEST TABLE
  (
     PAR_COLUMN INT,
     PERIOD     INT,
     VALUE      NUMERIC(22, 6)
  ) 
INSERT INTO @TEST VALUES 
(1,601,10 ),
(1,602,20 ),
(1,603,30 ),
(1,604,40 ),
(1,605,50 ),
(1,606,60 ),
(2,601,100),
(2,602,200),
(2,603,300),
(2,604,400),
(2,605,500),
(2,606,600)

注意:value列中的数据永远不会是整数，并且值将有小数部分。为了显示近似问题，我将示例值保留为整数。

<小时/>

方法一:EXP + LOG + SUM() Over(排序依据)

在此方法中，我使用EXP + LOG + SUM() Over(Order by)技术来查找累积乘法。此方法中的值不准确；结果存在一些舍入和近似问题。

SELECT *,
       Exp(Sum(Log(Abs(NULLIF(VALUE, 0))))
             OVER(
               PARTITION BY PAR_COLUMN
               ORDER BY PERIOD)) AS CUM_MUL
FROM   @TEST;

结果:

PAR_COLUMN  PERIOD  VALUE       CUM_MUL
----------  ------  ---------   ----------------
1           601     10.000000   10
1           602     20.000000   200             -- 10 * 20 = 200(correct)
1           603     30.000000   6000.00000000001 -- 200 * 30 = 6000.000000000 (not 6000.00000000001) incorrect
1           604     40.000000   240000
1           605     50.000000   12000000
1           606     60.000000   720000000.000001  -- 12000000 * 60 = 720000000.000000 (not 720000000.000001) incorrect
2           601     100.000000  100
2           602     200.000000  20000
2           603     300.000000  5999999.99999999 -- 20000.000000 *300.000000 = 6000000.000000 (not 5999999.99999999) incorrect
2           604     400.000000  2399999999.99999  
2           605     500.000000  1199999999999.99
2           606     600.000000  719999999999998

<小时/>

方法 2:传统乘法(递归 CTE)

此方法完美运行，没有任何舍入或近似问题。

;WITH CTE
     AS (SELECT TOP 1 WITH TIES PAR_COLUMN,
                                PERIOD,
                                VALUE,
                                CUM_MUL = VALUE
         FROM   @TEST
         ORDER  BY PERIOD
         UNION ALL
         SELECT T.PAR_COLUMN,
                T.PERIOD,
                T.VALUE,
                Cast(T.VALUE * C.CUM_MUL AS NUMERIC(22, 6))
         FROM   CTE C
                INNER JOIN @TEST T
                        ON C.PAR_COLUMN = T.PAR_COLUMN
                           AND T.PERIOD = C.PERIOD + 1)
SELECT *
FROM   CTE 
ORDER BY PAR_COLUMN,PERIOD

结果

PAR_COLUMN  PERIOD  VALUE       CUM_MUL
----------  ------  ---------   ----------------
1           601     10.000000   10.000000
1           602     20.000000   200.000000
1           603     30.000000   6000.000000
1           604     40.000000   240000.000000
1           605     50.000000   12000000.000000
1           606     60.000000   720000000.000000
2           601     100.000000  100.000000
2           602     200.000000  20000.000000
2           603     300.000000  6000000.000000
2           604     400.000000  2400000000.000000
2           605     500.000000  1200000000000.000000
2           606     600.000000  720000000000000.000000

<小时/>

谁能告诉我为什么方法 1 中的值不准确以及如何修复它？我尝试将数据类型更改为Float，并增加numeric中的scale，但没有用。

我真的很想使用方法 1，它比方法 2 快得多。

编辑:现在我知道近似的原因了。谁能找到解决此问题的方法吗？

Answer 1

在纯 T-SQL 中 LOG和EXP使用 float 进行操作类型(8字节)，其中只有15-17 significant digits 。如果对足够大的值求和，即使最后 15 位数字也可能变得不准确。您的数据是numeric(22,6) ，所以 15 位有效数字是不够的。

POWER可返回numeric具有潜在更高精度的类型，但这对我们来说没什么用，因为 LOG和LOG10只能返回float无论如何。

为了演示该问题，我将示例中的类型更改为 numeric(15,0)并使用POWER而不是EXP :

DECLARE @TEST TABLE
  (
     PAR_COLUMN INT,
     PERIOD     INT,
     VALUE      NUMERIC(15, 0)
  );

INSERT INTO @TEST VALUES 
(1,601,10 ),
(1,602,20 ),
(1,603,30 ),
(1,604,40 ),
(1,605,50 ),
(1,606,60 ),
(2,601,100),
(2,602,200),
(2,603,300),
(2,604,400),
(2,605,500),
(2,606,600);

SELECT *,
    POWER(CAST(10 AS numeric(15,0)),
        Sum(LOG10(
            Abs(NULLIF(VALUE, 0))
            ))
        OVER(PARTITION BY PAR_COLUMN ORDER BY PERIOD)) AS Mul
FROM @TEST;

结果

+------------+--------+-------+-----------------+
| PAR_COLUMN | PERIOD | VALUE |       Mul       |
+------------+--------+-------+-----------------+
|          1 |    601 |    10 |              10 |
|          1 |    602 |    20 |             200 |
|          1 |    603 |    30 |            6000 |
|          1 |    604 |    40 |          240000 |
|          1 |    605 |    50 |        12000000 |
|          1 |    606 |    60 |       720000000 |
|          2 |    601 |   100 |             100 |
|          2 |    602 |   200 |           20000 |
|          2 |    603 |   300 |         6000000 |
|          2 |    604 |   400 |      2400000000 |
|          2 |    605 |   500 |   1200000000000 |
|          2 |    606 |   600 | 720000000000001 |
+------------+--------+-------+-----------------+

这里的每一步都会失去精度。计算 LOG 会失去精度，SUM 会失去精度，EXP/POWER 会失去精度。对于这些内置函数，我认为您无能为力。

<小时/>

所以，答案是 - 将 CLR 与 C# 一起使用 decimal 类型(不是 double )，支持更高的精度(28-29 个有效数字)。您的原始 SQL 类型 numeric(22,6)会适合它。你不需要 LOG/EXP 的技巧.

<小时/>

哎呀。我尝试创建一个计算 Product 的 CLR 聚合。它在我的测试中有效，但仅作为一个简单的聚合，即

这有效:

SELECT T.PAR_COLUMN, [dbo].[Product](T.VALUE) AS P
FROM @TEST AS T
GROUP BY T.PAR_COLUMN;

甚至OVER (PARTITION BY)作品:

SELECT *,
    [dbo].[Product](T.VALUE) 
    OVER (PARTITION BY PAR_COLUMN) AS P
FROM @TEST AS T;

但是，使用 OVER (PARTITION BY ... ORDER BY ...) 运行产品不起作用(使用 SQL Server 2014 Express 12.0.2000.8 检查):

SELECT *,
    [dbo].[Product](T.VALUE) 
    OVER (PARTITION BY T.PAR_COLUMN ORDER BY T.PERIOD 
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUM_MUL
FROM @TEST AS T;

Incorrect syntax near the keyword 'ORDER'.

搜索发现了这个connect item ，它被关闭为“不会修复”，并且这个 question .

<小时/>

C# 代码:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections.Generic;
using System.Text;

namespace RunningProduct
{
    [Serializable]
    [SqlUserDefinedAggregate(
        Format.UserDefined,
        MaxByteSize = 17,
        IsInvariantToNulls = true,
        IsInvariantToDuplicates = false,
        IsInvariantToOrder = true,
        IsNullIfEmpty = true)]
    public struct Product : IBinarySerialize
    {
        private bool m_bIsNull; // 1 byte storage
        private decimal m_Product; // 16 bytes storage

        public void Init()
        {
            this.m_bIsNull = true;
            this.m_Product = 1;
        }

        public void Accumulate(
            [SqlFacet(Precision = 22, Scale = 6)] SqlDecimal ParamValue)
        {
            if (ParamValue.IsNull) return;

            this.m_bIsNull = false;
            this.m_Product *= ParamValue.Value;
        }

        public void Merge(Product other)
        {
            SqlDecimal otherValue = other.Terminate();
            this.Accumulate(otherValue);
        }

        [return: SqlFacet(Precision = 22, Scale = 6)]
        public SqlDecimal Terminate()
        {
            if (m_bIsNull)
            {
                return SqlDecimal.Null;
            }
            else
            {
                return m_Product;
            }
        }

        public void Read(BinaryReader r)
        {
            this.m_bIsNull = r.ReadBoolean();
            this.m_Product = r.ReadDecimal();
        }

        public void Write(BinaryWriter w)
        {
            w.Write(this.m_bIsNull);
            w.Write(this.m_Product);
        }
    }
}

安装 CLR 程序集:

-- Turn advanced options on
EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 1 ;
GO
RECONFIGURE WITH OVERRIDE ;
GO
-- Enable CLR
EXEC sys.sp_configure @configname = 'clr enabled', @configvalue = 1 ;
GO
RECONFIGURE WITH OVERRIDE ;
GO

CREATE ASSEMBLY [RunningProduct]
AUTHORIZATION [dbo]
FROM 'C:\RunningProduct\RunningProduct.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE AGGREGATE [dbo].[Product](@ParamValue numeric(22,6))
RETURNS numeric(22,6)
EXTERNAL NAME [RunningProduct].[RunningProduct.Product];
GO

<小时/>

这个question详细讨论了运行 SUM 的计算和 Paul White shows in his answer如何编写一个 CLR 函数来高效地计算运行 SUM。对于编写计算运行 Product 的函数来说，这将是一个良好的开始。

请注意，他使用了不同的方法。 Paul 没有创建自定义聚合函数，而是创建了一个返回表的函数。该函数将原始数据读入内存并执行所有所需的计算。

通过使用您选择的编程语言在客户端实现这些计算，可能会更容易达到预期的效果。只需阅读整个表格并计算客户端上的运行产品即可。如果在服务器上计算的运行产品是进一步聚合数据的更复杂计算的中间步骤，那么创建 CLR 函数就有意义。

<小时/>

我又想到了一个想法。

查找提供 Log 的第三方 .NET 数学库和Exp具有高精度的功能。制作这些标量函数的 CLR 版本。然后使用EXP + LOG + SUM() Over (Order by)方法，其中 SUM是内置的T-SQL函数，支持Over (Order by)和Exp和Log是不返回 float 的自定义 CLR 函数，但是高精度decimal .

请注意，高精度计算也可能会很慢。在查询中使用 CLR 标量函数也可能会使其变慢。