PostgreSQL 9.2 から、組み込みの範囲型が利用できるようになりました。リリースされた直後から結構便利に使っているので、紹介しようと思います。概ねマニュアルに書いてあることの紹介となりますので、正確さを重視する方はマニュアルをご覧になった方が良いかもしれません。
どんなものなのか
ざっくり
「1 から 10 まで」とか「2013 年 1 月 1 日から 2013 年 12 月 31 日まで」というようなデータを１つの要素として取り扱えます。
組み込みの型
最初から用意されている型は int4range, int8range, numrange, tsrange, tstzrange, daterange の６つです。この他にもユーザーが新しい範囲型を定義することが出来ます。
リテラル表現
リテラルはありません。コンストラクタ関数を使うか、文字列をキャストする事になります。私はハードコードする時はキャストを使っていますが、要素の識別子を使うならコンストラクタ関数を使うのが適していると思います。
-- 1 以上 10 以下
SELECT int4range(1, 10, '[]');
-- 1 以上 10 以下
SELECT '[1,10]'::int4range;
どう便利なのか
悩ましかった「開始」と「終了」への１つの解として
テーブル設計の結果、下記のようになったケースに心当たりはありませんか？
| name | start_date | end_date |
|---|---|---|
| hoge | 2013-01-01 | 2013-12-31 |
| fuga | 2013-01-01 | 2013-02-29 |
| moge | 2013-01-01 | null |
| ponyo | 2013-01-01 | null |
もしくは
| name | start_date | end_date |
|---|---|---|
| hoge | 2013-01-01 | 2013-12-31 |
| fuga | 2013-01-01 | 2013-02-29 |
| moge | 2013-01-01 | 9999-12-31 |
| ponyo | 2013-01-01 | 9999-12-31 |
とか。
前者は null を許す要素の存在が精神を蝕みますよね。
後者についても「9999 年てｗ」となるわけですが、現実的な落としどころとしては良くある事ですよね。
範囲型を使うと下記のようなテーブルになります
| name | term |
|---|---|
| hoge | [2013-01-01,2014-01-01) |
| fuga | [2013-01-01,2013-03-01) |
| moge | [2013-01-01,) |
| ponyo | [2013-01-01,) |
これで、心置きなく NOT NULL 制約を追加出来ますし、遙か超未来の日付を投入するなどの苦しい選択をせずに済みます。
式インデックスとしての利用
かと言って、既にあるシステムを範囲型に置き換えるなどの対応は大変だろうと思います。そこで、既存のテーブル定義のままでも範囲型の恩恵にあずかれる方法を紹介します。
| name | start_date | end_date |
|---|---|---|
| hoge | 2013-01-01 | 2013-12-31 |
| fuga | 2013-01-01 | 2013-02-29 |
| moge | 2013-01-01 | null |
| ponyo | 2013-01-01 | null |
さっき出てきた、こういうテーブル（仮に users とする）に、新しいインデックスを追加します。
CREATE INDEX users_term_index ON users USING gist(daterange(start_date, end_date, '[]'));
これは式に対するインデックスという仕組みを使っています。範囲演算子を使うようなクエリにおいて速度のメリットがあります。また、可読性も上がると思います。下記をご覧下さい。
-- start_date から end_date が 2012-08-01 から 2013-02-01 までの間と一部でも重なるユーザー
SELECT name
FROM users
WHERE start_date <= '2013-02-01'::DATE
AND end_date >= '2012-08-01'::DATE;
-- 上記を範囲型で表現
SELECT name
FROM users
WHERE daterange(start_date, end_date, '[]') && daterange('2012-08-01', '2013-02-01', '[]');
範囲の重なりを表現する条件を書くとき、いつも紙に図を書いてみないと自信を持って前者のようなクエリを書けない私にとって、後者の書き方が出来るというのは僥倖です。
前者の書き方をする場合 btree (start_date DESC, end_date) のような怪しいインデックスを SET enable_seqscan TO off などで無理矢理使用すると結構速くなるんですが、後者は普通にそれと同じくらいの速度が出ます。（シチュエーションにもよるので鵜呑みにはされませんように）
ただし、式インデックスは更新コストが大きいので、取り出しと更新の比率によって採用するかどうかを慎重に考える必要があります。ご注意下さい。
制約条件
テーブル定義の中の制約として範囲型を用いることができます。
CREATE TABEL users(
name text,
term daterange,
exclude using gist(term with &&) -- テーブル内での重複した期間を許さない
);
上のように定義したテーブルは、下のように動作します。
insert into users (name, term) values ('hoge', '[2013-01-01,2013-02-01)'::daterange);
insert into users (name, term) values ('hoge', '[2013-02-01,2013-03-01)'::daterange);
-- ここまでは正常、以下は name がどうあれ範囲に重複があるので制約違反
insert into users (name, term) values ('fuga', '[2013-02-28,2013-03-31)'::daterange);
また、btree_gist という拡張を使うと、範囲型でないデータ型でも演算子を使った制約を設定出来ます。
CREATE EXTENSION btree_gist; -- CREATE EXTENSION 可能なアカウントにて実行
CREATE TABEL users(
name text,
term daterange,
exclude using gist(name with =, term with &&) -- ユーザー毎に重複した期間を許さない
);
上のように定義したテーブルは、下のように動作します。
insert into users (name, term) values ('hoge', '[2013-01-01,2013-02-01)'::daterange);
insert into users (name, term) values ('hoge', '[2013-02-01,2013-03-01)'::daterange);
insert into users (name, term) values ('fuga', '[2013-01-01,2013-02-01)'::daterange);
-- ここまでは正常、以下は name が等しくて範囲に重複がある行があるので制約違反
insert into users (name, term) values ('hoge', '[2013-02-28,2013-03-31)'::daterange);
注意点
「閉じた境界」と「開いた境界」
範囲型を取り扱うにあたっては「閉じた境界」と「開いた境界」に注意が必要です。と言っても、要するに「以上・以下・より大きい・未満」という話でしかありません。
コンストラクタ関数の利用例 int4range(1, 10, '[]') における第三の引数を見てみましょう。'[]' という文字列があります。これが境界の開閉について指定している部分です。
まとめると下記の通りです。
'[' = 以上
']' = 以下
'(' = より大きい
')' = 未満
この場合、「以上」と「以下」の事を閉じた境界、「より大きい」と「未満」の事を開いた境界と言います。
なお、コンストラクタ関数の第三の引数を省略したときには '[)' 「以上・未満」となります。また、テーブル定義に範囲型を使っている場合に、そのテーブルを psql 等を使って SELECT した時の結果表示についても、[1, 11) のような「以上・未満」表記となります。私は SQL の BETWEEN 句に慣れていたので、始点と終点共に閉じた境界とするスタイルで使っていましたが、範囲型の基本は '[)' 「以上・未満」であると思っておいた方が、ハマる事は少なそうです。
'[)' スタイルの利点は numrange や tsrange の場合に顕著です。
-- false
SELECT tsrange('2013-01-01 00:00:00', '2014-01-01 00:00:00', '[)') @> '2014-01-01 00:00:00'::timestamp;
-- true
SELECT tsrange('2013-01-01 00:00:00', '2014-01-01 00:00:00', '[]') @> '2014-01-01 00:00:00'::timestamp;
上記のような例について閉じた境界を使って終点を書こうとすると
--- 0.009 秒超過したので false
SELECT tsrange('2013-01-01 00:00:00', '2013-12-31 23:59:59.99', '[]') @> '2013-12-31 23:59:59.999'::timestamp;
--- 丁度境界値なので true
SELECT tsrange('2013-01-01 00:00:00', '2013-12-31 23:59:59.99', '[]') @> '2013-12-31 23:59:59.99'::timestamp;
このように、非常に細かい精度の「ヘリ」を定義する必要が出てきてキツいですね。以上・未満スタイルの方がスッキリと書けている事が分かると思います。詳しい話はマニュアルの離散的な範囲型という項に、もうちょっとまともな説明があります。
まとめ
範囲型を使うと、例えば「各月毎にアクティブだった期間があるユーザー数を集計」みたいなクエリを下記のように、どりゃっ！と書けたりして便利です。
WITH months AS (
SELECT to_char("day", 'YYYY年MM月') AS "month"
,daterange("day"::DATE, ("day" + '1 month'::interval)::DATE, '[)') AS term
FROM generate_series('2013-01-01', '2013-12-01', '1 month'::INTERVAL) AS "day")
-- 各月の表
-- month | term
--------------+-------------------------
-- 2013年01月 | [2013-01-01,2013-02-01)
-- 2013年02月 | [2013-02-01,2013-03-01)
-- 2013年03月 | [2013-03-01,2013-04-01)
-- 2013年04月 | [2013-04-01,2013-05-01)
-- 2013年05月 | [2013-05-01,2013-06-01)
-- 2013年06月 | [2013-06-01,2013-07-01)
-- 2013年07月 | [2013-07-01,2013-08-01)
-- 2013年08月 | [2013-08-01,2013-09-01)
-- 2013年09月 | [2013-09-01,2013-10-01)
-- 2013年10月 | [2013-10-01,2013-11-01)
-- 2013年11月 | [2013-11-01,2013-12-01)
-- 2013年12月 | [2013-12-01,2014-01-01)
-- ユーザーテーブルと期間の重なりを条件に結合して集計
SELECT months."month"
,count(users.name)
FROM months
JOIN users ON users.term && months.term
GROUP BY months."month"
;
「範囲に対する範囲条件」みたいなクエリは頭が混乱してバグりがちなんですが、範囲型を使うと直感的に書けるのが嬉しいです。特定のドメインについては今までよりも適した表現となるはずですので、検討されてはいかがでしょうか。ただし、プログラマのためのＳＱＬの一番最後の節（39.15）に「新機能は信用しない」と書いてある通り、新しめの機能を導入する際には、性能の測定や動作確認を充分される事をオススメします。
なお、アプリケーションでどう使うかについて、Rails で PostgreSQL の範囲型を使ってみるという記事も書きましたので、ご興味のある方はそちらも是非ご覧下さい。
以上です。