我正在尝试执行累积乘法.我正在尝试两种方法来做到这一点
样本数据：
DECLARE @TEST TABLE
(
PAR_COLUMN INT,PERIOD INT,VALUE NUMERIC(22,6)
)
INSERT INTO @TEST VALUES
(1,601,10 ),(1,602,20 ),603,30 ),604,40 ),605,50 ),606,60 ),(2,100),200),300),400),500),600)
注意：值列中的数据永远不会是整数,值将具有小数部分.为了显示近似问题,我将示例值保持为整数.
方法1：EXP LOG SUM()Over(Order by)
在这个方法中我使用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：Tradictional Multiplication(递归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,并通过增加数字的比例但没有用.
解决方法
您可以为您的数据舍入到大倍数：
--720000000000000 must be multiple of 600 select round( 719999999999998/600,0 ) * 600 --result: 720000000000000
create TABLE T
(
PAR_COLUMN INT,6)
)
INSERT INTO T VALUES
(1,10.1 ),--<--- I put decimals just to test!
(1,600)
查询1：
with T1 as (
SELECT *,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,2,1.)
OVER(
PARTITION BY PAR_COLUMN
ORDER BY PERIOD ) AS CUM_MAX3
FROM T )
select PAR_COLUMN,( 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工作没有小数
