Range Type について初心者がまとめてみる!PostgreSQL

2021-02-05

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]'::int4rangetrue
<>等しくない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'::timestamptrue
<@・・・により範囲が包含される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)
https://www.postgresql.jp/document/9.2/html/functions-range.htmlより

少し戸惑う「::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
https://www.postgresql.jp/document/9.2/html/functions-range.html より

おわり

閾値や期間などrangeを使えば手早くSQLが書けそうですね。
Range Typeが便利なのですが、境界の仕組みには慣れるまで少し時間がかかりますよね。

PostgreSQL, SQL

Posted by Nakamoto