PostgreSQLのSQLの特徴

第3章では，PostgreSQLの開発に便利な，バージョン10，11の新機能を紹介します。

そこでまずは，RDBMSの開発には欠かせないSQLの話です。本節ではSQLを通してPostgreSQLの特徴をつかみ，開発効率を上げていきましょう。

そのあとの節では，Window関数，ストアドファンクション／プロシージャ，JITコンパイラ，パラレルクエリという開発で役立つ新機能の解説を行います。各節の紹介を通して，PostgreSQLの魅力を堪能してください。

PostgreSQLは標準SQLを重視する

PostgreSQLは，標準SQLを重視しています。SQLには標準規格があり，SQLの構文や振る舞いが明記されています。PostgreSQLの開発では，標準SQLの最新の公式バージョンに準拠することを重視しており，標準的な機能や考え方に矛盾しないように気が配られています。そのため標準SQLで必須とされた機能の多くは，多少構文や機能に違いがあってもサポートされています。

結果として，Oracle DBなどのほかのRDBMSと互換性のあるSQLが多く，ほかのRDBMSを利用したことがある人であれば，PostgreSQLを覚えるのは苦ではありません。

標準SQLに準拠している機能

標準SQLに準拠している機能の一覧は，公式ドキュメントに明記されています。基本的なSQLの構文はもちろん，型の定義からトランザクション分離レベルまでサポートしています。

標準SQLに準拠していない機能

残念ながら，PostgreSQLをまだ標準SQLのすべての機能をサポートしているわけではありません。サポートされていない機能の一覧も公式ドキュメントに明記されています。ここに明記されているように，MERGE文は後述のUPSERTのみ，サポートしています。遅延可能制約は一部の制約が利用可能で，その対象は外部キー制約と一意キー制約です。

PostgreSQLは今後も標準SQLに準拠していくための機能追加を進めていく予定です。まだサポートされていない標準SQLの一覧を眺めると，少し未来のPostgreSQLの姿が想像できます。

PostgreSQL独自のSQLと機能

PostgreSQLは標準SQLを重視していますが，独自の構文などで機能を提供することもあります。独自の構文を利用すると互換性を失いますが，それを補って余りある効果が期待されるときは採用しています。以降では，そんなPostgreSQLの独自機能を紹介します。

UPSERT─⁠─追加と更新を同時に行う

データの挿入時に該当のキーにデータがなければINSERT，あればUPDATEを行うことをUPSERTと呼びます。

これは，標準SQLではMERGE文として定義されており，Oracle DBではMERGE文として提供されています。MySQLではINSERT ... ON DUPLICATE KEY UPDATEの構文でサポートしています。PostgreSQLは，これに似たINSERT ... ON CONFLICT DO UPDATEの構文をサポートしました。

PostgreSQLのUPSERTは，バージョン9.5からサポートされた機能です。本来のMERGE文はDELETEの機能も必要なのですが，実装の難易度が高く，実現しませんでした。議論の結果，PostgreSQLは標準SQLとしてのMERGE文にこだわって実装されないことより，要望の多かったUPSERTのみを切り出して実装することにしました。

待望の機能としてリリースされたUPSERTは，便利で多くのシーンで活躍しています。このように，標準SQLだけでなく，必要とされている機能をどのように提供するかということに真摯に向き合っているのがPostgreSQLの開発です。

独自の関数

RDBMSで独自に提供されている機能の筆頭と言えば関数で，PostgreSQLも例外ではありません。その中でも特徴的な機能は，JSON関数と演算子です。PostgreSQLにはJSONを保存するためのJSON型とJSONB型があり，それを扱うための関数です。昨今，データフォーマットとしてJSONを利用することは一般的になっており，PostgreSQLはそのニーズにいち早く応えています。

Window関数─⁠─集計の味方

標準SQLにはWindow関数が定義されており，PostgreSQLもサポートしています。Window関数はPostgreSQL 8.4で導入され，定期的に機能追加されてきました。PostgreSQL 11でもWindow関数に新機能が提供されており，より便利になっています。

Window関数の役割

まず，Window関数の役割を見てみましょう。Window関数は，ランキングなどを取り出すときによく使われます。MySQLにも最近MySQL 8で追加されました。

ここでは，実際に売上表から売上ランキングを検索してみます。

図1のSQLを使って売上表を作成します。図1では，第1章で紹介したメタコマンドの\xを利用して，縦表示と横表示を切り替えています。

テーブルの用意
demo=# CREATE TABLE public."売上" (id serial NOT NULL,
"売上日時" timestamp NOT NULL DEFAULT now(),
"商品名" character varying(64) NOT NULL,
"価格" numeric NOT NULL DEFAULT 0,
"ジャンル" text NOT NULL);

バルクINSERTでテストデータを作る
demo=# INSERT INTO "売上" ("売上日時","商品名","価格
","ジャンル") VALUES
(now()-((random()*10000)::int%365)*interval '1 day',
'WEB+DB PRESS Vol.107',1480,'紙'),
(now()-((random()*10000)::int%365)*interval '1 day',
'WEB+DB PRESS Vol.106',1480,'電子'),
(now()-((random()*10000)::int%365)*interval '1 day',
'WEB+DB PRESS Vol.105',1480,'紙'),
(now()-((random()*10000)::int%365)*interval '1 day',
'WEB+DB PRESS Vol.104',1480,'電子');

demo=# \x
Expanded display is on.

データの中身の確認
demo=# SELECT * FROM "売上" WHERE
"商品名" = 'WEB+DB PRESS Vol.105' LIMIT 1;

-[ RECORD 1 ]-------------------------------
id       | 22251
売上日時 | 2018-09-08 07:42:00.067831
商品名   | WEB+DB PRESS Vol.105
価格     | 1480
ジャンル | 紙

この売上表の8月から10月の各月における，日別の売上件数の上位3件を取得します。

Window関数を利用しない場合

まずは，Window関数を使わずに集計します。Window関数を利用しない場合，図2のようになります。月ごとのSQLは対象月が増えるほどUNIONが増えます。そして，UNIONの数だけSQLは実行されます。対象月が毎月増えていく場合，UNIONも毎月増やす必要があり，SQLのメンテナンスに苦労することが想像できます。

8～10月の各月の日別の売上件数の上位3件を表示

demo=# SELECT "売上日", "件数" FROM
 (SELECT "売上日時"::date AS "売上日",
   COUNT(*) AS "件数" FROM "売上"
   WHERE "売上日時" >= '2018/10/01 00:00:00'
     AND "売上日時" <= '2018/10/31 23:59:59'
   GROUP BY "売上日"
ORDER BY "件数" DESC,売上日 LIMIT 3) AS oct
UNION ALL SELECT * FROM
 (SELECT "売上日時"::date AS "売上日",
   COUNT(*) AS "件数" FROM "売上"
   WHERE "売上日時" >= '2018/09/01 00:00:00'
     AND "売上日時" <= '2018/09/30 23:59:59'
   GROUP BY "売上日"
ORDER BY "件数" DESC,売上日 LIMIT 3) AS Sep
UNION ALL SELECT * FROM
 (SELECT "売上日時"::date AS "売上日",
   COUNT(*) AS "件数" FROM "売上"
   WHERE "売上日時" >= '2018/08/01 00:00:00'
     AND "売上日時" <= '2018/08/31 23:59:59'
   GROUP BY "売上日"
ORDER BY "件数" DESC,売上日 LIMIT 3) AS Aug;

売上日      | 件数
------------+------
 2018-10-13 |  101
 2018-10-01 |   99
 2018-10-05 |   97
 2018-09-04 |  104
 2018-09-10 |  100
 2018-09-26 |   99
 2018-08-25 |  108
 2018-08-19 |  102
 2018-08-24 |  100
(9 rows)

Window関数を利用した場合

Window関数を利用すると，この複雑な処理をまとめたSQLにできます。クエリ回数が減るため，性能も高速です。

Window関数を利用した例が図3です。Window関数は，使いたい関数名のあとにOVER()で対象を指定します。PARTITION BY句は，対象に指定されたカラムの値を使って集合を作ります。この集合のことをウィンドウと呼びます。図3の例では，売上月ごとにPARTITION BYで指定したウィンドウの結果をソートしており，さらにWindow関数を使って順位列を作成しています。

8～10月の各月の日別の売上件数の上位3件を表示

demo=# SELECT "売上日", "件数", "順位"
FROM (
  SELECT *, dense_rank() OVER (PARTITION BY "売上月"
   ORDER BY "件数" DESC, 売上日) AS "順位"
    FROM (
      SELECT
        extract(month from "売上日時") AS "売上月",
        "売上日時"::date AS "売上日",
        COUNT(*) AS "件数"
      FROM "売上"
    WHERE "売上日時" >= '2018/08/01 00:00:00'
      AND "売上日時" <= '2018/10/31 23:59:59'
    GROUP BY "売上日", "売上月"
  ) AS "集計"
) AS "集計ランキング"
WHERE "順位" <= 3
ORDER BY "売上月" DESC, "順位";

   売上日   | 件数 | 順位
------------+------+------
 2018-10-13 |  101 |    1
 2018-10-01 |   99 |    2
 2018-10-05 |   97 |    3
 2018-09-04 |  104 |    1
 2018-09-10 |  100 |    2
 2018-09-26 |   99 |    3
 2018-08-25 |  108 |    1
 2018-08-19 |  102 |    2
 2018-08-24 |  100 |    3
(9 rows)

これが，Window関数の便利さです。対象の月が変動しても，売上日時のWHERE句を指定するだけで対応できます。また，前述したようにDBの負荷を減らしながら集計できます。

主なWindow関数を表1にまとめました。使いこなしてデータ分析や集計を効率化していきましょう。