詳解 PostgreSQL[10/11対応]―現場で役立つ新機能と実践知識

第3章 アプリケーション開発に便利な機能―Window 関数,ストアドファンクション/プロシージャ,JIT,パラレルクエリ

この記事を読むのに必要な時間:およそ 11 分

ウィンドウフレーム─⁠─Window関数の対象を決める

Window関数を使うときにPARTITION BYなどで作ったウィンドウに対し,どこまで集計対象にするかを決めることができます。これをウィンドウフレームと言います。

ウィンドウフレームは,フレームに指定する対象をモードで指定します。モードの種類には,行を指定するROWS BETWEEN,指定されたカラムの値の範囲を指定するRANGE BETWEEN,そしてPostageSQL 11から追加されたGROUPS BETWEENがあります。GROUPS BETWEENは,同一の行をひとまとまりにしたグループを指定します。

ウィンドウフレームのモードを指定したら,次はフレームに指定した対象の絞り込みの範囲を決めます。絞り込みの範囲はPRECEDINGとFOLLOWINGで指定します。PRECEDINGは指定した値より上,FOLLOWINGは指定した値より下を集計の対象にします。

実際の利用例は図4とおりです。図4はWITH句を使って,連番のid列とvalue列を持った擬似テーブルtを作っています。tに対してウインドウフレームの各モードを指定し,同じ絞り込みの範囲を指定した場合の結果を集計しています。

図4 ウィンドウフレームの利用例

demo=# WITH t(id, value) AS (VALUES (1, 1), (2, 1), (3, 3), (4, 5), (5, 5), (6, 5), (7, 6))
SELECT id, value,
  array_agg(id) OVER ROWS as row_id, array_agg(value) OVER ROWS as row_value,
  array_agg(id) OVER RANGE as renge_id, array_agg(value) OVER RANGE as renge_value,
  array_agg(id) OVER GROUPS as groups_id, array_agg(value) OVER GROUPS as groups_value
FROM t WINDOW obj AS (ORDER BY value),
  ROWS AS (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  RANGE AS (obj RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  GROUPS AS (obj GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING);

 id | value | row_id  | row_value | renge_id  | renge_value |   groups_id   | groups_value
----+-------+---------+-----------+-----------+-------------+---------------+---------------
  1 |     1 | {1,2}   | {1,1}     | {1,2}     | {1,1}       | {1,2,3}       | {1,1,3}
  2 |     1 | {1,2,3} | {1,1,3}   | {1,2}     | {1,1}       | {1,2,3}       | {1,1,3}
  3 |     3 | {2,3,4} | {1,3,5}   | {3}       | {3}         | {1,2,3,4,5,6} | {1,1,3,5,5,5}
  4 |     5 | {3,4,5} | {3,5,5}   | {4,5,6,7} | {5,5,5,6}   | {3,4,5,6,7}   | {3,5,5,5,6}
  5 |     5 | {4,5,6} | {5,5,5}   | {4,5,6,7} | {5,5,5,6}   | {3,4,5,6,7}   | {3,5,5,5,6}
  6 |     5 | {5,6,7} | {5,5,6}   | {4,5,6,7} | {5,5,5,6}   | {3,4,5,6,7}   | {3,5,5,5,6}
  7 |     6 | {6,7}   | {5,6}     | {4,5,6,7} | {5,5,5,6}   | {4,5,6,7}     | {5,5,5,6}
(7 rows)

ROWSが指定されているROWS AS (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)は,⁠自分のレコードよりも1つ前 AND 自分のレコードより1後ろ」と指定したことになります。

ROWSが上下の行を見ていたのに対し,RANGEが指定されているRANGE AS (obj RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)は,⁠自分の値よりも1つ少ない AND 自分の値よりも1つ多い」と指定したことになります。

GROUPSが指定されているGROUPS AS (obj GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING)は,⁠同じ値のグループよりも1つ前のグループ AND 同じ値のグループよりも1つ後ろのグループ」と指定したことになります。

EXCLUDE句による除外指定

PostageSQL 11からはさらに,ウィンドウフレームに対してEXCLUDE句で除外指定できるようになりました。次の4種類が指定できます。

EXCLUDE CURRENT ROW
自分の行を除外する
EXCLUDE GROUP
自分と同じ値の行を除外する
EXCLUDE TIES
重複した値の行を除外する
EXCLUDE NO OTHERS
ウィンドウフレーム以外の値を省略する

EXCLUDE NO OTHERSを指定した場合は,EXCLUDE句を省略したときと同様の動きをし,EXCLUDE句を指定できなかったPostgreSQL 10までの挙動をします。

実際の利用例は図5とおりです。

図5 EXCLUDE句の利用例

demo=# WITH t(value) AS (VALUES (1), (1), (3), (5), (5), (5), (6))
SELECT
 value, array_agg(value)
    OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  EXCLUDE CURRENT ROW) AS current_row, array_agg(value)
    OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  EXCLUDE GROUP) AS group, array_agg(value)
    OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  EXCLUDE TIES) AS ties, array_agg(value)
    OVER (obj ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  EXCLUDE NO OTHERS) AS no_others
FROM t WINDOW obj AS (ORDER BY value);

 value | current_row | group |  ties   | no_others
-------+-------------+-------+---------+-----------
     1 | {1}         |       | {1}     | {1,1}
     1 | {1,3}       | {3}   | {1,3}   | {1,1,3}
     3 | {1,5}       | {1,5} | {1,3,5} | {1,3,5}
     5 | {3,5}       | {3}   | {3,5}   | {3,5,5}
     5 | {5,5}       |       | {5}     | {5,5,5}
     5 | {5,6}       | {6}   | {5,6}   | {5,5,6}
     6 | {5}         | {5}   | {5,6}   | {5,6}
(7 rows)

ストアドファンクション/プロシージャ

PostgreSQLは従来から,ストアドファンクションを定義できます。PostgreSQL 11からは,ストアドプロシージャも定義できるようになりました。両方の使い方を見てみましょう。

ストアドファンクション─⁠─SQLから呼べる独自関数

ストアドファンクションとはユーザー定義関数の一つで,PL/pgSQLをはじめとした手続型言語で記述します。ストアドファンクションを使うと,データベース内での複数の処理をまとめることができます。たとえば一度の呼び出しで複数のSQLを実行できると,性能面でメリットがあります。トリガと組み合わせると,複雑なデータ処理の参照整合性を担保したまま処理できます。これにより,アプリケーション側のロジックを維持したままデータベースのリファクタリングを行えます。

デメリットとしては,アプリケーションからは見えないところで処理が実行されるためストアドファンクションの存在が暗黙知になりがちなことと,テストやデプロイに課題が多いことが挙げられます。

PL/pgSQLで書く

ストアドファンクションを定義する場合,主にPL/pgSQLを使います。ここでは慣れ親しんだFizzBuzz注1を書いてみましょう。

ストアドファンクションでのFizzBuzz

demo=# CREATE OR REPLACE FUNCTION fizzbuzz()
demo-# RETURNS setof text as $$
demo$# BEGIN
demo$#   FOR i IN 1 .. 100 LOOP
demo$#     IF MOD(i, 15) = 0 THEN
demo$#       return next 'FizzBuzz';
demo$#     ELSIF MOD(i, 5) = 0 THEN
demo$#       return next 'Buzz';
demo$#     ELSIF MOD(i, 3) = 0 THEN
demo$#       return next 'Fizz';
demo$#     ELSE
demo$#       return next i::text;
demo$#     END IF;
demo$#   END LOOP;
demo$#   RETURN;
demo$# END;
demo$# $$ LANGUAGE plpgsql;
CREATE FUNCTION

ストアドファンクションの実行

demo=# SELECT array_agg(val) AS fizzbuzz FROM LATERAL
   (SELECT fizzbuzz() AS val) AS list;
 fizzbuzz
------------------------------------------------------
 {1,2,Fizz,4,Buzz,Fizz,7,8,Fizz,Buzz,11,Fizz,13,14,Fizz
Buzz,16,17,Fizz,19,Buzz,Fizz,22,23,Fizz,Buzz ...
(省略)

一般的な関数と同じように,データベースの関数を定義できます。もちろん引数でSQLの検索結果を渡せます。また,ストアドファンクションの中でSQLを実行して結果を取得できます。

注意点は,ストアドファンクションは関数ですので戻り値が必要です。

ほかの言語で書く

PostgreSQLのストアドファンクションは多くの言語で定義できます。PL/pgSQL以外に標準でサポートしているものは,PL/PerlとPL/Pythonです。拡張を追加すれば,さらにPL/Java,PL/v8,PL/Rなどが使えます。

今回はPL/PythonでFizzBuzzを書いてみます。

PL/Pythonの有効化

demo=# CREATE EXTENSION plpythonu;

PL/Pythonでのストアドファンクション

demo=# CREATE OR REPLACE FUNCTION
demo-# fizzbuzz_py(input integer)
demo-# ;RETURNS TEXT
demo$# AS $$
demo$# if input % 15 == 0:
demo$#     return "fizz buzz"
demo$# elif input % 3 == 0:
demo$#     return "fizz"
demo$# elif input % 5 == 0:
demo$#     return "buzz"
demo$# else:
demo$#     return str(input)
demo$# $$ LANGUAGE plpythonu;
CREATE FUNCTION

ストアドファンクションの実行

demo=# SELECT array_agg(val) AS fizzbuzz FROM LATERAL
   (SELECT fizzbuzz_py(GENERATE_SERIES(1, 13)) AS val)
AS list;
 fizzbuzz_py
-------------
 {1,2,Fizz,4,Buzz,Fizz,7,8,Fizz,Buzz,11,Fizz,13}
(13 rows)

今回は引数で値を受け取るようにしました。ロジックに大きな差はないのですが,慣れ親しんだ言語だと読みやすさが大きく違うのではないでしょうか。PL/PythonやPL/v8は,PL/pgSQLよりも性能面で有利という特徴もあります。

注意点として,PL/PythonはAmazon RDSでは非対応です。逆にPL/v8はAmazon RDSでは拡張を追加しなくても利用できます。

注1)
1から順にカウントアップしていき,3で割れるときはFizz,5で割れるときはBuzz,3と5の両方で割れる場合はFizzBuzzと答える遊びです。

ストアドプロシージャ─⁠─SQLの処理をまとめる

PostgreSQL 11からは,ストアドプロシージャも定義できるようになりました。ストアドプロシージャにより,戻り値が不要な場合でも複数の処理をまとめることができます。

呼び出し方法は,SELECT文ではなく,CALLストアドプロシージャ名のようにCALL文で呼び出します。たとえば引数を受け取り,その値をもとにINSERTする処理は,次のようになります。

demo=# CREATE TABLE tbl_a (id int);
CREATE TABLE
demo=# CREATE PROCEDURE
demo-# insert_data(a integer, b integer)
demo-# LANGUAGE SQL
demo-# AS $$
demo$# INSERT INTO tbl_a VALUES (a);
demo$# INSERT INTO tbl_a VALUES (b);
demo$# $$;
CREATE PROCEDURE
demo=# CALL insert_data(1, 2);
CALL
demo=# select * from tbl_a;
 id
----
  1
  2
(2 rows)

Oracle DBからの移行やバッチ処理などで,ストアドプロシージャを求める声は多くありました。最新版のPostgreSQL 11から利用できるようになり,選択肢の幅がより一層広がりました。

著者プロフィール

曽根壮大(そねたけとも)

株式会社オミカレ副社長兼CTO。数々の業務システム,Webサービスなどの開発・運用を担当し,2017年に株式会社はてなでサービス監視サービス「Mackerel」のCRE(Customer Reliability Engineer)を経て現職。 コミュニティでは,Microsoft MVPをはじめ,日本PostgreSQLユーザ会の理事として勉強会の開催を担当し,各地で登壇している。 builderscon 2017,YAPC::Kansaiなどのイベントでベストスピーカーを受賞し,分かりやすく実践的な内容のトークに定評がある。 他に,岡山Python勉強会を主催し,オープンラボ備後にも所属。著書に『Software Design』誌で,データベースに関する連載「RDBアンチパターン」をまとめた『失敗から学ぶRDBの正しい歩き方』を執筆。

@soudai1025
はてなid:Soudai

著書