LOG 和 EXP 函数中的舍入问题 [英] Rounding issue in LOG and EXP functions
问题描述
我正在尝试执行累积乘法.我正在尝试两种方法来做到这一点
I am trying to perform cumulative multiplication. I am trying two methods to do this
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 列中的数据永远不会是整数，并且值会有小数部分.为了显示近似问题，我将示例值保留为整数.
Note: The data in value column will never be integer and values will have decimal part. To show approximation problem I have kept example values as integers.
在此方法中，我使用 EXP + LOG + SUM() Over(Order by) 技术来查找累积乘法.在这种方法中，数值不准确；结果中存在一些舍入和近似问题.
In this method am using EXP + LOG + SUM() Over(Order by) technique to find cumulative multiplication. In this method values are not accurate; there is some rounding and approximation issue in the result.
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)
此方法完美运行，没有任何舍入或近似问题.
Method 2: Tradictional Multiplication (Recursive CTE)
This method works perfectly without any rounding or approximation problem.
;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 但没有用.
Can anyone tell me why in method 1 values are not accurate and how to fix it? I tried by changing the data types to Float and by increasing the scale in numeric but no use.
我真的很想使用方法 1，它比方法 2 快得多.
I really want to use method 1 which is much faster than method 2.
现在我知道近似值的原因了.任何人都可以找到解决此问题的方法吗?
Now I know the reason for approximation. Can anyone find a fix for this problem?
推荐答案
对于您的数据，您可以四舍五入到大倍数:
You can round to big multiple, for your data:
--720000000000000 must be multiple of 600
select
round( 719999999999998/600, 0 ) * 600
--result: 720000000000000
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 不用小数
这篇关于LOG 和 EXP 函数中的舍入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
