文档

Array数组类型

更新时间:

云原生数据仓库AnalyticDB PostgreSQL版支持将字段定义为数组类型。数组类型可以是任何基础类型、用户自定义类型、枚举类型或复合类型。本文介绍云原生数据仓库AnalyticDB PostgreSQL版数组类型的使用,包括建表、数据写入、查询、修改及数组的操作符和函数。

数组类型的声明

数组类型是通过在数组元素类型名后附加方括号[]来命名的。

示例:执行以下命令,创建一个由基础类型数组构成的表。

CREATE TABLE sal_emp (
    id              int,
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
) DISTRIBUTED BY(id);

表的详细信息如下:

  • 表名:sal_emp

  • id字段:int类型,记录员工号。

  • name字段:text类型字符串,记录员工姓名。

  • pay_by_quarter字段:一维integer数组,记录员工每个季度的薪资。

  • schedule字段:两维text数组,记录员工周计划。

  • 该表按id哈希分布。

数组值写入

通过INSERT语句进行数组值写入,示例如下。

INSERT INTO sal_emp
    VALUES (1,
    'Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES (2,
    'Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

查询数据写入情况。

SELECT * FROM sal_emp;

返回结果如下。

 id | name  |      pay_by_quarter       |                 schedule
----+-------+---------------------------+-------------------------------------------
  2 | Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
  1 | Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
(2 rows)

访问数组

AnalyticDB PostgreSQL版使用以1为基的数组,即一个n元素的数组从array[1]开始,到array[n]结束。您也可以访问一个数组的任意矩形片段(子数组),对于一维或更多维数组, 可以用下标下界:下标上界表示一个数组的某个片段。

本章以查询sal_emp表中的数据为例,演示如何访问数组的一个元素。

  • 示例一: 查询在第二季度薪资发生变化的员工姓名,示例如下。

    SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

    返回结果如下。

     name
    -------
     Carol
    (1 row)
  • 示例二:查询所有员工第三季度的薪资,示例如下。

    SELECT pay_by_quarter[3] FROM sal_emp;

    返回结果如下。

     pay_by_quarter
    ----------------
              10000
              25000
    (2 rows)
  • 示例三:查询员工Bill该周前两天的第一件计划,示例如下。

    SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

    返回结果如下。

            schedule
    ------------------------
     {{meeting},{training}}
    (1 row)

数组函数

AnalyticDB PostgreSQL版支持的数组函数如下表。

函数

返回类型

描述

示例

结果

array_append(anyarray, anyelement)

anyarray

向数组末尾添加元素。

array_append(ARRAY[1,2], 3)

{1,2,3}

array_cat(anyarray, anyarray)

anyarray

连接两个数组。

array_cat(ARRAY[1,2,3], ARRAY[4,5])

{1,2,3,4,5}

array_ndims(anyarray)

int

返回数组的维数。

array_ndims(ARRAY[[1,2,3], [4,5,6]])

2

array_dims(anyarray)

text

返回数组维数的文本表示。

array_dims(ARRAY[[1,2,3], [4,5,6]])

[1:2][1:3]

array_fill(anyelement, int[], [, int[]])

anyarray

返回数组初始化提供的值和维度,可选下界不为1。

array_fill(7, ARRAY[3], ARRAY[2])

[2:4]={7,7,7}

array_length(anyarray, int)

int

返回数组维度的长度。

array_length(array[1,2,3], 1)

3

array_lower(anyarray, int)

int

返回数组维数的下界。

array_lower('[0:2]={1,2,3}'::int[], 1)

0

array_prepend(anyelement, anyarray)

anyarray

向数组开头添加元素。

array_prepend(1, ARRAY[2,3])

{1,2,3}

array_remove(anyarray, anyelement)

anyarray

从数组中删除所有等于给定值的元素(数组必须是一维的)。

array_remove(ARRAY[1,2,3,2], 2)

{1,3}

array_replace(anyarray, anyelement, anyelement)

anyarray

用新值替换每个等于给定值的数组元素。

array_replace(ARRAY[1,2,5,4], 5, 3)

{1,2,3,4}

array_to_string(anyarray, text [, text])

text

使用分隔符和NULL字符串连接数组元素。

array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*')

1,2,3,*,5

array_upper(anyarray, int)

int

返回数组维数的上界。

array_upper(ARRAY[1,8,3,7], 1)

4

cardinality(anyarray)

int

返回数组中的总元素数量。如果数组是空,则为0。

cardinality(ARRAY[[1,2],[3,4]])

4

string_to_array(text, text [, text])

text[]

使用指定的分隔符和NULL字符串把字符串分裂成数组元素。

string_to_array('xx~^~yy~^~zz', '~^~', 'yy')

{xx,NULL,zz}

unnest(anyarray)

setof anyelement

扩大一个数组为一组行。

unnest(ARRAY[1,2])

1
2

unnest(anyarray, anyarray [, ...])

setof anyelement, anyelement [, ...]

扩展多个数组(可能是不同的类型)到一组行。 仅允许在FROM子句中使用。

unnest(ARRAY[1,2],ARRAY['foo','bar','baz'])

1 foo
2 bar
NULL baz
说明
  • string_to_array函数中,如果分隔符参数是NULL, 输入字符串中的每个字符将在结果数组中变成独立的元素。如果分隔符是一个空白字符串, 那么整个输入字符串将变为一个元素的数组。否则输入字符串将在每个分隔字符串处分裂。

  • string_to_array函数中,如果省略NLL字符串参数或为NULL, 将没有输入字符串的子串被NULL代替。在array_to_string函数中, 如果省略NULL字符串参数或为NULL,在数组中的任何NULL元素将被跳过, 并且不再输出字符串中出现。

示例

  • 示例一:使用array_dims函数查询数组的当前维数,示例如下。

    SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

    返回结果如下。

    array_dims
    ------------
     [1:2][1:2]
    (1 row)
  • 示例二:使用array_upperarray_lower函数查询数组特定维的上界和下界,示例如下。

    SELECT array_upper(schedule, 1), array_lower(schedule, 1) FROM sal_emp WHERE name = 'Carol';

    返回结果如下。

     array_upper | array_lower
    -------------+-------------
               2 |           1
    (1 row)
  • 示例三:使用array_length函数查看特定维数数组的长度。

    SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';

    返回结果如下。

     array_length
    --------------
                2
    (1 row)
  • 示例四:使用cardinality函数,查看返数组中所有维度元素的总数量。

    SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';

    返回结果如下。

     cardinality
    -------------
               4
    (1 row)

修改数组

AnalyticDB PostgreSQL版支持修改数组,包含如下几种修改方式:

  • 完全代替一个数组值,示例如下。

    UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
        WHERE name = 'Carol';
  • 只更新数组中的某一个元素,示例如下。

    UPDATE sal_emp SET pay_by_quarter[4] = 15000
        WHERE name = 'Bill';
  • 更新一个数组的某个片段,示例如下。

    UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
        WHERE name = 'Carol';
  • 使用连接操作符||构造新的数组值。连接操作符允许把一个元素压入一维数组的开头或者结尾。示例如下。

    SELECT ARRAY[1,2] || ARRAY[3,4];

    返回结果如下。

     ?column?
    -----------
     {1,2,3,4}
    (1 row)

    连接操作符||也支持两个N维的数组,或者一个N维和一个N+1维的数组,示例如下。

    SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];

    返回结果如下。

          ?column?
    ---------------------
     {{5,6},{1,2},{3,4}}
    (1 row)
  • 使用array_prependarray_appendarray_cat函数构造数组值。array_prependarray_append只支持一维数组,array_cat支持多维数组。

    使用连接操作符比直接使用这些函数更好。实际上, 这些函数主要用于实现连接操作符。在用户定义的创建函数里直接使用函数可能有必要。通过函数构造数组值的示例如下。

    SELECT array_prepend(1, ARRAY[2,3]);
     array_prepend
    ---------------
     {1,2,3}
    (1 row)
    
    SELECT array_append(ARRAY[1,2], 3);
     array_append
    --------------
     {1,2,3}
    (1 row)
    
    SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
     array_cat
    -----------
     {1,2,3,4}
    (1 row)
    
    SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
          array_cat
    ---------------------
     {{1,2},{3,4},{5,6}}
    (1 row)
    
    SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
          array_cat
    ---------------------
     {{5,6},{1,2},{3,4}}

在数组中检索

检索一个数组中的某个数值时,必须检索该数组中的每一个值。您可以通过数组操作符检索符合条件的记录。

  • 示例一:检索pay_by_quarter字段中包含10000元素的记录。

    SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
  • 示例二:检索数组中pay_by_quarter字段所有元素值都等于10000的记录。

    SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
  • 示例三:使用&&操作符检索,左操作数是否与右操作数重叠。

    SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];

AnalyticDB PostgreSQL版支持的数组操作符如下表。

操作符

描述

示例

结果

=

等于

ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]

t

<>

不等于

ARRAY[1,2,3] <> ARRAY[1,2,4]

t

<

小于

ARRAY[1,2,3] < ARRAY[1,2,4]

t

>

大于

ARRAY[1,4,3] > ARRAY[1,2,4]

t

<=

小于或等于

ARRAY[1,2,3] <= ARRAY[1,2,3]

t

>=

大于或等于

ARRAY[1,4,3] >= ARRAY[1,4,3]

t

@>

包含

ARRAY[1,4,3] @> ARRAY[3,1]

t

<@

被包含于

ARRAY[2,7] <@ ARRAY[1,7,4,2,6]

t

&&

重叠(有共同元素)

ARRAY[1,4,3] && ARRAY[2,1]

t

||

数组与数组连接(一维)

ARRAY[1,2,3] || ARRAY[4,5,6]

{1,2,3,4,5,6}

||

数组与数组连接(多维)

ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]

{{1,2,3},{4,5,6},{7,8,9}}

||

元素与数组连接

3 || ARRAY[4,5,6]

{3,4,5,6}

||

数组与元素连接

ARRAY[4,5,6] || 7

{4,5,6,7}

使用索引加速数组检索

当数组中包含非常多元素,需要使用@>操作符检索是否包含某个元素时,数组元素越多,检索性能也越差,您可以通过构建索引来加速。

重要

GIN索引仅支持资源类型为存储弹性模式的实例,不支持Serverless实例。

本文以如下示例,展示如何通过索引加速数组检索。

-- 创建一个包含int数组列的表。
REATE TABLE multivalue (
    id      int,
    values  int[]
) DISTRIBUTED BY(id);

-- 向表中写入数据,共写入1000行,每行数据中的values数组包含1000000个元素。
INSERT INTO multivalue SELECT g, ARRAY(SELECT generate_series(1, 1000000)) FROM generate_series(1, 1000) as g;

在没有索引的情况下,查询数组元素中包含800000这个元素的记录,需要通过seq scan,性能较差。

EXPLAIN ANALYZE SELECT id FROM multivalue WHERE values @> ARRAY[800000];
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1.00 rows=1 width=4) (actual time=711.216..3478.225 rows=1000 loops=1)
   ->  Seq Scan on multivalue  (cost=0.00..1.00 rows=1 width=4) (actual time=10.420..2629.403 rows=508 loops=1)
         Filter: ("values" @> '{800000}'::integer[])
 Planning time: 0.080 ms
   (slice0)    Executor memory: 39K bytes.
   (slice1)    Executor memory: 12733K bytes avg x 2 workers, 12733K bytes max (seg0).
 Memory used:  1048576kB
 Optimizer: Postgres query optimizer
 Execution time: 3483.157 ms
(9 rows)

Time: 3483.667 ms

对数组列创建GIN索引后,相同查询通过索引扫描,性能大幅提升,耗时由3483.667毫秒(ms)下降至9.731毫秒(ms)。

CREATE INDEX idx_values on multivalue USING GIN (values);

EXPLAIN ANALYZE SELECT id FROM multivalue WHERE values @> ARRAY[800000];
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=12.04..14.10 rows=5 width=4) (actual time=3.727..4.127 rows=1000 loops=1)
   ->  Bitmap Heap Scan on multivalue  (cost=12.04..14.10 rows=3 width=4) (actual time=1.826..1.872 rows=508 loops=1)
         Recheck Cond: ("values" @> '{800000}'::integer[])
         ->  Bitmap Index Scan on idx_values  (cost=0.00..12.04 rows=3 width=0) (actual time=1.462..1.462 rows=508 loops=1)
               Index Cond: ("values" @> '{800000}'::integer[])
 Planning time: 0.155 ms
   (slice0)    Executor memory: 49K bytes.
   (slice1)    Executor memory: 331K bytes avg x 2 workers, 331K bytes max (seg0).  Work_mem: 9K bytes max.
 Memory used:  1048576kB
 Optimizer: Postgres query optimizer
 Execution time: 9.138 ms
(11 rows)

Time: 9.731 ms

当然,通过GIN索引提升数组检索性能的同时,也会带来写入性能的下降(因为写入数据时需要更新索引),另外索引数据本身也会占用额外空间。业务在具体使用时,可以从查询性能,写入性能,存储空间三个维度综合来选择是否需要索引。

相关文档

更多关于Array类型的使用,请参见PostgreSQL文档