AnalyticDB:アレイタイプ

ARRAYタイプの宣言

ARRAY型の名前は、配列要素のデータ型名に括弧 ([ ]) を付けることで指定されます。

たとえば、次のステートメントを実行して、基本型配列の列を含むテーブルを作成します。

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

パラメーター：

• sal_emp: テーブルの名前。

• id: 従業員ID。INTタイプです。

• name: 従業員名。TEXTタイプです。

• pay_by_quarter: 四半期ごとの給与。1次元のINTEGER ARRAYタイプです。

• schedule: 週ごとのスケジュール。2次元のTEXT ARRAYタイプです。

• id: テーブルは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 for PostgreSQLは、配列に1ベースの番号付け規則を使用します。 n個の要素を持つ配列は、array[1] で始まり、array[n] で終わります。 配列またはサブ配列の任意の矩形スライスにアクセスすることもできます。 アレイスライスは、1つ以上のアレイ次元について下限: 上限を書くことによって示される。

次の例では、sal_empテーブルの配列要素にアクセスする方法について説明します。

• 例1: 第2四半期に給与が変更された従業員の名前を照会します。

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

  サンプル結果:

   name
  -------
   Carol
  (1 row)

• 例2: すべての従業員の第3四半期の給与を照会します。

  SELECT pay_by_quarter[3] FROM sal_emp;

  サンプル結果:

   pay_by_quarter
  ----------------
            10000
            25000
  (2 rows)

• 例3: 週の最初の2日間のBillのスケジュールの最初の項目を照会します。

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

  サンプル結果:

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

配列関数

次の表に、AnalyticDB for PostgreSQLでサポートされている配列関数を示します。

1
2 

1 foo
2バー
NULL baz 

例

• 例1: array_dims関数を使用して、配列の次元数を照会します。

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

  サンプル結果:

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

• 例2: 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)

• 例3: array_length関数を使用して、指定した配列ディメンションの長さを照会します。

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

  サンプル結果:

   array_length
  --------------
              2
  (1 row)

• 例4: カーディナリティ関数を使用して、配列内のすべてのディメンションの要素の総数を照会します。

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

  サンプル結果:

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

配列の変更

AnalyticDB for 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';

• 配列値を作成するには、連結演算子 (| |) を使用します。 連結演算子は、単一の要素を1次元配列の始めまたは終わりにプッシュすることを可能にする。 例：

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

  サンプル結果:

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

  連結演算子 (| |) は、2つの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関数は、1次元配列のみをサポートします。 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}}

配列で検索

配列内の値を検索するには、各値をチェックする必要があります。 配列演算子を使用して、特定の条件に一致するレコードを検索できます。

• 例1: 配列に10000の値が含まれるpay_by_quarter列の行を照会します。

  SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

• 例2: 配列のすべての値が10000に等しいpay_by_quarter列の行を照会します。

  SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

• 例3:&&演算子を使用して、左オペランドが右オペランドと重複するかどうかを確認します。

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

次の表に、AnalyticDB for PostgreSQLでサポートされている配列演算子を示します。

インデックスを使用した配列検索の高速化

配列に多数の要素が含まれている場合は、@> 演算子を使用して特定の要素を検索できます。 配列内の要素が多いと、検索パフォーマンスが低下します。 この場合、インデックスを作成して配列検索を高速化できます。

次の例では、インデックスを使用して配列検索を高速化する方法について説明します。

-- Create a table that contains a column of the INT ARRAY type. 
REATE TABLE multivalue (
    id      int,
    values  int[]
) DISTRIBUTED BY(id);

-- Write 1,000 rows of data to the table. The values column must contain 1,000,000 array elements in each row. 
INSERT INTO multivalue SELECT g, ARRAY(SELECT generate_series(1, 1000000)) FROM generate_series(1, 1000) as g;

テーブルにインデックスが含まれていません。 配列に800000の値が含まれている値列の行を照会します。 この場合、シーケンシャルスキャンが使用され、パフォーマンスが低下します。

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

values列に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インデックスは、配列検索のパフォーマンスを向上させます。 ただし、データの書き込み時にインデックスを更新する必要があるため、GINインデックスは書き込みパフォーマンスを低下させます。 さらに、インデックスは追加のストレージスペースを占有します。 クエリのパフォーマンス、書き込みパフォーマンス、およびストレージ容量に基づいて、インデックスを使用するかどうかを選択できます。

関連ドキュメント

ARRAY型の使用方法の詳細については、「PostgreSQLドキュメント」をご参照ください。