SQL Window Functions
A window function allows for computation across several rows of a result set, similar to an aggregate function. However, rather than returning a single computed result across all of the rows in the result set, a window function returns each row that it operates on along with a value that the window function computes and associates with that row. The result set of a window function is not a grouped or summarized set of rows, as is the case with aggregate functions. Window functions can also be referred to as analytic functions.
A SQL SELECT statement column_list can contain multiple window functions. If you specify a window function then it must be an item in the SELECT column_list or ORDER BY clause. It cannot be used as an expression in a WHERE clause. It also cannot be used in a GROUP BY or HAVING clause. This is because NuoDB first evaluates the expressions in these clauses and then for each row, it executes the window function. Window functions are supported in derived tables and SQL sub-SELECT statements. These sub-SELECT statements can be used as an item in the SELECT statement column_list or in the WHERE clause.
Syntax
WINDOW_FUNCTION( arguments ) OVER ( [ partition_clause ] [ order_by_clause ] )WINDOW_FUNCTION-
The following window functions are supported:
arguments-
argumentspassed to the window function. Not all window functions acceptarguments. See specific functions below for details on window functions that allowarguments. OVERclause-
A window function contains an
OVERclause. This is what differentiates it from other functions. ThisOVERclause will define how all of the rows being returned by the SQLSELECTstatement will be grouped and sorted for calculating the returned value for each row.The following parameters are contained in the
OVERclause:partition_clause-
The
partition_clauseformat is:PARTITION BY column_name [ , column_name , ... ]The
partition_clauseis optional.The
partition_clausedefines how theSELECTresult set is to be grouped for computing the values returned by the window function. In each partition group, every row has the same value for all of the columns defined by thepartition_clause. This group of rows is considered the window of rows. The window function returns a value for each row in the partition group, or window, based on some computed value across all rows in the partition group. See each individual window function below for a definition of the computed value for that window function.If no
partition_clauseis provided, the window function will return a value for each row based on some computed value across the entireSELECTresult set. order_by_clause-
The
order_by_clauseformat is:ORDER BY column_name [ ASC | DESC ] [ , column_name [ ASC | DESC ] , ... ]The
order_by_clauseis optional for all window functions except theROW_NUMBERwindow function.The
order_by_clausedefines the order in which the window function computes and returns the value for each row defined in the partition group, or window. If you specify apartition clause, then for each partition group, rows are ordered and assigned computed values based on thisorder_by_clause.The order for each column can be specified in either ascending, the default, or descending order.
The
order_by_clauseis not the same as the SQLSELECTstatementORDER BYclause. Window functions are executed at the end of query execution, just before the SQLSELECTstatementORDER BYclause is evaluated. If there is no SQLSELECTstatementORDER BYclause, then the output rows are ordered by the columns specified by the window functionorder_by_clause, within the groups defined by thepartition_clause, if one is provided. See the last two examples for theAVG()window function. This example uses anORDER BYclause to change the ordering of output rows after the window function is executed.
Functions
See each window function below for syntax and how they each compute values.
ROW_NUMBER()
ROW_NUMBER() OVER ( [ partition_clause ] order_by_clause )The ROW_NUMBER() window function will return a unique sequential number for each row, starting with 1 and incrementing by 1. This number is assigned to each row in the order defined by the order_by_clause. This order_by_clause is required since the value returned for each row is dependent on this ordering.
If a partition_clause is provided, the function will assign 1 to the first row returned in each partition group, 2 to the second row in each partition group, and so on. This value is reset back to 1 and incremented by 1 and assigned to rows in each partition group.
The following example assigns ROW_NUMBER to players based on their number of goals, highest to lowest. This ranking is partitioned by team. ROW_NUMBER always returns a unique value for every row in the partition. Therefore, players on the same team that have the same number of goals will be assigned a ROW_NUMBER value incrementally.
In this example, since there is no ORDER BY clause provided for the SQL SELECT statement, the output rows are sorted based on the window function order_by_clause, namely the player_goal column value in descending order, within each partition group, defined by team_name. The team_name column, added to the order_by_clause, causes the output rows to be ordered by team_name in ascending order. If the team_name is eliminated from the order_by_clause, then the output rows would have been ordered by team_name as specified by the partition_clause, but ordered in descending order, based on the descending order specified in the order_by_clause.
|
SELECT PLAYER_NAME, TEAM_NAME, POSITION, STINT, GOALS, ASSISTS
, ROW_NUMBER() OVER (PARTITION BY TEAM_NAME ORDER BY TEAM_NAME ASC, GOALS DESC) AS PLAYER_GOAL
FROM ( SELECT p.firstname||' '|| p.lastname AS PLAYER_NAME
, t.name as TEAM_NAME, s.position, s.stint
, SUM(s.goals) AS GOALS, SUM(s.assists) AS ASSISTS
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976
GROUP BY p.firstname||' '|| p.lastname, t.name, s.stint, s.position);
PLAYER_NAME TEAM_NAME POSITION STINT GOALS ASSISTS PLAYER_GOAL
-------------------- ------------------------- --------- ------ ------ -------- ------------
Willi Plett Atlanta Flames R 1 33 23 1
Eric Vail Atlanta Flames L 1 32 39 2
Tom Lysiak Atlanta Flames C 1 30 51 3
Curt Bennett Atlanta Flames L 1 22 25 4
Ken Houston Atlanta Flames R 1 20 24 5
Bill Clement Atlanta Flames C 1 17 26 6
Guy Chouinard Atlanta Flames C 1 17 33 7
Rey Comeau Atlanta Flames C 1 15 18 8
Bobby Simpson Atlanta Flames L 1 13 10 9
Richard Mulhern Atlanta Flames D 1 12 32 10
Tim Ecclestone Atlanta Flames L 1 9 18 11
Hilliard Graves Atlanta Flames R 1 8 5 12
John Gould Atlanta Flames R 2 8 15 13
Randy Manery Atlanta Flames D 1 5 24 14
Dave Shand Atlanta Flames D 1 5 11 15
Ed Kea Atlanta Flames D 1 4 21 16
Bill Flett Atlanta Flames R 1 4 4 17
Larry Romanchych Atlanta Flames R 1 4 5 18
Larry Carriere Atlanta Flames D 1 2 3 19
Pat Ribble Atlanta Flames D 1 2 2 20
Pat Quinn Atlanta Flames D 1 1 12 21
Barry Gibbs Atlanta Flames D 1 1 16 22
Phil Myre Atlanta Flames G 1 0 2 23
Dan Bouchard Atlanta Flames G 1 0 1 24
Rick Bowness Atlanta Flames R 1 0 4 25
Mark Napier Birmingham Bulls R 1 60 36 1
Vaclav Nedomansky Birmingham Bulls R 1 36 33 2
Tim Sheehy Birmingham Bulls R 2 26 21 3
Peter Marrin Birmingham Bulls C 1 23 37 4
Paul Henderson Birmingham Bulls L 1 23 25 5
Jeff Jacques Birmingham Bulls R 1 21 27 6
Lou Nistico Birmingham Bulls L 1 20 36 7
John Stewart Birmingham Bulls C 1 17 24 8
Dave Gorman Birmingham Bulls R 2 9 13 9
Richard Farda Birmingham Bulls C 1 9 26 10
Gavin Kirk Birmingham Bulls C 1 9 18 11
Tom Simpson Birmingham Bulls R 1 7 6 12
Dale Hoganson Birmingham Bulls D 1 7 48 13
Jim Turkiewicz Birmingham Bulls D 1 6 25 14
Gord Gallant Birmingham Bulls L 2 4 13 15
Frank Mahovlich Birmingham Bulls L 1 3 20 16
Gilles Bilodeau Birmingham Bulls L 1 2 6 17
Jean-Guy Lagace Birmingham Bulls D 1 2 25 18
Jean-Luc Phaneuf Birmingham Bulls R 1 2 7 19
Wayne Carleton Birmingham Bulls C 1 1 0 20
Pat Westrum Birmingham Bulls D 2 1 11 21
Terry Ball Birmingham Bulls D 1 1 6 22
Wayne Wood Birmingham Bulls G 1 0 0 23
John Stewart Birmingham Bulls L 2 0 0 24
Rich Hart Birmingham Bulls D 1 0 0 25
Jerry Rollins Birmingham Bulls D 1 0 0 26
Keith Kokkola Birmingham Bulls D 1 0 0 27
Peter Folco Birmingham Bulls D 1 0 0 28
Rick Cunningham Birmingham Bulls D 1 0 8 29
Jim Marsh Birmingham Bulls D 1 0 0 30
Dave Syvret Birmingham Bulls D 1 0 0 31
Buzz Schneider Birmingham Bulls L 1 0 0 32
Ray McKay Birmingham Bulls D 2 0 1 33
John Garrett Birmingham Bulls G 1 0 3 34
Paul Heaver Birmingham Bulls D 1 0 0 35
Peter McNab Boston Bruins C 1 38 48 1
Jean Ratelle Boston Bruins C 1 33 61 2
Gregg Sheppard Boston Bruins C 1 31 36 3
Don Marcotte Boston Bruins L 1 27 18 4
Bobby Schmautz Boston Bruins R 1 23 29 5
John Bucyk Boston Bruins L 1 20 23 6
Rick Middleton Boston Bruins R 1 20 22 7
Stan Jonathan Boston Bruins L 1 17 13 8
Wayne Cashman Boston Bruins L 1 15 37 9
Terry O'Reilly Boston Bruins R 1 14 41 10
Brad Park Boston Bruins D 1 12 55 11
Matti Hagman Boston Bruins C 1 11 17 12
Earl Anderson Boston Bruins R 1 10 11 13
Dave Forbes Boston Bruins L 1 9 11 14
Hank Nowak Boston Bruins L 1 7 5 15
Mike Milbury Boston Bruins D 1 6 18 16
Rick Smith Boston Bruins D 2 6 16 17
John Wensink Boston Bruins L 1 4 6 18
Gary Doak Boston Bruins D 1 3 13 19
Doug Halward Boston Bruins D 1 2 2 20
Ray Maluta Boston Bruins D 1 2 3 21
Dallas Smith Boston Bruins D 1 2 20 22
Darryl Edestrand Boston Bruins D 1 0 3 23
Gerry Cheevers Boston Bruins G 1 0 4 24
Clayton Pachal Boston Bruins L 1 0 0 25
Joe Zanussi Boston Bruins D 1 0 1 26
Gilles Gilbert Boston Bruins G 1 0 1 27
Al Sims Boston Bruins D 1 0 0 28
Jim Pettie Boston Bruins G 1 0 0 29
...In the previous example, there are several players with the same number of goals. Some examples have been highlighted. The ROW_NUMBER() window function can compute a different value for each of these duplicate rows every time it executes, depending on the order in which it sorts the duplicate rows. Multiple order_by_clause columns can be provided to further defined the ordering of these duplicate rows.
In the following example, the assists column s is added to the order_by_clause. This re-orders these duplicate rows, where players have the same number of goals. The ROW_NUMBER() window function assigns values now ordered by number of goals and number of assists within goals.
SELECT PLAYER_NAME, TEAM_NAME, POSITION, STINT, GOALS, ASSISTS
, ROW_NUMBER() OVER (PARTITION BY TEAM_NAME ORDER BY TEAM_NAME ASC
, GOALS DESC) AS PLAYER_GOAL
, ROW_NUMBER() OVER (PARTITION BY TEAM_NAME ORDER BY TEAM_NAME ASC
, GOALS DESC, ASSISTS DESC) AS PLAYER_GOAL_ASSIST
FROM ( SELECT p.firstname||' '|| p.lastname AS PLAYER_NAME
, t.name as TEAM_NAME, s.position, s.stint
, SUM(s.goals) AS GOALS, SUM(s.assists) AS ASSISTS
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976
GROUP BY p.firstname||' '|| p.lastname, t.name, s.stint, s.position);
PLAYER_NAME TEAM_NAME POSITION STINT GOALS ASSISTS PLAYER_GOAL PLAYER_GOAL_ASSIST
-------------------- ------------------------- --------- ------ ------ -------- ------------ -------------------
Willi Plett Atlanta Flames R 1 33 23 1 1
Eric Vail Atlanta Flames L 1 32 39 2 2
Tom Lysiak Atlanta Flames C 1 30 51 3 3
Curt Bennett Atlanta Flames L 1 22 25 4 4
Ken Houston Atlanta Flames R 1 20 24 5 5
Guy Chouinard Atlanta Flames C 1 17 33 7 6
Bill Clement Atlanta Flames C 1 17 26 6 7
Rey Comeau Atlanta Flames C 1 15 18 8 8
Bobby Simpson Atlanta Flames L 1 13 10 9 9
Richard Mulhern Atlanta Flames D 1 12 32 10 10
Tim Ecclestone Atlanta Flames L 1 9 18 11 11
John Gould Atlanta Flames R 2 8 15 13 12
Hilliard Graves Atlanta Flames R 1 8 5 12 13
Randy Manery Atlanta Flames D 1 5 24 14 14
Dave Shand Atlanta Flames D 1 5 11 15 15
Ed Kea Atlanta Flames D 1 4 21 16 16
Larry Romanchych Atlanta Flames R 1 4 5 18 17
Bill Flett Atlanta Flames R 1 4 4 17 18
Larry Carriere Atlanta Flames D 1 2 3 19 19
Pat Ribble Atlanta Flames D 1 2 2 20 20
Barry Gibbs Atlanta Flames D 1 1 16 22 21
Pat Quinn Atlanta Flames D 1 1 12 21 22
Rick Bowness Atlanta Flames R 1 0 4 25 23
Phil Myre Atlanta Flames G 1 0 2 23 24
Dan Bouchard Atlanta Flames G 1 0 1 24 25
...The following example returns the three highest scoring players in the NHL for the year 1976.
SELECT * FROM
(SELECT p.playerid, p.firstname||' '||p.lastname AS PLAYER_NAME
, t.name as TEAM_NAME, s.position,s.goals, s.assists
, ROW_NUMBER() OVER (ORDER BY s.goals DESC) AS PLAYER_NUM
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976)
WHERE player_num < 5;
PLAYERID PLAYER_NAME TEAM_NAME POSITION GOALS ASSISTS PLAYER_NUM
--------- -------------- ------------------ --------- ------ -------- -----------
hedbean01 Anders Hedberg Winnipeg Jets R 70 61 1
cloutre01 Real Cloutier Quebec Nordiques R 66 75 2
napiema01 Mark Napier Birmingham Bulls R 60 36 3
shuttst01 Steve Shutt Montreal Canadiens L 60 45 4In the previous example, there are two players with the same number of goals, both having 60 goals. Because the ROW_NUMBER() window function returns a unique value for each row, these two players are assigned unique values based on some ordering of rows that may not be consistent from one execution to the next. Therefore, there is no guarantee that the ROW_NUMBER() window function will assign these same value these last two row each time the SQL statement executes. For the window function to return consistent values, one or more columns should be used in the order_by_clause that allows the window function to order the results consistently. In the following example, adding the assists column to the order_by_clause changes the results, as follows:
SELECT * FROM
(SELECT p.playerid, p.firstname||' '||p.lastname AS PLAYER_NAME
, t.name as TEAM_NAME, s.position,s.goals, s.assists
, ROW_NUMBER() OVER (ORDER BY s.goals DESC, s.assists DESC) AS PLAYER_NUM
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976)
WHERE player_num < 5;
PLAYERID PLAYER_NAME TEAM_NAME POSITION GOALS ASSISTS PLAYER_NUM
--------- -------------- ------------------ --------- ------ -------- -----------
hedbean01 Anders Hedberg Winnipeg Jets R 70 61 1
cloutre01 Real Cloutier Quebec Nordiques R 66 75 2
shuttst01 Steve Shutt Montreal Canadiens L 60 45 3
napiema01 Mark Napier Birmingham Bulls R 60 36 4COUNT()
COUNT( expression ) OVER ( [ partition_clause ] order_by_clause )The COUNT() window function computes the number of rows in the window. The expression can be defined as an asterick (*), which counts all rows, or a column_name, which counts all rows that have a non-NULL value as specified by the expression. The expression cannot include DISTINCT.
The function uses the order_by_clause to determine the rows to include in computing the COUNT() value. This COUNT() value is cumulative. Therefore, the COUNT() value for the first row is computed using the first row. The COUNT() value for the second row is computed to include the first and second row, and so on. If the expression is defined as a column_name, and that row, or any row previous, contains a NULL value, that row is not included in the computed value,
If the order_by_clause is not provided, this count expression value is computed across all rows in the window and the result is assigned to each row in the window. In other words, the same value is assigned to each row and that value is the total rows in the window.
If a partition_clause is provided, this COUNT() value is reset for the first row returned in each partition group based on the partition_clause.
In the following example, we can use the COUNT aggregate function to return the number of players on each team in the year 1976 by using the GROUP BY clause on team.
SELECT t.name as tEAM_NAM, count(*)
FROM players p, scoring s, teams t
WHERE p.playerid = s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND t.year = 1976
GROUP BY t.name ORDER BY t.name;
TEAM_NAM COUNT
------------------------- ------
Atlanta Flames 25
Birmingham Bulls 35
Boston Bruins 29
Buffalo Sabres 26
Calgary Cowboys 41
Chicago Black Hawks 31
Cincinnati Stingers 26
Cleveland Barons 35
Colorado Rockies 32
Detroit Red Wings 36
Edmonton Oilers 42
Houston Aeros 26
Indianapolis Racers 30
Los Angeles Kings 27
Minnesota Fighting Saints 24
Minnesota North Stars 27
Montreal Canadiens 25
New England Whalers 33
New York Islanders 24
New York Rangers 30
Philadelphia Flyers 32
Phoenix Roadrunners 26
Pittsburgh Penguins 28
Quebec Nordiques 25
San Diego Mariners 27
St. Louis Blues 31
Toronto Maple Leafs 28
Vancouver Canucks 31
Washington Capitals 35
Winnipeg Jets 28This same result can be returned by using the COUNT() window function, as follows:
SELECT distinct * FROM
( SELECT t.name as TEAM_NAME
, count(p.playerid) over (partition by t.teamid)
FROM players p, scoring s, teams t
WHERE p.playerid = s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976);But what if, in addition to selecting this aggregate count by team, we wanted to select the names of the players for each team. The COUNT() window function allows us to do this. Window functions provide a way to select both the group by column(s) as well as each individual row in the group.
SELECT p.firstname||' '||p.lastname AS PLAYER_NAME
,t.name as TEAM_NAME
,count(p.playerid) over (partition by t.teamid)
FROM players p, scoring s, teams t
WHERE p.playerid = s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976
ORDER BY t.name, PLAYER_NAME;
PLAYER_NAME TEAM_NAME COUNT
-------------------- ------------------------- ------
Barry Gibbs Atlanta Flames 25
Bill Clement Atlanta Flames 25
Bill Flett Atlanta Flames 25
Bobby Simpson Atlanta Flames 25
Curt Bennett Atlanta Flames 25
Dan Bouchard Atlanta Flames 25
Dave Shand Atlanta Flames 25
Ed Kea Atlanta Flames 25
Eric Vail Atlanta Flames 25
Guy Chouinard Atlanta Flames 25
Hilliard Graves Atlanta Flames 25
John Gould Atlanta Flames 25
Ken Houston Atlanta Flames 25
Larry Carriere Atlanta Flames 25
Larry Romanchych Atlanta Flames 25
Pat Quinn Atlanta Flames 25
Pat Ribble Atlanta Flames 25
Phil Myre Atlanta Flames 25
Randy Manery Atlanta Flames 25
Rey Comeau Atlanta Flames 25
Richard Mulhern Atlanta Flames 25
Rick Bowness Atlanta Flames 25
Tim Ecclestone Atlanta Flames 25
Tom Lysiak Atlanta Flames 25
Willi Plett Atlanta Flames 25
Buzz Schneider Birmingham Bulls 35
Dale Hoganson Birmingham Bulls 35
Dave Gorman Birmingham Bulls 35
Dave Syvret Birmingham Bulls 35
Frank Mahovlich Birmingham Bulls 35
Gavin Kirk Birmingham Bulls 35
Gilles Bilodeau Birmingham Bulls 35
Gord Gallant Birmingham Bulls 35
Jean-Guy Lagace Birmingham Bulls 35
Jean-Luc Phaneuf Birmingham Bulls 35
Jeff Jacques Birmingham Bulls 35
Jerry Rollins Birmingham Bulls 35
Jim Marsh Birmingham Bulls 35
Jim Turkiewicz Birmingham Bulls 35
John Garrett Birmingham Bulls 35
John Stewart Birmingham Bulls 35
John Stewart Birmingham Bulls 35
Keith Kokkola Birmingham Bulls 35
Lou Nistico Birmingham Bulls 35
Mark Napier Birmingham Bulls 35
Pat Westrum Birmingham Bulls 35
Paul Heaver Birmingham Bulls 35
Paul Henderson Birmingham Bulls 35
Peter Folco Birmingham Bulls 35
Peter Marrin Birmingham Bulls 35
Ray McKay Birmingham Bulls 35
Rich Hart Birmingham Bulls 35
Richard Farda Birmingham Bulls 35
Rick Cunningham Birmingham Bulls 35
Terry Ball Birmingham Bulls 35
Tim Sheehy Birmingham Bulls 35
Tom Simpson Birmingham Bulls 35
Vaclav Nedomansky Birmingham Bulls 35
Wayne Carleton Birmingham Bulls 35
Wayne Wood Birmingham Bulls 35
Al Sims Boston Bruins 29
Bobby Schmautz Boston Bruins 29
Brad Park Boston Bruins 29
Clayton Pachal Boston Bruins 29
Dallas Smith Boston Bruins 29
Darryl Edestrand Boston Bruins 29
Dave Forbes Boston Bruins 29
Don Marcotte Boston Bruins 29
Doug Halward Boston Bruins 29
Earl Anderson Boston Bruins 29
Gary Doak Boston Bruins 29
Gerry Cheevers Boston Bruins 29
Gilles Gilbert Boston Bruins 29
Gregg Sheppard Boston Bruins 29
Hank Nowak Boston Bruins 29
Jean Ratelle Boston Bruins 29
Jim Pettie Boston Bruins 29
Joe Zanussi Boston Bruins 29
John Bucyk Boston Bruins 29
John Wensink Boston Bruins 29
Matti Hagman Boston Bruins 29
Mike Milbury Boston Bruins 29
Peter McNab Boston Bruins 29
Ray Maluta Boston Bruins 29
Rick Middleton Boston Bruins 29
Rick Smith Boston Bruins 29
Stan Jonathan Boston Bruins 29
Terry O'Reilly Boston Bruins 29
Wayne Cashman Boston Bruins 29
...SUM()
SUM( expression ) OVER ( [ partition_clause ] order_by_clause )The SUM() window function computes the sum value for the item identified by the expression argument. This expression can be defined as a table column_name, an arithmetic expression of columns and/or variables, etc.
The function uses the order_by_clause to sort the rows. It then computes the sum for the column(s) identified by the expression. For the first row in the window, the function uses only that row to calculate the sum. It then assigns that value to the first row. For the second row in the window, the function uses the first and second rows to calculate the sum and then the function assigns this value to the second row. For the third row in the window, the function uses the first three rows to calculate the sum, and so on.
If a partition_clause is provided, this total expression value computation is reset for the first row returned in each partition group, or window, based on the partition_clause.
The following example returns, for all teams in the NHL, each player’s weight and height. It uses the SUM() window function to return the total weight and height associated with each row in the window. The total expression value is computed for each row in the partition by using the current row plus all previous rows in the partition and sorted based on the order_by_clause
SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
, p.weight as PLAYER_WEIGHT, p.height as PLAYER_HEIGHT
, SUM(p.weight) OVER (PARTITION BY t.name order by p.firstname) AS TOTAL_WEIGHT
, SUM(p.height) OVER (PARTITION BY t.name ORDER BY p.firstname) AS TOTAL_HEIGHT
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976;
TEAM_NAME YEAR PLAYER_NAME PLAYER_WEIGHT PLAYER_HEIGHT TOTAL_WEIGHT TOTAL_HEIGHT
------------------------- ----- -------------------- -------------- -------------- ------------- -------------
Atlanta Flames 1976 Barry Gibbs 195 71 195 71
Atlanta Flames 1976 Bill Clement 194 73 594 217
Atlanta Flames 1976 Bill Flett 205 73 594 217
Atlanta Flames 1976 Bobby Simpson 190 72 784 289
Atlanta Flames 1976 Curt Bennett 195 75 979 364
Atlanta Flames 1976 Dan Bouchard 190 72 1169 436
Atlanta Flames 1976 Dave Shand 200 74 1369 510
Atlanta Flames 1976 Ed Kea 200 75 1569 585
Atlanta Flames 1976 Eric Vail 220 73 1789 658
Atlanta Flames 1976 Guy Chouinard 182 71 1971 729
Atlanta Flames 1976 Hilliard Graves 175 71 2146 800
Atlanta Flames 1976 John Gould 197 71 2343 871
Atlanta Flames 1976 Ken Houston 210 74 2553 945
Atlanta Flames 1976 Larry Romanchych 180 73 2923 1091
Atlanta Flames 1976 Larry Carriere 190 73 2923 1091
Atlanta Flames 1976 Pat Quinn 205 75 3338 1242
Atlanta Flames 1976 Pat Ribble 210 76 3338 1242
Atlanta Flames 1976 Phil Myre 185 73 3523 1315
Atlanta Flames 1976 Randy Manery 185 72 3708 1387
Atlanta Flames 1976 Rey Comeau 190 68 3898 1455
Atlanta Flames 1976 Richard Mulhern 188 73 4086 1528
Atlanta Flames 1976 Rick Bowness 185 73 4271 1601
Atlanta Flames 1976 Tim Ecclestone 195 70 4466 1671
Atlanta Flames 1976 Tom Lysiak 185 73 4651 1744
Atlanta Flames 1976 Willi Plett 205 75 4856 1819
Birmingham Bulls 1976 Buzz Schneider 175 71 175 71
Birmingham Bulls 1976 Dale Hoganson 190 70 365 141
Birmingham Bulls 1976 Dave Syvret 190 72 740 284
Birmingham Bulls 1976 Dave Gorman 185 71 740 284
Birmingham Bulls 1976 Frank Mahovlich 205 72 945 356
Birmingham Bulls 1976 Gavin Kirk 165 70 1110 426
Birmingham Bulls 1976 Gilles Bilodeau 220 73 1330 499
Birmingham Bulls 1976 Gord Gallant 175 71 1505 570
Birmingham Bulls 1976 Jean-Guy Lagace 185 70 1690 640
Birmingham Bulls 1976 Jean-Luc Phaneuf 165 68 1855 708
Birmingham Bulls 1976 Jeff Jacques 180 71 2035 779
Birmingham Bulls 1976 Jerry Rollins 195 74 2230 853
Birmingham Bulls 1976 Jim Turkiewicz 185 70 2595 995
Birmingham Bulls 1976 Jim Marsh 180 72 2595 995
Birmingham Bulls 1976 John Stewart 180 72 3130 1207
Birmingham Bulls 1976 John Garrett 175 68 3130 1207
Birmingham Bulls 1976 John Stewart 180 72 3130 1207
Birmingham Bulls 1976 Keith Kokkola 210 75 3340 1282
Birmingham Bulls 1976 Lou Nistico 170 67 3510 1349
Birmingham Bulls 1976 Mark Napier 182 70 3692 1419
Birmingham Bulls 1976 Pat Westrum 185 70 3877 1489
Birmingham Bulls 1976 Paul Henderson 180 71 4252 1633
Birmingham Bulls 1976 Paul Heaver 195 73 4252 1633
Birmingham Bulls 1976 Peter Marrin 160 70 4597 1775
Birmingham Bulls 1976 Peter Folco 185 72 4597 1775
Birmingham Bulls 1976 Ray McKay 183 76 4780 1851
Birmingham Bulls 1976 Rich Hart 195 72 4975 1923
Birmingham Bulls 1976 Richard Farda 175 69 5150 1992
Birmingham Bulls 1976 Rick Cunningham 180 71 5330 2063
Birmingham Bulls 1976 Terry Ball 160 69 5490 2132
Birmingham Bulls 1976 Tim Sheehy 185 73 5675 2205
Birmingham Bulls 1976 Tom Simpson 190 69 5865 2274
Birmingham Bulls 1976 Vaclav Nedomansky 205 74 6070 2348
Birmingham Bulls 1976 Wayne Wood 190 73 6472 2496
Birmingham Bulls 1976 Wayne Carleton 212 75 6472 2496
Boston Bruins 1976 Al Sims 182 72 182 72
Boston Bruins 1976 Bobby Schmautz 172 69 354 141
Boston Bruins 1976 Brad Park 200 72 554 213
Boston Bruins 1976 Clayton Pachal 185 70 739 283
Boston Bruins 1976 Dallas Smith 180 71 919 354
Boston Bruins 1976 Darryl Edestrand 180 71 1099 425
Boston Bruins 1976 Dave Forbes 180 70 1279 495
Boston Bruins 1976 Don Marcotte 183 71 1462 566
Boston Bruins 1976 Doug Halward 200 73 1662 639
Boston Bruins 1976 Earl Anderson 185 73 1847 712
Boston Bruins 1976 Gary Doak 175 71 2022 783
Boston Bruins 1976 Gerry Cheevers 185 71 2207 854
Boston Bruins 1976 Gilles Gilbert 175 73 2382 927
Boston Bruins 1976 Gregg Sheppard 170 68 2552 995
Boston Bruins 1976 Hank Nowak 195 73 2747 1068
Boston Bruins 1976 Jean Ratelle 180 73 2927 1141
Boston Bruins 1976 Jim Pettie 195 72 3122 1213
Boston Bruins 1976 Joe Zanussi 180 70 3302 1283
Boston Bruins 1976 John Wensink 200 72 3717 1427
Boston Bruins 1976 John Bucyk 215 72 3717 1427
Boston Bruins 1976 Matti Hagman 183 72 3900 1499
Boston Bruins 1976 Mike Milbury 200 73 4100 1572
Boston Bruins 1976 Peter McNab 210 75 4310 1647
Boston Bruins 1976 Ray Maluta 173 68 4483 1715
Boston Bruins 1976 Rick Smith 190 71 4843 1857
Boston Bruins 1976 Rick Middleton 170 71 4843 1857
Boston Bruins 1976 Stan Jonathan 175 68 5018 1925
Boston Bruins 1976 Terry O'Reilly 200 73 5218 1998
Boston Bruins 1976 Wayne Cashman 208 73 5426 2071
...In the previous example, take a look at the 2nd and 3rd rows. Both have "Bill" as a FirstName. The SUM() window function will return the same cumulative value for each row that has the same order_by_clause column value within the window partition. If we change the order_by_clause to include multiple columns that will result in unique values, the SUM() window function will compute cumulative and not duplicate values for the same 2nd and 3rd rows.
SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
, p.weight as PLAYER_WEIGHT, p.height as PLAYER_HEIGHT
, SUM(p.weight) OVER (PARTITION BY t.name order by p.firstname,p.lastname) AS TOTAL_WEIGHT
, SUM(p.height) OVER (PARTITION BY t.name ORDER BY p.firstname,p.lastname) AS TOTAL_HEIGHT
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976;
TEAM_NAME YEAR PLAYER_NAME PLAYER_WEIGHT PLAYER_HEIGHT TOTAL_WEIGHT TOTAL_HEIGHT
------------------------- ----- -------------------- -------------- -------------- ------------- -------------
Atlanta Flames 1976 Barry Gibbs 195 71 195 71
Atlanta Flames 1976 Bill Clement 194 73 389 144
Atlanta Flames 1976 Bill Flett 205 73 594 217
Atlanta Flames 1976 Bobby Simpson 190 72 784 289
Atlanta Flames 1976 Curt Bennett 195 75 979 364
Atlanta Flames 1976 Dan Bouchard 190 72 1169 436
Atlanta Flames 1976 Dave Shand 200 74 1369 510
Atlanta Flames 1976 Ed Kea 200 75 1569 585
Atlanta Flames 1976 Eric Vail 220 73 1789 658
Atlanta Flames 1976 Guy Chouinard 182 71 1971 729
Atlanta Flames 1976 Hilliard Graves 175 71 2146 800
Atlanta Flames 1976 John Gould 197 71 2343 871
Atlanta Flames 1976 Ken Houston 210 74 2553 945
Atlanta Flames 1976 Larry Carriere 190 73 2743 1018
Atlanta Flames 1976 Larry Romanchych 180 73 2923 1091
Atlanta Flames 1976 Pat Quinn 205 75 3128 1166
Atlanta Flames 1976 Pat Ribble 210 76 3338 1242
Atlanta Flames 1976 Phil Myre 185 73 3523 1315
Atlanta Flames 1976 Randy Manery 185 72 3708 1387
Atlanta Flames 1976 Rey Comeau 190 68 3898 1455
Atlanta Flames 1976 Richard Mulhern 188 73 4086 1528
Atlanta Flames 1976 Rick Bowness 185 73 4271 1601
Atlanta Flames 1976 Tim Ecclestone 195 70 4466 1671
Atlanta Flames 1976 Tom Lysiak 185 73 4651 1744
Atlanta Flames 1976 Willi Plett 205 75 4856 1819
...We can also remove the order_by_clause and change the report to show, for each team, in the year 1976, the total number of goals for that team. This can be done with the SUM() window function, partitioned by the team.
SELECT DISTINCT * FROM (
SELECT t.name AS TEAM_NAME
,SUM(s.goals) OVER (PARTITION BY s.teamid)
FROM scoring s, teams t
WHERE s.teamid = t.teamid AND s.year = t.year
AND s.year = 1976);
TEAM_NAME SUM
------------------------- ----
Atlanta Flames 264
Birmingham Bulls 289
Boston Bruins 312
Buffalo Sabres 301
Calgary Cowboys 252
Chicago Black Hawks 240
Cincinnati Stingers 354
Cleveland Barons 240
Colorado Rockies 226
Detroit Red Wings 183
Edmonton Oilers 243
Houston Aeros 320
Indianapolis Racers 276
Los Angeles Kings 271
Minnesota Fighting Saints 136
Minnesota North Stars 240
Montreal Canadiens 387
New England Whalers 275
New York Islanders 288
New York Rangers 272
Philadelphia Flyers 323
Phoenix Roadrunners 281
Pittsburgh Penguins 240
Quebec Nordiques 353
San Diego Mariners 284
St. Louis Blues 239
Toronto Maple Leafs 301
Vancouver Canucks 235
Washington Capitals 221
Winnipeg Jets 366AVG()
AVG( expression ) OVER ( [ partition_clause ] order_by_clause )The AVG() window function computes the average of the expression argument. This expression can be defined as a table column name or an arithmetic expression of table column and/or variable.
The order_by_clause is optional. If it is provided, the function uses the order_by_clause to sort the rows before computing the average value of the expression for any given row in the window. This average expression value is assigned to each row of the partition group cumulatively. Therefore, the AVG() window function uses only the first row of the partition group to calculate the average expression value for that row. It uses the first two rows of the partition group to compute the average expression value returned for the second row. It uses the first three rows of the partition group to compute the average expression value returned for the third row, and so on.
If the order_by_clause is not provided, this average expression value is computed across all rows in the window and the result is assigned to each row in the window. In other words, the same value is assigned to each row.
If a partition_clause is provided, this average expression value computation is reset for the first row returned in each partition group, or window, based on the partition_clause.
The following example returns, for all teams in the NHL, each player’s weight and height. It uses the AVG() window function to return the average weight and height associated with each row in the window. The average expression value is computed for each row in the partition by using the current row plus all previous rows in the partition and sorted based on the order_by_clause.
The AVG() window function returns a value with the same precision and scale as the precision and scale of the expression.
|
SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
, p.weight as PLAYER_WEIGHT, p.height as PLAYER_HEIGHT
, AVG(p.weight) OVER (PARTITION BY t.name order by p.firstname,p.lastname) AS AVG_WEIGHT
, AVG(p.height) OVER (PARTITION BY t.name ORDER BY p.firstname,p.lastname) AS AVG_HEIGHT
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976;
TEAM_NAME YEAR PLAYER_NAME PLAYER_WEIGHT PLAYER_HEIGHT AVG_WEIGHT AVG_HEIGHT
------------------------- ----- -------------------- -------------- -------------- ----------- -----------
Atlanta Flames 1976 Barry Gibbs 195 71 195 71
Atlanta Flames 1976 Bill Clement 194 73 194 72
Atlanta Flames 1976 Bill Flett 205 73 198 72
Atlanta Flames 1976 Bobby Simpson 190 72 196 72
Atlanta Flames 1976 Curt Bennett 195 75 195 72
Atlanta Flames 1976 Dan Bouchard 190 72 194 72
Atlanta Flames 1976 Dave Shand 200 74 195 72
Atlanta Flames 1976 Ed Kea 200 75 196 73
Atlanta Flames 1976 Eric Vail 220 73 198 73
Atlanta Flames 1976 Guy Chouinard 182 71 197 72
Atlanta Flames 1976 Hilliard Graves 175 71 195 72
Atlanta Flames 1976 John Gould 197 71 195 72
Atlanta Flames 1976 Ken Houston 210 74 196 72
Atlanta Flames 1976 Larry Carriere 190 73 195 72
Atlanta Flames 1976 Larry Romanchych 180 73 194 72
Atlanta Flames 1976 Pat Quinn 205 75 195 72
Atlanta Flames 1976 Pat Ribble 210 76 196 73
Atlanta Flames 1976 Phil Myre 185 73 195 73
Atlanta Flames 1976 Randy Manery 185 72 195 73
Atlanta Flames 1976 Rey Comeau 190 68 194 72
Atlanta Flames 1976 Richard Mulhern 188 73 194 72
Atlanta Flames 1976 Rick Bowness 185 73 194 72
Atlanta Flames 1976 Tim Ecclestone 195 70 194 72
Atlanta Flames 1976 Tom Lysiak 185 73 193 72
Atlanta Flames 1976 Willi Plett 205 75 194 72
Birmingham Bulls 1976 Buzz Schneider 175 71 175 71
Birmingham Bulls 1976 Dale Hoganson 190 70 182 70
Birmingham Bulls 1976 Dave Gorman 185 71 183 70
Birmingham Bulls 1976 Dave Syvret 190 72 185 71
Birmingham Bulls 1976 Frank Mahovlich 205 72 189 71
Birmingham Bulls 1976 Gavin Kirk 165 70 185 71
Birmingham Bulls 1976 Gilles Bilodeau 220 73 190 71
Birmingham Bulls 1976 Gord Gallant 175 71 188 71
Birmingham Bulls 1976 Jean-Guy Lagace 185 70 187 71
Birmingham Bulls 1976 Jean-Luc Phaneuf 165 68 185 70
Birmingham Bulls 1976 Jeff Jacques 180 71 185 70
Birmingham Bulls 1976 Jerry Rollins 195 74 185 71
Birmingham Bulls 1976 Jim Marsh 180 72 185 71
Birmingham Bulls 1976 Jim Turkiewicz 185 70 185 71
Birmingham Bulls 1976 John Garrett 175 68 184 70
Birmingham Bulls 1976 John Stewart 180 72 184 71
Birmingham Bulls 1976 John Stewart 180 72 184 71
Birmingham Bulls 1976 Keith Kokkola 210 75 185 71
Birmingham Bulls 1976 Lou Nistico 170 67 184 71
Birmingham Bulls 1976 Mark Napier 182 70 184 70
Birmingham Bulls 1976 Pat Westrum 185 70 184 70
Birmingham Bulls 1976 Paul Heaver 195 73 185 71
Birmingham Bulls 1976 Paul Henderson 180 71 184 71
Birmingham Bulls 1976 Peter Folco 185 72 184 71
Birmingham Bulls 1976 Peter Marrin 160 70 183 71
Birmingham Bulls 1976 Ray McKay 183 76 183 71
Birmingham Bulls 1976 Rich Hart 195 72 184 71
Birmingham Bulls 1976 Richard Farda 175 69 183 71
Birmingham Bulls 1976 Rick Cunningham 180 71 183 71
Birmingham Bulls 1976 Terry Ball 160 69 183 71
Birmingham Bulls 1976 Tim Sheehy 185 73 183 71
Birmingham Bulls 1976 Tom Simpson 190 69 183 71
Birmingham Bulls 1976 Vaclav Nedomansky 205 74 183 71
Birmingham Bulls 1976 Wayne Carleton 212 75 184 71
Birmingham Bulls 1976 Wayne Wood 190 73 184 71
Boston Bruins 1976 Al Sims 182 72 182 72
Boston Bruins 1976 Bobby Schmautz 172 69 177 70
Boston Bruins 1976 Brad Park 200 72 184 71
Boston Bruins 1976 Clayton Pachal 185 70 184 70
Boston Bruins 1976 Dallas Smith 180 71 183 70
Boston Bruins 1976 Darryl Edestrand 180 71 183 70
Boston Bruins 1976 Dave Forbes 180 70 182 70
Boston Bruins 1976 Don Marcotte 183 71 182 70
Boston Bruins 1976 Doug Halward 200 73 184 71
Boston Bruins 1976 Earl Anderson 185 73 184 71
Boston Bruins 1976 Gary Doak 175 71 183 71
Boston Bruins 1976 Gerry Cheevers 185 71 183 71
Boston Bruins 1976 Gilles Gilbert 175 73 183 71
Boston Bruins 1976 Gregg Sheppard 170 68 182 71
Boston Bruins 1976 Hank Nowak 195 73 183 71
Boston Bruins 1976 Jean Ratelle 180 73 182 71
Boston Bruins 1976 Jim Pettie 195 72 183 71
Boston Bruins 1976 Joe Zanussi 180 70 183 71
Boston Bruins 1976 John Bucyk 215 72 185 71
Boston Bruins 1976 John Wensink 200 72 185 71
Boston Bruins 1976 Matti Hagman 183 72 185 71
Boston Bruins 1976 Mike Milbury 200 73 186 71
Boston Bruins 1976 Peter McNab 210 75 187 71
Boston Bruins 1976 Ray Maluta 173 68 186 71
Boston Bruins 1976 Rick Middleton 170 71 186 71
Boston Bruins 1976 Rick Smith 190 71 186 71
Boston Bruins 1976 Stan Jonathan 175 68 185 71
Boston Bruins 1976 Terry O'Reilly 200 73 186 71
Boston Bruins 1976 Wayne Cashman 208 73 187 71
...If the order_by_clause is removed, the average weight for each team is calculated and the result is assigned to each row in the team window. In other words, the same average weight is assigned to each row in the team window. Using this average weight value, we can then calculate, for each player on the team, the difference between their weigh and the average team weight.
SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
, p.weight as PLAYER_WEIGHT
, AVG(p.weight) OVER (PARTITION BY t.name ) AS AVG_TEAM_WEIGHT
, p.weight - AVG(p.weight) OVER (PARTITION BY t.name ) AS WEIGHT_DIFF
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976;
TEAM_NAME YEAR PLAYER_NAME PLAYER_WEIGHT AVG_TEAM_WEIGHT WEIGHT_DIFF
------------------------- ----- -------------------- -------------- ---------------- ------------
Atlanta Flames 1976 Hilliard Graves 175 194 -19
Atlanta Flames 1976 John Gould 197 194 3
Atlanta Flames 1976 Pat Quinn 205 194 11
Atlanta Flames 1976 Bill Flett 205 194 11
Atlanta Flames 1976 Bobby Simpson 190 194 -4
Atlanta Flames 1976 Curt Bennett 195 194 1
Atlanta Flames 1976 Randy Manery 185 194 -9
Atlanta Flames 1976 Eric Vail 220 194 26
Atlanta Flames 1976 Richard Mulhern 188 194 -6
Atlanta Flames 1976 Tim Ecclestone 195 194 1
Atlanta Flames 1976 Tom Lysiak 185 194 -9
Atlanta Flames 1976 Phil Myre 185 194 -9
Atlanta Flames 1976 Larry Carriere 190 194 -4
Atlanta Flames 1976 Guy Chouinard 182 194 -12
Atlanta Flames 1976 Rick Bowness 185 194 -9
Atlanta Flames 1976 Ed Kea 200 194 6
Atlanta Flames 1976 Barry Gibbs 195 194 1
Atlanta Flames 1976 Larry Romanchych 180 194 -14
Atlanta Flames 1976 Bill Clement 194 194 0
Atlanta Flames 1976 Dave Shand 200 194 6
Atlanta Flames 1976 Pat Ribble 210 194 16
Atlanta Flames 1976 Ken Houston 210 194 16
Atlanta Flames 1976 Dan Bouchard 190 194 -4
Atlanta Flames 1976 Rey Comeau 190 194 -4
Atlanta Flames 1976 Willi Plett 205 194 11
Birmingham Bulls 1976 Paul Henderson 180 184 -4
Birmingham Bulls 1976 Jerry Rollins 195 184 11
Birmingham Bulls 1976 Paul Heaver 195 184 11
Birmingham Bulls 1976 Lou Nistico 170 184 -14
Birmingham Bulls 1976 Peter Marrin 160 184 -24
Birmingham Bulls 1976 Rich Hart 195 184 11
Birmingham Bulls 1976 Jim Marsh 180 184 -4
Birmingham Bulls 1976 Jeff Jacques 180 184 -4
Birmingham Bulls 1976 Terry Ball 160 184 -24
Birmingham Bulls 1976 John Garrett 175 184 -9
Birmingham Bulls 1976 Jim Turkiewicz 185 184 1
Birmingham Bulls 1976 Peter Folco 185 184 1
Birmingham Bulls 1976 Dave Gorman 185 184 1
Birmingham Bulls 1976 Dave Syvret 190 184 6
Birmingham Bulls 1976 Jean-Luc Phaneuf 165 184 -19
Birmingham Bulls 1976 Vaclav Nedomansky 205 184 21
Birmingham Bulls 1976 John Stewart 180 184 -4
Birmingham Bulls 1976 John Stewart 180 184 -4
Birmingham Bulls 1976 Mark Napier 182 184 -2
Birmingham Bulls 1976 Gilles Bilodeau 220 184 36
Birmingham Bulls 1976 Ray McKay 183 184 -1
Birmingham Bulls 1976 Pat Westrum 185 184 1
Birmingham Bulls 1976 Gord Gallant 175 184 -9
Birmingham Bulls 1976 Tom Simpson 190 184 6
Birmingham Bulls 1976 Jean-Guy Lagace 185 184 1
Birmingham Bulls 1976 Keith Kokkola 210 184 26
Birmingham Bulls 1976 Richard Farda 175 184 -9
Birmingham Bulls 1976 Gavin Kirk 165 184 -19
Birmingham Bulls 1976 Wayne Wood 190 184 6
Birmingham Bulls 1976 Tim Sheehy 185 184 1
Birmingham Bulls 1976 Dale Hoganson 190 184 6
Birmingham Bulls 1976 Rick Cunningham 180 184 -4
Birmingham Bulls 1976 Frank Mahovlich 205 184 21
Birmingham Bulls 1976 Wayne Carleton 212 184 28
Birmingham Bulls 1976 Buzz Schneider 175 184 -9
Boston Bruins 1976 Earl Anderson 185 187 -2
Boston Bruins 1976 Rick Smith 190 187 3
Boston Bruins 1976 Dallas Smith 180 187 -7
Boston Bruins 1976 Al Sims 182 187 -5
Boston Bruins 1976 Brad Park 200 187 13
Boston Bruins 1976 Clayton Pachal 185 187 -2
Boston Bruins 1976 Matti Hagman 183 187 -4
Boston Bruins 1976 Gerry Cheevers 185 187 -2
Boston Bruins 1976 Gilles Gilbert 175 187 -12
Boston Bruins 1976 Don Marcotte 183 187 -4
Boston Bruins 1976 John Bucyk 215 187 28
Boston Bruins 1976 Darryl Edestrand 180 187 -7
Boston Bruins 1976 Rick Middleton 170 187 -17
Boston Bruins 1976 Dave Forbes 180 187 -7
Boston Bruins 1976 Gregg Sheppard 170 187 -17
Boston Bruins 1976 Terry O'Reilly 200 187 13
Boston Bruins 1976 Doug Halward 200 187 13
Boston Bruins 1976 Hank Nowak 195 187 8
Boston Bruins 1976 Jim Pettie 195 187 8
Boston Bruins 1976 Ray Maluta 173 187 -14
Boston Bruins 1976 Jean Ratelle 180 187 -7
Boston Bruins 1976 Wayne Cashman 208 187 21
Boston Bruins 1976 John Wensink 200 187 13
Boston Bruins 1976 Stan Jonathan 175 187 -12
Boston Bruins 1976 Bobby Schmautz 172 187 -15
Boston Bruins 1976 Mike Milbury 200 187 13
Boston Bruins 1976 Joe Zanussi 180 187 -7
Boston Bruins 1976 Peter McNab 210 187 23
Boston Bruins 1976 Gary Doak 175 187 -12
...We can also add an ORDER BY clause to sort the output by players that have the greatest weight difference.
SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
, p.weight as PLAYER_WEIGHT
, AVG(p.weight) OVER (PARTITION BY t.name ) AS AVG_TEAM_WEIGHT
, p.weight - AVG(p.weight) OVER (PARTITION BY t.name ) AS WEIGHT_DIFF
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976
ORDER BY ABS(WEIGHT_DIFF) DESC;
TEAM_NAME YEAR PLAYER_NAME PLAYER_WEIGHT AVG_TEAM_WEIGHT WEIGHT_DIFF
------------------------- ----- -------------------- -------------- ---------------- ------------
Calgary Cowboys 1976 Bruce Greig 220 183 37
Calgary Cowboys 1976 Veli-Pekka Ketola 220 183 37
Birmingham Bulls 1976 Gilles Bilodeau 220 184 36
Winnipeg Jets 1976 Veli-Pekka Ketola 220 184 36
Washington Capitals 1976 Rick Green 220 186 34
Houston Aeros 1976 Steve West 150 183 -33
Vancouver Canucks 1976 Bobby Lalonde 155 188 -33
Montreal Canadiens 1976 Larry Robinson 225 192 33
Buffalo Sabres 1976 Jerry Korab 220 188 32
Phoenix Roadrunners 1976 Serge Beaudoin 215 183 32
Vancouver Canucks 1976 Bob Dailey 220 188 32
New England Whalers 1976 Doug Roberts 212 182 30
Detroit Red Wings 1976 Bobby Sheehan 155 185 -30
Philadelphia Flyers 1976 Bob Dailey 220 191 29
New York Rangers 1976 Dave Tataryn 160 189 -29
New York Islanders 1976 Jude Drouin 160 189 -29
New York Rangers 1976 Gilles Gratton 160 189 -29
Cleveland Barons 1976 Frank Spring 216 187 29
Washington Capitals 1976 Harvey Bennett 215 186 29
Birmingham Bulls 1976 Wayne Carleton 212 184 28
San Diego Mariners 1976 Brent Hughes 205 177 28
Edmonton Oilers 1976 Barry Long 210 182 28
Vancouver Canucks 1976 Bruce Bullock 160 188 -28
Phoenix Roadrunners 1976 Robbie Ftorek 155 183 -28
Buffalo Sabres 1976 Don Edwards 160 188 -28
Boston Bruins 1976 John Bucyk 215 187 28
Houston Aeros 1976 Gary Donaldson 155 183 -28
Calgary Cowboys 1976 Rich Lemieux 155 183 -28
Minnesota North Stars 1976 Gary Smith 215 188 27
New England Whalers 1976 Mike Antonovich 155 182 -27
Buffalo Sabres 1976 Bill Hajt 215 188 27
Cleveland Barons 1976 Lyle Bradley 160 187 -27
Edmonton Oilers 1976 Mike Antonovich 155 182 -27
Atlanta Flames 1976 Eric Vail 220 194 26
Winnipeg Jets 1976 Barry Long 210 184 26
Colorado Rockies 1976 Roger Lemelin 215 189 26
Pittsburgh Penguins 1976 Russ Anderson 210 184 26
Minnesota Fighting Saints 1976 Jack Carlson 205 179 26
Washington Capitals 1976 Rick Bragnalo 160 186 -26
New York Islanders 1976 Clark Gillies 215 189 26
Pittsburgh Penguins 1976 Ron Stackhouse 210 184 26
Birmingham Bulls 1976 Keith Kokkola 210 184 26
San Diego Mariners 1976 Kevin Morrison 202 177 25
Cleveland Barons 1976 Angelo Moretto 212 187 25
New York Islanders 1976 Glenn Resch 165 189 -24
Buffalo Sabres 1976 Joe Kowal 212 188 24
Birmingham Bulls 1976 Peter Marrin 160 184 -24
Birmingham Bulls 1976 Terry Ball 160 184 -24
...MAX()
MAX( expression ) OVER ( [ partition_clause ] order_by_clause )The MAX() window function identifies the maximum value for expression argument. This expression can be defined as a table column, an arithmetic expression of columns and/or variables, etc.
The order_by_clause is optional. If it is provided, the function uses the order_by_clause to sort the rows before computing the maximum value of the expression for any given row in the window. This maximum expression value is assigned to each row of the partition group cumulatively. Therefore, the MAX() window function uses only the first row of the partition group to calculate the maximum expression value for that row. It uses the first two rows of the partition group to compute the maximum expression value returned for the second row. It uses the first three rows of the partition group to compute the maximum expression value returned for the third row, and so on.
If the order_by_clause is not provided, this maximum expression value is computed across all rows in the window and the result is assigned to each row in the window. In other words, each row in the window has the same maximum value.
If a partition_clause is provided, this maximum expression value computation is reset for the first row returned in each partition group, or window, based on the partition_clause.
The following example returns, for all teams in the NHL, each player’s weight and height. It uses the MAX() window function to return the maximum weight and height associated with each row in the window. The maximum expression value is computed for each row in the partition by using the current row plus all previous rows in the partition and sorted based on the order_by_clause.
Notice that the MAX() window function returns the same value for the 2nd and 3rd row when the order_by_clause column value within the partition window contains the same value, "Bill". The MAX() window function treats these rows the same for computing the row value. Adding the LastName column makes the order_by_clause value unique. The MAX() window function will compute each row value cumulatively.
SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
, p.weight as PLAYER_WEIGHT, p.height as PLAYER_HEIGHT
, MAX(p.weight) OVER (PARTITION BY t.name order by p.firstname) AS MAX_WEIGHT
, MAX(p.weight) OVER (PARTITION BY t.name ORDER BY p.firstname,p.lastname) AS MAX_WEIGHT_2
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976;
TEAM_NAME YEAR PLAYER_NAME PLAYER_WEIGHT PLAYER_HEIGHT MAX_WEIGHT MAX_WEIGHT_2
------------------------- ----- -------------------- -------------- -------------- ----------- -------------
Atlanta Flames 1976 Barry Gibbs 195 71 195 195
Atlanta Flames 1976 Bill Clement 194 73 205 195
Atlanta Flames 1976 Bill Flett 205 73 205 205
Atlanta Flames 1976 Bobby Simpson 190 72 205 205
Atlanta Flames 1976 Curt Bennett 195 75 205 205
Atlanta Flames 1976 Dan Bouchard 190 72 205 205
Atlanta Flames 1976 Dave Shand 200 74 205 205
Atlanta Flames 1976 Ed Kea 200 75 205 205
Atlanta Flames 1976 Eric Vail 220 73 220 220
Atlanta Flames 1976 Guy Chouinard 182 71 220 220
Atlanta Flames 1976 Hilliard Graves 175 71 220 220
Atlanta Flames 1976 John Gould 197 71 220 220
Atlanta Flames 1976 Ken Houston 210 74 220 220
Atlanta Flames 1976 Larry Carriere 190 73 220 220
Atlanta Flames 1976 Larry Romanchych 180 73 220 220
Atlanta Flames 1976 Pat Quinn 205 75 220 220
Atlanta Flames 1976 Pat Ribble 210 76 220 220
Atlanta Flames 1976 Phil Myre 185 73 220 220
Atlanta Flames 1976 Randy Manery 185 72 220 220
Atlanta Flames 1976 Rey Comeau 190 68 220 220
Atlanta Flames 1976 Richard Mulhern 188 73 220 220
Atlanta Flames 1976 Rick Bowness 185 73 220 220
Atlanta Flames 1976 Tim Ecclestone 195 70 220 220
Atlanta Flames 1976 Tom Lysiak 185 73 220 220
Atlanta Flames 1976 Willi Plett 205 75 220 220
Birmingham Bulls 1976 Buzz Schneider 175 71 175 175
Birmingham Bulls 1976 Dale Hoganson 190 70 190 190
Birmingham Bulls 1976 Dave Gorman 185 71 190 190
Birmingham Bulls 1976 Dave Syvret 190 72 190 190
Birmingham Bulls 1976 Frank Mahovlich 205 72 205 205
Birmingham Bulls 1976 Gavin Kirk 165 70 205 205
Birmingham Bulls 1976 Gilles Bilodeau 220 73 220 220
Birmingham Bulls 1976 Gord Gallant 175 71 220 220
Birmingham Bulls 1976 Jean-Guy Lagace 185 70 220 220
Birmingham Bulls 1976 Jean-Luc Phaneuf 165 68 220 220
Birmingham Bulls 1976 Jeff Jacques 180 71 220 220
Birmingham Bulls 1976 Jerry Rollins 195 74 220 220
Birmingham Bulls 1976 Jim Marsh 180 72 220 220
Birmingham Bulls 1976 Jim Turkiewicz 185 70 220 220
Birmingham Bulls 1976 John Garrett 175 68 220 220
Birmingham Bulls 1976 John Stewart 180 72 220 220
Birmingham Bulls 1976 John Stewart 180 72 220 220
Birmingham Bulls 1976 Keith Kokkola 210 75 220 220
Birmingham Bulls 1976 Lou Nistico 170 67 220 220
Birmingham Bulls 1976 Mark Napier 182 70 220 220
Birmingham Bulls 1976 Pat Westrum 185 70 220 220
Birmingham Bulls 1976 Paul Heaver 195 73 220 220
Birmingham Bulls 1976 Paul Henderson 180 71 220 220
Birmingham Bulls 1976 Peter Folco 185 72 220 220
Birmingham Bulls 1976 Peter Marrin 160 70 220 220
Birmingham Bulls 1976 Ray McKay 183 76 220 220
Birmingham Bulls 1976 Rich Hart 195 72 220 220
Birmingham Bulls 1976 Richard Farda 175 69 220 220
Birmingham Bulls 1976 Rick Cunningham 180 71 220 220
Birmingham Bulls 1976 Terry Ball 160 69 220 220
Birmingham Bulls 1976 Tim Sheehy 185 73 220 220
Birmingham Bulls 1976 Tom Simpson 190 69 220 220
Birmingham Bulls 1976 Vaclav Nedomansky 205 74 220 220
Birmingham Bulls 1976 Wayne Carleton 212 75 220 220
Birmingham Bulls 1976 Wayne Wood 190 73 220 220
Boston Bruins 1976 Al Sims 182 72 182 182
Boston Bruins 1976 Bobby Schmautz 172 69 182 182
Boston Bruins 1976 Brad Park 200 72 200 200
Boston Bruins 1976 Clayton Pachal 185 70 200 200
Boston Bruins 1976 Dallas Smith 180 71 200 200
Boston Bruins 1976 Darryl Edestrand 180 71 200 200
Boston Bruins 1976 Dave Forbes 180 70 200 200
Boston Bruins 1976 Don Marcotte 183 71 200 200
Boston Bruins 1976 Doug Halward 200 73 200 200
Boston Bruins 1976 Earl Anderson 185 73 200 200
Boston Bruins 1976 Gary Doak 175 71 200 200
Boston Bruins 1976 Gerry Cheevers 185 71 200 200
Boston Bruins 1976 Gilles Gilbert 175 73 200 200
Boston Bruins 1976 Gregg Sheppard 170 68 200 200
Boston Bruins 1976 Hank Nowak 195 73 200 200
Boston Bruins 1976 Jean Ratelle 180 73 200 200
Boston Bruins 1976 Jim Pettie 195 72 200 200
Boston Bruins 1976 Joe Zanussi 180 70 200 200
Boston Bruins 1976 John Bucyk 215 72 215 215
Boston Bruins 1976 John Wensink 200 72 215 215
Boston Bruins 1976 Matti Hagman 183 72 215 215
Boston Bruins 1976 Mike Milbury 200 73 215 215
Boston Bruins 1976 Peter McNab 210 75 215 215
Boston Bruins 1976 Ray Maluta 173 68 215 215
Boston Bruins 1976 Rick Middleton 170 71 215 215
Boston Bruins 1976 Rick Smith 190 71 215 215
Boston Bruins 1976 Stan Jonathan 175 68 215 215
Boston Bruins 1976 Terry O'Reilly 200 73 215 215
Boston Bruins 1976 Wayne Cashman 208 73 215 215
...MIN()
MIN( expression ) OVER ( [ partition_clause ] order_by_clause )The MIN() window function identifies the minimum value for the expression argument. This expression can be defined as a table column, an arithmetic expression of columns and/or variables, etc. This function behaves similar to the MAX window function.
The order_by_clause is optional. If it is provided, the function uses the order_by_clause to sort the rows before computing the minimum value of the expression for any given row in the window. This minimum expression value is assigned to each row of the partition group cumulatively. Therefore, the MIN() window function uses only the first row of the partition group to calculate the minimum expression value for that row. It uses the first two rows of the partition group to compute the minimum expression value returned for the second row. It uses the first three rows of the partition group to compute the minimum expression value returned for the third row, and so on.
If the order_by_clause is not provided, this minimum expression value is computed across all rows in the window and the result is assigned to each row in the window. In other words, each row in the window has the same minimum value.
If a partition_clause is provided, this minimum expression value computation is reset for the first row returned in each partition group, or window, based on the partition_clause.
The following example returns, for all teams in the NHL, each player’s weight and height. It uses the MIN() window function to return the minimum weight and height associated with each row in the window. The minimum expression value is computed for each row in the partition by using the current row plus all previous rows in the partition and sorted based on the order_by_clause.
SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
, p.weight as PLAYER_WEIGHT, p.height as PLAYER_HEIGHT
, MIN(p.weight) OVER (PARTITION BY t.name order by p.firstname,p.lastname) AS MIN_WEIGHT
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976;
TEAM_NAME YEAR PLAYER_NAME PLAYER_WEIGHT PLAYER_HEIGHT MIN_WEIGHT
------------------------- ----- -------------------- -------------- -------------- -----------
Atlanta Flames 1976 Barry Gibbs 195 71 195
Atlanta Flames 1976 Bill Clement 194 73 194
Atlanta Flames 1976 Bill Flett 205 73 194
Atlanta Flames 1976 Bobby Simpson 190 72 190
Atlanta Flames 1976 Curt Bennett 195 75 190
Atlanta Flames 1976 Dan Bouchard 190 72 190
Atlanta Flames 1976 Dave Shand 200 74 190
Atlanta Flames 1976 Ed Kea 200 75 190
Atlanta Flames 1976 Eric Vail 220 73 190
Atlanta Flames 1976 Guy Chouinard 182 71 182
Atlanta Flames 1976 Hilliard Graves 175 71 175
Atlanta Flames 1976 John Gould 197 71 175
Atlanta Flames 1976 Ken Houston 210 74 175
Atlanta Flames 1976 Larry Carriere 190 73 175
Atlanta Flames 1976 Larry Romanchych 180 73 175
Atlanta Flames 1976 Pat Quinn 205 75 175
Atlanta Flames 1976 Pat Ribble 210 76 175
Atlanta Flames 1976 Phil Myre 185 73 175
Atlanta Flames 1976 Randy Manery 185 72 175
Atlanta Flames 1976 Rey Comeau 190 68 175
Atlanta Flames 1976 Richard Mulhern 188 73 175
Atlanta Flames 1976 Rick Bowness 185 73 175
Atlanta Flames 1976 Tim Ecclestone 195 70 175
Atlanta Flames 1976 Tom Lysiak 185 73 175
Atlanta Flames 1976 Willi Plett 205 75 175
Birmingham Bulls 1976 Buzz Schneider 175 71 175
Birmingham Bulls 1976 Dale Hoganson 190 70 175
Birmingham Bulls 1976 Dave Gorman 185 71 175
Birmingham Bulls 1976 Dave Syvret 190 72 175
Birmingham Bulls 1976 Frank Mahovlich 205 72 175
Birmingham Bulls 1976 Gavin Kirk 165 70 165
Birmingham Bulls 1976 Gilles Bilodeau 220 73 165
Birmingham Bulls 1976 Gord Gallant 175 71 165
Birmingham Bulls 1976 Jean-Guy Lagace 185 70 165
Birmingham Bulls 1976 Jean-Luc Phaneuf 165 68 165
Birmingham Bulls 1976 Jeff Jacques 180 71 165
Birmingham Bulls 1976 Jerry Rollins 195 74 165
Birmingham Bulls 1976 Jim Marsh 180 72 165
Birmingham Bulls 1976 Jim Turkiewicz 185 70 165
Birmingham Bulls 1976 John Garrett 175 68 165
Birmingham Bulls 1976 John Stewart 180 72 165
Birmingham Bulls 1976 John Stewart 180 72 165
Birmingham Bulls 1976 Keith Kokkola 210 75 165
Birmingham Bulls 1976 Lou Nistico 170 67 165
Birmingham Bulls 1976 Mark Napier 182 70 165
Birmingham Bulls 1976 Pat Westrum 185 70 165
Birmingham Bulls 1976 Paul Heaver 195 73 165
Birmingham Bulls 1976 Paul Henderson 180 71 165
Birmingham Bulls 1976 Peter Folco 185 72 165
Birmingham Bulls 1976 Peter Marrin 160 70 160
Birmingham Bulls 1976 Ray McKay 183 76 160
Birmingham Bulls 1976 Rich Hart 195 72 160
Birmingham Bulls 1976 Richard Farda 175 69 160
Birmingham Bulls 1976 Rick Cunningham 180 71 160
Birmingham Bulls 1976 Terry Ball 160 69 160
Birmingham Bulls 1976 Tim Sheehy 185 73 160
Birmingham Bulls 1976 Tom Simpson 190 69 160
Birmingham Bulls 1976 Vaclav Nedomansky 205 74 160
Birmingham Bulls 1976 Wayne Carleton 212 75 160
Birmingham Bulls 1976 Wayne Wood 190 73 160
Boston Bruins 1976 Al Sims 182 72 182
Boston Bruins 1976 Bobby Schmautz 172 69 172
Boston Bruins 1976 Brad Park 200 72 172
Boston Bruins 1976 Clayton Pachal 185 70 172
Boston Bruins 1976 Dallas Smith 180 71 172
Boston Bruins 1976 Darryl Edestrand 180 71 172
Boston Bruins 1976 Dave Forbes 180 70 172
Boston Bruins 1976 Don Marcotte 183 71 172
Boston Bruins 1976 Doug Halward 200 73 172
Boston Bruins 1976 Earl Anderson 185 73 172
Boston Bruins 1976 Gary Doak 175 71 172
Boston Bruins 1976 Gerry Cheevers 185 71 172
Boston Bruins 1976 Gilles Gilbert 175 73 172
Boston Bruins 1976 Gregg Sheppard 170 68 170
Boston Bruins 1976 Hank Nowak 195 73 170
Boston Bruins 1976 Jean Ratelle 180 73 170
Boston Bruins 1976 Jim Pettie 195 72 170
Boston Bruins 1976 Joe Zanussi 180 70 170
Boston Bruins 1976 John Bucyk 215 72 170
Boston Bruins 1976 John Wensink 200 72 170
Boston Bruins 1976 Matti Hagman 183 72 170
Boston Bruins 1976 Mike Milbury 200 73 170
Boston Bruins 1976 Peter McNab 210 75 170
Boston Bruins 1976 Ray Maluta 173 68 170
Boston Bruins 1976 Rick Middleton 170 71 170
Boston Bruins 1976 Rick Smith 190 71 170
Boston Bruins 1976 Stan Jonathan 175 68 170
Boston Bruins 1976 Terry O'Reilly 200 73 170
Boston Bruins 1976 Wayne Cashman 208 73 170
...