概要
ここに2013-14のレギュラーシーズンの全選手・全試合のスタッツ情報があります。
例として一部を表示します。このようなスタッツデータが26401件あります。
| PLAYER_NAME | GAME_DATE | GAME_ID | MATCHUP | PTS | AST | REB | BLK | STL |
| Nassir Little | 2023-10-24 | 0022300062 | PHX @ GSW | 3 | 0 | 1 | 0 | 1 |
| Rui Hachimura | 2023-10-24 | 0022300061 | LAL @ DEN | 6 | 0 | 3 | 0 | 0 |
| Yuta Watanabe | 2023-10-24 | 0022300062 | PHX @ GSW | 8 | 0 | 4 | 1 | 0 |
| Eric Gordon | 2023-10-24 | 0022300062 | PHX @ GSW | 10 | 1 | 4 | 1 | 1 |
| Reggie Jackson | 2023-10-24 | 0022300061 | DEN vs. LAL | 8 | 1 | 3 | 0 | 1 |
| Max Christie | 2023-10-24 | 0022300061 | LAL @ DEN | 0 | 0 | 0 | 0 | 0 |
| Jaxson Hayes | 2023-10-24 | 0022300061 | LAL @ DEN | 0 | 0 | 1 | 0 | 0 |
| Michael Porter Jr. | 2023-10-24 | 0022300061 | DEN vs. LAL | 12 | 2 | 12 | 0 | 2 |
| Jordan Goodwin | 2023-10-24 | 0022300062 | PHX @ GSW | 2 | 3 | 4 | 0 | 1 |
| Dario Šarić | 2023-10-24 | 0022300062 | GSW vs. PHX | 7 | 1 | 7 | 2 | 0 |
| LeBron James | 2023-10-24 | 0022300061 | LAL @ DEN | 21 | 5 | 8 | 0 | 1 |
| Collin Gillespie | 2023-10-24 | 0022300061 | DEN vs. LAL | 0 | 0 | 0 | 0 | 0 |
| Klay Thompson | 2023-10-24 | 0022300062 | GSW vs. PHX | 15 | 3 | 7 | 1 | 0 |
| Cory Joseph | 2023-10-24 | 0022300062 | GSW vs. PHX | 0 | 1 | 1 | 0 | 0 |
| Jalen Pickett | 2023-10-24 | 0022300061 | DEN vs. LAL | 2 | 0 | 0 | 0 | 0 |
| Braxton Key | 2023-10-24 | 0022300061 | DEN vs. LAL | 0 | 0 | 0 | 0 | 0 |
| Gabe Vincent | 2023-10-24 | 0022300061 | LAL @ DEN | 6 | 2 | 1 | 0 | 1 |
| Maxwell Lewis | 2023-10-24 | 0022300061 | LAL @ DEN | 0 | 0 | 0 | 0 | 0 |
| Aaron Gordon | 2023-10-24 | 0022300061 | DEN vs. LAL | 15 | 5 | 7 | 1 | 2 |
| Peyton Watson | 2023-10-24 | 0022300061 | DEN vs. LAL | 3 | 0 | 0 | 1 | 0 |
ここで、「2013-14のレギュラーシーズンで総得点が1位〜3位の選手を求めよ」という問題を考えます。
解法1
最もシンプルな方法として、「選手ごとの総得点を求める→総得点をソート」
-- 選手ごとに総得点を求める WITH total_by_player AS ( SELECT PLAYER_NAME, SUM(PTS) AS total, FROM `nba.leaguegamelog_player` WHERE _SEASON_TYPE = "Regular Season" AND SEASON_ID = "22023" GROUP BY PLAYER_NAME ) -- 総得点が高い順にソート SELECT PLAYER_NAME, total FROM total_by_player ORDER BY total DESC LIMIT 3
結果はこのようになります。
| PLAYER_NAME | total |
| Luka Dončić | 2370 |
| Shai Gilgeous-Alexander | 2254 |
| Giannis Antetokounmpo | 2222 |
このようにクエリを書くと簡単に求めることができます。
この解法は考え方がシンプルで理解しやすいのが利点です。
一方で、合計PTSと同時に合計ASTや合計REBのランキングを同時に求められないという欠点があります。
解法2
この解法はwindow関数を使う方法になります。
「選手ごとの総得点のランキングを算出→総得点がN位となる選手を抽出（N=1,2,3）」という解法です。
WITH total_rank AS ( SELECT PLAYER_NAME, SUM(PTS) AS total_pts, -- SUM(PTS)でソートして1-basedのシーケンシャル番号を付与 ROW_NUMBER() OVER (ORDER BY SUM(PTS) DESC) AS rank_pts, FROM `nba.leaguegamelog_player` WHERE _SEASON_TYPE = "Regular Season" AND SEASON_ID = "22023" GROUP BY PLAYER_NAME ) SELECT MAX(CASE WHEN rank_pts = 1 THEN PLAYER_NAME END) AS player_name1, MAX(CASE WHEN rank_pts = 1 THEN total_pts END) AS total_pts1, MAX(CASE WHEN rank_pts = 2 THEN PLAYER_NAME END) AS player_name2, MAX(CASE WHEN rank_pts = 2 THEN total_pts END) AS total_pts2, MAX(CASE WHEN rank_pts = 3 THEN PLAYER_NAME END) AS player_name3, MAX(CASE WHEN rank_pts = 3 THEN total_pts END) AS total_pts3, FROM total_rank
結果はこのようになります。
| player_name1 | total_pts1 | player_name2 | total_pts2 | player_name3 | total_pts3 |
| Luka Dončić | 2370 | Shai Gilgeous-Alexander | 2254 | Giannis Antetokounmpo | 2222 |
このクエリの特徴としては、合計ASTなど他のスタッツに関しても同時に算出できます。
WITH total_rank AS ( SELECT PLAYER_NAME, SUM(PTS) AS total_pts, SUM(AST) AS total_ast, ROW_NUMBER() OVER (ORDER BY SUM(PTS) DESC) AS rank_pts, ROW_NUMBER() OVER (ORDER BY SUM(AST) DESC) AS rank_ast, FROM `nba.leaguegamelog_player` WHERE _SEASON_TYPE = "Regular Season" AND SEASON_ID = "22023" GROUP BY PLAYER_NAME ) SELECT -- pts MAX(CASE WHEN rank_pts = 1 THEN PLAYER_NAME END) AS player_name1, MAX(CASE WHEN rank_pts = 1 THEN total_pts END) AS total_pts1, MAX(CASE WHEN rank_pts = 2 THEN PLAYER_NAME END) AS player_name2, MAX(CASE WHEN rank_pts = 2 THEN total_pts END) AS total_pts2, MAX(CASE WHEN rank_pts = 3 THEN PLAYER_NAME END) AS player_name3, MAX(CASE WHEN rank_pts = 3 THEN total_pts END) AS total_pts3, -- ast MAX(CASE WHEN rank_ast = 1 THEN PLAYER_NAME END) AS player_name1, MAX(CASE WHEN rank_ast = 1 THEN total_ast END) AS total_ast1, MAX(CASE WHEN rank_ast = 2 THEN PLAYER_NAME END) AS player_name2, MAX(CASE WHEN rank_ast = 2 THEN total_ast END) AS total_ast2, MAX(CASE WHEN rank_ast = 3 THEN PLAYER_NAME END) AS player_name3, MAX(CASE WHEN rank_ast = 3 THEN total_ast END) AS total_ast3, FROM total_rank
ASTのランキングもPTSのランキングと同時に導出することができました。
| player_name1 | total_pts1 | player_name2 | total_pts2 | player_name3 | total_pts3 | player_name1_1 | total_ast1 | player_name2_1 | total_ast2 | player_name3_1 | total_ast3 |
| Luka Dončić | 2370 | Shai Gilgeous-Alexander | 2254 | Giannis Antetokounmpo | 2222 | Tyrese Haliburton | 752 | Nikola Jokić | 708 | Luka Dončić | 686 |
別々のスタッツに関するランキングが同時に計算できるというメリットがある一方で、ご覧の通りレコード単位ではなくカラム単位で計算を行うため結果が見にくいという欠点があります。
その結果、求めたいランクの分だけSQLにカラムの計算を書かなければなりません。
まとめ
以上のように、グループごとの集約関数を施した値のランキングを求めたい場合
まず、下記のクエリを施し、idごとの「総計」及び「総計の順位」を求めます。
この結果は、idの数だけレコードが存在し、「id」「総計」「総計の順位」というカラムで成り立っています。
SELECT id, SUM(col) AS total_col, ROW_NUMBER() OVER (ORDER BY SUM(col) DESC) AS rank, FROM tbl GROUP BY id
次に上記の結果テーブルに対して、MAX(CASE WHEN rank = N THEN id END)やMAX(CASE WHEN rank = N THEN total_col END)のように、そのランクにあるレコード以外をNULLにして、MAX()に実質1つしか渡さないようにしてランクにあるカラム値を取得します。
