One of the most important purposes of a database is to organize the mountains of data that surround us. The <partition_definition> and <order_definition> sections of the OVER clause exist to help us do just that. We covered their syntax in the previous chapter, but some practical demonstrations of how these sections work is probably more useful than a dry syntax diagram on the path to mastering Window Functions.

Using the employees table from Chapter 1, here is a simple example of the ROW_NUMBER Window Function. First, we’ll call the function with none of the optional sections in the OVER clause:

The result is a little unexpected:

Looking at this result, you might be confused as to why the rnum column is out of order. In fact, your result may have a different rnum column entirely. It may be in order from 1 to 15, or it may have a completely different order. What is going on? The out-of-order rnum column happens because Window Functions are computed after the other parts of the SELECT statement have been fetched (the name, title, and office columns in our example) and after any WHERE, HAVING, or GROUP BY clauses, but before the final ORDER BY title clause. In fact, without an <order_definition> section in the OVER clause, Window Functions do not guarantee any particular ordering.

Ideally, we want the rnum column to always match the end output of the ORDER BY title clause. To ensure this happens, we add an <order_definition> section to the OVER clause like so:

Now both the rnum column and the final output are guaranteed to be ordered by the contents of the title column, so the result will always make logical sense:

Between the Cleveland and Memphis offices there are 15 employees. What if we want to number employees in separate offices, well, separately?

This is the purpose of the <partition_definition> section. It allows us to group our results. With a <partition_definition> section in the OVER clause, the ROW_NUMBER function will do its thing to each group independently. So, we’ll add PARTITION BY office to the OVER clause like so:

Unfortunately, the output is not as useful as we might have supposed:

As with other results in this chapter, yours may be slightly different from this. But the <partition_definition> section is working perfectly here. The first Cleveland and first Memphis employees in the result are both given a row number of 1. The second ones 2, and so on. There’s a little confusion further down in the results because Cleveland has more salespersons than Memphis, but the correct row numbers are all there. We just need to fix the output so that it is more understandable.

This is another instance that plainly shows that Window Functions do not guarantee ordering. The solution in our case is to just add the office column, not in the OVER clause, but to the final ORDER BY clause, before title, like so:

The output now looks like this:

Success! Each office’s employees are numbered independently, and the numbering is all in order.

Another common database task is to maintain a running total of something. This could be an account balance, the number of items sold over a period of months, or a host of other numeric values.

The commissions table we used back in Chapter 2 can be used to demonstrate this. It tracks commissions from salespersons in our fictional company. The table records the salesperson’s ID number, an ID number for the commission, the commission amount, and the date of the commission.

A brief sample of the data can be seen with the following query:

The complete output is quite long, but here’s the first ten rows of output:

To create a running commissions total, we need to take the first amount for a row matching a given salesperson and output it in a column. We’ll call this column total. Then, for each subsequent row we add the previous total to the current row’s commission and make that the new total and so on until we come to a new salesperson_id, whereupon we will start the process over. At least, that’s my way of thinking through the problem. Actually solving this using traditional SQL is a bit different. For example, one traditional SQL way of solving the problem is to use a self-join and the SUM function like so:

Inside our main query we have a subquery that looks for every row where the salesperson matches and the date is less than or equal to the date of the current row. For those rows that match the criteria, it sums them all up and outputs the answer in the total column. So, for the row with the oldest date stamp the only other row that it will match is itself, so the total column is the same as the commission_amount column for that row. For the row with the second oldest date stamp, the rows that match will be the oldest row and itself, so those are what are summed together into the total column. The process continues until all the rows that match a given salesperson_id have been fetched, and then the process restarts with the next salesperson.

The result is several thousand lines long, but here’s the first few rows:

All in all, it works, and you could even say it works well. It’s easy to confirm that the total column is accurately keeping a running total and that when the salesperson_id changes the count starts over.

But the process for how we get at the total is awkward. If this were a paper ledger, we wouldn’t continuously re-add everything that came before; we would simply add the new value to the old total.

Also, all of the adding and re-adding our query is doing means we’re constantly going out and fetching new groups of rows to sum together. All of this fetching and re-fetching takes time. Indexes can help, especially on large tables, but using a subquery like this doesn’t seem like the right way to go about solving our original task of displaying a running total.

A Window Function can do this job much better, and it does it in a more natural way. First off, like our previous Window Function example where we were numbering employees, we need to use both the <partition_definition> and <order_definition> sections in our OVER clause. We’ll PARTITION BY salesperson_id and ORDER BY commission_date to match the final ORDER BY clause of the query.

We then need to tell the Window Function what rows to add together for our total column. Aggregate Window Functions, like SUM, can use moving window frames to quickly identify the data they need to process. For this we’ll use a <frame_definition> that goes from the beginning of our result set, or UNBOUNDED PRECEDING, to the CURRENT ROW, so our full <frame_definition> section will be:

The result of this query is the same as we saw for the subquery version, so there’s no need to show it again.

When this query is run, on the first row the window frame is just the first row. As the query continues, the frame expands, and unlike subqueries, it doesn’t need to refetch any data; it just looks at the data that is already there. Then, when the frame crosses our partition boundary, it resets to just the current row and starts growing again. Instead of hundreds or thousands of trips back and forth to the database to fetch the information, which is what happens with a subquery, the Window Function only needs to make one pass.

This performance difference is so dramatic it can even be seen on the relatively small commissions table. On my laptop, processing the whole 3,000+ rows in the table takes 2.56 seconds to run the self-join version of the query. Contrast that with the Window Function version, which completes instantly. Two and a half seconds isn’t much, but as table size increases the speed advantage of Window Functions keeps growing. One of the MariaDB engineers performed some tests on similar, but much larger, tables using a similar query. Table 5-1 shows the results. All times are in seconds.

Without an index on the table, the Window Function version of the query always wins by an order of magnitude over the self-join version. With an index on the table, the self-join version of the query can keep up with the Window Function version for a while, but after the table gets above a million rows, the Window Function takes the lead and is almost twice as fast.

Another common analytical query is to find the top N numbers of something, be it the top five selling items, the top ten salaries, or the top three scorers in the youth basketball league.

Using our commissions table again, it is trivial to use SQL to find the top five commissions earned. One way to do it is with the following SQL (with LIMIT 5 to keep the output small):

The result of this query looks like this:

We could improve upon this by doing a JOIN with the employees table to display the name of the employee and their office alongside the commission amount. The following SQL does just that:

The result looks like the following:

This is all well and good, and we now know who received the top commissions company-wide, but what if we want to find out the top two commissions from each office? It seems like a reasonable extension of the previous query, but it causes the complexity of our query to jump by quite a bit. There are probably other ways to do this, but here’s a query that does it:

There’s a lot going on here, but basically we have our original query, some derived tables, and a subquery in our WHERE clause that uses the COUNT function to count the number of commissions in the commissions table that are from the same office AND where the commission amount is greater than the current row, and then limits those results to the top two. That probably stretches the definition of the word basically a bit too far. It’s quite frankly a bit of a mess, and it’s hard for mere humans to read and parse it. On the positive side, it does work. The result of this convoluted query is as follows:

Apart from the aforementioned readability issues, the primary downside to this query is that it is slow, especially if there are no indexes on the table. On my laptop, for example, this query takes over ten seconds to run. And even if there were indexes in place, there would be the overhead of creating and maintaining the indexes, which could be problematic if our tables are updated frequently.

A much better and faster way to get at the result we’re after is to use Window Functions, specifically the RANK function. To start things off, we can simply take our original query with the JOIN and add the RANK function to it.

Because we want to find out the top two commissions per office, inside the OVER clause we will ORDER BY the commission_amount column from the commissions table and PARTITION BY the office column from the employees table. We’ll call the result of the RANK function rnk, to keep things simple.

Lastly, to get the ordering correct, we’ll ORDER BY office and our new rnk column.

Putting all of that together, we end up with some SQL that looks like the following:

I’ve removed the LIMIT from this query, so it will output every row in the commissions table, which isn’t what we want, but by showing everything we can verify that each row is ranked and partitioned by office correctly. So, we’re close.

Here’s what the first few rows of the output look like:

However, when we try to run this new query we get the following error:

On the surface, this error is very confusing. We are referencing the rnk column in both the WHERE and ORDER BY clauses, and they’re right next to each other. So, why does it work in the ORDER BY clause and not in the WHERE clause? The reason is the same one we saw before in the ROW_NUMBER example. It bears repeating here: Window Functions aren’t computed until after any and all WHERE, HAVING, and GROUP BY clauses are finished. Once they are, the function will run, and only then will the rnk column exist, so with the way our query is written, only the ORDER BY clause can see it.

To solve our problem, we want the WHERE clause to be able to see the rnk column. So, we need to somehow force the RANK function to run prior to our WHERE clause. A simple way to do this is to use a derived table by taking our original query, everything from the initial SELECT down to just before the WHERE clause, and stuff it all into a simple SELECT like so:

For the AS ranks part, we could have used any name, since we don’t use or refer to our derived table anywhere else. In this case, the name ranks seemed logical enough.

After adding our derived table wrapper, here’s our final query:

Now that our main query is operating as a derived table, the WHERE clause is able to see the rnk column, and our output is as follows:

The only task left now is to try to find out how best to reward these hardworking salespeople. Maybe a gift certificate?

In this chapter, we explored how <partition_definition>, <order_definition>, and <frame_definition> sections are often used in the OVER clause. We also got to see the ROW_NUMBER and RANK Window Functions in practice, including how to work around some common issues that arise when using them and other Window Functions.

In the next chapter, we’ll continue our exploration of Window Functions with a deeper dive into using them to parse and generate graphs of real-world time-series temperature data and analyze fruit sales across a chain of stores.