Ozimar Henrique
Cláusula OVER
A cláusula OVER determina
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 )
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')
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
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
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'
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'
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
--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.
DROP TABLE #Acesso
Related articles
How to call a function inside my SQL Server?
Database function function and function
How to create an SQL to do multiple inserts in one statement?
Practical examples
Short: SQL Update using begin try and begin transaction
Commit and Rollback command
Getting the last 120 months of data from my database
how to do it in SQL language?
How to create a variable to use the IN clause SELECT query SQL?
Database code part
SQL Update using Try Catch
Na prática com exemplo real
Comando SQL Update usando Try Catch
Na prática e no database
SELECT Format Date Time for many countries
It is a good tip to use day by day
Creating a SiteMap using SQL Database
Como indexar melhor meu site e links?
Transformando linhas em colunas usando SQL Server
veja como funciona