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列中 的数据永远不会是整数，并且值将具有小数部分。为了显示近似问题，我将示例值保留为整数。

方法1：EXP + LOG + SUM（）结束（排序依据）

在这种方法中，我使用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并增加scalein，numeric但没有用。

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

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

Answer 1

您可以四舍五入为您的数据：

--720000000000000 must be multiple of 600

select
   round( 719999999999998/600,  0 ) * 600

--result: 720000000000000

在SQLFiddle上进行测试

create TABLE T 
  (
     PAR_COLUMN INT,
     PERIOD     INT,
     VALUE      NUMERIC(22, 6)
  ) 
INSERT INTO T VALUES 
(1,601,10.1 ),    --<--- I put decimals just to test!
(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)

查询1 ：

with T1 as (
SELECT *,
       Exp(Sum(Log(Abs(NULLIF(VALUE, 0))))
             OVER(
               PARTITION BY PAR_COLUMN
               ORDER BY PERIOD)) AS CUM_MUL,
       VALUE AS CUM_MAX1,
       LAG( VALUE , 1, 1.) 
             OVER(
               PARTITION BY PAR_COLUMN
               ORDER BY PERIOD ) AS CUM_MAX2,
       LAG( VALUE , 2, 1.) 
             OVER(
               PARTITION BY PAR_COLUMN
               ORDER BY PERIOD ) AS CUM_MAX3
FROM   T )
select PAR_COLUMN,  PERIOD,  VALUE, 
       ( round( ( CUM_MUL  / ( CUM_MAX1 * CUM_MAX2 * CUM_MAX3) ) ,6) 
         * 
         cast( ( 1000000 * CUM_MAX1 * CUM_MAX2 * CUM_MAX3) as bigint )
       ) / 1000000.
       as CUM_MUL
FROM T1

结果 ：

| PAR_COLUMN | PERIOD | VALUE |         CUM_MUL |
|------------|--------|-------|-----------------|
|          1 |    601 |  10.1 |            10.1 | --ok! because my data
|          1 |    602 |    20 |             202 |
|          1 |    603 |    30 |            6060 |
|          1 |    604 |    40 |          242400 |
|          1 |    605 |    50 |        12120000 |
|          1 |    606 |    60 |       727200000 |
|          2 |    601 |   100 |             100 |
|          2 |    602 |   200 |           20000 |
|          2 |    603 |   300 |         6000000 |
|          2 |    604 |   400 |      2400000000 |
|          2 |    605 |   500 |   1200000000000 |
|          2 |    606 |   600 | 720000000000000 |

注意我x1000000可以不使用小数