ウィンドウ関数 RANK() で順位付け
Oracle データベースはウィンドウ関数をサポートしていて非常に強力な SQL を 簡単に実行することが出来ます。そのウィンドウ関数を使ってランク付けする 方法を試してみます。ウィンドウ関数を使わなくてもサブクエリー等を駆使すればデータの順位付けは出来るのかもしれませんが、恐らくパフォーマンスは非常に悪いでしょう。ウィンドウ関数を使用することによって良いパフォーマンスで簡単にデータの順位付けが出来ます。
ウィンドウ関数を使うには、OVER 句を使うことによってウィンドウ関数として扱われます。ウィンドウ関数は問い合わせ結果に含まれる行だけでなく、それ以上の行にアクセスすることができます。
テストデータの準備
まず人口が多い上位３０カ国の順位データを以下のように用意しました。 なお "rank" 列の順位は世界で人口の多いランキング順位を示しています。SQL> SELECT * FROM population ;
RANK COUNTRY REGION POPULATION
----- ---------------- ---------- ----------
1 China Asia 1409517397
2 India Asia 1339180127
3 United States Americas 324459463
4 Indonesia Asia 263991379
5 Brazil Americas 209288278
6 Pakistan Asia 197015955
7 Nigeria Africa 190886311
8 Bangladesh Asia 164669751
9 Russia Europe 143989754
10 Mexico Americas 129163276
11 Japan Asia 127484450
12 Ethiopia Africa 104957438
13 Philippines Asia 104918090
14 Egypt Africa 97553151
15 Vietnam Asia 95540800
16 Germany Europe 82114224
17 Congo Africa 81339988
18 Iran Asia 81162788
19 Turkey Asia 80745020
20 Thailand Asia 69037513
21 United Kingdom Europe 66181585
22 France Europe 64979548
23 Italy Europe 59359900
24 Tanzania Africa 57310019
25 South Africa Africa 56717156
26 Myanmar Asia 53370609
27 South Korea Asia 50982212
28 Kenya Africa 49699862
29 Colombia Americas 49065615
30 Spain Europe 46354321
30 rows selected.
このデータを基にアジアの国々での人口が多い順にランク付けして順位を求めてみます。 まずアジアの国々だけを選択してみると次のようになっています。
SQL> SELECT * FROM population WHERE region = 'Asia';
RANK COUNTRY REGION POPULATION
----- ------------- -------- ----------
1 China Asia 1409517397
2 India Asia 1339180127
4 Indonesia Asia 263991379
6 Pakistan Asia 197015955
8 Bangladesh Asia 164669751
11 Japan Asia 127484450
13 Philippines Asia 104918090
15 Vietnam Asia 95540800
18 Iran Asia 81162788
19 Turkey Asia 80745020
20 Thailand Asia 69037513
26 Myanmar Asia 53370609
27 South Korea Asia 50982212
13 rows selected.
ご覧の通り世界の人口順位である "rank" 列の値は飛び飛びになっています。 これにアシアの順位を付けてみたいと思います。
ウィンドウ関数 RANK() で順位付けする
ではアジアでの順位をつけるためにウィンドウ関数である RANK() を使ってみましょう。 SQL は次のようになります。"asia_rank" という列でアシアでの順位を示すことにします。SQL>
SELECT
RANK,
COUNTRY,
REGION,
POPULATION,
RANK() OVER(ORDER BY population DESC) AS asia_rank
FROM population
WHERE region = 'Asia';
RANK COUNTRY REGION POPULATION ASIA_RANK
----- ------------- -------- ---------- ----------
1 China Asia 1409517397 1
2 India Asia 1339180127 2
4 Indonesia Asia 263991379 3
6 Pakistan Asia 197015955 4
8 Bangladesh Asia 164669751 5
11 Japan Asia 127484450 6
13 Philippines Asia 104918090 7
15 Vietnam Asia 95540800 8
18 Iran Asia 81162788 9
19 Turkey Asia 80745020 10
20 Thailand Asia 69037513 11
26 Myanmar Asia 53370609 12
27 South Korea Asia 50982212 13
13 rows selected.
これはつまり WHERE 句で region = 'Asia' としてアジアの国だけを絞り込んだ後、 OVER(ORDER BY population DESC) で population の降順でデータを並べ替え、 その並び替えられたデータに対して RANK() 関数で順位をつける、という流れです。
このウィンドウ関数を使えば複雑な SQL を書かなくても簡単にランキングを求められます。
