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
SELECT #Acesso.Nome, #acesso.Data, TabGroup.Total FROM #Acesso INNER JOIN ( SELECT Nome, COUNT(*) Total FROM #Acesso WHERE Data<'2012-01-14' GROUP BY Nome ) TabGroup ON #Acesso.Nome=TabGroup.Nome WHERE Data<'2012-01-14'

--SOLUÇÃO COM OVER
SELECT Nome, Data,count(*) over(partition by Nome) TotalAcessos FROM #Acesso WHERE Data<'2012-01-14'

Vamos ver agora o uso do OVER com a função ROW_NUMBER() do grupo de Ranking window functions.

OBJETIVO: Listar o último acesso dos funcionários
--SOLUÇÃO SELECT Nome, Data FROM ( SELECT ROW_NUMBER() over(partition by Nome order by Data desc) Ordem, Nome, Data FROM #Acesso ) Result WHERE Ordem=1

A função ROW_NUMBER() retorna o número sequencial de uma linha em uma partição de um conjunto de resultados, iniciando em 1 para a primeira linha de cada partição.

Esta cláusula OVER será útil para o meu próximo Post-Ranking window functions(RANK-NTILE-DENSE_RANK-ROW_NUMBER)

Não esqueça de apagar a tabela temporária.