Tutorial
When I look at the work I do, I consider myself to be a builder. Like most builders, I have tools I use when working. The more I understand those tools, the easier it is to do the work. One of those tools is the Window function. I like its versatility; its ability to slice, dice, and control data.
In this tutorial, we’ll dig into the Frame Clause in a window function. We use the frame clause to control the number of rows included in the calculation. We find the frame clause when working with aggregate window functions or some value window functions. This tutorial assumes you have a basic knowledge of window functions. If you are new to window functions, I recommend reading SQL Window Functions, A Love Hate Relationship.
Windows functions are a powerful tool when working with data. To help explain the frame clause, I show how to use it to calculate a 6 month moving average.
The Data: How to access it?
To follow along, you can access the data by:
- Click here and I’ll send you a username and password along with instructions on how to access the data so you query the database directly. I have a few user seats available. There is no fee to sign up or personal information collected. I’m exploring new ways to help readers easily engage with the material.
- Download a csv file directly from here so you can upload it to your own database.
Now we’ve got that of the way, we can dig deeper and learn more about the frame clause in window functions.
Frame Clause: What is it?
The frame clause determines rows to include in the window function calculation. I’m use the term calculation as a reminder the window function produces a numerical result AND the rows specified in the frame clause determine the result. That’s really the main point. You can control the result of a window function by using the frame clause.
The frame clause has the following syntax:
Frame Clause: The Big Picture!
Conceptually, I have found it helpful to think of the window frame as executing downward and in a left-to-right motion. Downward goes to the next row. Left-to-right populates the results column before going downward to the next row.
In our example, let’s assume the processing starts in row 7:
- Row 7 (blue): The window frame includes the Current Row (row 7) and the 5 Preceding rows (rows 2–6) to determine the window function result, X. Once the column populates with a result processing continues downward and to the next row, row 8.
- Row 8 (orange): On row 8, the process repeats, the window frame includes the Current Row (row 8) and the 5 preceding rows (rows 3–7) to determine the window function result, Y. Once the column populates with a result the processing continues downward to the next row, row 9.
- Row 9 (gray): On row 9, the process repeats, the window frame includes the Current Row (row 9) and the 5 preceding rows (rows 3–7) to determine the window function result, Y. Once the column populates with a result the processing continues the to the next row, row 10.
Processing continues downward and to the right until we reach the last row. The key points to remember:
- The frame shifts downward AND populates the results column to the right after each row processes.
- The Current Row changes as each row processes.
Frame Clause: Calculating the 6 Month Moving Average
In our example, with the data shown above, let’s assume the syntax is "rows between 5 preceding and current row". The processing starts in row 7.
- Row 7 (blue): The window frame includes the Current Row (row 7) and the 5 preceding rows (rows 2–6) to determine the 6-month moving average, $582,497. The calculation for the 6-month moving average is below. Once the column is populated, processing continues downward to row 8.
- Row 8 (orange): On row 8, the process repeats, the window frame includes the Current Row (row 8) and the 5 Preceding rows (rows 3–7) to determine the 6-month moving average, $601,488. The calculation for the 6-month moving average is below. Once the column is populated, processing continues downward to row 9.
- Row 9 (gray): On row 9, the process repeats, the window frame includes the Current Row (row 9) and the 5 preceding rows (rows 4–8) to determine the 6-month moving average, $625,009. The calculation for the 6-month moving average is below. Once the column is populated, processing continues downward and left-to-right to the last row.
Our Scenario
The data in our the example below comprises sales revenue by month. The aim is to calculate the 6-month moving average using the frame clause. With that said, let’s dig into the code in the Sql editor. For a 6-month moving average, we use the Average Window function and set the frame clause to "rows between 5 preceding and current row". Here, we also order the month by ascending, since we want the aggregation to occur from the earliest to the last date.
When the query runs, it shows the result in the SQL Editor. The highlighted rows, 7–9, match the results shared in the earlier explanation. I recommend practicing by either downloading the dataset or requesting a username and password. That’s it for now. Continue growing, learning and developing.
Shared Inspiration: With each key stroke and lesson shared, I am reminded of my former classmate and then manager, Sophia. She helped to put the belief back in me with her words, kindness, focus, and leadership. When I finished working for her, I felt I could soar. To this day, I marvel at her and her husband Abel’s kindness. Every day is a chance to make someone’s life a little better. Take it!
