需要帮助进行以下计算 [英] Need Help for performing below calculation
问题描述
我有一个确定的值，例如10作为我的计算基础.现在，对应于值10的第一期间的增长为5.我想要的结果是10 *(1 + 5/100)，基本上是基数*(增长的1 +％).第一个期间的结果值将是下一个期间的新基准.假设下一个增长为6，则下一个周期的结果为(10(1 + 5/100))*(1+ 6/100).这基本上是运行乘法，可以使用多种方法来实现.现在，请有人提出实现此计算的最终最佳方法.
I have a certain value say 10 as a base for my calculation. Now the growth for the first period corresponding to value 10 is 5 . The resultant which i want is 10*(1+5/100) which is basically Base * (1+ % of Growth) . The resultant Value for the first period will be the new Base for the next period. Assuming the next growth be 6 , the result for the next period is (10(1+5/100)) * (1+ 6/100) . This is basically running multiplication and can be achieved using many ways. Now someone please suggest the ultimate best way to achieve this calculation.
- 10，5-> 10(1 + 5/100)= 10.50
- 10.50，6-> 10.50(1 + 6/100)= 11.1300
- 11.13，任何值，等等
我尝试使用其他数据样本但基本上运行乘法的方法.
The method I've tried using other data sample but which is running multiplication basically.
CREATE TABLE #t1
(
projection_details_sid INT,
period_sid INT,
growth NUMERIC(22, 6)
)
INSERT INTO #t1
(projection_details_sid,
period_sid,
growth)
VALUES ( 1,601,2 ),
( 1,602,2 ),
( 1,603,2 ),
( 1,604,1 ),
( 1,605,6 ),
( 1,606,3 )
SELECT *,
Exp(Sum(Log(growth))
OVER (
PARTITION BY projection_details_sid
ORDER BY projection_details_sid ROWS UNBOUNDED PRECEDING ))
FROM #t1
推荐答案
尝试递归查询.
下面的示例适用于Oracle，但可以轻松地用于SQL-Server.
Try a recursive query.
The below example is for Oracle, but it can be easily adopted to SQL-Server.
WITH our_recursive_query(projection_details_sid, period_sid, growth, base, our_result)
AS (
select projection_details_sid, period_sid, growth,
10.0 as base,
10 * ( 1 + growth/100) As our_result
from t1 where period_sid = 601
UNION ALL
SELECT a.projection_details_sid, a.period_sid, a.growth,
b.our_result as base,
b.our_result * ( 1 + a.growth/100) As our_result
FROM t1 a
JOIN our_recursive_query b
ON a.period_sid = b.period_sid + 1
)
SELECT * FROM our_recursive_query
结果是:
PROJECTION_DETAILS_SID PERIOD_SID GROWTH BASE OUR_RESULT
--------------------------------------- ---------- ---------- ------------ ------------
1 601 2 10.00000000 10.20000000
1 602 2 10.20000000 10.40400000
1 603 2 10.40400000 10.61208000
1 604 1 10.61208000 10.71820080
1 605 6 10.71820080 11.36129285
1 606 3 11.36129285 11.70213163
我假设period_sid递增1，因此我将.period_sid = b.period_sid + 1用作连接条件.如果在您的真实数据中不是这样，则需要使用row_number分析函数来稍稍修改查询.
I am assumming that period_sid is increassing by 1, therefore I am using .period_sid = b.period_sid + 1 as a join condition. If this is not true in your real data, you need to modify slighly the query with use of row_number analytic function.
@kordiko谢谢.还有没有其他可能达到目的 除了递归CTE以外，因为递归CTE具有类似的性能 while循环.
@kordiko thanks. is there any other possible to achieve the result other than recursive cte since recursive cte has a similar performance of while loop.
是的.在Oracle中，您可以创建自己的聚合函数，该函数执行多个数字链-类似于内置sum函数执行==> X1 + X2 + ... + Xn，但是执行X1 * X2 * .... * Xn代替.
一个例子:
Yes. In Oracle you can create your own aggregate function that performs a multiply of a chain of numbers - in the similar way as a built-in sum function does ==> X1+X2+...+Xn, but does X1*X2*....*Xn instead.
An example:
create or replace TYPE MyCumulativeMultiply_type
AS OBJECT (
cumulativeMultiplyResult NUMBER,
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT MyCumulativeMultiply_type) RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT MyCumulativeMultiply_type, your_parameter_to_aggregate IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(self IN OUT MyCumulativeMultiply_type, ctx2 IN MyCumulativeMultiply_type) RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(self IN MyCumulativeMultiply_type, returnvalue OUT NUMBER, flags IN NUMBER) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY MyCumulativeMultiply_type
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT MyCumulativeMultiply_type)
RETURN NUMBER
IS
BEGIN
-- instantiate our type, NULL the dummy attribute
ctx := MyCumulativeMultiply_type( 1 );
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT MyCumulativeMultiply_type, your_parameter_to_aggregate IN NUMBER)
RETURN NUMBER
IS
BEGIN
self.cumulativeMultiplyResult := self.cumulativeMultiplyResult * your_parameter_to_aggregate;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(self IN OUT MyCumulativeMultiply_type, ctx2 IN MyCumulativeMultiply_type)
RETURN NUMBER
IS
BEGIN
self.cumulativeMultiplyResult := self.cumulativeMultiplyResult * ctx2.cumulativeMultiplyResult;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(self IN MyCumulativeMultiply_type,
returnvalue OUT NUMBER,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
returnvalue := self.cumulativeMultiplyResult;
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION cumulative_multiply(arg NUMBER)
RETURN NUMBER
PARALLEL_ENABLE
AGGREGATE USING MyCumulativeMultiply_type;
/
现在查询是:
select t1.*
, cumulative_multiply( 1 + growth/100 ) OVER (order by period_sid ) as multiplier
, 10 * cumulative_multiply( 1 + growth/100 ) OVER (order by period_sid ) as our_result
from t1;
结果是:
PROJECTION_DETAILS_SID PERIOD_SID GROWTH MULTIPLIER OUR_RESULT
--------------------------------------- ---------- ---------- ------------ ------------
1 601 2 1.02000000 10.20000000
1 602 2 1.04040000 10.40400000
1 603 2 1.06120800 10.61208000
1 604 1 1.07182008 10.71820080
1 605 6 1.13612928 11.36129285
1 606 3 1.17021316 11.70213163
不幸的是，我不知道上述方法是否可以在SQL Server中使用.
Unfortunately I don't know it the above approach is possible in SQL-Server.
这篇关于需要帮助进行以下计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
