T-SQL Window Functions: For data analysis and beyond, 2nd Edition

T-SQL Window Functions: For data analysis and beyond, 2nd Edition

by Itzik Ben-Gan
Released November 2019
Publisher(s): Microsoft Press
ISBN: 9780135861554

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to live online training experiences, plus books, videos, and digital content from O’Reilly and nearly 200 trusted publishing partners.

Start your free trial

Book description

Use window functions to write simpler, better, more efficient T-SQL queries

Most T-SQL developers recognize the value of window functions for data analysis calculations. But they can do far more, and recent optimizations make them even more powerful. In T-SQL Window Functions, renowned T-SQL expert Itzik Ben-Gan introduces breakthrough techniques for using them to handle many common T-SQL querying tasks with unprecedented elegance and power. Using extensive code examples, he guides you through window aggregate, ranking, distribution, offset, and ordered set functions. You'll find a detailed section on optimization, plus an extensive collection of business solutions — including novel techniques available in no other book.

 

Microsoft MVP Itzik Ben-Gan shows how to:

• Use window functions to improve queries you previously built with predicates

• Master essential SQL windowing concepts, and efficiently design window functions

• Effectively utilize partitioning, ordering, and framing

• Gain practical in-depth insight into window aggregate, ranking, offset, and statistical functions

• Understand how the SQL standard supports ordered set functions, and find working solutions for functions not yet available in the language

• Preview advanced Row Pattern Recognition (RPR) data analysis techniques

• Optimize window functions in SQL Server and Azure SQL Database, making the most of indexing, parallelism, and more

• Discover a full library of window function solutions for common business problems

 

About This Book

• For developers, DBAs, data analysts, data scientists, BI professionals, and power users familiar with T-SQL queries

• Addresses any edition of the SQL Server 2019 database engine or later, as well as Azure SQL Database

 

Get all code samples at: MicrosoftPressStore.com/TSQLWindowFunctions/downloads

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Dedication Page
  5. Contents at a Glance
  6. Contents
  7. About the Author
  8. Introduction
  9. Chapter 1. SQL Windowing
    1. Evolution of Window Functions
    2. Background of Window Functions
    3. A Glimpse of Solutions Using Window Functions
    4. Elements of Window Functions
    5. Query Elements Supporting Window Functions
    6. Potential for Additional Filters
    7. Reuse of Window Definitions
    8. Summary
  10. Chapter 2. A Detailed Look at Window Functions
    1. Window Aggregate Functions
    2. Ranking Functions
    3. Statistical Functions
    4. Offset Functions
    5. Summary
  11. Chapter 3. Ordered Set Functions
    1. Hypothetical Set Functions
    2. Inverse Distribution Functions
    3. Offset Functions
    4. String Concatenation
    5. Summary
  12. Chapter 4. Row-Pattern Recognition in SQL
    1. Background
    2. Feature R010, “Row-Pattern Recognition: FROM Clause”
    3. Feature R020, “Row-Pattern Recognition: WINDOW Clause”
    4. Solutions Using Row-Pattern Recognition
    5. Summary
  13. Chapter 5. Optimization of Window Functions
    1. Sample Data
    2. Indexing Guidelines
    3. Emulating NULLS LAST Efficiently
    4. Improved Parallelism with APPLY
    5. Batch-Mode Processing
    6. Ranking Functions
    7. Aggregate and Offset Functions
    8. Distribution Functions
    9. Summary
  14. Chapter 6. T-SQL Solutions Using Window Functions
    1. Virtual Auxiliary Table of Numbers
    2. Sequences of Date and Time Values
    3. Sequences of Keys
    4. Paging
    5. Removing Duplicates
    6. Pivoting
    7. TOP N per Group
    8. Emulating IGNORE NULLS to Get the Last Non-NULL
    9. Mode
    10. Trimmed Mean
    11. Running Totals
    12. Max Concurrent Intervals
    13. Packing Intervals
    14. Gaps and Islands
    15. Median
    16. Conditional Aggregate
    17. Sorting Hierarchies
    18. Summary
  15. Index
  16. Code Snippets

Product information

  • Title: T-SQL Window Functions: For data analysis and beyond, 2nd Edition
  • Author(s): Itzik Ben-Gan
  • Release date: November 2019
  • Publisher(s): Microsoft Press
  • ISBN: 9780135861554