Nesting SQL Aggregate Functions
This co-op term, I’m working for the Jonah Group. My team is building a Big Data application for a corporate client, and I have spent a lot of my time writing the queries for canned reports that the client wishes to receive on their data. In the case of our project, we are storing all data using Hadoop and querying it using Hive Query Language, which closely resembles SQL.
Before I started at the Jonah Group in January, I didn’t have any experience writing database queries, so there were a few patterns in our codebase that took some getting used to. One construct in particular that I had trouble with was nesting aggregate functions (like
COUNT). I didn’t find many resources discussing these, so I thought I’d write my own.
Like I said, I first came across these ideas while using Hive, but the same principles apply in other SQL implementations. As I write this, I’ll be testing my code in PostgreSQL.
First things first, we’ll need some example data to practice with. Here’s the data I’ll use:
You can get the code to create this table, along with all of the queries discussed below, here.
Overview of Aggregate Functions
Let’s start by looking at the result of using
amount when we group by
year. We use this query:
SELECT year, COUNT(*) AS the_count, SUM(amount) AS the_sum FROM purchases GROUP BY year;
And we get this data:
By using the
GROUP BY statement, we squash all the records with matching values in the
year column together, and count or sum them as the case may be. This gives us the number of purchases and total amount spent in each year.
We could instead group by both
name. Then the query becomes:
SELECT year, name, COUNT(*) AS the_count, SUM(amount) AS the_sum FROM purchases GROUP BY year, name;
And we get this data:
Now we are squashing together all the records with matching
years and matching
names. This means that we get the number of purchases each person made in each year, along with the total amount they spent in each year.
Aggregating the Results
Let’s suppose that we now want to find the percentage of the total money spent in a year that is spent by each person. You may have to read that sentence a couple of times to make sense of it. It may also help to explain the basic procedure we want to follow. It will be something like this:
- Find the total amount spent by each person, in each year.
- Find the total amount spent by everyone in each year.
- Divide the first number by the second, for each person and each year. (And multiply by 100 to get a percentage.)
We already have the numbers needed for the first two steps in our queries above. The tricky part is combining them to finish of the third step. You could store information in some intermediate table, but there is a simpler way.
The first thing to note is that we want our result to have exactly one row for every combination of
name. This indicates that we should put both of these in a
GROUP BY clause.
We know how to get the total amount spent by each person each year when grouping by
name, from the second query we looked at. In our percentage calculation, this will be the numerator, and the denominator will be the sum of all those amounts, across the year we are looking at. To achieve this sum of sums, so to speak, we can use the
OVER clause. With it, we can calculate the two values we need like this:
SELECT year, name, SUM(amount) AS numerator, SUM(SUM(amount)) OVER (PARTITION BY year) AS denominator FROM purchases GROUP BY year, name;
This gives us:
Looking at the nested
SUM calls, we can think of the inner one as being affected by the
GROUP BY clause, calculating the sum for each combination of person and year. Then, the outer one sums up all the resulting amounts for each year, because only
year is included in the
PARTITION BY condition.
Putting it all together, we can use this query to calculate the percentages we are looking for:
SELECT year, name, SUM(amount) * 100.0 / SUM(SUM(amount)) OVER (PARTITION BY year) AS pct_of_spending FROM purchases GROUP BY year, name;
The result is:
A Note on MySQL
When I started writing this post, I was using MySQL, but then I realized that it does not support the
OVER clause. At that point, I switched over to PostgreSQL.
I don’t have any special insight into why MySQL doesn’t support this feature now, but hopefully they implement it in the near future, because it is very useful.