ウィンドウ関数 PARTITION BY で区間毎の集計
Oracle データベースはウィンドウ関数をサポートしていて非常に強力な SQL を 簡単に実行することが出来ます。そのウィンドウ関数を使って特定のデータを SELECT 内で集計する方法を試してみます。同じことは GROUP BY でも 頑張れば出来ますが、慣れればウィンドウ関数のほうが直感的にわかりやすく パフォーマンスも良いです。
ウィンドウ関数を使うには、OVER 句を使うことによってウィンドウ関数として扱われます。ウィンドウ関数は問い合わせ結果に含まれる行だけでなく、それ以上の行にアクセスすることができます。
テストデータの準備
まず人口が多い上位３０カ国の順位データを以下のように用意しました。 なお "rank" 列の順位は世界で人口の多いランキング順位を示しています。=> 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)
このデータを基に各国が属する地域(アジアやアフリカなど)の地域毎の総合計を 付け加えてデータを出力してみます。そうすると中国(China)の人口がアジアの中でどの程度の人数を占めているのかわかりやすくなります。
ウィンドウ関数 PARTITION BY を使う
ここで使うのは PARTITION BY と SUM() 関数です。PARTITION BY は 与えられた条件毎にデータを区切って SUM() 関数に情報を渡して集計します。以下に SQL と実行結果を記載します。"total_in_region" が地域毎の 合計人口となります。
SQL> SELECT country, region, population, SUM(population) OVER(PARTITION BY region) AS total_in_region FROM population ORDER BY population DESC; COUNTRY REGION POPULATION TOTAL_IN_REGION ---------------- ---------- ---------- --------------- China Asia 1409517397 4037616091 India Asia 1339180127 4037616091 United States Americas 324459463 711976632 Indonesia Asia 263991379 4037616091 Brazil Americas 209288278 711976632 Pakistan Asia 197015955 4037616091 Nigeria Africa 190886311 638463925 Bangladesh Asia 164669751 4037616091 Russia Europe 143989754 462979332 Mexico Americas 129163276 711976632 Japan Asia 127484450 4037616091 Ethiopia Africa 104957438 638463925 Philippines Asia 104918090 4037616091 Egypt Africa 97553151 638463925 Vietnam Asia 95540800 4037616091 Germany Europe 82114224 462979332 Congo Africa 81339988 638463925 Iran Asia 81162788 4037616091 Turkey Asia 80745020 4037616091 Thailand Asia 69037513 4037616091 United Kingdom Europe 66181585 462979332 France Europe 64979548 462979332 Italy Europe 59359900 462979332 Tanzania Africa 57310019 638463925 South Africa Africa 56717156 638463925 Myanmar Asia 53370609 4037616091 South Korea Asia 50982212 4037616091 Kenya Africa 49699862 638463925 Colombia Americas 49065615 711976632 Spain Europe 46354321 462979332 30 rows selected.
ご覧の通り各国の人口(population)の右列(total_in_region)に地域毎の 合計人口が集計されて表示されています。GROUP BY を使ったらサブクエリーを 使わなければならないところ、上手く直感的に集計できました。
また以下のように COUNT() 関数を使って各地域に属する国数を集計して各国のレコードに付加することも出来ます。
SQL> SELECT country, region, population, COUNT(population) OVER(PARTITION BY region) AS total_in_region FROM population ORDER BY population DESC; COUNTRY REGION POPULATION TOTAL_IN_REGION --------------- --------- ---------- --------------- China Asia 1409517397 13 India Asia 1339180127 13 United States Americas 324459463 4 Indonesia Asia 263991379 13 Brazil Americas 209288278 4 Pakistan Asia 197015955 13 Nigeria Africa 190886311 7 Bangladesh Asia 164669751 13 Russia Europe 143989754 6 Mexico Americas 129163276 4 Japan Asia 127484450 13 Ethiopia Africa 104957438 7 Philippines Asia 104918090 13 Egypt Africa 97553151 7 Vietnam Asia 95540800 13 Germany Europe 82114224 6 Congo Africa 81339988 7 Iran Asia 81162788 13 Turkey Asia 80745020 13 Thailand Asia 69037513 13 United Kingdom Europe 66181585 6 France Europe 64979548 6 Italy Europe 59359900 6 Tanzania Africa 57310019 7 South Africa Africa 56717156 7 Myanmar Asia 53370609 13 South Korea Asia 50982212 13 Kenya Africa 49699862 7 Colombia Americas 49065615 4 Spain Europe 46354321 6 30 rows selected.
このようにウィンドウ関数を使用すると非常に簡単にデータを区間毎に集計することが可能です。
