您可以四舍五入为您的数据：
--720000000000000 must be multiple of 600select 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_MAX3FROM 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_MULFROM 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可以不使用小数
