Window Functions in SQL

Last Updated: 15th September, 2024

Window functions perform aggregation operations on a set of query rows. However, aggregation operations group query rows into a single result row, whereas window functions produce results for each query row. In this lesson, we will see what windows functions in SQL are and the most common windows functions.

What is Windows Function?

Windows functions are built-in functions that allow you to perform calculations across rows that are related to the current row. These functions are commonly used in analytical queries and data processing operations to perform various aggregations, ranking, and grouping functions on data sets.

In general, a Windows function involves defining a window or subset of rows within the dataframe or group and applying a function to that window. The syntax usually involves specifying the window using a set of conditions or criteria, such as the range of rows or the partition key, and then specifying the function to apply.

Syntax of Windows Function

The syntax of a Windows Function in PostgreSQL is as follows:

<window function>([argument1 [, argument2, ...]]) 
    OVER ([PARTITION BYpartition_expression, ... ] 
                [ORDER BY sort_expression [ASC | DESC], ... ] 
                [frame_clause] )

Explanation:

• <window function>: the name of the window function to be used, such as SUM, AVG, MAX, MIN, etc.
• argument1,argument2,…: the arguments to the window function (if any).
• PARTITION BY: a clause used to group rows into partitions based on the values of one or more columns.
• ORDER BY: a clause used to order the rows within each partition based on one or more columns.
• ASC | DESC: specifies the order in which the rows should be sorted (ascending or descending).
• frame_clause: specifies the range of rows to be included in the window frame, such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW or RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING.

The OVER clause is required for a window function to operate as a window function. It defines the window specification, which includes the data's partitioning, ordering, and framing.

Example:

Suppose we have a table of sales that contains the following data:

We can use the AVG window function to calculate the average sales for each region and show the result alongside each row of the table. Here's the SQL query to do that:

SELECT region, month, sales,
       AVG(sales) OVER (PARTITION BY region ORDER BY month) AS avg_sales
FROM sales;

The result of the query would be:

In this example, we're using the AVG window function to calculate the average sales for each region. The OVER clause is used to specify the partitioning of the data by region and the ordering of the data by month. The AVG(sales) function is the window function that is applied to each partition, and it calculates the average sales for the rows within that partition. The result is a new column, avg_sales, that shows the average sales for each region in each month.

How does Windows Function in SQL Differ from the GroupBy Function?

• The Windows and GroupBy functions are used in data analysis and manipulation but serve different purposes.
• The GroupBy function is utilized to group data by one or more columns in a dataframe or a database table.
• This function combines all the rows with the same value in the specified column(s) and calculates aggregate functions like sum, count, mean, or any custom function on the grouped data.
• The result of the GroupBy function is a new dataframe or table with the grouped data and the calculated aggregate values.
• On the other hand, the Windows function performs calculations on a subset of rows within a group or the entire dataframe.
• It enables the calculation of metrics such as moving averages, cumulative sums, rank, and percentiles of a subset of rows relative to other rows in the same group or the entire dataframe.
• Unlike the GroupBy function, the Windows function does not combine or aggregate rows. Instead, it applies a function to a specific subset of rows in the dataframe or group.

Commonly used Windows Function

Before discussing windows functions, we will first define a table to understand how we use windows functions. Suppose we have a table student that contains the following data:

Here are some of the commonly used Windows functions in SQL:

1. ROW_NUMBER():

The ROW_NUMBER() window function assigns a unique sequential integer to each row within a partition of a result set. It is often used to generate a unique identifier for each row or to rank the rows based on a specific order.

Here's an example of using the ROW_NUMBER() window function in PostgreSQL:

We can use the ROW_NUMBER() window function to assign a unique number to each row based on the grade in descending order. Here's the SQL query to do that:

SELECT id, name, grade, 
       ROW_NUMBER() OVER (ORDER BY grade DESC) as row_num
FROM students;

The result of the query would be:

In this example, we're using the ROW_NUMBER() window function to assign a unique sequential number to each row based on the grade in descending order. The OVER clause is used to specify the ordering of the data by grade. The result is a new column, row_num, that shows the ranking of each student based on their grade.

2. RANK():

The RANK() window function is used to assign a rank to each row within a partition of a result set based on the values in one or more columns. It is similar to the ROW_NUMBER() function but can result in tied rankings.

Here's an example of using the RANK() window function in PostgreSQL:

We can use the RANK() window function to assign a rank to each row based on the grade in descending order. Here's the SQL query to do that:

SELECT id, name, grade, 
       RANK() OVER (ORDER BY grade DESC) as rank
FROM students;

The result of the query would be:

In this example, we're using the RANK() window function to assign a rank to each row based on the grade in descending order. The OVER clause is used to specify the ordering of the data by grade. The result is a new column rank that shows the rank of each student based on their grade. Note that Frank and Charlie have the same grade of 85, so they have been assigned the same rank of 3, resulting in Alice's rank being 5 instead of 4.

3. DENSE_RANK():

The DENSE_RANK() window function assigns a rank to each row within a partition of a result set. It is similar to the ROW_NUMBER() function but does not leave gaps in the ranking when there are ties. If there are ties in the ranking, the next rank is assigned based on the number of tied rows.

Here's an example of using the DENSE_RANK():

SELECT id, name, grade, 
       DENSE_RANK() OVER (ORDER BY grade DESC) as dense_rank
FROM students;

We can use the DENSE_RANK() window function to assign a rank to each row based on the grade in descending order. Here's the SQL query to do that:

The result of the query would be:

In this example, we're using the DENSE_RANK() window function to assign a rank to each row based on the grade in descending order. The OVER clause is used to specify the ordering of the data by grade. The result is a new column, dense_rank, that shows the dense rank of each student based on their grade. Charlie and Frank have the same grade, so they both get rank 3. The next rank is 4, not 5, as it would be with ROW_NUMBER().

4. NTILE():

The NTILE() function is a window function in PostgreSQL that is used to divide a result set into a specified number of groups, or "buckets", based on a specified expression. It assigns a bucket number to each row within a partition of a result set.

Here's an example of using the NTILE() window function :

SELECT id, name, grade, 
       NTILE(3) OVER (ORDER BY grade DESC) as bucket_num
FROM students;

We want to divide the students into three buckets based on their grades. We can use the NTILE() function to assign each student to one of the three buckets. Here's the SQL query to do that:

The result of the query would be:

n this example, we're using the NTILE() window function to divide the students into three buckets based on their grade. The OVER clause is used to specify the ordering of the data by grade in descending order. The result is a new column, bucket_num, that shows the bucket number assigned to each student.

5. LAG():

The LAG() function is a window function in PostgreSQL that enables access to a previous row in a result set. It can be used to compare the values of the current row with the previous row or to calculate the difference between two consecutive rows.

Here's an example of using the LAG() window function:

We can use the LAG() window function to calculate the difference in grade between each student and the previous student based on their ordering by ID. Here's the SQL query to do that:

SELECT id, name, grade, 
       grade - LAG(grade, 1, 0) OVER (ORDER BY id) as grade_diff
FROM students;

The result of the query would be:

In this example, we're using the LAG() window function to calculate the difference in grade between each student and the previous student based on their ordering by ID. The OVER a clause is used to specify the ordering of the data by ID. The result is a new column grade_diff that shows the difference in grade between each student and the previous student, with a default value of 0 for the first row.

6. LEAD():

The LEAD() function is a window function that allows you to access the value of a subsequent row in the same result set. It is often used to compare the current row with the next row or to calculate the change or difference between consecutive rows.

Here's an example of using the LEAD() function:

Suppose we calculate the difference in grades between each student and the next student on the list. We can use the LEAD() function to retrieve the grade of the next student and then subtract it from the current student's grade. Here's the SQL query to do that:

SELECT name, grade, LEAD(grade) OVER (ORDER BY grade DESC) - grade as grade_diff
FROM students;

The result of the query would be:

In this example, we're using the LEAD() function to retrieve the grade of the next student in descending order. The OVER clause is used to specify the ordering of the data by grade. The result is a new column, grade_diff, that shows the difference in grades between each student and the next student in the list. Note that the last row has a null value for grade_diff because there is no subsequent row to compare it.

7. SUM(), AVG(), MIN(), MAX(), COUNT():

Aggregate functions that compute a single result for a group of rows within a partition.

We can use the window functions to calculate various student grade metrics. Here's an example SQL query that calculates the sum, average, minimum, maximum, and count of grades for each row:

SELECT id, name, grade,
       SUM(grade) OVER () AS sum_grades,
       AVG(grade) OVER () AS avg_grades,
       MIN(grade) OVER () AS min_grade,
       MAX(grade) OVER () AS max_grade,
       COUNT(grade) OVER () AS count_grades
FROM students;

The result of the query would be:

In this example, we're using various window functions to calculate different metrics for the students' grades. The OVER clause is used to specify that these calculations should be performed over the entire result set (i.e. no partitioning is necessary). The result is a new set of columns sum_grades, avg_grades, min_grade, max_grade, and count_grades, which show the sum, average, minimum, maximum, and count of grades, respectively, for each row.

Complete List of SQL Window Functions

Window functions operate over a window of rows defined by the OVER() clause.

Here are the primary types of window functions, along with a list of common window functions under each type:

1. Ranking Functions

These functions assign a rank to each row within a partition.

• ROW_NUMBER(): Assigns a unique sequential number to rows, starting at 1 for each partition.
• RANK(): Similar to ROW_NUMBER(), but rows with the same values receive the same rank, and the next rank is skipped accordingly.
• DENSE_RANK(): Similar to RANK(), but does not skip ranks when there are ties (duplicate values).
• NTILE(n): Divides rows in a partition into n buckets and assigns a bucket number to each row.

2. Aggregate Functions

These functions return a scalar value calculated from values in a set of rows. When used as window functions, they calculate values across a defined window.

• SUM(): Returns the sum of the values in the window.
• AVG(): Returns the average value of the values in the window.
• MIN(): Returns the minimum value in the window.
• MAX(): Returns the maximum value in the window.
• COUNT(): Returns the count of values in the window.

3. Value Functions

These functions return values relative to the current row in the window.

• FIRST_VALUE(): Returns the first value in the window frame.
• LAST_VALUE(): Returns the last value in the window frame.
• LAG(offset, default): Returns the value from the row before the current row by the specified offset. You can specify a default value if no previous row exists.
• LEAD(offset, default): Returns the value from the row after the current row by the specified offset. You can specify a default value if no next row exists.

4. Offset Functions

These functions provide access to a row at a specific offset from the current row.

• LAG(): Accesses data from a previous row within the same result set.
• LEAD(): Accesses data from a subsequent row within the same result set.

5. Rowset Functions

These functions calculate values based on a set of rows relative to the current row.

• CUME_DIST(): Calculates the cumulative distribution of a value in a set of values.
• PERCENT_RANK(): Calculates the relative rank of a row within a partition.
• PERCENTILE_CONT(): Calculates a continuous percentile value based on interpolation between values.
• PERCENTILE_DISC(): Calculates the discrete percentile value.

6. Statistical/Numeric Functions

These functions perform numerical and statistical analysis on a set of rows.

• STDDEV(): Returns the standard deviation of the values in the window.
• VARIANCE(): Returns the variance of the values in the window.
• COVAR_POP(): Returns the population covariance of a set of number pairs.
• COVAR_SAMP(): Returns the sample covariance of a set of number pairs.
• CORR(): Returns the correlation coefficient of a set of number pairs.

7. Windowed Specific Variants of Standard Functions

These are standard SQL functions with windowed versions that apply over a specific window of rows.

• OVER(): Defines a window frame over which the window function will operate.
• PARTITION BY: Divides the result set into partitions to which the window function is applied.
• ORDER BY: Specifies the order of rows within the window.

Advanced Windowing Syntax in SQL

Advanced windowing syntax in SQL includes powerful features such as frame specifications, the EXCLUDE clause, the FILTER clause, and window chaining. These features offer greater control over how window functions are applied, especially when working with complex analytical queries. Below is a detailed explanation of each.

1. Frame Specifications (ROWS vs. RANGE)

Frame specifications allow you to define the range of rows over which the window function is applied within the partition. The two most common types of frame specifications are ROWS and RANGE:

• ROWS: Defines a frame based on a specific number of rows around the current row. It looks at the physical number of rows.
• RANGE: Defines a frame based on the values in the ORDER BY clause. It operates on logical ranges of values rather than physical rows.

Syntax:

Example:

This calculates the sum of the current row, the three preceding rows, and the next row.

Frame options:

• UNBOUNDED PRECEDING: From the start of the partition.
• CURRENT ROW: Only the current row.
• N PRECEDING: A specific number of rows before the current row.
• N FOLLOWING: A specific number of rows after the current row.
• UNBOUNDED FOLLOWING: All rows to the end of the partition.

2. EXCLUDE Clause

The EXCLUDE clause (part of SQL:2011 standard) is used to explicitly exclude certain rows from the frame while calculating a window function. This is useful when you want more control over which rows are considered in your window function.

Syntax:

Exclusion Options:

• CURRENT ROW: Excludes the current row.
• GROUP: Excludes all rows that are peers (equal in the ORDER BY clause).
• TIES: Excludes rows that have the same ORDER BY values as the current row.
• NO OTHERS: No rows are excluded (default behavior).

Example:

This calculates the sum of all previous salaries, excluding the current row.

3. FILTER Clause

The FILTER clause allows you to apply a condition to aggregate functions so that only rows that meet the condition are included in the calculation.

Syntax:

Example:

In this example, only salaries greater than 50,000 are summed, partitioned by department.

You can use the FILTER clause with any aggregate function such as SUM(), COUNT(), AVG(), MIN(), or MAX().

4. Window Chaining

Window chaining allows you to apply multiple window functions on the same result set by reusing a window definition or extending an existing window specification.

Syntax:

Example:

In this example, a window named w is defined, which partitions the result set by department_id. The SUM() function uses the window as defined, while the AVG() function reuses w and adds an ORDER BY clause with a frame specification for running averages.

5. Combining Frame Specifications, EXCLUDE, FILTER, and Chaining

These advanced features can be combined to create highly specific and powerful window queries.

Example: Combining multiple advanced windowing features

In this query:

1. SUM(amount) computes the sum of amounts from the last two transactions, excluding the current transaction.
2. AVG(amount) only includes transactions where the amount is greater than 100.
3. COUNT(transaction_id) counts all transactions for each customer.

How to Use a Window Function in SQL?

Step 1: Create the Table

Let’s start by creating a simple table to illustrate the use of window functions. We'll use a sales table with columns for salesperson, region, month, and amount.

Step 2: Insert Data

Now, insert sample data into the sales table.

Create and insert data

Step 3: Basic Window Functions

1. ROW_NUMBER()

This function assigns a unique sequential number to rows within a partition of a result set, starting at 1 for the first row in each partition.

Explanation:

• We partitioned the data by region and ordered by amount DESC to assign row numbers to each salesperson within each region.

Output:

Row number

2. RANK()

This function assigns a rank to rows within a partition of a result set. The rank of a row is one plus the number of ranks that come before it.

Output:

Rank

Notice that ranks can be tied.

3. DENSE_RANK()

Similar to RANK(), but it does not leave gaps in ranking when there are ties.

Output:

Dense rank

3. NTILE()

This function divides rows in the result set into a specified number of approximately equal parts (buckets).

Explanation: This divides the rows into three buckets based on the amount.

Output:

ntile

Step 4: Aggregate Functions with Windowing

1. SUM()

Window functions can be combined with aggregate functions. The following example calculates the cumulative sum of amount by salesperson:

Output:

Sum

2. AVG()

Similarly, we can calculate a running average:

Output:

Avg

Step 5: Advanced Window Function Concepts

1. LAG() and LEAD()

• LAG() retrieves the value from the previous row.
• LEAD() retrieves the value from the next row.

Example: Comparing the current month’s amount with the previous month’s value using LAG().

Output:

Lag

2. FIRST_VALUE() and LAST_VALUE()

These functions return the first or last value in the window.

Example: Retrieve the first sales amount in each region.

Output:

First Value

Step 6: Frame Specifications

Running Total with ROWS Frame

Output:

Running Total with ROWS Frame

This query calculates a cumulative running total of sales amounts from the first row up to the current row.

Running Total with Filtering Rows (Exclude Rows Where Amount < 500)

Output:

Running Total with Filtering Rows

This query calculates a running total but only includes sales amounts of 500 or more, treating lesser amounts as 0

Two-Month Rolling Sum

Output:

Running Total with Filtering Rows

This query calculates the sum of sales for the current month and the previous month, creating a two-month rolling total.

Sum of Previous Rows (Exclude Current Row)

SELECT salesperson, month, amount,

SUM(amount) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS sum_excluding_current

FROM sales;

Output:

Sum of Previous Rows

This query calculates the sum of all previous sales amounts but excludes the current row’s amount.

Using LAG() to Get Previous Month’s Amount

Output:

Using LAG() to Get Previous Month’s Amount

This query retrieves the sales amount from the previous month for each row, returning NULL for the first row as there is no previous value.

By applying these window functions, you can solve complex analytical queries without needing to resort to self-joins or subqueries.

Conclusion

In conclusion, window functions in SQL Server are used to calculate calculations across related rows of data sets. Unlike aggregate operations that group query rows into a single result row, a window function produces a result for each query row.

Key Takeaways

• This lesson explains Windows Function and how it differs from GroupBy Function in SQL.
• Windows Function allows you to perform calculations across rows that are related to the current row. At the same time, GroupBy Function is used to group data by one or more columns in a dataframe or a database table.
• The lesson also provides the syntax of the Windows Function in PostgreSQL and an example.
• Additionally, it lists some of the commonly used Windows Functions in SQL, including ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LAG(), and LEAD().

Quiz

1. What is a Window Function in SQL? 
   1. A function that performs operations on a set of rows and produces a result for each row 
   2. A function that groups rows into a single result row  
   3. A function that calculates aggregate functions on a dataset  
   4. A function that sorts rows within a dataset

Answer:a. A function that performs operations on a set of rows and produces a result for each row

2. Which clause is required for a Window Function to operate in SQL?
   1. ORDER BY  
   2. PARTITION BY  
   3. GROUP BY 
   4. HAVING

Answer:b. PARTITION BY

3. Which Window Function in SQL assigns a unique sequential integer to each row within a partition of a result set?
   1. RANK()
   2. DENSE_RANK()
   3. ROW_NUMBER()  
   4. NTILE()

Answer:c. ROW_NUMBER()

4. Which Window Function in SQL returns the rank of each row within a result set, with ties receiving the same rank and leaving gaps? 
   1. RANK()  
   2. DENSE_RANK()  
   3. ROW_NUMBER() 
   4. NTILE()

Answer:a. RANK()

5. Which Window Function in SQL returns the average value of a specified column over a partition of a result set?  
   1. SUM()  
   2. COUNT()  
   3. AVG() 
   4. MAX()

Answer:c. AVG()