Using analytic (window) functions in BigQuery

google analytics cheat sheet This is a topic that many people are looking for. is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, would like to introduce to you Using analytic (window) functions in BigQuery . Following along are instructions in the video below:

“To our google analytics sample dataset let s start diving into some of the bigquery bigquery functions that can you can start to make some simple mathematical magic happen with functions these are some of my favorites and they re called window functions and they basically let you calculate sums or averages. Or do all kinds of math functions on separate windows. Within a table at one time. And this is not a necessarily unique concept of bigquery bigquery.

Just has a great implementation of it so let s say that we re running this query. Where we re grouping page views by channel. So we re getting page views across channels. And let s just run this quickly to take a look at this data set have a comma.

There okay so we have page views by channel and let s order by page views descending so we have ever largest first okay cool briona search. So what if we want to calculate the percentage of page views. So let s calculate the percentage of page views for each of these channels. So we can get a sense of what our kind of concentrations are here so the first thing to note is if you want to run window functions.

You should do other aggregations to say we re getting our total page views by by channel. You should do those separately just to keep your aggregations can consistent because if you if you play around with merging window functions and separate aggregations you ll find it it can throw some errors and just be tricky to sort out so instead i d recommend it either nesting or defining your tables separately with a width parameter. Which i went through in another video. So we re just going to say we re basically in an a star function our queries here and wrap another query around these results.


So we re just gonna group by channel on the data set and get our by channel. Let s also add date in here. Just for fun even though. We have one date.

It s just good practice. So let s say date. We want to select date channel. Grouping pageviews and then we want the total page views over the sum of page views of total pages.

So let s just get the total page views first in the window. And i so you can see what what actually this window function. Does is you can basically type some of some metric and this you know i ll link to the list of functions that are supported. But you could pull max or average.

And we ll go through a few of them there s a bun that a bunch of different matched math functions. You could run some of page views over partition by date so this basically partition body is basically like grouping. So i m gonna say for each date. Calculate the total pageviews.


So basically if you re leaving something out of the partition that means you re ignoring it so we don t care about the channel group we want to just aggregate. All of them and let s say total page views let s just pull that so you can see what this does is it in each individual row. It gives us the total and it s the same across the entire window right it won t change based on since the dates are all the same its popular and we re partitioning by date this value will be the same for each date. If we partition by channel.

Grouping and then and date then it would just basically show the pages would equal the total page views because we re partitioning at a level of granularity that we don t need so instead we re just going to partition by date and we re gonna pull that the total page views what if we wanted the average page views we could do the same thing. And say okay here s just here s the average on the same partition and that s the average across all all channels. And if you re using the same window. This is called the window this in between the parentheses.

Here the partition by you also an order by louisville will go through in another video. But we won t need those for these just aggregate math functions. But if you re using the same window twice you can define it after at the end of your query. And just say window w1 as and you could pluck this out this partition by and define.

It here and then you can reference it multiple times and you can say over w1. So you don t have to keep typing out the same window. And if you change your window. You can just change it once rather than twice and similarly.


If these were separate windows right you could just list comma. Separated a bunch of windows at the bottom. So oftentimes. If for example.

I m calculating like totals across months and then across years you know you ll have one window partitioned by the month. One window partition by the year and you can sum up a you know total for the year and a total for the month. Within the same query so that s really helpful when building reports that have you know period over period comparisons. But for sake of this query.

We re just calculating the total and the average. We don t need to do that let s also do one thing. Let s blend our page let s get our percentage of page views for the page. Which is what we were really after so we can do is basically calculate our page views to kind of blend these metrics to calculate page views over the window function that got the total page views and we ll call this percent of page views cool so.

We can see and let s order this by percent of page views and remember that the window function. The window definition. If you re going to use that always goes at the end of the function. Does it no i guess order by would go after the window cool learn something every day cool.


So if we look at this you ll see that organic search is 43 of pageviews referral is 30 and then on down from there. So this is a pretty concentrated concentrated traffic. Here. So that s you know kind of an intro to window functions.

Again. They re really helpful for looking at totals over you know time when you re trying to compare you know what s a percentage of or you know difference from the average for example. If we wanted like is this is organic search above or below the average pageviews. Then we could do some math on the average from within the same query.

So these really make life easier. Especially. If you re looking at basically building reports right looking at a different date range to compare. But within the same view so if that helps we re gonna move on now to a little bit more complex of window functions and ” .


Thank you for watching all the articles on the topic Using analytic (window) functions in BigQuery . All shares of are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.

“Walking through using window SQL function logic (OVER (PARTITION BY)) in BigQuery SQL to calculate a row s percentage contribution to the dataset s total (using the Google Analytics sample dataset).nnLearn more and grab the SQL cheat sheet at”,


Leave a Comment