1 / 25

Writing Better Queries with Window Functions

Writing Better Queries with Window Functions. Who am I?. Kathi Kellenberger, MVP Pragmatic Works www.bidn.com/blogs/kathikellenberger kkellenberger@pragmaticworks.com @ auntkathi. Teaching SSRS and SSRS Master Classes.

mohammad-rojas
Download Presentation

Writing Better Queries with Window Functions

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. Writing Better Queries with Window Functions

  2. Who am I? Kathi Kellenberger, MVPPragmatic Workswww.bidn.com/blogs/kathikellenbergerkkellenberger@pragmaticworks.com @auntkathi Teaching SSRS and SSRS Master Classes Beginning SSRS Joes 2 ProsBeginning T-SQL 2012Beginning T-SQL 2008SQL Server MVP Deep Dives (Vol 1)

  3. History 2005: Window functions introduced 2012: Window functions enhanced Best resource: Microsoft SQL Server2012 High-Performance T-SQL Using Window Functions by Itzik Ben-Gan

  4. What are window functions? • Ranking functions • Window aggregates • 2012 Enhancements: ORDER BY • 2012 Enhancements: Framing • Analytic functions Agenda Making Business Intelligent

  5. What are window functions? Function performs over a SET (window of the results) How to identify a window function The OVER clause defines the window • Where to put window functions SELECT and ORDER BY clauses only • Important! The FROM, WHERE, GROUP BY and HAVING clauses operate BEFORE the window functions PartitionsNOT the same as GROUP BY

  6. Ranking functions Available since 2005 ROW_NUMBER() A unique # over the window RANK() Deals with ties DENSE_RANK() Deals with ties NTILE() Divide rows into buckets

  7. ROW_NUMBER() example

  8. ROW_NUMBER() example

  9. Ranking functions: Tuning Sequence Project Operator Index: Partition + Order by + Covering If there is a filter, add filter columns first Watch out for reverse directionor different ordering

  10. Ranking Functions Demo

  11. Window aggregate functions Your favorite aggregates with no GROUP BY Add aggregate function to a non-aggregate query Calculate over the window: the entire result set or partition Pre-2012 functionality NOT optimized for performance

  12. Window aggregate example

  13. Window aggregate example

  14. Window Aggregates Demo

  15. 2012 enhancements ORDER BY clauseadded to window aggregates Performance optimization Even further define the window with ROWS and RANGE: FRAMING

  16. Window aggregate ORDER BY example

  17. Running Total Demo

  18. FRAME: ROWS and RANGE Further define window Terms UNBOUNDED PRECEDING UNBOUNDED FOLLOWING CURRENT ROW # PRECEDING # FOLLOWING RANGE is not fully implementedand can hurt performance

  19. Framing Rows between unbounded preceding and current row

  20. Framing Rows between current row and unbounded following

  21. Framing Rows between 2 preceding and current row

  22. Framing Rows between 5 precedingand 2 following

  23. Framing Demo

  24. The Analytic Functions LAG() and LEAD()Look back or look forward FIRST_VALUE() and LAST_VALUE()The very first or very last PERCENT_RANK() and CUME_DIST()Calculate ranking PERCENTILE_DISC() and PERCENTILE_CONT()Given a percent, find the value

  25. Analytic Functions Demo

More Related