transact - Cálculo en servidor Sql
transact sql tutorial (6)
Estoy tratando de realizar el siguiente cálculo
Data de muestra:
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 |
+-----+------+------+------+------+----------+
Nota: No son solo 3 registros cada ccp puede tener N no.of registros
Resultado Esperado :
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)) )
Sé que podemos hacer esto utilizando Recursive CTE, no es eficiente ya que tengo que hacer esto para más de 5 millones de registros.
Estoy buscando implementar algo así como este enfoque basado en conjuntos
Para 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 )
¿Hay alguna forma de calcular en una sola consulta?
Actualización:
Aún estoy abierto a sugerencias. Creo firmemente que debería haber algunos para hacer esto usando SUM () Over(Order by) función agregada de ventana.
Después de jugar con él por algún tiempo, creo que la respuesta a la pregunta sobre si esto se puede hacer con una sum() over (order by) es NO. Este código es lo más cercano que pude obtener:
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;
Esto devolverá resultados correctos para la primera fila en cada grupo de ccp . Al calcular row_num usando rno desc lugar, la fila final en cada ccp será correcta.
Parece que las únicas formas de hacer que esto funcione de la manera simple que sugiere la sintaxis serían:
- Soporte de sintaxis para hacer referencia a la fila real dentro de la función agregada sobre. Esto existe en T-SQL hasta donde puedo encontrar.
- Soporte de sintaxis para una función de ventana dentro de una función de ventana. Esto tampoco está permitido en T-SQL por el siguiente error:
Las funciones con ventana no se pueden usar en el contexto de otra función o agregado en ventana.
Este fue un problema interesante. Me gustaría saber cómo funciona esta solución frente a su gran conjunto de datos, aunque el resultado real sea incorrecto.
Esta respuesta puede ser decepcionante, pero es probable que descubra que un enfoque CLR iterativo funciona competitivamente con cualquier enfoque TSQL.
Pruebe lo siguiente (basado en Ejecutar sumas una vez más: ¡SQLCLR salva el día! )
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 Question41803909()
{
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();
string prevCcp = null;
decimal offset = 0;
while (reader.Read())
{
string ccp = (string)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.SetString(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();
}
}
};
Finalmente logré el resultado usando el siguiente enfoque
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
Resultado:
+-----+------+------+------+------+----------+-------------+
| 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 |
+-----+------+------+------+------+----------+-------------+
Otra opción
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;
Prueba esto:
;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
Pero el rendimiento depende de los índices. Publicar la estructura del índice para más detalles. Se puede lograr el mejor rendimiento cuando lo dividas en tablas más temporales.
Un acercamiento con una self join No estoy seguro si esto sería más eficiente que su versión con 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