InspireUpdated January 2024
Lately, I’ve found myself using a number of window functions in BigQuery. They’re really handy functions but can be difficult to wrap your head around.
In this post, I’ll take you through the basic principles of window functions. So, if you’ve been struggling with how to apply or even understand functions such as first_value and dense_rank or have ever wondered how to create a cumulative sum, this post is for you.
SQL window functions enable you to query either a subset or full set of rows within your data set, and return a value on each row of the results. You can use them to calculate running totals, rolling averages, or simply to remove duplicate rows from your data set.
There are many types of functions that can be used with a window. They fall into three main categories:
- Numbering: RANK, DENSE_RANK, ROW_NUMBER
- Aggregating: SUM, AVG, COUNT, MIN, MAX
- Navigational: LEAD, LAG, FIRST_VALUE, LAST_VALUE
A window function follows this general format, though you won’t necessarily use each clause in every statement:
- Function_name: This is the analytics function of your choice e.g. SUM, RANK, LEAD
- Expression: This is the column you are querying or can be a CASE WHEN logical statement. It is left blank for some numbering functions like RANK and ROW_NUMBER
- OVER: This determines the window or the set of rows the function will operate within. If you leave it blank between the parentheses, it will query all rows in the data set.
- PARTITION BY: This clause is essentially like a group by. If you partition by user_pseudo_id, the function will query all the rows for each user at a time. If you partition by user_pseudo_id and ga_session_id, the function will query all the rows within each session for each user.
- ORDER BY: Sometimes you’ll need to order the rows in your partition. This is important for navigational and numbering functions so the query knows where to start and finish. You can order by multiple columns and order in ascending (ASC) or descending (DESC) order.
- Frame_clause: This section lets you choose a subset of rows within your partition. If you wanted to query a running total or a rolling average, you specify here.
Still with me? Now we know what Window Functions are, let's look at some simple examples to illustrate them in action.
Using Windows Functions
Example 1 - Find the total sales across the full catalogue
If you want to query all rows in the data set, all you need to do is omit the PARTITION BY/ORDER BY and leave between the parentheses of the OVER clause as blank (). The example below is summing the sales for all products in the data set.
Example 2 - Find the total sales within each category
A partition is like a GROUP BY function except this time you get the aggregated sum on each row of the data set. The statement below sums the sales for each category.
Example 3 - Find the total sales within each subcategory
You can partition by multiple columns with simply a comma between them and it will sum all the values within each partitioned column combination, in this case, each category and subcategory combo.
Now that you have the total / subtotals on each row, you can do calculations across the columns. For example, what percent of total sales came from each product.
Example 4 - Find the top-selling products in each category
Another window function you could use is RANK. This requires an ORDER BY clause. The example below will rank each product within each category from highest sales to lowest (ORDER BY Sales DESC).
Rank can be useful for limiting your results to the top X products.
Example 5 - Find the running total
A frame clause follows this format: ROWS BETWEEN X AND Y (where X is the starting point and Y is the end point of the window).
You can either designate a set number of rows (e.g. 5 PRECEDING) or you can use UNBOUNDED, which means an unlimited number of rows. If you wanted to query the full set of rows you would say UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING which is simply all rows before and after the current row.
Below is an example of a running total. In this example, I have used ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, because I want to add all the sales before and up to the current row. On the 3rd August, the running total of $25,400 is the result of adding $9,000 + $15,000 (UNBOUNDED PRECEDING) and $1,400 (CURRENT ROW).
The frame clause gives you flexibility depending on your use case. You could add the sales from the current row and the next row using ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING. Alternatively, if you want to add 5 rows before and after the current row, then your statement will look like this ROWS BETWEEN 5 PRECEDING and 5 FOLLOWING.
Example 6 - Rolling Average
Below is an example using a frame clause to calculate a 3-day rolling average of sales. The function changes to an AVG (instead of SUM) and the frame clause looks at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
Note: the first two rows are not really a three-day average because there are not two preceding rows in the data set. Another thing to be mindful of is the number of rows in your partitions because this could have unexpected results if they are uneven. For example, if you had a date with no sales, there could be a missing row in the data set. When the statement looks back two rows, it will actually be looking back three dates, resulting in a four-day rolling average.
So these are the basic principles of window functions. You can find the full list of the compatible functions that can have an OVER clause in BigQuery on these pages (Numbering, Aggregating, Navigational).
So, there we have it. Some cool ways you can use window functions in BigQuery. If you're new to BigQuery, or want to up your data analysis game, check out our [Google BigQuery] course (https://www.jellyfish.com/training/courses/getting-started-with-google-bigquery).
This post first appeared on the Data Runs Deep blog. Data Runs Deep joined the Jellyfish global family in 2020 and the knowledge-sharing continues under the Jellyfish banner in Australia and around the world.
Corinne Brooker
