range type（範囲型）という言葉を初めて聞いたのですが奥が深そうだったので、なんとなくわかればいいかな程度にまとめました。といってもなかなかボリュームがあります。
より詳しくは以下のPostgreSQL 9.3.2文書 第 8章データ型のページをご覧ください。
参考元：https://www.postgresql.jp/document/9.3/html/rangetypes.html
Range Typeとは
PostgreSQL9.2からrange type（範囲型）が追加されました。特定の値の範囲を表わすデータ型です。
例えば日時があるとして開始日と終了日があった場合、それを期間化して使うことができます。
従来であれば<>などの演算子で範囲を取得していましたがRange Type を使用することで行数を減らし可読性を高めることができます。
範囲型には大きく離散的な範囲型と組み込みの範囲型があります。
※ 離散的な範囲型についてはここでは割愛します。
組み込みの範囲型
以下の範囲を扱う型があります。
- int4range — integerの範囲
- int8range — bigintの範囲
- numrange — numericの範囲
- tsrange — timestamp without time zoneの範囲
- tstzrange — timestamp with time zoneの範囲
- daterange — dateの範囲
独自の範囲を決めることも可能だそうですが、追いきれないので割愛します。
閉じた境界と開いた境界
空でない範囲には必ず2つの境界、下限値と上限値があります。これらの値の間にある値はすべてその範囲に含まれます。 閉じた境界とはその境界値自体が範囲に含まれることを意味し、開いた境界とは、その境界値が範囲に含まれないことを意味します。
閉じた下限値は”[“で、開いた下限値は”(“で表します。 同様に、閉じた上限値は”]”で、開いた上限値は”)”で表します。
閉じた境界と開いた境界の例
たとえば 1-5 の範囲の値を取得する時に 1 と 5 を含める場合は「閉じた境界」、 1 と 5 を含めない場合は「開いた境界」となります。
記号で書くと 1 と 5 を含める閉じた境界の場合は [1-5] 、1 と 5 を含めない場合は「開いた境界」の場合は (1-5) となります。
1は含めたいけど5は含めたくない [ 1-5 ) などの書き方も可能です。
- ‘[‘ = 以上
- ‘]’ = 以下
- ‘(‘ = より大きい
- ‘)’ = 未満
範囲演算子と範囲関数について
range type（範囲型）を使用する際には範囲演算子と範囲関数を使用します。
範囲演算子
あまり使い馴染みのない演算子があるので軽く目を通しておきましょう。
| 演算子 | 説明 | 例 | 結果 |
|---|---|---|---|
| = | 等しい | int4range(1,5) = ‘[1,4]’::int4range | true |
| <> | 等しくない | numrange(1.1,2.2) <> numrange(1.1,2.3) | true |
| < | 未満 | int4range(1,10) < int4range(2,3) | true |
| > | より大きい | int4range(1,10) > int4range(1,5) | true |
| <= | 以下 | numrange(1.1,2.2) <= numrange(1.1,2.2) | true |
| >= | 以上 | numrange(1.1,2.2) >= numrange(1.1,2.0) | true |
| @> | 範囲を包含する | int4range(2,4) @> int4range(2,3) | true |
| @> | 要素を包含する | ‘[2011-01-01,2011-03-01)’::tsrange @> ‘2011-01-10’::timestamp | true |
| <@ | ・・・により範囲が包含される | int4range(2,4) <@ int4range(1,7) | true |
| <@ | ・・・により要素が包含される | 42 <@ int4range(1,7) | false |
| && | 重複する(共通点を持つ) | int8range(3,7) && int8range(4,12) | true |
| << | 厳密に左に位置する | int8range(1,10) << int8range(100,110) | true |
| >> | 厳密に右に位置する | int8range(50,60) >> int8range(20,30) | true |
| &< | 右側を越えない | int8range(1,20) &< int8range(18,20) | true |
| &> | 左側を越えない | int8range(7,20) &> int8range(5,10) | true |
| -|- | 隣接 | numrange(1.1,2.2) -|- numrange(2.2,3.3) | true |
| + | 結合範囲 | numrange(5,15) + numrange(10,20) | [5,20) |
| * | 交差範囲 | int8range(5,15) * int8range(10,20) | [10,15) |
| – | 差分範囲 | int8range(5,15) – int8range(10,20) | [5,10) |
少し戸惑う「::int4range」 などはこれはこの型だよと明示するために使われています。
@マークは初めて見るので怖いですが、範囲にその値を含むか含まないかなど慣れてしまえばそこまで難しくないです。
true/falseの結果に戸惑った方は以下の記事を参考にしていただければと思います。
範囲関数
範囲用の関数です。
| 関数 | 戻り値型 | 説明 | 例 | 結果 |
|---|---|---|---|---|
| lower(anyrange) | 範囲の要素の型 | 範囲の下限 | lower(numrange(1.1,2.2)) | 1.1 |
| upper(anyrange) | 範囲の要素の型 | 範囲の上限 | upper(numrange(1.1,2.2)) | 2.2 |
| isempty(anyrange) | boolean | 空の範囲か？ | isempty(numrange(1.1,2.2)) | false |
| lower_inc(anyrange) | boolean | 下限は内包されているか？ | lower_inc(numrange(1.1,2.2)) | true |
| upper_inc(anyrange) | boolean | 上限は内包されているか？ | upper_inc(numrange(1.1,2.2)) | false |
| lower_inf(anyrange) | boolean | 下限は無限大か？ | lower_inf(‘(,)’::daterange) | true |
| upper_inf(anyrange) | boolean | 上限は無限大か？ | upper_inf(‘(,)’::daterange) | true |
おわり
閾値や期間などrangeを使えば手早くSQLが書けそうですね。
Range Typeが便利なのですが、境界の仕組みには慣れるまで少し時間がかかりますよね。
