ウィンドウ関数 ROW_NUMBER() 関数で連番を振る
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)
"rank" 列は既に人口の多い順に順番が付けられていますが、このデータを基に 人口の低い順に連番を付けてみます。
ROW_NUMBER() 関数を使って前の行の値を取得する
ここで使うのは ROW_NUMBER() 関数 と OVER(ORDER BY) です。まず OVER(ORDER BY population) で 人口を昇順(人口が少ない順)に並べ替え、次に呼び出される ROW_NUMBER() 関数で連番を振っていきます。以下に SQL と実行結果を記載します。人口の少ない順に１から連番が振られています。
=> SELECT *, ROW_NUMBER() OVER(ORDER BY population) FROM population ; +------+----------------+----------+------------+------------+ | rank | country | region | population | row_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)
このようにウィンドウ関数を使用すると非常に簡単にレコードに対して連番を振ることが可能です。
