Window Functions no SQL Server

                Hoje vamos falar sobre um recurso interessante e útil da linguagem SQL, as Window Functions. Este recurso foi incluído no padrão Ansi/ISO na versão 2003, mas só foi completamente implementado na versão 2008. Assim qualquer SGBD que utilize o padrão ANSI/ISO 2008 permitirá a utilização total das Window Functions.

Window functions é um conjunto de funções que se aplicam a um conjunto de registros, por este motivo o nome. Window em inglês significa Janela, assim sendo, podemos concluir que elas são aplicadas uma “janela” de dados. Este conjunto de funções tem várias utilidades dentro da linguagem SQL, por este motivo elas são divididas em 3 categorias de funções: as de agregação, as de ranqueamento e as de off-set.

Vamos começar falando sobre o primeiro grupo de funções as de Agregação.

            Funções de Agregação

                Estas funções têm como objetivo executar alguma operação sobre determinado conjunto de registros, que pode ser soma, média, contagem e etc.  Provavelmente estas funções sejam as mais conhecidas por serem iguais as funções utilizadas nas agregações com o Group By.  A diferença entre elas é a forma como os registros são agrupados para a agregação.

                Quando utilizamos o Group By todos os registros são agrupados, retornando um registro por grupo. Vamos a um exemplo:

                Imagine que seu gestor queira saber quanto cada departamento da empresa gasta em salários, a primeira solução é um Group By por departamento. A query para este relatório seria algo parecido com o código abaixo:

[code language=”SQL”]

SELECT

d.id,

d.departamento,

count(e.salario) AS Quantidade,

sum(e.salario) AS [Salario por departamento]

FROM dbo.empregado e

INNER JOIN dbo.departamento d ON d.id = e.iddepartamento

GROUP BY d.id, d.departamento

[/code]

                Como podemos ver estamos listando os empregados, somando seus salários conforme o departamento de cada um e para auxiliar na análise incluímos a quantidade de empregados em cada departamento. Esta query retornou 4 registos, um para cada departamento com os seus totalizadores, conforme a imagem abaixo:

img1

                Ok, retornamos o que o gestor solicitou, mas ele percebeu que sem o valor por funcionário ele não tem como saber quanto cada salario representa no montante do departamento. Então temos que incluir no mesmo relatório os detalhes, ou seja, os funcionários e seus respectivos salários.

                A solução mais comum é a utilização de subqueries, no exemplo abaixo criei duas CTE (Common Table Expression), uma para a soma dos salários e uma para a contagem. Na query principal listei os mesmos totais mostrados anteriormente e inclui o nome do funcionário, seu salário e seu departamento. E precisei incluir mais dois joins com as duas CTEs para poder mostrar os respectivos totais. Abaixo como ficou a query, podemos ver que ela ficou mais extensa e mais complexa.

[code language=”SQL”]

WITH soma (id, total) as

(

SELECT e2.iddepartamento,  sum(e2.salario) AS total

FROM dbo.empregado e2

GROUP BY e2.iddepartamento

),

cont (id, total) as

(

SELECT e2.iddepartamento,  count(e2.salario) AS total

FROM dbo.empregado e2

GROUP BY e2.iddepartamento

)

SELECT e.nome, d.departamento, e.salario, c.total AS Qtd, s.total AS soma

FROM dbo.empregado e

INNER JOIN dbo.departamento d ON d.id = e.iddepartamento

INNER JOIN soma s ON s.id = e.iddepartamento

INNER JOIN cont c ON c.id = e.iddepartamento

[/code]

Abaixo temos o resultado desta query onde são mostrados todos os dados que o gestor solicitou:

img2

A partir disto podemos concluir que para cada agregação que fizermos necessitamos fazer uma nova subquery e acrescentar mais um inner join na consulta, o que tornará a query cada vez mais custosa, tanto pelas subqueryes quanto pelos joins.

Ou seja, esta é uma solução que funciona por que retorna o que necessitamos, mas podemos fazer melhor.

Com Window functions vamos obter o mesmo resultado e simplicar muito a query, afinal as funções de agregação são as mesmas o que muda é a forma como as tuplas serão agrupadas. Para este agrupamento utilizaremos duas clausulas a OVER e a PARTITION BY. A clausula OVER permite agrupar as tuplas sem que o detalhe seja escondido e a PARTITION BY indica por qual campo as tuplas serão agrupadas, no caso abaixo pelo ID do departamento.

[code language=”SQL”]

SELECT

e.nome,

d.departamento,

e.salario,

count(e.salario) OVER(PARTITION BY d.id) AS Qtd,

sum(e.salario) OVER(PARTITION BY d.id) AS soma

FROM dbo.empregado e

INNER JOIN dbo.departamento d ON d.id = e.iddepartamento

[/code]

Abaixo o resultado da query acima:

img3

Assim temos o mesmo resultado que com subqueries e joins.

A clausula OVER pode ser utilizada sem a PARTITION BY, neste caso a engine do SQL Server entenderá que a janela de dados são todos os dados retornados.

Podemos ver que com as Window Functions a query fica mais simples, legível e fácil de entender, se for necessário incluir mais algum totalizador é só alterar a clausula SELECT incluído mais algum campo ou mais alguma agregação incluído o OVER e o PARTITION BY.

Nos próximos posts iremos falar das outras categorias, aguarde.

Essa é uma colaboração especial do @tiagocrespi.

Abraço, Rodrigo

Author: SQLCrespi

Rodrigo Crespi é MVP Data Platform, MCT, MCSE, MCSA, MCIT, MCDBA e MCP em SQL Server, possuí outras certificações em .NET Framework, Dynamics CRM e etc. Iniciou trabalhando com SQL Server na versão 6.5 na época como desenvolvedor, logo direcionou a sua carreira para a área de administração de bancos de dados. Atualmente é Sócio da CrespiDB – Soluções em Plataformas de Dados, além de professor universitário na Uniftec – Caxias do Sul Autor de diversos artigos publicados em sites e revistas sobre SQL Server e engenharia de software, mantem o canal SQLCrespi no Youtube, o blog SQLCrespi, além de ser dedicado ao SQL Server RS PASS Chapter Leader do grupo de profissionais em SQL Server do Rio Grande do Sul.

Deixe uma resposta