sql server - sumar - Calcule el total acumulado/saldo en ejecución
sum sql server (4)
En SQL Server 2008+
SELECT T1.* ,
T2.RunningSum
FROM dbo.Transactions As T1
CROSS APPLY ( SELECT SUM(amt) AS RunningSum
FROM dbo.Transactions AS CAT1
WHERE ( CAT1.TId <= T1.TId )
) AS T2
En el servidor SQL 2012+
SELECT * ,
SUM(T1.amt) OVER ( ORDER BY T1.TId
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS RunningTotal
FROM dbo.Transactions AS t1
Tengo una mesa:
create table Transactions(Tid int,amt int)
Con 5 filas:
insert into Transactions values(1, 100)
insert into Transactions values(2, -50)
insert into Transactions values(3, 100)
insert into Transactions values(4, -100)
insert into Transactions values(5, 200)
Salida deseada:
TID amt balance
--- ----- -------
1 100 100
2 -50 50
3 100 150
4 -100 50
5 200 250
Básicamente, para el primer balance de registros será el mismo que para amt , el saldo de 2nd onwards sería la suma del saldo anterior + amt actual. Estoy buscando un enfoque óptimo. Podría pensar en usar la función o la subconsulta correlacionada, pero no estoy seguro de cómo hacerlo exactamente.
Estamos en 2008R2 y uso variables y una tabla temporal. Esto también le permite hacer cosas personalizadas al calcular cada fila usando una declaración de caso (es decir, ciertas transacciones pueden actuar de forma diferente, o puede que solo desee un total para tipos de transacción específicos)
DECLARE @RunningBalance int = 0
SELECT Tid, Amt, 0 AS RunningBalance
INTO #TxnTable
FROM Transactions
ORDER BY Tid
UPDATE #TxnTable
SET @RunningBalance = RunningBalance = @RunningBalance + Amt
SELECT * FROM #TxnTable
DROP TABLE #TxnTable
Tenemos una tabla de transacciones con 2,3 millones de filas con un artículo que tiene más de 3.300 transacciones, y ejecutar este tipo de consulta no lleva tiempo.
Para aquellos que no usan SQL Server 2012 o superior, es probable que un cursor sea el método respaldado y garantizado más eficiente fuera de CLR. Hay otros enfoques, como la "actualización peculiar" que puede ser ligeramente más rápida pero no garantizada para funcionar en el futuro, y por supuesto enfoques basados en conjuntos con perfiles de rendimiento hiperbólicos a medida que la tabla aumenta, y métodos CTE recursivos que a menudo requieren #tempdb I / O o resulta en derrames que producen aproximadamente el mismo impacto.
INNER JOIN - no hagas esto:
El enfoque lento basado en conjuntos tiene la forma:
SELECT t1.TID, t1.amt, RunningTotal = SUM(t2.amt)
FROM dbo.Transactions AS t1
INNER JOIN dbo.Transactions AS t2
ON t1.TID >= t2.TID
GROUP BY t1.TID, t1.amt
ORDER BY t1.TID;
¿La razón por la cual esto es lento? A medida que la tabla se hace más grande, cada fila incremental requiere leer n-1 filas en la tabla. Esto es exponencial y está sujeto a fallas, tiempos de espera o simplemente usuarios enojados.
Subconsulta correlacionada: no hagas esto tampoco:
La forma de subconsulta es igualmente dolorosa por razones igualmente dolorosas.
SELECT TID, amt, RunningTotal = amt + COALESCE(
(
SELECT SUM(amt)
FROM dbo.Transactions AS i
WHERE i.TID < o.TID), 0
)
FROM dbo.Transactions AS o
ORDER BY TID;
Actualización peculiar: hazlo bajo tu propio riesgo:
El método de "actualización peculiar" es más eficiente que el anterior, pero el comportamiento no está documentado, no hay garantías sobre el orden, y el comportamiento podría funcionar hoy, pero podría romperse en el futuro. Lo incluyo porque es un método popular y eficiente, pero eso no significa que lo respalde. La razón principal por la que incluso respondí esta pregunta en lugar de cerrarla como duplicado es porque la otra pregunta tiene una actualización peculiar como respuesta aceptada .
DECLARE @t TABLE
(
TID INT PRIMARY KEY,
amt INT,
RunningTotal INT
);
DECLARE @RunningTotal INT = 0;
INSERT @t(TID, amt, RunningTotal)
SELECT TID, amt, RunningTotal = 0
FROM dbo.Transactions
ORDER BY TID;
UPDATE @t
SET @RunningTotal = RunningTotal = @RunningTotal + amt
FROM @t;
SELECT TID, amt, RunningTotal
FROM @t
ORDER BY TID;
CTE recursivos
Este primero se basa en TID para ser contiguo, sin espacios vacíos:
;WITH x AS
(
SELECT TID, amt, RunningTotal = amt
FROM dbo.Transactions
WHERE TID = 1
UNION ALL
SELECT y.TID, y.amt, x.RunningTotal + y.amt
FROM x
INNER JOIN dbo.Transactions AS y
ON y.TID = x.TID + 1
)
SELECT TID, amt, RunningTotal
FROM x
ORDER BY TID
OPTION (MAXRECURSION 10000);
Si no puede confiar en esto, puede usar esta variación, que simplemente crea una secuencia contigua usando ROW_NUMBER() :
;WITH y AS
(
SELECT TID, amt, rn = ROW_NUMBER() OVER (ORDER BY TID)
FROM dbo.Transactions
), x AS
(
SELECT TID, rn, amt, rt = amt
FROM y
WHERE rn = 1
UNION ALL
SELECT y.TID, y.rn, y.amt, x.rt + y.amt
FROM x INNER JOIN y
ON y.rn = x.rn + 1
)
SELECT TID, amt, RunningTotal = rt
FROM x
ORDER BY x.rn
OPTION (MAXRECURSION 10000);
Dependiendo del tamaño de los datos (p. Ej., Columnas de las que no tenemos conocimiento), puede encontrar un mejor rendimiento general al rellenar primero las columnas relevantes en una tabla #temp y procesarlas en lugar de en la tabla base:
CREATE TABLE #x
(
rn INT PRIMARY KEY,
TID INT,
amt INT
);
INSERT INTO #x (rn, TID, amt)
SELECT ROW_NUMBER() OVER (ORDER BY TID),
TID, amt
FROM dbo.Transactions;
;WITH x AS
(
SELECT TID, rn, amt, rt = amt
FROM #x
WHERE rn = 1
UNION ALL
SELECT y.TID, y.rn, y.amt, x.rt + y.amt
FROM x INNER JOIN #x AS y
ON y.rn = x.rn + 1
)
SELECT TID, amt, RunningTotal = rt
FROM x
ORDER BY TID
OPTION (MAXRECURSION 10000);
DROP TABLE #x;
Solo el primer método CTE ofrecerá un rendimiento que rivaliza con la actualización peculiar, pero supone una gran suposición sobre la naturaleza de los datos (sin lagunas). Los otros dos métodos retrocederán y en esos casos también puede usar un cursor (si no puede usar CLR y aún no está en SQL Server 2012 o superior).
Cursor
A todo el mundo se le dice que los cursores son malvados, y que deben evitarse a toda costa, pero esto realmente supera el rendimiento de la mayoría de los demás métodos compatibles, y es más seguro que la actualización peculiar. Los únicos que prefiero sobre la solución de cursor son los métodos 2012 y CLR (abajo):
CREATE TABLE #x
(
TID INT PRIMARY KEY,
amt INT,
rt INT
);
INSERT #x(TID, amt)
SELECT TID, amt
FROM dbo.Transactions
ORDER BY TID;
DECLARE @rt INT, @tid INT, @amt INT;
SET @rt = 0;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT TID, amt FROM #x ORDER BY TID;
OPEN c;
FETCH c INTO @tid, @amt;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rt = @rt + @amt;
UPDATE #x SET rt = @rt WHERE TID = @tid;
FETCH c INTO @tid, @amt;
END
CLOSE c; DEALLOCATE c;
SELECT TID, amt, RunningTotal = rt
FROM #x
ORDER BY TID;
DROP TABLE #x;
SQL Server 2012 o superior
Las nuevas funciones de ventana introducidas en SQL Server 2012 hacen que esta tarea sea mucho más fácil (y funciona mejor que todos los métodos anteriores):
SELECT TID, amt,
RunningTotal = SUM(amt) OVER (ORDER BY TID ROWS UNBOUNDED PRECEDING)
FROM dbo.Transactions
ORDER BY TID;
Tenga en cuenta que en conjuntos de datos más grandes, encontrará que lo anterior funciona mucho mejor que cualquiera de las dos opciones siguientes, ya que RANGE utiliza un spool en el disco (y el predeterminado usa RANGE). Sin embargo, también es importante tener en cuenta que el comportamiento y los resultados pueden diferir, así que asegúrese de que ambos devuelvan resultados correctos antes de decidir entre ellos en función de esta diferencia.
SELECT TID, amt,
RunningTotal = SUM(amt) OVER (ORDER BY TID)
FROM dbo.Transactions
ORDER BY TID;
SELECT TID, amt,
RunningTotal = SUM(amt) OVER (ORDER BY TID RANGE UNBOUNDED PRECEDING)
FROM dbo.Transactions
ORDER BY TID;
CLR
Para completar, estoy ofreciendo un enlace al método CLR de Pavel Pawlowski, que es, con mucho, el método preferible en las versiones anteriores a SQL Server 2012 (pero no en 2000, obviamente).
http://www.pawlowski.cz/2010/09/sql-server-and-fastest-running-totals-using-clr/
Conclusión
Si está en SQL Server 2012 o superior, la elección es obvia: use la nueva construcción SUM() OVER() (con ROWS frente a RANGE ). Para versiones anteriores, querrá comparar el rendimiento de los enfoques alternativos en su esquema, datos y, teniendo en cuenta los factores no relacionados con el rendimiento, determinar qué enfoque es el adecuado para usted. Muy bien puede ser el enfoque CLR. Aquí están mis recomendaciones, en orden de preferencia:
-
SUM() OVER() ... ROWS, si en 2012 o superior - Método CLR, si es posible
- Primer método CTE recursivo, si es posible
- Cursor
- Los otros métodos recursivos CTE
- Actualización peculiar
- Unirse y / o subconsulta correlacionada
Para obtener más información con las comparaciones de rendimiento de estos métodos, consulte esta pregunta en http://dba.stackexchange.com :
https://dba.stackexchange.com/questions/19507/running-total-with-count
También publiqué en mi blog más detalles sobre estas comparaciones aquí:
http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals
También para totales acumulados agrupados / particionados, consulte las siguientes publicaciones:
http://sqlperformance.com/2014/01/t-sql-queries/grouped-running-totals
Particionar resultados en una consulta de totales acumulados
Si usa la versión 2012, aquí hay una solución
select *, sum(amt) over (order by Tid) as running_total from Transactions
Para versiones anteriores
select *,(select sum(amt) from Transactions where Tid<=t.Tid) as running_total from Transactions as t