Window functions in SQL are a very useful technique and something you might not learn
about in your SQL journey.
In this video, I’ll explain in an easy-to-understand way, what a window function is, what problems
they can solve, and show several examples of them on an SQL database.
Window functions are available on many databases including Oracle, SQL Server, MySQL, and Postgres,
so if you’re using any of these databases, then this concept will be useful for you.
Let’s get into it!
Welcome to the Database Star YouTube channel - the place for developers looking to improve
their database and SQL skills.
A window function, otherwise known as an analytical function, is a type of SQL function that lets
you perform calculations based on data in different groups of rows.
What does this actually mean?
And how is it useful?
Let’s take a look at a common use for window functions: calculating a running total.
Let’s say we have a table called orders, which contains an ID, an order date, and a
total amount or value of the order.
And, we’ve been asked to calculate the running total of the order amounts, or the total amount
so far for each record.
So, our expected output would look like this.
We want another column that shows the running total.
After the first order, we have a running total of 100.
After the second order, the running total is 350, because it includes the 100 from the
previous running total and the 250 from this total.
The next row shows 430, which is 350 plus the 80 from this row.
How do we calculate this?
We could do this in our application code once we get the results back.
But there is a better way to do it, and that is in SQL using window functions.
As mentioned before, a window function lets you perform calculations on data in different
groups of rows.
Specifically, it’s a collection of keywords you can add to some existing SQL functions
to do this.
For our running total, the function we want to use is SUM, because we are adding numbers
together.
We can add some keywords to the SUM function to make it a window function.
We can do this to many other functions, but we’ll start with an example using SUM first.
Here’s what the window function syntax looks like.
We have the name of the function, then an expression inside, which could be a column
or values or something else.
We then have the keyword OVER.
Inside the brackets, we have a Partition By clause, and an Order By clause.
Both of these are optional.
The Over keyword indicates that this is to be used as a window function.
The Partition By clause will let you define the window of data to look at.
We’ll see some examples of this later in the video.
Finally, the Order By clause defines the order that the function will run on the data.
This is different to how the results are ordered.
Now we’ve seen the syntax, let’s use the SUM function in a query.
We’ll use MySQL Workbench on a MySQL database in this demonstration, but window functions
are available on Oracle, SQL Server, and Postgres as well.
If there are any differences between vendors, I’ll point them out, but the syntax is the
same.
We’ll start with these Create and Insert statements here, which set up the orders table
and insert some sample data.
You can get these scripts in my GitHub repository using the link in the description.
We run these two commands to create the tables and insert the data.
Let’s run a Select query to see the data in the table.
It’s the same as we saw earlier in the video.
So, how do we calculate the running total using the SUM function as a window function?
First, we add the SUM function to the query.
We want to sum the order_total column, so put that inside the brackets.
To use this as a window function, to show the running total, we add the OVER keyword.
We’ll add a pair of brackets after it.
Inside the brackets for the Over keyword, we can add two things.
We can add the partition by clause, which defines the window or group of data we’re
looking at.
This is helpful if we want something like a running total per month, and the window
would be the month.
But, we want to see the running total for all records, so we’ll leave this out.
The second parameter is the order by clause, which defines how the data is ordered for
the calculation.
We want to include this order by clause.
This is because the running total is calculated based on records in the previous row.
How do we define what a previous row is?
It’s the one with the previous order_id.
It could also be the order_date, but there may be two orders on the same date so we won’t
know which one comes first.
So, we update our query to use the Order By clause.
We specify Order By, then order_id, and then the keyword ASC for ascending order.
This will calculate the running total on orders based on order_id in ascending order.
Next, we’ll give it a column alias of running_total, so we know what is in the results.
We’ll also need to add an Order By clause at the end of the query, because we want to
see the data in the same order that the calculation is being done in.
If we omit this, then the data may be displayed in a different order, and the running total
will look strange.
Let’s run the query and see the results.
We can see our new running_total column here.
It has a running total of the order_total column, increasing with each row.
We’ve successfully used a window function in an SQL query!
In the previous example, we calculated a running total based on all records in the table.
Window functions can let you specify a window, which means the calculation is done on only
some of the records.
A window is kind of like a sub-group of records, or a partition.
It’s a set of records that the function will look at when performing its calculation.
To demonstrate the windowing or partition feature of a window function, we’ll need
some more records.
Let’s insert a few more records into the orders table.
We can run this and the data is inserted.
Let’s say we want to see a running total of orders for each day.
When the first order for the day is completed, we show the total of that order.
When the second order for the day is completed, we show the total of both orders, and the
same process for any more orders on that day.
When an order comes in for the next day, the running total would reset, and show the total
for that order only.
The running total would then increase for any orders on that day.
How do we do this in our function?
Here’s our SQL from the previous example.
We can add one thing to our SUM function to calculate the running total per day, and that
is the PARTITION BY clause.
We add this clause, then the order_date column.
This means for every unique value of order_date, a new partition or window is created, and
the SUM function will start a new calculation.
We’ve left the rest of the function the way it is, including the Order By clause within
the sum function.
Let’s run the query.
We can do this and see the results here.
We can see the results are different.
The running total changes with each row.
But it doesn’t seem to make sense.
Order ID 5 has a running total of 470 but the previous row is 10.
What’s happening here?
It’s showing like this because we are ordering the data by the order_id field.
The calculation is being done in groups of the order_date, so the numbers are correct,
but the order of rows makes it confusing to interpret.
Let’s change the query to order by the order_date field instead, then the order ID.
When we run this query, we can see the data looks better.
The running total is increased for each row on the same date, such as the first three
rows.
On the fourth row, the order date changes, and the running total resets.
The same thing happens on the last row when the next order date is reached.
Notice how all results are shown.
The power of these window functions is that you can perform these aggregate calculations,
such as SUM, without having to use the Group By clause.
This can be helpful in many situations.
What are some other examples of when you may need to use a window function?
You can use COUNT as a window function to count the number of records created each day.
Or, use it with a date conversion function to count the number of orders so far in the
same month, or year.
You could use the AVG function to calculate the average of the order_total for the entire
table, to easily see which orders are above or below average.
You could also use the Partition By clause to calculate the average per month.
You can do similar things with the MAX and MIN functions.
You can use the RANK and DENSE_RANK functions to find the rank or position of a row based
on the entire result or within groups.
It can be done on all kinds of data, not just orders.
Anything with a value that you would use inside one of these functions you could consider
using in a window function.
So, that’s how you can use a window function in SQL to create partitions or windows of
your data and perform calculations.
If you want to learn more about database design and SQL, visit my website at databasestar.com
If you liked this video, consider subscribing to my channel.
Thanks for watching.