SQL Server – Como criar uma AutoSoma (igual do Excel) utilizando Window functions (Running totals)

SQL Server – Como criar uma AutoSoma (igual do Excel) utilizando Window functions (Running totals)
5 (100%) 2 votos

Olá pessoal,
Como vocês estão ?

Hoje vou apresentar um recurso interessante do SQL Server e que muita gente não conhece, que é o uso de Window functions para realizar cálculos cumulativos em um result set no SQL Server, semelhantes ao recurso de Auto Soma do Excel.

Essa necessidade surgiu para mim através de uma solicitação similar à que vou apresentar nesse post, onde temos uma base de vendas com data e quero selecionar os meses onde a quantidade de vendas acumulada do ano foi menor que 40. Caso esse valor seja atingido durante o mês, esse mês não deve ser selecionado.

Para chegar a essa informação, pensei em agrupar os dados por mês e criar uma soma acumulativa da quantidade de vendas. Com uma massa de dados pequena, podemos criar um loop WHILE para percorrer todas as linhas da tabela, recuperar a quantidade daquela linha, somar numa variável e atualizar novamente esse registro pra fazer a soma, mas quando aplicamos isso numa tabela com milhões de registros, esse processo pode demorar horas para processar.

Caso você queira conhecer mais algumas formas diferentes de agrupar dados, acesse o postSQL Server – Agrupando dados utilizando ROLLUP, CUBE e GROUPING SETS.

Base de Testes

Utilize esse script para montar a base de testes para esse post.

Exemplo de tabela de Vendas populada:

Implementando o recurso de AutoSoma no SQL Server

Para implementar o recurso de AutoSoma no SQL Server, vamos utilizar a função de agregação SUM() em conjunto com uma Window function, além de alguns parâmetros especialmente criados para essa necessidade.

Caso você queira implementar isso utilizando a solução do WHILE que mencionei acima, você pode fazer da forma que demonstrei logo abaixo. Precisei fazer algo parecido em uma tabela de 1.4 milhões de registros e pelos meus cálculos, ia demorar algumas horas só para processar isso, o que acabou me fazendo buscar uma nova solução.

Exemplo de tabela de Vendas agrupada populada:

Para fazer isso de forma muito mais rápida e prática, vamos utilizar uma função de agregação e Window function. No exemplo que dei acima, em uma tabela de 1.4 milhões de registros (já agrupados), essa query demorou 8 segundos para processar.

Resultado:

É isso aí, pessoal.
Espero que vocês tenham gostado desse post.

Abraço!

SQL Server – Como criar uma AutoSoma autosum (igual do Excel) like excel excel-like utilizando Window functions running totals running totals

SQL Server – Como criar uma AutoSoma autosum (igual do Excel) like excel excel-like utilizando Window functions running totals running totals

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

Deixe uma resposta