Learning Snowflake SQL and Scripting

Chapter 14. Window Functions

The SQL language has been around for a long time, and for many years the result sets from queries had to be fed into spreadsheets or reporting engines where additional logic was performed to generate the desired final results. For example, subtotals, rankings, and row-to-row comparisons could not be generated within a query and required external processing. Thankfully, modern SQL implementations, including Snowflake’s, offer a host of built-in functions used to generate additional column values after the from, where, group by, and having clauses of a query have been evaluated. These functions, known as window functions, are examined in detail in this chapter.

Windowing Concepts

Before diving into the various window functions, it will be helpful to understand what windows are, how they are defined, and how they are utilized.

Data Windows

Data windows are subsets of rows in a result set. You are already familiar with this concept if you have utilized the group by clause, which groups rows into subsets based on data values. When using group by, you can apply functions such as max(), min(), count(), and sum() across the rows in each group. A data window is similar to a group, except that windows are created when the select clause is being evaluated. Once data windows have been defined, you can apply windowing functions, such as max() and rank(), to the data in each data window.

A data window can span a single row or all of the rows in the result set, ...