sql server - sumar - Calcule el total acumulado/saldo en ejecución

sum sql server

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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:

1. SUM() OVER() ... ROWS , si en 2012 o superior
2. Método CLR, si es posible
3. Primer método CTE recursivo, si es posible
4. Cursor
5. Los otros métodos recursivos CTE
6. Actualización peculiar
7. 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

Múltiples totales acumulados con Group By

Answer 4

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