You are currently viewing WINDOW FUNCTIONS IN SQL: THE WINDOW FRAME

WINDOW FUNCTIONS IN SQL: THE WINDOW FRAME

Have you been battling window frames while utilizing window functions in your SQL queries? Window frames within SQL window functions is a powerful tool for data analysis that allows you to control the scope of calculations, customize your findings, and precisely solve data challenges.

In this article, we will explore what a window frame is, several methods of defining window frames in SQL with real life examples, various window bounds within a window frame, differences between the ROWS BETWEEN and RANGE BETWEEN clauses and how best to use them. Let’s explore!

WHAT IS A WINDOW FRAME IN SQL WINDOW FUNCTION?

Before we delve into the concept of a window frame in SQL, let’s briefly recap what a Window Function is. In SQL, a Window Function is a robust capability that enables you to perform calculations or operations on a group of table rows, often referred to as “records,” that are associated with the current row. This group of related records is referred to as a “window” or “window frame.”

A window frame represents a specific subset of rows within a partition over which window function (an aggregation, calculation, or operation) is applied. Essentially, it’s a collection of rows that have a connection to the current row where the window function is utilized for computation. The composition of the window frame can vary for each row in the query result, as it depends on the specific row being processed. This means that every row in the query result has its own unique window frame.

A window frame can be defined using the PARTITION BY subclause or ROWS or RANGE subclause within the OVER() clause of a window function.

From the picture above, the table consists of sales record which made up of four window frames defined by the ProductID column. Window frame 1 denoted by the blue border has records for product with ProductID 700, Window frame 2 denoted by the red border has records for product with ProductID 701, Window frame 3 denoted by the black border has records for product with ProductID 702, Window frame 4 denoted by the yellow border has records for product with ProductID 703.

CREATING A WINDOW FRAME UTILIZING THE PARTITION BY CLAUSE

The “PARTITION BY” subclause is embedded in the OVER clause. The OVER clause follows the Window Function and defines the window or partition of rows over which the function operates.

The “PARTITION BY” subclause is that part of the OVER clause that allows you to divide your result set into partitions or groups based on one or more columns. The Window Function will then operate independently within each partition.

The “PARTITION BY” subclause is like a virtual boundary that separates your dataset into smaller groups or partitions. When you apply a window function with “PARTITION BY,” it ensures that the function’s calculations occur within each of these partitions separately. It’s like the GROUP BY clause, but there’s a difference. GROUP BY gives a summary, but window functions keep the original details. The partition in window functions is just for calculations.

Below is a fictitious sales table named sales_data. ABC Limited is a luxury brand that stores their sales record daily in the table named sales_data. The table consists of the SalesID, ProductID, OrderQty, UnitPrice, Amount, SalesDate and CustomerID columns.

The create the table, use the command provided below:

Firstly, our database UrBizEdge_win_func:

https://gist.github.com/anibihakeem/4dbe2a96b77f4348db8141a2d6aa25b2

then our table, sales_data:

https://gist.github.com/anibihakeem/847752acb50894eff385ed5f1d8c58b9

We can simply get the running total for each product sold at ABC Limited by partitioning the sales_data table by the ProductID and applying an aggregate window function called SUM. To achieve this, use the command provided below:

https://gist.github.com/anibihakeem/1ca398d7f99e6c4fbf530108447ab600

From the above picture, you can see the window frames created by the previous query, each indicated by a distinct color. For each row in the “sales_data” table, it calculates the running total of the “Amount” column for that specific product using the “ProductID”. The “PARTITION BY ProductID” clause ensures that the running total resets for each different product. The “ORDER BY SalesDate” clause orders the rows within each product’s partition by their sales dates.

The result shows the “ProductID,” “SalesDate,” “Amount,” and the running total of “Amount” (newly added column “running_total”) for each row, which represents the cumulative sales for that product up to that point in time.

Similarly, ABC Limited can utilize their business data to track monthly sales performance to identify trends, seasonality, and any changes in average transaction values over time. To achieve this, the below query can be used.

https://gist.github.com/anibihakeem/0d057e7cc18e9b51361fcdedfa8f40d7

The given query utilizes the FORMAT function to extract the abbreviated month name from the SalesDate column, aliased as “Month.” Additionally, the query employs an aggregate window function (AVG) with the PARTITION BY DATEPART(Month, SalesDate) clause to calculate the average transaction value for each month. This clause partitions the data based on the numeric representation of the month extracted from the SalesDate column.

The query result displays distinct window frames indicated by different colors. The output includes columns for the abbreviated month name, transaction amount, and the average transaction value per month.

CREATING WINDOW FRAME USING WINDOW BOUNDS

A window frame is a set of rows related to the current row in a dataset. The bounds of a window frame define the range of rows over which a window function operates, allowing customization of both lower and upper bounds.

The “ROWS BETWEEN” or “RANGE BETWEEN” clauses are utilized within the “OVER()” clause of a window function to specify these bounds. While both ROWS and RANGE can be used in the “OVER()” clause, they function differently and are suitable for different scenarios.

Before delving into the distinctions between ROWS and RANGES, let’s explore the various types of window bounds available for use.

The image above illustrates various types of bounds available for window functions, including:

  • UNBOUNDED PRECEDING: Represents the start of the partition or window frame, encompassing all rows from the beginning of the partition up to the current row.
  • N PRECEDING: Encompasses ‘n’ rows before the current row. If ‘n’ is a positive integer, it includes that number of preceding rows. If ‘n’ is zero, it includes only the current row.
  • CURRENT ROW: Represents the current row being processed.
  • M FOLLOWING: Encompasses ‘n’ rows after the current row. If ‘n’ is a positive integer, it includes that number of following rows. If ‘n’ is zero, it includes only the current row.
  • UNBOUNDED FOLLOWING: Represents the end of the partition or frame, including all rows from the current row to the end of the partition.

These bounds offer the flexibility to precisely define the range of rows considered for the calculations performed by the window function.

USING ROWS BETWEEN and RANGES BETWEEN

ROWS BETWEEN

The ROWS BETWEEN clause defines the range of rows based on the row count, not on the values themselves. It specifies a fixed number of rows before and/or after the current row within the partition.

It is useful when you want to include a specific number of rows relative to the current row, regardless of the actual values in those rows. It’s typically used for operations where the order of rows matters more than the specific values within them.

Syntax:

WindowFunction()OVER(ORDER BY column1 ROWS BETWEEN lowerBound and UpperBound)

Example:

  • ROWS BETWEEN 3 PRECEDING AND CURRENT ROW; this frame starts 3 rows before the current row and ends on the current row. Hence, the frame has 4 rows.
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING; this frame starts 1 row before the current row and ends one row after the current row. Hence, the frame has 3 rows.
  • ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING; this frame starts 5 rows before the current row and ends one row before the current row. Hence, the frame has 5 rows.

RANGE BETWEEN:

The RANGE BETWEEN clause defines the range of rows based on the values of the data within the rows, not on the row count. It includes rows whose values fall within a specified range relative to the current row.

Syntax:

WindowFunction()OVER(ORDER BY column1 RANGE BETWEEN lowerBound and UpperBound)

Example:

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; specifies that the range includes all rows from the beginning of the partition up to and including the current row.
  • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING; specifies that the range includes the current row and all rows following it within the partition.
  • ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING; specifies that the range includes all rows within the entire partition.
  • RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING; this includes rows whose values fall within 10 above or below the current row’s value.

In contrast to some SQL database management systems that permit custom window frames like N PRECEDING and M FOLLOWING in the RANGE BETWEEN clause, SQL Server Management Studio (SSMS) offers a more structured approach. In SSMS, utilization of the RANGE BETWEEN clause requires specifying one of the following predefined window frames:

  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  • RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

 

Let’s examine various scenarios in which it becomes necessary to define the limits or boundaries of a window frame. Recall our company, ABC Limited? Suppose the management wants to get rolling sum of order quantity by product. In this scenario, we will calculate the rolling sum of quantities of products ordered. The query required to generate this report is as follows:

https://gist.github.com/anibihakeem/f6739a4f0f87b1ec1ff92ee16bc450f6

From the results above, the last column shows the running sum of all products ordered. The RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW clause specifies that the range includes all rows from the beginning of the partition (or the start of the ordered quantity data) up to and including the current row.

Similarly, assuming the ABC management wants to determine the sales growth rate by comparing the current sales amount with the average of the previous and next sales. The query required to generate this report is as follows;

https://gist.github.com/anibihakeem/19685b138721beb8e1217bcb51e3911a

(Amount – AVG(Amount) OVER (ORDER BY SalesDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) / AVG(Amount) OVER (ORDER BY SalesDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS SalesGrowthRate: This expression calculates the sales growth rate for each sale.

Amount – AVG(Amount) OVER (ORDER BY SalesDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING): It subtracts the average amount of the preceding and following sales from the current amount.

/ AVG(Amount) OVER (ORDER BY SalesDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING): It then divides the result by the average amount of the surrounding sales.

CONCLUSION

In this article, we discussed what window frames are and how they can be defined. We explored two ways of defining window frames, using the PARTITION BY clauses and also using WINDOW BOUNDS. Window bounds includes Unbouned Preceding, N Preceding, Current Row, M Following and Unbounded Following.

To learn more about SQL, you should sign up for our classes, keep up with the blog, and subscribe to our YouTube channel.

Leave a Reply