ウィンドウ関数 LAG() と LEAD() で前後の集計値を取得
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.
このデータを基に自分より上位にある国の人口を表示する列を追加してみます。 言い換えますと１つ前の行の値を取得して表示してみます。
LAG() 関数を使って前の行の値を取得する
ここで使うのは LAG() 関数 と OVER(ORDER BY) です。まず OVER(ORDER BY population DESC) で人口を降順に並べ替え、次に呼び出される LAG(population) で前の行の人口 (population) の値を取得しています。以下に SQL と実行結果を記載します。"previous_population" が自分より 上位の人口となります。これを使えば前の行との差異を簡単に求められます。
SQL> SELECT country, region, population, lag(population) OVER(ORDER BY population DESC) AS previous_population FROM population ORDER BY population DESC; COUNTRY REGION POPULATION PREVIOUS_POPULATION --------------- ---------- ---------- ------------------- China Asia 1409517397 India Asia 1339180127 1409517397 United States Americas 324459463 1339180127 Indonesia Asia 263991379 324459463 Brazil Americas 209288278 263991379 Pakistan Asia 197015955 209288278 Nigeria Africa 190886311 197015955 Bangladesh Asia 164669751 190886311 Russia Europe 143989754 164669751 Mexico Americas 129163276 143989754 Japan Asia 127484450 129163276 Ethiopia Africa 104957438 127484450 Philippines Asia 104918090 104957438 Egypt Africa 97553151 104918090 Vietnam Asia 95540800 97553151 Germany Europe 82114224 95540800 Congo Africa 81339988 82114224 Iran Asia 81162788 81339988 Turkey Asia 80745020 81162788 Thailand Asia 69037513 80745020 United Kingdom Europe 66181585 69037513 France Europe 64979548 66181585 Italy Europe 59359900 64979548 Tanzania Africa 57310019 59359900 South Africa Africa 56717156 57310019 Myanmar Asia 53370609 56717156 South Korea Asia 50982212 53370609 Kenya Africa 49699862 50982212 Colombia Americas 49065615 49699862 Spain Europe 46354321 49065615 30 rows selected.
LEAD() 関数を使って次の行の値を取得する
先の例の LAG() 関数は前の行の値を取得していましたが、LEAD() 関数は次の行の値を取得します。SQL> SELECT country, region, population, lead(population) OVER(ORDER BY population DESC) AS next_population FROM population ORDER BY population DESC; COUNTRY REGION POPULATION NEXT_POPULATION --------------- --------- ---------- --------------- China Asia 1409517397 1339180127 India Asia 1339180127 324459463 United States Americas 324459463 263991379 Indonesia Asia 263991379 209288278 Brazil Americas 209288278 197015955 Pakistan Asia 197015955 190886311 Nigeria Africa 190886311 164669751 Bangladesh Asia 164669751 143989754 Russia Europe 143989754 129163276 Mexico Americas 129163276 127484450 Japan Asia 127484450 104957438 Ethiopia Africa 104957438 104918090 Philippines Asia 104918090 97553151 Egypt Africa 97553151 95540800 Vietnam Asia 95540800 82114224 Germany Europe 82114224 81339988 Congo Africa 81339988 81162788 Iran Asia 81162788 80745020 Turkey Asia 80745020 69037513 Thailand Asia 69037513 66181585 United Kingdom Europe 66181585 64979548 France Europe 64979548 59359900 Italy Europe 59359900 57310019 Tanzania Africa 57310019 56717156 South Africa Africa 56717156 53370609 Myanmar Asia 53370609 50982212 South Korea Asia 50982212 49699862 Kenya Africa 49699862 49065615 Colombia Americas 49065615 46354321 Spain Europe 46354321 30 rows selected.
上記結果からも分かる通り、次の行の人口(population)が取得できています。 この結果を利用して簡単に次の行との差異を求められます。
このようにウィンドウ関数を使用すると非常に簡単に集計後のデータの前後の値を取得することが可能です。
