Window functions

1. Window functions

Welcome back. In this last video of the chapter we'll cover a type of formula which bridges the gap between single row and whole column calculations called window functions.

2. Window functions

Window functions are most often used for rolling or moving calculations, like a moving 6 week average or year-to-date cumulative total.

3. Helping out Oakmark Bank

To illustrate how window functions work, we'll assist a Manager at Oakmark Bank who monitors the number of new bank accounts opening over time.

4. Helping out Oakmark Bank

The number of account openings varies greatly month-to-month, but the manager cares equally about the monthly openings and about the general trend of whether account openings are rising or falling,

5. Helping out Oakmark Bank

so you'd like to calculate a moving average incorporating the past 3 months for each row.

6. Window functions in Sigma

The Moving Average function we'll use to solve this problem is just one type of window function in Sigma. Sigma has many other types of moving window functions, including MovingSum, MovingCount, and MovingMin and MovingMax. Beyond moving window functions, Sigma also has three other types of window functions called cumulative, shifting, and ranking window functions. Use cases for each type of window function differ, but they all rely upon a predefined window or subset of rows to do their calculation. See Sigma's function index for a full list of all Sigma window functions.

7. DEMO

Back to our Oakmark example, we'll start with a table sorted by month that shows us how many new accounts were opened each month at Oakmark Bank. For this calculation of a 3-month moving average, we’ll use a type of window function called a ‘moving’ function. As we move down the rows, the calculation updates, to include a ‘moving’ window of 3 rows each time. The window remains in a relative position to the current row. Here, our window will always include the current row and the 2 rows above it. Moving window functions in Sigma have a common syntax. You’ll point to the column on which the calculation is based and provide a number of rows to include in the ‘window’ above and below your current row. With all Moving functions in Sigma, the row you are currently on is always included in the calculation, so we’ll ask for 2 rows above our current row, and leave the ‘below’ parameter empty. This will average a ‘window’ of 3 months’ worth of data. Note that some rows of the table, like the first and second row here, do not have 2 months to look back to. In these situations, Sigma simply takes into account the rows it has available. Additionally, Sigma developers must take care to sort their data properly before using a window function as the window function will not take into consideration whether sorting has been done or not. Now we can see that the volatility in month-to-month account openings was masking a broader trend of increasing account openings over time - good news for Oakmark Bank. This example illustrates how window calculations help spot trends while keeping row details visible.

8. Let's practice!

Now its your turn. In the next exercises you’ll practice writing window functions for Oakmark Bank’s Operations team.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.