ウィンドウ関数 PARTITION BY で区間毎の集計
PostgreSQL はウィンドウ関数をサポートしていて非常に強力な 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" が地域毎の 合計人口となります。
=> 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)
ご覧の通り各国の人口(population)の右列(total_in_region)に地域毎の合計人口が集計されて表示されています。GROUP BY を使ったらサブクエリーを使わなければならないところ、上手く直感的に集計できました。
また以下のように COUNT() 関数を使って各地域に属する国数を集計して各国のレコードに付加することも出来ます。
=> 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)
このようにウィンドウ関数を使用すると非常に簡単にデータを区間毎に集計することが可能です。
