Sql Server 中的计算

本文介绍了Sql Server 中的计算的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我尝试执行以下计算

示例数据:

CREATE TABLE #Table1 ( rno int identity(1,1), ccp varchar(50), [col1] INT, [col2] INT, [col3] INT, col4 as [col2]/100.0 ); INSERT INTO #Table1 (ccp,[col1],[col2],[col3]) VALUES ('ccp1',15,10,1100), ('ccp1',20,10,1210), ('ccp1',30,10,1331), ('ccp2',10,15,900), ('ccp2',15,15,1000), ('ccp2',20,15,1010) +-----+------+------+------+------+----------+ | rno | ccp | col1 | col2 | col3 | col4 | +-----+------+------+------+------+----------+ | 1 | ccp1 | 15 | 10 | 1100 | 0.100000 | | 2 | ccp1 | 20 | 10 | 1210 | 0.100000 | | 3 | ccp1 | 30 | 10 | 1331 | 0.100000 | | 4 | ccp2 | 10 | 15 | 900 | 0.150000 | | 5 | ccp2 | 15 | 15 | 1000 | 0.150000 | | 6 | ccp2 | 20 | 15 | 1010 | 0.150000 | +-----+------+------+------+------+----------+

注意:不仅仅是3条记录，每个ccp可以有N条记录

Note : It is not just 3 records each ccp can have N no.of records

预期结果:

1083.500000 --1100 - (15 * (1+0.100000)) 1169.850000 --1210 - ((20 * (1+0.100000)) + (15 * (1+0.100000)* (1+0.100000)) ) 1253.835000 --1331 - ((30 * (1+0.100000)) + (20 * (1+0.100000)* (1+0.100000)) + (15 * (1+0.100000)* (1+0.100000) *(1+0.100000)) ) 888.500000 --900 - (10 * (1+0.150000)) 969.525000 --1000 - ((15 * (1+0.150000)) + (10 * (1+0.150000)* (1+0.150000)) ) 951.953750 --1010 - ((20 * (1+0.150000)) + (15 * (1+0.150000)* (1+0.150000)) + (10 * (1+0.150000)* (1+0.150000) *(1+0.150000)) )

我知道我们可以使用递归 CTE 来做到这一点，但效率不高，因为我必须对超过 500 万条记录执行此操作.

I know we can do this using Recursive CTE, it is not efficient since i have to do this for more than 5 million records.

我希望实现类似这种基于集合的方法

I am looking to implement something like this set based approach

对于 ccp : ccp1

SELECT col3 - ( col1 * ( 1 + col4 ) ) FROM #Table1 WHERE rno = 1 SELECT rno, col3 - ( ( col1 * Power(( 1 + col4 ), 1) ) + ( Lag(col1, 1) OVER( ORDER BY rno ) * Power(( 1 + col4 ), 2) ) ) FROM #Table1 WHERE rno IN ( 1, 2 ) SELECT rno, col3 - ( ( col1 * Power(( 1 + col4 ), 1) ) + ( Lag(col1, 1) OVER( ORDER BY rno ) * Power(( 1 + col4 ), 2) ) + ( Lag(col1, 2) OVER( ORDER BY rno ) * Power(( 1 + col4 ), 3) ) ) FROM #Table1 WHERE rno IN ( 1, 2, 3 )

有没有办法在单个查询中计算?

Is there a way to calculate in single query?

仍然愿意接受建议.我坚信应该有一些使用 SUM () Over(Order by) 窗口聚合函数来做到这一点.

Still am open to suggestions. I strongly beleive there should be some to do this using SUM () Over(Order by) window aggregate function.

推荐答案

self join 的方法.不确定这是否比使用 cross apply 的版本更有效.

An approach with a self join. Not sure if this would be any more efficient than your version with cross apply.

WITH T AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY CCP ORDER BY RNO) AS RN FROM #TABLE1) SELECT T1.RNO, T1.CCP, T1.COL1, T1.COL2, T1.COL3, T1.COL3-SUM(T2.COL1*POWER(1+T1.COL2/100.0,T1.RN-T2.RN+1)) AS RES FROM T T1 JOIN T T2 ON T1.CCP=T2.CCP AND T1.RN>=T2.RN GROUP BY T1.RNO, T1.CCP, T1.COL1, T1.COL2, T1.COL3

示例演示