Calcul dans Sql Server

Une approche avec une self join . Je ne sais pas si ce serait plus efficace que votre version avec 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 

Sample Demo

Enfin, j'ai atteint le résultat en utilisant l'approche ci-dessous

 SELECT a.*, col3 - res AS Result FROM #TABLE1 a CROSS apply (SELECT Sum(b.col1 * Power(( 1 + b.COL2 / 100.00 ), new_rn)) AS res FROM (SELECT Row_number() OVER( partition BY ccp ORDER BY rno DESC) new_rn,* FROM #TABLE1 b WHERE a.ccp = b.ccp AND a.rno >= b.rno)b) cs 

Résultat :

 +-----+------+------+------+------+----------+-------------+ | rno | ccp | col1 | col2 | col3 | col4 | Result | +-----+------+------+------+------+----------+-------------+ | 1 | ccp1 | 15 | 10 | 1100 | 0.100000 | 1083.500000 | | 2 | ccp1 | 20 | 10 | 1210 | 0.100000 | 1169.850000 | | 3 | ccp1 | 30 | 10 | 1331 | 0.100000 | 1253.835000 | | 4 | ccp2 | 10 | 15 | 900 | 0.150000 | 888.500000 | | 5 | ccp2 | 15 | 15 | 1000 | 0.150000 | 969.525000 | | 6 | ccp2 | 20 | 15 | 1010 | 0.150000 | 951.953750 | +-----+------+------+------+------+----------+-------------+ 

Cette réponse peut être décevante, mais vous findez probablement qu'une approche CLR itérative fonctionne de manière compétitive avec n'importe quelle approche TSQL.

Essayez ce qui suit (sur la base des sums en cours d' exécution encore une fois: SQLCLR sauve la journée! )

 using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void StackoverflowQuestion41803909() { using (SqlConnection conn = new SqlConnection("context connection=true;")) { SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = @" SELECT [rno], [ccp], [col1], [col2], [col3], [col4] FROM Table1 ORDER BY ccp, rno "; SqlMetaData[] columns = new SqlMetaData[7]; columns[0] = new SqlMetaData("rno", SqlDbType.Int); columns[1] = new SqlMetaData("ccp", SqlDbType.VarChar, 50); columns[2] = new SqlMetaData("col1", SqlDbType.Int); columns[3] = new SqlMetaData("col2", SqlDbType.Int); columns[4] = new SqlMetaData("col3", SqlDbType.Int); columns[5] = new SqlMetaData("col4", SqlDbType.Decimal, 17, 6); columns[6] = new SqlMetaData("result", SqlDbType.Decimal, 17, 6); SqlDataRecord record = new SqlDataRecord(columns); SqlContext.Pipe.SendResultsStart(record); conn.Open(); SqlDataReader reader = comm.ExecuteReader(); ssortingng prevCcp = null; decimal offset = 0; while (reader.Read()) { ssortingng ccp = (ssortingng)reader[1]; int col1 = (int)reader[2]; int col3 = (int)reader[4]; decimal col4 = (decimal)reader[5]; if (prevCcp != ccp) { offset = 0; } offset = ((col1 + offset) * (1 + col4)); record.SetInt32(0, (int)reader[0]); record.SetSsortingng(1, ccp); record.SetInt32(2, col1); record.SetInt32(3, (int)reader[3]); record.SetInt32(4, col3); record.SetDecimal(5, col4); record.SetDecimal(6, col3 - offset); SqlContext.Pipe.SendResultsRow(record); prevCcp = ccp; } SqlContext.Pipe.SendResultsEnd(); } } }; 

Une autre option

 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), ('ccp1',40,10,1331), ('ccp2',10,15,900), ('ccp2',15,15,1000), ('ccp2',20,15,1010); select t.*, col3-s from( select *, rn = row_number() over(partition by ccp order by rno) from #Table1 ) t cross apply ( select s=sum(pwr*col1) from( select top(rn) col1, pwr = power(1+col4, rn + 1 - row_number() over(order by rno)) from #Table1 t2 where t2.ccp=t.ccp order by row_number() over(order by rno) )t3 )t4 order by rno; 

Après avoir joué avec lui pendant un certain time, je crois que la réponse à la question de la prime de savoir si cela peut être fait avec une sum() over (order by) est NON. Ce code est aussi proche que possible:

 select *, col3 - sum(col1 * power(1 + col4, row_num)) over (partition by ccp order by col1) from ( select *, row_number() over (partition by ccp order by rno asc) row_num from @Table1 ) a order by 1,2; 

Cela returnnera des résultats corrects pour la première ligne dans chaque groupe ccp . En calculant row_num en utilisant rno desc place, la dernière ligne de chaque ccp sera correcte.

Il semble que les seules façons de faire fonctionner cela de la manière simple suggérée par la syntaxe serait:

1. Prise en charge de la syntaxe pour referencer la ligne réelle dans la fonction d'agrégation. Cela existe dans T-SQL autant que je peux find.
2. Prise en charge de la syntaxe pour une fonction de window dans une fonction de window. Cela n'est pas non plus autorisé dans T-SQL par l'erreur suivante:

Les fonctions fenêtrées ne peuvent pas être utilisées dans le context d'une autre fonction fenêtrée ou d'un agrégat.

C'était un problème intéressant. Je serais curieux de savoir comment cette solution fonctionne contre votre grand set de données, même si le résultat réel est incorrect.

Essaye ça:

 ;with val as ( select *, (1 + col2 / 100.00) val, row_number() over(partition by ccp order by rno desc) rn from #Table1), res as ( select v1.rno, --min(v1.ccp) ccp, --min(v1.col1) col1, --min(v1.col2) col2, min(v1.col3) col3, sum(v2.col1 * power(v2.val, 1 + v2.rn - v1.rn)) sum_val from val v1 left join val v2 on v2.ccp = v1.ccp and v2.rno <= v1.rno group by v1.rno) select *, col3 - isnull(sum_val, 0) from res 

Mais les performances dépendent des index. Afficher la structure de l'index pour plus de détails. Les meilleures performances peuvent être obtenues lorsque vous le divisez en plusieurs tables temporaires.