Heads up! To view this whole video, sign in with your Courses Plus account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed SQL Window Functions!
You have completed SQL Window Functions!
Preview
Grouping Data With Partitions
Definitions
ORDER BY — Keyword to introduce the ordering clause. Followed by expressions that determine the order of rows within the window.
Functions
These functions can only be used as window functions because they rely on the context of other rows.
- RANK() — Ranks each row based on the window function’s ORDER BY clause
- FIRST_VALUE(expression) — Returns the value of the expression for the first record in the frame/partition
- LAST_VALUE(expression) — Returns the value of the expression for the last record in the frame/partition
- LEAD(expression,n,default) — Returns the value of the expression for the row n rows after the current row in the partition. If the specified row does not exist, returns the default value. If n is not specified, defaults to 1 (the next row)
- LAG(expression,n,default) — Returns the value of the expression for the row n rows before the current row in the partition. If the specified row does not exist, returns the default value. If n is not specified, defaults to 1 (the previous row)
Resources
See more in the PostgreSQL documentation: https://www.postgresql.org/docs/devel/static/functions-window.html
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,
SUM(SUM(CHECKMATE)) OVER (PARTITION BY PLAYER_TEAM)
/SUM(COUNT(MATCH_ID)) OVER (PARTITION BY PLAYER_TEAM) as TEAM_WIN_RATE,
MIN(PLAYER_RANKING) OVER (PARTITION BY SUM(CHECKMATE)) as TIEBREAKER,
MIN(PLAYER_RANKING) OVER (PARTITION BY SUM(CHECKMATE),
PLAYER_TEAM) as TIEBREAKER_TEAM,
RANK() OVER (PARTITION BY PLAYER_TEAM ORDER BY PLAYER_RANKING DESC) as RANKING
FROM treehouse.chess_data_matches
GROUP BY PLAYER_NAME, PLAYER_RANKING,PLAYER_TEAM
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
Some functions are only
available as window functions,
0:00
not as regular aggregates.
0:03
This is because they are only useful in
the context of other records but not for
0:04
the row itself.
0:08
To answer our fourth question,
who is the top ranked player on each team?
0:10
We'll use one of these functions.
0:13
Rank, it gives the ranking of each row
within the window, starting with one.
0:15
Windows specific functions order
the rows within the partition
0:20
to determine whether one row
comes before or after another.
0:23
The order clause,
0:27
which is specified within
the parentheses after the partition,
0:28
gives us the ability to define
the ordering of rows within the window.
0:31
Let's use rank to find the top
ranked players in the tournament.
0:34
We'll add a new field to the report
using this function, and
0:38
then add the over keyword.
0:42
This won't run yet
0:44
because the function has no way of
determining how to rank the players.
0:45
We might want to rank them alphabetically
by name or by number of matches one.
0:48
To answer the question,
we want to order them by their ranking, so
0:52
we need to specify that in the order for
the rank function.
0:55
We'll introduce the clause inside
the parentheses with the words, order by,
0:59
followed by the expression used to
determine order, player ranking.
1:03
Like with partitions,
1:10
you can pass multiple expressions to
order by clauses and you can order by
1:11
aggregates as long as they return
one value per row of the result set.
1:15
When we this ranks the players, but
it's the opposite of what we want.
1:20
The lowest ranked players ranked first and
1:25
the highest ranked players
have high rankings.
1:28
We can easily reverse the order
by specifying a sort order for
1:31
player ranking.
1:34
The default is ASC or ascending.
1:35
Since we want the rank to start with the
highest ranking, we'll specify DESC for
1:38
descending This gives us the ranking for
each player across the entire tournament.
1:43
We want to know the rank within the team,
so
1:53
we'll add in a partition by player team so
that the ranking is done within each team.
1:55
The partition clause goes inside the
parenthesis before the order by clause.
1:59
When we run that, we get the ranking for
each player within their team.
2:10
It's a little bit hard to see right now
because the records are still ordered
2:14
by number of wins and that doesn't match
the order by player ranking exactly.
2:17
Let's change that so
2:22
that it's easier to see which players
are at the top of their team.
2:23
To avoid typing the entire
window function again,
2:26
we use the short cut where we
use the number of the field.
2:29
In this case, our rank window
function is the 15th field.
2:31
Right now we have DESC specified,
but we want to change that to AFC so
2:35
that the number one ranked
player will be at the top.
2:40
Let's run that.
2:43
Now we can see that Xavier, Kate, and
2:45
Gina are the highest ranked
players on their respective teams.
2:47
There are other window functions which
provide information about the rows that
2:51
appear before or after a particular row,
or determine the first or
2:55
last value in a window.
2:58
Check the teacher's notes for
more information on those.
3:00
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up