SQL Saturday #100 Brazil
SELECT *
FROM (VALUES('Fabiano Neves Amorim'),
('Sr.Nimbus – SQL Server MVP'),
('[email protected] | @mcflyamorim'),
('http://blogfabiano.com')) AS Tab("Sobre mim:")
Windowing Functions no SQL
Server 2012
Patrocinadores
Agenda







Set Based vs Row by Row
O que são windows functions?
Novas funções implementadas no SQL2012
O que ainda falta?
Window Frame
Demos
Perguntas e respostas
Set Based vs Row by Row
O que são windows functions?
• Similar as funções de agregação
• Retornam um valor a partir de dados
agregados
• Padrão SQL:2008
• Suportado no Oracle, DB2, PostegreSQL
• It’s all about Sets
Clausula OVER
função de agregação + partition by + order by + window frame
SELECT ID_Conta,
DT_Lancamento,
VL_Lancamento,
SUM(VL_Lancamento)
OVER(PARTITION BY ID_Conta
ORDER BY DT_Lancamento
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM tbLancamentos
GO
PARTITION BY
ORDER BY
Default
WINDOW
FRAME
SQL Server 2012
 Suporte quase completo a clausula
OVER()
 Order by
 Partition By
 Window frame
 Novas functions:
• LEAD(), LAG(), FISRT_VALUE(), LAST_VALUE(),
CUME_DIST(), PERCENT_RANK(),
PERCENTILE_CONT(), PERCENTILE_DISC()
Window Frame
[ROWS | RANGE] BETWEEN <Start expr> AND <End expr>
<Start expr>
UNBOUNDED PECEDING: Window inicia na
primeira linha da partição.
CURRENT ROW: Window inicia na linha atual.
<unsigned integer literal> PRECEDING ou
FOLLOWING
<End expr>
UNBOUNDED FOLLOWING: Window termina na
última linha da partição.
CURRENT ROW: Window termina na linha atual.
<unsigned integer literal> PRECEDING ou
FOLLOWING
Window Frame, duas “janelas”
USE NorthWind
GO
SELECT OrderID,
CustomerID
FROM Orders
WHERE CustomerID IN
(1,2)
ORDER BY CustomerID
Window Frame, “janelas” coexistem
Window Frame – First_Value
SELECT OrderID, CustomerID,
FIRST_VALUE(OrderID) OVER(PARTITION BY CustomerID
ORDER BY OrderID) AS FirstOrderID
FROM Orders
WHERE CustomerID IN (1,2)
Window Frame – Last_Value
SELECT OrderID, CustomerID,
LAST_VALUE(OrderID) OVER(PARTITION BY CustomerID
ORDER BY OrderID) AS FirstOrderID
FROM Orders
WHERE CustomerID IN (1,2)
Window Frame – Last_Value
…LAST_VALUE(OrderID)
OVER(PARTITION BY CustomerID ORDER BY OrderID
ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS FirstOrderID ...
Demonstração
O que ainda falta no SQL2012?
 Funções:
 FIRST, retorna o primeiro valor de um grupo ordenado
MAX(Cidade) KEEP (DENSE_RANK FIRST ORDER BY SUM(Valor_Pedido))*
 LAST: último valor de um grupo ordenado
MIN(Cidade) KEEP (DENSE_RANK LAST ORDER BY SUM(Valor_Pedido))*
 NULLs FIRST, NULLs LAST
 OVER(ORDER BY Coluna1 NULLs FIRST)
 Interval (Year, Month, Day, Hour, Minute, Second)
 Window Clause
SELECT LAG(Col1) OVER MinhaWin AS Col1 FROM
Tabela1
WINDOW MinhaWin AS (ORDER BY Coluna1 ROWS 2
PRECEDING)
* não standard
Recursos
 Treinamentos Sr.Nimbus
 http://www.srnimbus.com.br/
 Artigos simple-talk:
 http://tinyurl.com/SQLSat100-WF1
 http://tinyurl.com/SQLSat100-WF2
 Artigos meu blog:
 http://blogfabiano.com/category/windowsfunctions/
 Video SQL Bits IX
 Dave Ballantyne
http://www.sqlbits.com/Sessions/Event9/Whats_n
ew_in_Denali-TSQL
Dúvidas e brindes!
SELECT "Sobre Mim:"
FROM (VALUES('Fabiano Neves Amorim'),
('Sr.Nimbus – SQL Server MVP'),
('[email protected]|@mcflyamorim'),
('http://blogfabiano.com')) AS Tab("Sobre mim:")
?