ウィンドウ関数 FIRST_VALUE() と LAST_VALUE 関数で最大値と最小値を得る
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)
FIRST_VALUE() 関数を使って最小値を取得する
ここで使うのはウィンドウ関数の FIRST_VALUE() 関数 と OVER(ORDER BY) です。まず OVER(ORDER BY population ASC) で人口を昇順(人口が少ない順)に並べ替え、次に呼び出される FIRST_VALUE() 関数で最初の値(最小値)を取得しています。以下に SQL と実行結果を記載します。各行の first_value 列に最小値が設定されているのが分かります。
SELECT rank, country, region, population, FIRST_VALUE(population) OVER (ORDER BY population ASC) FROM population ORDER BY population DESC; +------+----------------+----------+------------+-------------+ | rank | country | region | population | first_value | +------+----------------+----------+------------+-------------+ | 1 | China | Asia | 1409517397 | 46354321 | | 2 | India | Asia | 1339180127 | 46354321 | | 3 | United States | Americas | 324459463 | 46354321 | | 4 | Indonesia | Asia | 263991379 | 46354321 | | 5 | Brazil | Americas | 209288278 | 46354321 | | 6 | Pakistan | Asia | 197015955 | 46354321 | | 7 | Nigeria | Africa | 190886311 | 46354321 | | 8 | Bangladesh | Asia | 164669751 | 46354321 | | 9 | Russia | Europe | 143989754 | 46354321 | | 10 | Mexico | Americas | 129163276 | 46354321 | | 11 | Japan | Asia | 127484450 | 46354321 | | 12 | Ethiopia | Africa | 104957438 | 46354321 | | 13 | Philippines | Asia | 104918090 | 46354321 | | 14 | Egypt | Africa | 97553151 | 46354321 | | 15 | Vietnam | Asia | 95540800 | 46354321 | | 16 | Germany | Europe | 82114224 | 46354321 | | 17 | Congo | Africa | 81339988 | 46354321 | | 18 | Iran | Asia | 81162788 | 46354321 | | 19 | Turkey | Asia | 80745020 | 46354321 | | 20 | Thailand | Asia | 69037513 | 46354321 | | 21 | United Kingdom | Europe | 66181585 | 46354321 | | 22 | France | Europe | 64979548 | 46354321 | | 23 | Italy | Europe | 59359900 | 46354321 | | 24 | Tanzania | Africa | 57310019 | 46354321 | | 25 | South Africa | Africa | 56717156 | 46354321 | | 26 | Myanmar | Asia | 53370609 | 46354321 | | 27 | South Korea | Asia | 50982212 | 46354321 | | 28 | Kenya | Africa | 49699862 | 46354321 | | 29 | Colombia | Americas | 49065615 | 46354321 | | 30 | Spain | Europe | 46354321 | 46354321 | +------+----------------+----------+------------+-------------+ (30 rows)
LAST_VALUE() 関数を使って最大値を取得する
ここで使うのはウィンドウ関数の LAST_VALUE() 関数 と OVER(ORDER BY) です。まず OVER(ORDER BY population) で人口を昇順(人口が少ない順)に並べ替え、次に呼び出される LAST_VALUE() 関数で最後の値(最大値)を取得しています。LAST_VALUE() 関数については、PostgreSQL 公式ドキュメントで「有用ではない結果を得ることになりがちです」と言っており、正しい結果が得られないと言っています。しかしそれを避ける方法として ORDER BY に "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" を付け加えれば正しい結果が得られると述べられています。この例では正しい結果を得るために "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" を付け加えています。
以下に SQL と実行結果を記載します。各行の last_value 列に最大値が設定されているのが分かります。
SELECT
rank,
country,
region,
population,
LAST_VALUE(population) OVER (ORDER BY population
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM population
ORDER BY
population DESC;
+------+----------------+----------+------------+------------+
| rank | country | region | population | last_value |
+------+----------------+----------+------------+------------+
| 1 | China | Asia | 1409517397 | 1409517397 |
| 2 | India | Asia | 1339180127 | 1409517397 |
| 3 | United States | Americas | 324459463 | 1409517397 |
| 4 | Indonesia | Asia | 263991379 | 1409517397 |
| 5 | Brazil | Americas | 209288278 | 1409517397 |
| 6 | Pakistan | Asia | 197015955 | 1409517397 |
| 7 | Nigeria | Africa | 190886311 | 1409517397 |
| 8 | Bangladesh | Asia | 164669751 | 1409517397 |
| 9 | Russia | Europe | 143989754 | 1409517397 |
| 10 | Mexico | Americas | 129163276 | 1409517397 |
| 11 | Japan | Asia | 127484450 | 1409517397 |
| 12 | Ethiopia | Africa | 104957438 | 1409517397 |
| 13 | Philippines | Asia | 104918090 | 1409517397 |
| 14 | Egypt | Africa | 97553151 | 1409517397 |
| 15 | Vietnam | Asia | 95540800 | 1409517397 |
| 16 | Germany | Europe | 82114224 | 1409517397 |
| 17 | Congo | Africa | 81339988 | 1409517397 |
| 18 | Iran | Asia | 81162788 | 1409517397 |
| 19 | Turkey | Asia | 80745020 | 1409517397 |
| 20 | Thailand | Asia | 69037513 | 1409517397 |
| 21 | United Kingdom | Europe | 66181585 | 1409517397 |
| 22 | France | Europe | 64979548 | 1409517397 |
| 23 | Italy | Europe | 59359900 | 1409517397 |
| 24 | Tanzania | Africa | 57310019 | 1409517397 |
| 25 | South Africa | Africa | 56717156 | 1409517397 |
| 26 | Myanmar | Asia | 53370609 | 1409517397 |
| 27 | South Korea | Asia | 50982212 | 1409517397 |
| 28 | Kenya | Africa | 49699862 | 1409517397 |
| 29 | Colombia | Americas | 49065615 | 1409517397 |
| 30 | Spain | Europe | 46354321 | 1409517397 |
+------+----------------+----------+------------+------------+
(30 rows)
このようにウィンドウ関数を使用すると非常に簡単に最大値と最小値を取得することが可能です。
