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:
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
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:
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.
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
Abaixo temos o resultado desta query onde são mostrados todos os dados que o gestor solicitou:
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.
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
Abaixo o resultado da query acima:
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