ランキング
PostgreSQL 集計統計とウィンドウ関数
Michel Semaan
Data Scientist
ランキング関数
ROW_NUMBER()は、値が同じ行があっても常に一意の番号を割り当てますRANK()は同じ値の行に同じ番号を割り当て、次の番号をスキップしますDENSE_RANK()も同じ値の行に同じ番号を割り当てますが、次の番号をスキップしません
ソーステーブル
クエリ
SELECT
Country, COUNT(DISTINCT Year) AS Games
FROM Summer_Medals
WHERE
Country IN ('GBR', 'DEN', 'FRA',
'ITA', 'AUT', 'BEL',
'NOR', 'POL', 'ESP')
GROUP BY Country
ORDER BY Games DESC;
結果
| Country | Games |
|---------|-------|
| GBR | 27 |
| DEN | 26 |
| FRA | 26 |
| ITA | 25 |
| AUT | 24 |
| BEL | 24 |
| NOR | 22 |
| POL | 20 |
| ESP | 18 |
ランキング関数の違い - ROW_NUMBER
クエリ
WITH Country_Games AS (...)
SELECT
Country, Games,
ROW_NUMBER()
OVER (ORDER BY Games DESC) AS Row_N
FROM Country_Games
ORDER BY Games DESC, Country ASC;
結果
| Country | Games | Row_N |
|---------|-------|-------|
| GBR | 27 | 1 |
| DEN | 26 | 2 |
| FRA | 26 | 3 |
| ITA | 25 | 4 |
| AUT | 24 | 5 |
| BEL | 24 | 6 |
| NOR | 22 | 7 |
| POL | 20 | 8 |
| ESP | 18 | 9 |
ランキング関数の違い - RANK
クエリ
WITH Country_Games AS (...)
SELECT
Country, Games,
ROW_NUMBER()
OVER (ORDER BY Games DESC) AS Row_N,
RANK()
OVER (ORDER BY Games DESC) AS Rank_N
FROM Country_Games
ORDER BY Games DESC, Country ASC;
結果
| Country | Games | Row_N | Rank_N |
|---------|-------|-------|--------|
| GBR | 27 | 1 | 1 |
| DEN | 26 | 2 | 2 |
| FRA | 26 | 3 | 2 |
| ITA | 25 | 4 | 4 |
| AUT | 24 | 5 | 5 |
| BEL | 24 | 6 | 5 |
| NOR | 22 | 7 | 7 |
| POL | 20 | 8 | 8 |
| ESP | 18 | 9 | 9 |
ランキング関数の違い - DENSE_RANK
クエリ
WITH Country_Games AS (...)
SELECT
Country, Games,
ROW_NUMBER()
OVER (ORDER BY Games DESC) AS Row_N,
RANK()
OVER (ORDER BY Games DESC) AS Rank_N,
DENSE_RANK()
OVER (ORDER BY Games DESC) AS Dense_Rank_N
FROM Country_Games
ORDER BY Games DESC, Country ASC;
ROW_NUMBERとRANKの最後の順位は、行数と同じになります
結果
| Country | Games | Row_N | Rank_N | Dense_Rank_N |
|---------|-------|-------|--------|--------------|
| GBR | 27 | 1 | 1 | 1 |
| DEN | 26 | 2 | 2 | 2 |
| FRA | 26 | 3 | 2 | 2 |
| ITA | 25 | 4 | 4 | 3 |
| AUT | 24 | 5 | 5 | 4 |
| BEL | 24 | 6 | 5 | 5 |
| NOR | 22 | 7 | 7 | 5 |
| POL | 20 | 8 | 8 | 6 |
| ESP | 18 | 9 | 9 | 7 |
DENSE_RANKの最後の順位は、ランク付け対象のユニーク値の数になります
パーティションなしのランキング - ソーステーブル
クエリ
SELECT
Country, Athlete, COUNT(*) AS Medals
FROM Summer_Medals
WHERE
Country IN ('CHN', 'RUS')
AND Year = 2012
GROUP BY Country, Athlete
HAVING COUNT(*) > 1
ORDER BY Country ASC, Medals DESC;
結果
| Country | Athlete | Medals |
|---------|-------------------|--------|
| CHN | SUN Yang | 4 |
| CHN | Guo Shuang | 3 |
| CHN | WANG Hao | 3 |
| ... | ... | ... |
| RUS | MUSTAFINA Aliya | 4 |
| RUS | ANTYUKH Natalya | 2 |
| RUS | ISHCHENKO Natalia | 2 |
| ... | ... | ... |
パーティションなしのランキング
クエリ
WITH Country_Medals AS (...)
SELECT
Country, Athlete, Medals,
DENSE_RANK()
OVER (ORDER BY Medals DESC) AS Rank_N
FROM Country_Medals
ORDER BY Country ASC, Medals DESC;
結果
| Country | Athlete | Medals | Rank_N |
|---------|-------------------|--------|--------|
| CHN | SUN Yang | 4 | 1 |
| CHN | Guo Shuang | 3 | 2 |
| CHN | WANG Hao | 3 | 2 |
| ... | ... | ... | ... |
| RUS | MUSTAFINA Aliya | 4 | 1 |
| RUS | ANTYUKH Natalya | 2 | 3 |
| RUS | ISHCHENKO Natalia | 2 | 3 |
| ... | ... | ... | ... |
パーティションありのランキング
クエリ
WITH Country_Medals AS (...)
SELECT
Country, Athlete,
DENSE_RANK()
OVER (PARTITION BY Country
ORDER BY Medals DESC) AS Rank_N
FROM Country_Medals
ORDER BY Country ASC, Medals DESC;
結果
| Country | Athlete | Medals | Rank_N |
|---------|-------------------|--------|--------|
| CHN | SUN Yang | 4 | 1 |
| CHN | Guo Shuang | 3 | 2 |
| CHN | WANG Hao | 3 | 2 |
| ... | ... | ... | ... |
| RUS | MUSTAFINA Aliya | 4 | 1 |
| RUS | ANTYUKH Natalya | 2 | 2 |
| RUS | ISHCHENKO Natalia | 2 | 2 |
| ... | ... | ... | ... |
練習しましょう！
PostgreSQL 集計統計とウィンドウ関数
