1 / 17

SQL Saturday #100 Brazil

SQL Saturday #100 Brazil. Windowing Functions no SQL Server 2012. SELECT * FROM ( VALUES ( 'Fabiano Neves Amorim' ), ( 'Sr.Nimbus – SQL Server MVP' ), ( 'fabiano.amorim@srnimbus.com.br | @mcflyamorim' ), ( 'http://blogfabiano.com' )) AS Tab ( "Sobre mim:" ). Patrocinadores. Agenda.

cardea
Download Presentation

SQL Saturday #100 Brazil

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Saturday #100 Brazil Windowing Functions no SQL Server 2012 SELECT* FROM (VALUES('Fabiano Neves Amorim'), ('Sr.Nimbus – SQL Server MVP'), ('fabiano.amorim@srnimbus.com.br | @mcflyamorim'), ('http://blogfabiano.com'))ASTab("Sobre mim:")

  2. Patrocinadores

  3. Agenda Set Based vs Row by Row O quesão windows functions? Novasfunçõesimplementadas no SQL2012 O queaindafalta? WindowFrame Demos Perguntas e respostas

  4. Set Based vs Row by Row

  5. O quesã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

  6. 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

  7. SQL Server 2012 • Suporte quasecompletoa clausula OVER() • Order by • Partition By • Window frame • Novas functions: • LEAD(), LAG(), FISRT_VALUE(), LAST_VALUE(), CUME_DIST(), PERCENT_RANK(), PERCENTILE_CONT(), PERCENTILE_DISC()

  8. WindowFrame [ROWS | RANGE] BETWEEN <Start expr> AND <End expr> <Start expr> UNBOUNDED PECEDING: Windowinicia na primeira linha da partição. CURRENT ROW: Windowinicia na linha atual. <unsignedinteger literal> PRECEDING ou FOLLOWING <Endexpr> UNBOUNDED FOLLOWING: Windowtermina na última linha da partição. CURRENT ROW: Windowtermina na linha atual. <unsignedinteger literal> PRECEDING ou FOLLOWING

  9. WindowFrame, duas “janelas” USE NorthWind GO SELECT OrderID, CustomerID FROM Orders WHERE CustomerID IN (1,2) ORDER BY CustomerID

  10. WindowFrame, “janelas” coexistem

  11. WindowFrame – First_Value SELECT OrderID, CustomerID, FIRST_VALUE(OrderID) OVER(PARTITION BY CustomerID ORDER BY OrderID) AS FirstOrderID FROM Orders WHERE CustomerID IN (1,2)

  12. WindowFrame – Last_Value SELECT OrderID, CustomerID, LAST_VALUE(OrderID) OVER(PARTITION BY CustomerID ORDER BY OrderID) AS FirstOrderID FROM Orders WHERE CustomerID IN (1,2)

  13. WindowFrame – Last_Value …LAST_VALUE(OrderID) OVER(PARTITION BY CustomerIDORDER BY OrderID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FirstOrderID...

  14. Demonstração

  15. O que ainda falta no SQL2012? SELECT LAG(Col1) OVER MinhaWin AS Col1 FROM Tabela1 WINDOW MinhaWin AS (ORDER BY Coluna1 ROWS 2 PRECEDING) • Funções: • FIRST, retorna o primeiro valor de um grupoordenadoMAX(Cidade) KEEP (DENSE_RANK FIRST ORDER BY SUM(Valor_Pedido))* • LAST: último valor de um grupoordenadoMIN(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 * não standard

  16. Recursos • Treinamentos Sr.Nimbus • http://www.srnimbus.com.br/ • Artigos simple-talk: • http://tinyurl.com/SQLSat100-WF1 • http://tinyurl.com/SQLSat100-WF2 • Artigosmeu blog: • http://blogfabiano.com/category/windows-functions/ • Video SQL Bits IX • Dave Ballantyne http://www.sqlbits.com/Sessions/Event9/Whats_new_in_Denali-TSQL

  17. Dúvidas e brindes! SELECT"Sobre Mim:" FROM (VALUES('Fabiano Neves Amorim'), ('Sr.Nimbus – SQL Server MVP'), ('fabiano.amorim@srnimbus.com.br|@mcflyamorim'), ('http://blogfabiano.com'))ASTab("Sobre mim:") ?

More Related