ウィンドウ関数 ROW_NUMBER() 関数で連番を振る
Oracle はウィンドウ関数をサポートしていて非常に強力な SQL を 簡単に実行することが出来ます。そのウィンドウ関数を使って、任意の並び順のレコードに行番号を振ってみます。複雑なクエリーを組めば出来ないこともないですが、慣れればウィンドウ関数のほうが直感的にわかりやすく パフォーマンスも良いです。
ウィンドウ関数を使うには、OVER 句を使うことによってウィンドウ関数として扱われます。ウィンドウ関数は問い合わせ結果に含まれる行だけでなく、それ以上の行にアクセスすることができます。
テストデータの準備
まず人口が多い上位３０カ国の順位データを以下のように用意しました。 なお "rank" 列の順位は世界で人口の多いランキング順位を示しています。CREATE TABLE population (
rank number,
country varchar2(20) NOT NULL,
region varchar2(20),
population number
);
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.
"rank" 列は既に人口の多い順に順番が付けられていますが、このデータを基に 人口の低い順に連番を付けてみます。
ROW_NUMBER() 関数を使って前の行の値を取得する
ここで使うのは ROW_NUMBER() 関数 と OVER(ORDER BY) です。まず OVER(ORDER BY population) で人口を昇順(人口が少ない順)に並べ替え、次に呼び出される ROW_NUMBER() 関数で連番を振っていきます。以下に SQL と実行結果を記載します。人口の少ない順に１から連番が振られています。
SQL>
SELECT
RANK,
COUNTRY,
REGION,
POPULATION,
ROW_NUMBER() OVER(ORDER BY population) AS line_number
FROM population ;
RANK COUNTRY REGION POPULATION LINE_NUMBER
----- --------------- ---------- ---------- -----------
30 Spain Europe 46354321 1
29 Colombia Americas 49065615 2
28 Kenya Africa 49699862 3
27 South Korea Asia 50982212 4
26 Myanmar Asia 53370609 5
25 South Africa Africa 56717156 6
24 Tanzania Africa 57310019 7
23 Italy Europe 59359900 8
22 France Europe 64979548 9
21 United Kingdom Europe 66181585 10
20 Thailand Asia 69037513 11
19 Turkey Asia 80745020 12
18 Iran Asia 81162788 13
17 Congo Africa 81339988 14
16 Germany Europe 82114224 15
15 Vietnam Asia 95540800 16
14 Egypt Africa 97553151 17
13 Philippines Asia 104918090 18
12 Ethiopia Africa 104957438 19
11 Japan Asia 127484450 20
10 Mexico Americas 129163276 21
9 Russia Europe 143989754 22
8 Bangladesh Asia 164669751 23
7 Nigeria Africa 190886311 24
6 Pakistan Asia 197015955 25
5 Brazil Americas 209288278 26
4 Indonesia Asia 263991379 27
3 United States Americas 324459463 28
2 India Asia 1339180127 29
1 China Asia 1409517397 30
30 rows selected.
このようにウィンドウ関数を使用すると非常に簡単にレコードに対して連番を振ることが可能です。
