ウィンドウ関数 LAG() と LEAD() で前後の集計値を取得
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)
このデータを基に自分より上位にある国の人口を表示する列を追加してみます。 言い換えますと１つ前の行の値を取得して表示してみます。
LAG() 関数を使って前の行の値を取得する
ここで使うのは LAG() 関数 と OVER(ORDER BY) です。まず OVER(ORDER BY population DESC) で人口を降順に並べ替え、次に呼び出される LAG(population) で前の行の人口 (population) の値を取得しています。以下に SQL と実行結果を記載します。"previous_population" が自分より 上位の人口となります。これを使えば前の行との差異を簡単に求められます。
=> SELECT country, region, population, lag(population) OVER(ORDER BY population DESC) AS previous_population FROM population ORDER BY population DESC; +----------------+----------+------------+---------------------+ | country | region | population | previous_population | +----------------+----------+------------+---------------------+ | China | Asia | 1409517397 | | | India | Asia | 1339180127 | 1409517397 | | United States | Americas | 324459463 | 1339180127 | | Indonesia | Asia | 263991379 | 324459463 | | Brazil | Americas | 209288278 | 263991379 | | Pakistan | Asia | 197015955 | 209288278 | | Nigeria | Africa | 190886311 | 197015955 | | Bangladesh | Asia | 164669751 | 190886311 | | Russia | Europe | 143989754 | 164669751 | | Mexico | Americas | 129163276 | 143989754 | | Japan | Asia | 127484450 | 129163276 | | Ethiopia | Africa | 104957438 | 127484450 | | Philippines | Asia | 104918090 | 104957438 | | Egypt | Africa | 97553151 | 104918090 | | Vietnam | Asia | 95540800 | 97553151 | | Germany | Europe | 82114224 | 95540800 | | Congo | Africa | 81339988 | 82114224 | | Iran | Asia | 81162788 | 81339988 | | Turkey | Asia | 80745020 | 81162788 | | Thailand | Asia | 69037513 | 80745020 | | United Kingdom | Europe | 66181585 | 69037513 | | France | Europe | 64979548 | 66181585 | | Italy | Europe | 59359900 | 64979548 | | Tanzania | Africa | 57310019 | 59359900 | | South Africa | Africa | 56717156 | 57310019 | | Myanmar | Asia | 53370609 | 56717156 | | South Korea | Asia | 50982212 | 53370609 | | Kenya | Africa | 49699862 | 50982212 | | Colombia | Americas | 49065615 | 49699862 | | Spain | Europe | 46354321 | 49065615 | +----------------+----------+------------+---------------------+ (30 rows)
LEAD() 関数を使って次の行の値を取得する
先の例の LAG() 関数は前の行の値を取得していましたが、LEAD() 関数は次の行の値を取得します。=> SELECT country, region, population, lead(population) OVER(ORDER BY population DESC) AS next_population FROM population ORDER BY population DESC; +----------------+----------+------------+-----------------+ | country | region | population | next_population | +----------------+----------+------------+-----------------+ | China | Asia | 1409517397 | 1339180127 | | India | Asia | 1339180127 | 324459463 | | United States | Americas | 324459463 | 263991379 | | Indonesia | Asia | 263991379 | 209288278 | | Brazil | Americas | 209288278 | 197015955 | | Pakistan | Asia | 197015955 | 190886311 | | Nigeria | Africa | 190886311 | 164669751 | | Bangladesh | Asia | 164669751 | 143989754 | | Russia | Europe | 143989754 | 129163276 | | Mexico | Americas | 129163276 | 127484450 | | Japan | Asia | 127484450 | 104957438 | | Ethiopia | Africa | 104957438 | 104918090 | | Philippines | Asia | 104918090 | 97553151 | | Egypt | Africa | 97553151 | 95540800 | | Vietnam | Asia | 95540800 | 82114224 | | Germany | Europe | 82114224 | 81339988 | | Congo | Africa | 81339988 | 81162788 | | Iran | Asia | 81162788 | 80745020 | | Turkey | Asia | 80745020 | 69037513 | | Thailand | Asia | 69037513 | 66181585 | | United Kingdom | Europe | 66181585 | 64979548 | | France | Europe | 64979548 | 59359900 | | Italy | Europe | 59359900 | 57310019 | | Tanzania | Africa | 57310019 | 56717156 | | South Africa | Africa | 56717156 | 53370609 | | Myanmar | Asia | 53370609 | 50982212 | | South Korea | Asia | 50982212 | 49699862 | | Kenya | Africa | 49699862 | 49065615 | | Colombia | Americas | 49065615 | 46354321 | | Spain | Europe | 46354321 | | +----------------+----------+------------+-----------------+ (30 rows)
上記結果からも分かる通り、次の行の人口(population)が取得できています。 この結果を利用して簡単に次の行との差異を求められます。
このようにウィンドウ関数を使用すると非常に簡単に集計後のデータの前後の値を取得することが可能です。
