更新时间:2024-09-02 GMT+08:00

范围类型

范围类型是表示某些元素类型(称为范围的子类型)的值范围的数据类型。例如,时间戳范围可用于表示保留会议室的时间范围。在这种情况下,数据类型为tsrange(“时间戳范围”的缩写),时间戳是子类型。子类型必须具有总体的顺序,以便很好地定义元素值是在值的范围内、之前还是之后。

范围类型可以表达单一范围值中的多个元素值,并且可以很清晰地表达诸如范围重叠等概念。用于计划安排的时间和日期范围,也可以用于价格范围或仪器的测量范围等。

内置范围类型

GaussDB(DWS)带有下列内置范围类型:

  • int4range — integer的范围
  • int8range — bigint的范围
  • numrange — numeric的范围
  • tsrange — 不带时区的timestamp的范围
  • tstzrange — 带时区的timestamp的范围
  • daterange — date的范围

GaussDB(DWS)暂不支持用户自定义范围类型。

创建表reservation并插入数据,其中during字段类型为tsrange:

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

查询范围是否包含:

SELECT int4range(10, 20) @> 3;
 ?column?
----------
 f
(1 row)

查询范围是否重叠:

SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
 ?column?
----------
 t
(1 row)

抽取范围的上界:

SELECT upper(int8range(15, 25));
 upper
-------
    25
(1 row)

计算范围的交集:

SELECT int4range(10, 20) * int4range(15, 25);
 ?column?
----------
 [15,20)
(1 row)

查询范围是否为空:

SELECT isempty(numrange(1, 5));
 isempty
---------
 f
(1 row)

包含和排除边界

每一个非空范围都有两个界限:下界和上界。这些值之间的所有点都被包括在范围内。包含界限意味着边界点本身也被包括在范围内,而排除边界意味着边界点不被包括在范围内。

在范围的文本形式中,包含下界用“[”表示,排除下界用“(”表示。同样,包含上界用“]”表示,排除上界用“)”表示。

函数lower_inc(anyrange)lower_inc(anyrange)分别测试一个范围值的上下界。

无限(无界)范围

范围的下界可以省略,这意味着所有小于上界的值都包括在范围中,例如(,3]。同样,范围的上界被省略,则所有大于下界的值都包括在范围中。如果下界和上界都被省略,则该元素类型的所有值都被认为在范围内。如果缺失的边界指定为包含则自动将包含转换为排除,例如[,]将转换为(,)。可以将这些缺失的值视为+/-无穷大,但它们是特殊的范围类型值,并且被视为超出任何范围元素类型的+/-无穷大值。

具有“无穷大”概念的元素类型可以将其作为显式边界值。例如,在时间戳范围,[today,infinity)不包括特殊的timestamp值infinity,尽管[today,infinity]包括它,就好比 [today,)和[today,]。

函数lower_inf(anyrange)upper_inf(anyrange)分别测试一个范围的无限上下界。

范围的输入/输出

范围值的输入必须遵循下列模式之一:

     (lower-bound,upper-bound)
     (lower-bound,upper-bound]
     [lower-bound,upper-bound)
     [lower-bound,upper-bound]
     empty

圆括号或方括号指示上下界是否为排除的或者包含的。最后一个模式是empty,它表示一个空范围(一个不包含点的范围)。

lower-bound可以作为子类型的合法输入的一个字符串,或者是空,表示没有下界。同样,upper-bound可以是作为子类型的合法输入的一个字符串,或者是空,表示没有上界。

每个界限值可以使用双引号引用。如果界限值包含圆括号、方括号、逗号、双引号或反斜线时,则必须使用双引号引用,否则这些符号会被认作范围语法的一部分。要想把双引号或反斜线放在被引用的界限值中,需要在双引号或反斜线前面加一个反斜线(在双引号引用的界限值中的一对双引号表示一个双引号字符,这与SQL字符串中的单引号规则类似)。此外,可以避免引用并且使用反斜线转义来保护所有数据字符,否则它们会被当做返回语法的一部分。什么都不写则表示一个无限界限,因此,要表示空字符串的界限值,可以写成""。

范围值前后允许有空格,但是圆括号或方括号之间的任何空格会被当做上下界值的一部分(取决于元素类型,它可能是有意义的也可能是无意义的)。

示例

查询包括3,不包括7,并且包括3和7之间的所有点:

SELECT '[3,7)'::int4range;
 int4range
-----------
 [3,7)
(1 row)

查询既不包括3也不包括 7,但是包括之间的所有点:

SELECT '(3,7)'::int4range;
 int4range
-----------
 [4,7)
(1 row)

查询只包括单独一个点4:

SELECT '[4,4]'::int4range;
 int4range
-----------
 [4,5)
(1 row)

查询不包括点(并且将被标准化为 '空'):

SELECT '[4,4)'::int4range;
 int4range
-----------
 empty
(1 row)

构造范围

每一种范围类型都有一个与其同名的构造器函数。使用构造器函数比写一个范围文字常数更方便,因为它避免了对界限值的额外引用。构造器函数接受两个或三个参数。两个参数的形式以标准的形式构造一个范围(包含下界,排除上界),而三个参数的形式按照第三个参数指定的界限形式构造一个范围。第三个参数必须是下列字符串之一: “()”、 “(]”、 “[)”或者 “[]”。 例如:

完整形式是:下界、上界以及指示界限包含性/排除性的文本参数:

SELECT numrange(1.0, 14.0, '(]');
  numrange
------------
 (1.0,14.0]
(1 row)

如果第三个参数被忽略,则假定为 '[)':

SELECT numrange(1.0, 14.0);
  numrange
------------
 [1.0,14.0)
(1 row)

尽管这里指定了'(]',单返回结果时该值将被转换成标准形式,因为int8range是一种离散范围类型

SELECT int8range(1, 14, '(]');
 int8range
-----------
 [2,15)
(1 row)

界限使用NULL导致范围是无界的:

SELECT numrange(NULL, 2.2);
 numrange
----------
 (,2.2)
(1 row)

离散范围类型

离散范围是指其元素类型具有定义明确的“步长”的范围,如integer或date。在这些类型中,当两个元素之间没有有效值时,它们可以被说成是相邻。

离散范围类型的每个元素值都有一个明确的“下一个”或“上一个”值。这样就可以通过选择下一个或上一个元素值,在范围界限的包含和排除表达之间转换。例如,在整数范围类型中,[4,8]和(3,9)表示相同的值集合,但对于超过numeric的范围,情况并非如此。

离散范围类型应具有识别元素类型所需步长的规范函数。规范化函数负责将范围类型的等价值转换为具有相同的表示,特别是与包含或者排除界限一致。如果未指定规范化函数,则具有不同格式的范围将始终被视为不相等,即使它们实际上是表达相同的一组值。

内置范围类型int4range、int8range和daterange都使用规范形式,该形式包括下界并且排除上界,也就是[)。但是,用户定义的范围类型可以使用其他约定。

自定义范围类型

用户可以定义范围类型。例如,要创建一个 subtype float8的范围类型:

CREATE TYPE floatrange AS RANGE (
        subtype = float8,
        subtype_diff = float8mi
    );

SELECT '[1.234, 5.678]'::floatrange;

索引

可以为范围类型的表列创建GiST索引。例如:

CREATE TABLE reservation (room int, during tsrange);
CREATE INDEX reservation_idx ON reservation USING GIST (during);

GiST索引可以加速涉及以下范围操作符的查询: =、 &&、 <@、 @>、 <<、 >>、 -|-、 &<以及 &> 。详见范围操作符

此外,也可以在范围类型的表列上创建B-tree索引。对于这些索引类型,有用的范围操作就是等值。范围类型的B-tree支持主要是为了允许在查询内部进行排序,而不是创建真正的索引。