1 / 33

T-SQL Window Function Deep Dive part 1

T-SQL Window Function Deep Dive part 1. Kathi Kellenberger @ auntkathi Kathi.kellenberger@red-gate.com http://auntkathisql.com. What are T-SQL window functions? 2005 Ranking functions Window aggregates 2012 Enhancements Accumulating window aggregates Framing Offset functions

Ava
Download Presentation

T-SQL Window Function Deep Dive part 1

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. T-SQL Window Function Deep Dive part 1 Kathi Kellenberger @auntkathi Kathi.kellenberger@red-gate.com http://auntkathisql.com

  2. What are T-SQL window functions? 2005 Ranking functions Window aggregates 2012 Enhancements Accumulating window aggregates Framing Offset functions Statistical functions Agenda

  3. What are window functions? Not OS, based on ANSI-SQL standards 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

  4. 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 ORDER BY is required in OVER clause

  5. ROW_NUMBER() example

  6. Demo 1 Ranking functions

  7. 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 No ORDER BY Partition by OR use the Empty Over Clause

  8. Window Aggregate example

  9. Demo 2 Window aggregates

  10. 2012 enhancements Accumulation aggregates Even further define the window with FRAMING Eight new functions Offset Statistical

  11. Accumulating aggregate example

  12. Demo 3 Accumulating aggregates

  13. FRAMING: ROWS and RANGE Further define the window Each row has its own window Supported for specific function types

  14. Framing: Vocabulary

  15. FRAMING ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ROWS UNBOUNDED PRECEDING

  16. FRAMING ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

  17. FRAMING ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

  18. Demo 4 Framing

  19. OFFSET Functions LAG() Grab a column from a previous row LEAD() Grab a column from a later row FIRST_VALUE() Grab a column from the first row Supports framing LAST_VALUE() Grab a column from the last row Supports framing -- be careful!!

  20. LAG example

  21. LEAD example

  22. First_VALUE example

  23. LAST_VALUE example

  24. Demo 5 Offset functions

  25. Statistical functions PERCENT_RANK() Relative rank. “My score is better than 90% of the scores” CUME_DIST() Cumulative distribution over a group of rows. “My score is at 90%” PERCENTILE_DISC() Computes a specific percentile PERCENTILE_CONT() Computes an exact percentile

  26. PERCENT_RANK example 25, 50, 75, 100

  27. CUME_DIST example 25, 50, 75, 100

  28. PERCENTILE_DISC example 0.5 Percentile_Disc = 50

  29. PERCENTILE_CONT example 0.5 Percentile_cont = 62.5

  30. Demo 6 Statistical functions

  31. My book: Expert T-SQL Window Functions Itzik Ben-Gan’s book: Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions http://www.auntkathisql.com My Pluralsight course Resources

  32. Just like Jimi Hendrix …  We love to get feedback Please complete the session feedback forms

  33. SQLBits - It's all about the community... Please visit Community Corner, we are trying this year to get more people to learn about the SQL Community, equally if you would be happy to visit the community corner we’d really appreciate it.

More Related