Ordering Rows in a Window3:02 with Danielle Kiowski
Grouping Data With Partitions
ORDER BY — Keyword to introduce the ordering clause. Followed by expressions that determine the order of rows within the window.
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)
See more in the PostgreSQL documentation: https://www.postgresql.org/docs/devel/static/functions-window.html
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
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 up