Window Functions in SQL

by Jonathan Gennick

Window Functions in SQL is a compendium of five articles on window functions (i.e., the OVER clause) as implemented in Microsoft SQL Server 2012. The articles are freely available from my website, at "". You are welcome to read them there at no charge. I have gathered them together into this Kindle book for those who prefer to own a copy of the content on their reader. Window functions provide some of the most expressive problem-solving syntax and capability to hit the SQL language in years. They are truly a game-changer. They do away with the need to spend hours writing and debugging obtuse queries involving self-joins and nested-subqueries just to generate results that you can describe to a human in a matter of seconds. It is now trivial to mix summary and detail results, generate running sums and moving averages, and much more.The following five articles take you on a tour of the functionality. Each article begins with a business scenario and applies a specific aspect of syntax or set of window functions to quickly and accurately generate useful results for the business. The goal in these articles is less about learning the raw syntax -- you can read the manuals for that -- and more about learning to recognize the general types of business and query problems to which window functions can be applied.1) Detail in the One of the most common use cases for window functions in SQL Server is to mix or compare summary and detail data in the same row.2) H.G. Wells and Analytic functions such as LAG and LEAD are perfect for working with time-series data in which the time interval from row to row is consistent.3) A look at the underlying mechanics of window functions and how they are executed.4) Pulling Rank for the Attack business questions involving words or phrases such as “topmost” or “bottommost”, “top N” or “bottom N”, or that are otherwise answerable by ranking the rows in a result set according to some criteria that you can apply to one or more columns of data.5) You've Got Framing! Framing clause support in SQL Server 2012 makes it easy to compute running totals, moving averages, and to otherwise examine data as it moves through a frame of reference.Do you enjoy the challenge of writing a query that will confound future programmers and leave them unable to discern what you did and why? Then don't read this book. But if you're interested in getting the job done and getting home for dinner on time, then you can't afford to be without knowledge of window functions and the expressive power they bring to the table. They truly are a game-changer. Window functions are now implemented across most database platforms. You will find them supported in Oracle Database (where they are termed "analytic functions"), IBM DB2, PostgreSQL, and now SQL Server 2012 and beyond. Everything you read in these articles should apply across the board. Queries should execute as shown in all database brands. However, the example data is specific to Microsoft SQL Server, and the example queries and their outputs as shown in the articles were generated from Microsoft SQL Server.

44 pages, Kindle Edition

First published January 16, 2012