Moving Beyond the Row: The OVER Keyword

4:36 with Danielle Kiowski

The OVER keyword lets you transform an aggregate function into a window function. This lets an aggregate function see, and access, all of the results of a query.

Teacher's Notes
Video Transcript
Downloads

Definitions

OVER — keyword used to create window functions. Follow with empty parentheses to access the entire result set.

Code Example

SELECT PLAYER_NAME, 
    PLAYER_RANKING,
    PLAYER_TEAM,
    COUNT(MATCH_ID) as MATCHES_PLAYED,
    SUM(CHECKMATE) as MATCHES_WON,
    SUM(CHECKMATE)/COUNT(MATCH_ID) as WIN_RATE,
    AVG(PLAYER_RANKING) OVER () as AVG_RANKING,
    PLAYER_RANKING - AVG(PLAYER_RANKING) OVER () as DIFF_FROM_AVERAGE,
    SUM(SUM(CHECKMATE)) OVER () as TOTAL_MATCHES,
    SUM(MAX(CHECKMATE)) OVER () as PLAYERS_WITH_WINS,
    MAX(SUM(CHECKMATE)) OVER () as HIGHEST_WINS
FROM treehouse.chess_data_matches
GROUP BY PLAYER_NAME, PLAYER_RANKING,PLAYER_TEAM

0:00
When we use aggregate functions like sum, count, or
0:04
average in a query, they calculate a result using
0:07
the records that are associated with their row by the GROUP BY statement.
0:11
They can't access the values of any records outside of their grouping.
0:15
So the values that they return are placed in the context of the overall results
0:19
with the over keyword we can open a Window so
0:22
that these aggregate functions can see and access all the results of the query.
0:27
This transforms a regular aggregate function into a Window function.
0:31
Using just the over keyword,
0:33
we can add information about the entire tournament to our existing report.
0:37
To answer our first question,
0:39
how does each player's ranking compare to the overall field.
0:42
We calculate the average ranking of all the players in the tournament.
0:45
To do this, we'll add a new field to the report.
0:49
Let's start out by adding the AVG(PLAYER_RANKING) AS AVG_RANKING.
1:03
This gives us a different number for
1:05
each player because the report is grouped by player.
1:08
The average function returns the player's own ranking on each row.
1:12
That's not what we want, we want the average across all the rows.
1:15
Let's have the over keyword to give the function access to all the rows
1:20
then we'll add empty parentheses.
1:22
The parentheses create a space.
1:24
To give the window function more instructions for
1:26
how to aggregate which we'll cover later.
1:29
For now, we'll leave those options blank.
1:31
So that we're querying over the whole result set.
1:34
Now, when we run the report, it's easy to see whether each player is above or
1:39
below the average ranking which is 1,738.7.
1:42
We can see this directly by subtracting that average from the player's ranking.
1:47
Let's make a new field to do that.
1:51
We'll start out with the player's own ranking.
1:54
Player ranking.
1:57
Then we'll subtract the window function that we just created.
2:01
Average of player ranking over the entire result set.
2:14
This tells us how far above or below average each player's ranking is.
2:21
It's important to know that unlike regular aggregating functions, window functions do
2:25
not have access to all of the records in the tables that you're selecting from.
2:29
Instead, they have access to the results of the grouping
2:32
that you've specified in the queries group by clause.
2:35
This means that if your query has a group by clause.
2:39
You can only use the grouping fields directly while any other fields
2:42
have to be aggregated inside the window function aggregation for example, for
2:47
our second question how many total matches were played in the tournament.
2:51
We want to add up the check me field over the entire result set
2:54
If we try to do that directly using some of Checkmate over empty parentheses,
2:59
it will not run because Checkmate is not a grouping field.
3:03
It's helpful to think of the window function as querying
3:06
over the results of the existing query.
3:08
In other words the window only looks on to the rows returned by the query
3:12
when the function looks at each row of the result set it has to get one value for
3:16
that row.
3:17
To use and its calculation.
3:19
This example doesn't work because the query results don't include
3:22
the check mate field.
3:23
Each row represents a collection of records with
3:26
multiple values for check mate.
3:28
If we want to add up all of those values, we have to first sum them up for
3:32
the row using sum of Checkmate.
3:34
And then we can pass that aggregation to the window function.
3:37
We can do this by adding a sum call
3:41
inside of the sum that belongs to the window function.
3:44
Overall, we get sum of sum of checkmate over empty parentheses.
3:49
By summing up the sums of all the players matches one,
3:52
we find that there were 90 total matches in the tournament.
3:55
You can make some match inner and outer aggregations.
3:59
For example If we pass (MAX(CHECKMATE) to the window function instead of sum,
4:03
we'll get the number PLAYERS_WITH_WINS
4:19
If we reverse that and take the MAX(SUM(CHECKMATE),
4:22
we'll get the highest number of wins by any player.
4:28
Combining different aggregations and window functions multiplies the power of
4:31
the original aggregation, so we can answer more complex questions about the dataset.

You need to sign up for Treehouse in order to download course files.

SQL Window Functions

Moving Beyond the Row: The OVER Keyword

Definitions

Code Example

Sign in