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.