ウィンドウ関数 RANK() で順位付け
PostgreSQL はウィンドウ関数をサポートしていて非常に強力な SQL を 簡単に実行することが出来ます。そのウィンドウ関数を使ってランク付けする方法を試してみます。ウィンドウ関数を使わなくてもサブクエリー等を駆使すればデータの順位付けは出来るのかもしれませんが、恐らくパフォーマンスは非常に悪いでしょう。ウィンドウ関数を使用することによって良いパフォーマンスで簡単にデータの順位付けが出来ます。
ウィンドウ関数を使うには、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)
このデータを基にアジアの国々での人口が多い順にランク付けして順位を求めてみます。 まずアジアの国々だけを選択してみると次のようになっています。
=> 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)
ご覧の通り世界の人口順位である "rank" 列の値は飛び飛びになっています。 これにアジアの国のみの順位を付けてみたいと思います。
ウィンドウ関数 RANK() で順位付けする
ではアジアでの順位をつけるためにウィンドウ関数である RANK() を使ってみましょう。 SQL は次のようになります。"asia_rank" という列でアシアでの順位を示すことにします。=> SELECT *, 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)
これはつまり WHERE 句で region = 'Asia' としてアジアの国だけを絞り込んだ後、 OVER(ORDER BY population DESC) で population の降順でデータを並べ替え、 その並び替えられたデータに対して RANK() 関数で順位をつける、という流れです。
このウィンドウ関数を使えば複雑な SQL を書かなくても簡単にランキングを求められます。
