Nota de Migração: Este artigo foi migrado para o Portal Wiki em Português. O conteúdo será atualizado aqui http://social.technet.microsoft.com/wiki/pt-br/contents/articles/12565.clausula-over.aspx. 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
VALUES
(‘Ozimar’, ’2012-01-12 08:35′)
(‘Bill’, ’2012-01-12 09:02′)
(‘Steven’, ’2012-01-12 09:08′)
(‘Steven’, ’2012-01-12 09:32′)
(‘Ozimar’, ’2012-01-13 08:15′)
(‘Bill’, ’2012-01-13 08:27′)
(‘Steven’, ’2012-01-13 08:54′)
(‘Ozimar’, ’2012-01-14 09:21′)
(‘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
Nome,
COUNT
(*) TotalAcessos
GROUP
BY
) TabGroup
ON
A.Nome=TabGroup.Nome
–SOLUÇÃO COM OVER
Nome, Data,
count
(*) over(partition
by
Nome) TotalAcessos
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.
#Acesso.Nome, #acesso.Data, TabGroup.Total
(*) Total
WHERE
Data<’2012-01-14′
#Acesso.Nome=TabGroup.Nome
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
Nome, Data
ROW_NUMBER() over(partition
order
Data
desc
) Ordem, Nome, Data
) Result
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.
DROP
Abraços
Ozimar Henrique http://ozimar.com
Fernando Lugão Veltem edited Revision 2. Comment: adicionado pt-br ao título
Luciano Lima [MVP] Brazil edited Original. Comment: Adicionado a tag pt-BR no título.