开发者社区> 云化数据仓库> 正文

ADB PG 6.0 新特性支持 - Range数据类型

简介: Range Typesrange types可以用来标识一些类型(range's subtype)的范围。例如:ranges of timestamp可以用来表示一个会议室预定的起始和结束时间,对应的类型为tsrange,而timestamp为tsrange的subtype。

Range Types

range types可以用来标识一些类型(range's subtype)的范围。例如:ranges of timestamp可以用来表示一个会议室预定的起始和结束时间,对应的类型为tsrange,而timestamp为tsrange的subtype。subtype必须要能够以某种方式来排序,这样才能对某个值确定是在range范围内,还是在之前或之后。
range type十分有效,因为它仅用一个值就可以表示在某个范围内的一连串值,而且对于overlapping ranges的概念,range type也可以简洁清楚的表达出来。使用range type的常见场景如:time and date range在会议议程上的安排。

当前内置支持的range类型

当前ADB PG 6.0将PG的代码基线合入到了9.4,因此也支持了range类型,现在ADB PG 6.0支持的range类型有:

  • int4range — Range of integer
  • int8range — Range of bigint
  • numrange — Range of numeric
  • tsrange — Range of timestamp without time zone
  • tstzrange — Range of timestamp with time zone
  • daterange — Range of date

除此之外,用户也可以使用create type来创建自定义的range数据类型

使用案例

CREATE TABLE reservation (room int, during tsrange);

INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

select * from reservation;
 room |                    during
------+-----------------------------------------------
 1108 | ["2010-01-01 14:30:00","2010-01-01 15:30:00")

-- Containment
postgres=> SELECT int4range(10, 20) @> 3;
 ?column?
----------
 f
 postgres=> SELECT int4range(10, 20) @> 11;
 ?column?
----------
 t
(1 行记录)

-- Overlaps
postgres=> SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
 ?column?
----------
 t
 
 -- Extract the upper bound
postgres=> SELECT upper(int8range(15, 25));
 upper
-------
    25
    
postgres=> SELECT int4range(10, 20) * int4range(15, 25);
 ?column?
----------
 [15,20)

-- Is the range empty?
postgres=> SELECT isempty(numrange(1, 5));
 isempty
---------
 f
(1 行记录)

上下限

range类型的输入值必须符合以下几种模式:

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

其中lower-bound可以为subtype的一个值也可以为空,空表示没有界限。upper-bound类似。
示例:

-- includes 3, does not include 7, and does include all points in between
SELECT '[3,7)'::int4range;

-- does not include either 3 or 7, but includes all points in between
SELECT '(3,7)'::int4range;

-- includes only the single point 4
SELECT '[4,4]'::int4range;

-- includes no points (and will be normalized to 'empty')
SELECT '[4,4)'::int4range;

构建range类型

对于range类型,我们提供了一个构建函数来进行range值的构造,该构建函数相比于上面例子中手写的方式更加方便。构造函数可以接受2个参数或者3个参数。
当输入2个参数时,lower-bound是非包含的,upper-bound是包含的,当输入3个参数时,第三个参数提供了lower-bound和upper-bound是否是包含在range范围内的。所以第三个参数必须取如下值:"()", "(]", "[)", or "[]"。
示例:

-- The full form is: lower bound, upper bound, and text argument indicating
-- inclusivity/exclusivity of bounds.
SELECT numrange(1.0, 14.0, '(]');
-- If the third argument is omitted, '[)' is assumed.
SELECT numrange(1.0, 14.0);
-- Although '(]' is specified here, on display the value will be converted to
-- canonical form, since int8range is a discrete range type (see below).
SELECT int8range(1, 14, '(]');
-- Using NULL for either bound causes the range to be unbounded on that side.
SELECT numrange(NULL, 2.2);

range类型操作运算符和函数

range类型支持的运算符如下:

运算符 描述 示例 结果
= equal int4range(1,5) = '[1,4]'::int4range t
<> not equal numrange(1.1,2.2) <> numrange(1.1,2.3) t
< less than int4range(1,10) < int4range(2,3) t
< greater than int4range(1,10) > int4range(1,5) t
@> contains range int4range(2,4) @> int4range(2,3) t
@> contains element '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp t
<@ range is contained by int4range(2,4) <@ int4range(1,7) t
<@ element is contained by 42 <@ int4range(1,7) f
&& overlap (have points in common) int8range(3,7) && int8range(4,12) t
<< strictly left of int8range(1,10) << int8range(100,110) t
>> strictly right of int8range(50,60) >> int8range(20,30) t
&< does not extend to the right of int8range(1,20) &< int8range(18,20) t
&> does not extend to the left of int8range(7,20) &> int8range(5,10) t
-|- is adjacent to numrange(1.1,2.2) -|- numrange(2.2,3.3) t
+ union numrange(5,15) + numrange(10,20) [5,20)
* intersection int8range(5,15) * int8range(10,20) [10,15)
- difference int8range(5,15) - int8range(10,20) [5,10)

对于比较操作符<, >, <=, and >= ,首先会比较lower-bound,只有当lower-bound相等时才会去比较upper-bound。这些比较操作符在range类型的运算中并不常见,支持他们主要是为了支持range类型的B-tree索引访问。

对于left-of/right-of/adjacent这些运算符,如果元素的数据中有empty range存在,那么直接返回false。也就是说empty range不在任何range的前面或者后面。

Union和difference运算符在得出的结构如果包含两个不重叠的子range(也就是说最终结果无法用一个有效的range类型表示),那么这个运算最后会直接报错。

下面这个表,展示了range类型提供的函数:

函数 返回类型 描述 示例 结果
lower(anyrange) range's element type lower bound of range lower(numrange(1.1,2.2)) 1.1
upper(anyrange) range's element type upper bound of range upper(numrange(1.1,2.2)) 2.2
isempty(anyrange) boolean is the range empty? isempty(numrange(1.1,2.2)) false
lower_inc(anyrange) boolean is the lower bound inclusive? lower_inc(numrange(1.1,2.2)) true
upper_inc(anyrange) boolean is the upper bound inclusive? upper_inc(numrange(1.1,2.2)) false
lower_inf(anyrange) boolean is the lower bound infinite? lower_inf('(,)'::daterange) true
upper_inf(anyrange) boolean is the upper bound infinite? upper_inf('(,)'::daterange) true

当range为empty或者对应的界限为无限的时候,lower和upper函数会返回null。lower_inc, upper_inc, lower_inf,和 upper_inf函数在range为empty的时候都会返回false。

离散的range类型

在离散的range类型中,每个元素都找到与之“相邻”的元素,在“相邻”元素之间没有其它有效的元素,比如整型和date的range类型。与离散相对应的是连续range类型,连续range类型的特点是对于任意range中的两个元素,在其之间总能找到其它有效的元素,比如numeric类型。另外,对于离散的range类型,能够找到某个元素明确的“前”或“后”的元素,所以对于lower-bound或者uppper-bound来说,可以通过使用其“前”或“后”的值来修改是否包含的关系。例如,对于整型的range类型[4,8]可以写成与之等价的(3,9)。但是,这种改写对于连续的range类型却不适用。
对于离散的range类型,需要有一个canonicalization函数,通过这个函数可以得知range中前后元素之间的“步长”。通过这个canonicalization函数可以,可以将离散类型进行如[4,8]到(3,9)的等价转换。而当没有指定canonicalization函数函数时,不同格式的range数据将视为不同的range即使实际上他们是等价的。
ADB PG 6.0内置的range类型int4range, int8range, and daterange都指定了canonicalization函数。

自定义新的range类型

用户可以自己来定义range类型,如:定义一个子类型为float8的range类型:

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

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

上面float8是一个连续的数据类型,因此其对应的range类型为连续的range类型,所以在上面这个例子中可以不用定义canonicalization函数。
当定义离散range类型时,我们需要定义canonicalization函数,这样可以对不同形式的range表示而实际上等价的range数据视为等价。另外,canonicalization函数可以用来规整界限值,例如:在timestamp类型上定义了一个range类型的数据,元素之间的步长设置为1个小时,当某个界限值不是小时的整数倍时,canonicalization函数可以用来规整这个界限值,或者抛出错误。
在自定义range类型数据时,用户也可以指定子类型B-tree索引的操作类,比如排序,来自定义哪些元素将会落到一个给定的区间。
另外,当任何range类型需要和GiST或者SP-GiST来配合使用时,需要定义子类型差值(subtype_diff)函数(尽管在没有定义subtype_diff函数时索引仍然是可以工作的,但是当subtype_diff被定义时,索引使用的效率会更高)。子类型的差值函数的输入是两个子类型值,然后计算返回两者的差值(如:x减y),这个差值类型为float8。在上面float8的自定义range类型中,差值函数为两值相减函数,但是对于其它子类型则需要做一次类型转换使得最后的结果为float8数值类型。

索引

对于range类型,可以为其创建GiST或SP-GiST索引。如:

CREATE INDEX reservation_idx ON reservation USING gist (during);

GiST或SP-GiST索引可以来加速涉及range算子操作的查询,如:=, &&, <@, @>, <<, >>, -|-, &<, and &。
除此之外,也可以为range类型建立B-tree索引和hash索引。对于这两类索引而言,能够起到加速效果的基本只有等值查询,在真实的应用场景中,用户对range使用等值查询或者排序查询是很少的,因此,range类型的B-tree和hash类型的支持,只是为了支持在查询中使用排序和hash,而不是起到实际的加速作用。

参考资料

版权声明:本文中所有内容均属于阿里云开发者社区所有,任何媒体、网站或个人未经阿里云开发者社区协议授权不得转载、链接、转贴或以其他方式复制发布/发表。申请授权请邮件developerteam@list.alibaba-inc.com,已获得阿里云开发者社区协议授权的媒体、网站,在转载使用时必须注明"稿件来源:阿里云开发者社区,原文作者姓名",违者本社区将依法追究责任。 如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:developer2020@service.aliyun.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
上一篇:AnalyticDB for PostgreSQL使用资源队列进行负载管理与隔离 下一篇:ADB PG 6.0 新特性支持 - LATERAL语法
官网链接