Hola, chicos,
¿Cómo estás?

Hoy voy a presentar una característica interesante de SQL Server que mucha gente no conoce, que es el uso de funciones de Windows para realizar cálculos acumulativos en un conjunto de resultados en SQL Server, similar a la característica de suma automática en Excel.

Esta necesidad me surgió a través de una solicitud similar a la que presentaré en este post, donde tenemos una base de ventas con una fecha y quiero seleccionar los meses donde la cantidad de ventas acumuladas del año fue menor a 40. Si se alcanza este valor durante el mes, no se debe seleccionar ese mes.

Para obtener esta información, pensé en agrupar los datos por mes y crear una suma acumulada del monto de ventas. Con una pequeña cantidad de datos, podemos crear un bucle WHILE para recorrer todas las filas de la tabla, recuperar la cantidad de esa fila, agregarla a una variable y actualizar este registro nuevamente para hacer la suma, pero cuando aplicamos esto a una tabla con millones de registros, este proceso puede tardar horas en procesarse.

Si quieres saber más sobre las diferentes formas de agrupar datos, visita el postSQL Server: agrupación de datos mediante ROLLUP, CUBE y GROUPING SETS.

Base de prueba

Utilice este script para configurar la base de prueba para esta publicación.

IF (OBJECT_ID('tempdb..#Produtos') IS NOT NULL) DROP TABLE #Produtos
CREATE TABLE #Produtos (
    Codigo INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Ds_Produto VARCHAR(50) NOT NULL,
    Ds_Categoria VARCHAR(50) NOT NULL,
    Preco NUMERIC(18, 2) NOT NULL
)

IF (OBJECT_ID('tempdb..#Vendas') IS NOT NULL) DROP TABLE #Vendas
CREATE TABLE #Vendas (
    Codigo INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Dt_Venda DATETIME NOT NULL,
    Cd_Produto INT NOT NULL
)


INSERT INTO #Produtos ( Ds_Produto, Ds_Categoria, Preco )
VALUES
    ( 'Processador i7', 'Informática', 1500.00 ),
    ( 'Processador i5', 'Informática', 1000.00 ),
    ( 'Processador i3', 'Informática', 500.00 ),
    ( 'Placa de Vídeo Nvidia', 'Informática', 2000.00 ),
    ( 'Placa de Vídeo Radeon', 'Informática', 1500.00 ),
    ( 'Celular Apple', 'Celulares', 10000.00 ),
    ( 'Celular Samsung', 'Celulares', 2500.00 ),
    ( 'Celular Sony', 'Celulares', 4200.00 ),
    ( 'Celular LG', 'Celulares', 1000.00 ),
    ( 'Cama', 'Utilidades do Lar', 2000.00 ),
    ( 'Toalha', 'Utilidades do Lar', 40.00 ),
    ( 'Lençol', 'Utilidades do Lar', 60.00 ),
    ( 'Cadeira', 'Utilidades do Lar', 200.00 ),
    ( 'Mesa', 'Utilidades do Lar', 1000.00 ),
    ( 'Talheres', 'Utilidades do Lar', 50.00 )

    

DECLARE @Contador INT = 1, @Total INT = 100

WHILE(@Contador <= @Total)
BEGIN

    INSERT INTO #Vendas ( Cd_Produto, Dt_Venda )
    SELECT 
        (SELECT TOP 1 Codigo FROM #Produtos ORDER BY NEWID()) AS Cd_Produto,
        DATEADD(DAY, (CAST(RAND() * 364 AS INT)), '2017-01-01') AS Dt_Venda

    SET @Contador += 1

END

SELECT * FROM #Vendas

Ejemplo de una tabla de Ventas poblada:

Implementación de la función Autosuma en SQL Server

Para implementar la función AutoSuma en SQL Server, usaremos la función de agregación SUM() junto con una función de Ventana, además de algunos parámetros especialmente creados para esta necesidad.

Si desea implementar esto usando la solución MIENTRAS que mencioné anteriormente, puede hacerlo de la manera que demostré a continuación. Necesitaba hacer algo similar en una tabla con 1,4 millones de registros y según mis cálculos, me tomaría unas horas solo procesar esto, lo que terminó por hacerme buscar una nueva solución.

IF (OBJECT_ID('tempdb..#Vendas_Agrupadas') IS NOT NULL) DROP TABLE #Vendas_Agrupadas
SELECT 
    IDENTITY(INT, 1, 1) AS Ranking,
    CONVERT(VARCHAR(6), Dt_Venda, 112) AS Periodo,
    COUNT(*) AS Qt_Vendas_No_Mes,
    NULL AS Qt_Vendas_Acumuladas
INTO 
    #Vendas_Agrupadas
FROM 
    #Vendas
GROUP BY
    CONVERT(VARCHAR(6), Dt_Venda, 112)
    

DECLARE
    @Contador INT = 1, 
    @Total INT = (SELECT COUNT(*) FROM #Vendas_Agrupadas),
    @Qt_Vendas_Acumuladas INT = 0,
    @Qt_Vendas_No_Mes INT = 0


WHILE(@Contador <= @Total)
BEGIN
    
    SELECT @Qt_Vendas_No_Mes = Qt_Vendas_No_Mes
    FROM #Vendas_Agrupadas
    WHERE Ranking = @Contador


    SET @Qt_Vendas_Acumuladas += @Qt_Vendas_No_Mes


    UPDATE #Vendas_Agrupadas
    SET Qt_Vendas_Acumuladas = @Qt_Vendas_Acumuladas
    WHERE Ranking = @Contador


    SET @Contador += 1

END


SELECT * FROM #Vendas_Agrupadas

Ejemplo de una tabla de Ventas agrupada y poblada:

Para hacer esto de una forma mucho más rápida y práctica, usaremos una función de agregación y una función de Ventana. En el ejemplo que di arriba, en una tabla con 1,4 millones de registros (ya agrupados), esta consulta tardó 8 segundos en procesarse.

IF (OBJECT_ID('tempdb..#Vendas_Agrupadas') IS NOT NULL) DROP TABLE #Vendas_Agrupadas
SELECT 
    CONVERT(VARCHAR(6), Dt_Venda, 112) AS Periodo,
    COUNT(*) AS Qt_Vendas_No_Mes,
    NULL AS Qt_Vendas_Acumuladas
INTO 
    #Vendas_Agrupadas
FROM 
    #Vendas
GROUP BY
    CONVERT(VARCHAR(6), Dt_Venda, 112)


SELECT 
    Periodo,
    SUM(Qt_Vendas_No_Mes) OVER(ORDER BY Periodo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Qt_Vendas_Acumuladas    
FROM 
    #Vendas_Agrupadas

Resultado:

Eso es todo, amigos.
Espero que te haya gustado esta publicación.

¡Abrazo!

SQL Server: cómo crear una suma automática (igual que Excel) como Excel usando funciones de Windows ejecutando totales ejecutando totales

SQL Server: cómo crear una suma automática (igual que Excel) como Excel usando funciones de Windows ejecutando totales ejecutando totales