AnalyticDB：Array數群組類型

數群組類型的聲明

數群組類型是通過在數組元素類型名後附加方括弧[]來命名的。

樣本：執行以下命令，建立一個由基礎類型數組構成的表。

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版支援的數組函數如下表。

1
2

1 foo
2 bar
NULL baz

樣本

• 樣本一：使用array_dims函數查詢數組的當前維數，樣本如下。

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

  返回結果如下。

  array_dims
  ------------
   [1:2][1:2]
  (1 row)

• 樣本二：使用array_upper和array_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_prepend、array_append或array_cat函數構造數組值。array_prepend和array_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版支援的數組操作符如下表。

使用索引加速數組檢索

當數組中包含非常多元素，需要使用@>操作符檢索是否包含某個元素時，數組元素越多，檢索效能也越差，您可以通過構建索引來加速。

本文以如下樣本，展示如何通過索引加速數組檢索。

-- 建立一個包含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文檔。