Comando over partition

Cláusula OVER
A cláusula OVER determina o particionamento e a ordenação do conjunto de linhas antes da aplicação de funções de janela (Agregação e Classificação).

A sintaxe é a seguinte:

-Para Funções de Agregação (SUM – COUNT – AVG – etc.)
OVER(Partition by Campo)
-Para Funções de Classificação (RANK – NTILE – DENSE_RANK – ROW_NUMBER)
OVER(Partition by Campo Order by Campo)

Exemplo:

1-) Criação do ambiente. Criaremos uma tabela temporária que registra os acessos dos funcionários.
CREATE TABLE #Acesso
(
Codigo int identity(1,1) primary key, Nome Varchar(50), Data datetime
)
–Preenchimento da tabela
INSERT INTO #Acesso VALUES(‘Ozimar’, ’2012-01-12 08:35′)
INSERT INTO #Acesso VALUES(‘Bill’, ’2012-01-12 09:02′)
INSERT INTO #Acesso VALUES(‘Steven’, ’2012-01-12 09:08′)
INSERT INTO #Acesso VALUES(‘Steven’, ’2012-01-12 09:32′)
INSERT INTO #Acesso VALUES(‘Ozimar’, ’2012-01-13 08:15′)
INSERT INTO #Acesso VALUES(‘Bill’, ’2012-01-13 08:27′)
INSERT INTO #Acesso VALUES(‘Steven’, ’2012-01-13 08:54′)
INSERT INTO #Acesso VALUES(‘Ozimar’, ’2012-01-14 09:21′)
INSERT INTO #Acesso VALUES(‘Steven’, ’2012-01-14 09:32′)
OBJETIVO: Listar a relação de acessos dos funcionários incluindo uma coluna com o total de acessos.
–SOLUÇÃO SEM OVER
SELECT A.Nome, A.Data, TabGroup.TotalAcessos
FROM #Acesso A
INNER JOIN
(
SELECT Nome, COUNT(*) TotalAcessos FROM #Acesso GROUP BY Nome
) TabGroup ON A.Nome=TabGroup.Nome
–SOLUÇÃO COM OVER
SELECT Nome, Data, count (*) over (partition by Nome) TotalAcessos
FROM #Acesso
A cláusula Partition By funciona como se fosse um Group By. Criando grupos, que chamamos de janelas.
Veja que o código ficou bem mais simples, porém a vantagem fica ainda maior quando começamos a exigir mais da consulta.
Por exemplo, se precisar excluir um dia nesta pesquisa, no primeiro exemplo você irá colocar o filtro nas duas consultas, sendo que no segundo exemplo bastará um filtro, veja abaixo.
–SOLUÇÃO SEM OVER