Calculando acumulados en T-SQL

by Maxi Accotto | Sep 11, 2021 | Performance, SQL 2017, SQL 2019, SQL Server, TSQL

En algunas ocasiones tenemos la necesidad de armar consultas en T-SQL para retornar acumulados.

Imaginemos el siguiente resultado

Lo que nosotros tenemos es una tabla la cual contiene TRANID, PRODUCTID,TRANSACTIONDATE y QUANTITY y necesitamos calcular la columna total que como podemos observar hace un acumulado con la particularidad de poner en 0 de nuevo cada vez que cambia el código de producto.

En este tutorial te voy a mostrar las alternativas para poder resolver esto y cual es la mas eficiente de todas

Para poder ejecutar el código de ejemplo he utilizado la base de datos de ejemplos Adventureworks

Código de ejemplo utilizado en el video

USE ADVENTUREWORKS2019 
GO
-- PREPARAR DATOS DE PRUEBA
DROP TABLE IF EXISTS MOVIMIENTOS 
SELECT IDENTITY(INT, 1,1) AS TRANID
      ,[PRODUCTID]
      ,[TRANSACTIONDATE]
      ,[QUANTITY] * FLOOR(10 * RAND(CONVERT(VARBINARY, NEWID())))  AS QUANTITY
  INTO MOVIMIENTOS	
  FROM [ADVENTUREWORKS2019].[PRODUCTION].[TRANSACTIONHISTORY]
  CROSS JOIN (SELECT TOP 10 * FROM SYS.COLUMNS) AS C
  ORDER BY PRODUCTID,TRANID 

---------------------------------------
------    USANDO OVER
----------------------------------------
SELECT [TRANID]
      ,[PRODUCTID]
      ,[TRANSACTIONDATE]
      ,[QUANTITY]
	  , SUM(QUANTITY) 
	    OVER(PARTITION BY PRODUCTID ORDER BY TRANID) AS TOTAL  
  FROM DBO.MOVIMIENTOS 
  ORDER BY PRODUCTID,TRANID 

--------------------------------------
-- USANDO CURSORES Y TABLAS TEMPORALES
---------------------------------------

DECLARE @RESULT AS TABLE (TRANID INT,
                          PRODUCTID INT,
						  TRANSACTIONDATE DATETIME,
						  QUANTITY INT,
						  TOTAL INT
						  )
DECLARE
  @TRANID    AS INT,
  @PREVPRODUCTID AS INT,
  @PRODUCTID AS INT,
  @TRANSACTIONDATE   AS DATETIME,
  @QUANTITY  AS INT,
  @TOTAL  AS INT;

DECLARE C CURSOR FAST_FORWARD FOR
  SELECT TRANID,PRODUCTID,TRANSACTIONDATE,QUANTITY    
  FROM DBO.MOVIMIENTOS
  ORDER BY PRODUCTID,TRANID;
OPEN C
FETCH NEXT FROM C INTO @TRANID, @PRODUCTID, @TRANSACTIONDATE,@QUANTITY

SELECT @PREVPRODUCTID = @PRODUCTID , @TOTAL  = 0;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @PRODUCTID  <> @PREVPRODUCTID 
    SELECT @PREVPRODUCTID = @PRODUCTID , @TOTAL  = 0;
;

  SET @TOTAL = @TOTAL + @QUANTITY;
  INSERT INTO @RESULT 
  VALUES(@TRANID, @PRODUCTID, @TRANSACTIONDATE ,  @QUANTITY,@TOTAL);
 
  FETCH NEXT FROM C INTO @TRANID, @PRODUCTID, @TRANSACTIONDATE,@QUANTITY;
END

CLOSE C;
DEALLOCATE C;

SELECT * FROM @RESULT 
ORDER BY PRODUCTID,TRANID  

------------------------------------
-- USANDO SUBQUERY
-------------------------------------
SELECT T1.PRODUCTID , T1.TRANID, T1.QUANTITY ,
  SUM(T2.QUANTITY ) AS TOTAL
FROM DBO.MOVIMIENTOS AS T1
  JOIN DBO.MOVIMIENTOS AS T2
    ON T2.PRODUCTID  = T1.PRODUCTID 
   AND T2.TRANID <= T1.TRANID
GROUP BY T1.PRODUCTID , T1.TRANID, T1.QUANTITY
ORDER BY 1,2;