ARRAYS_OVERLAP

説明

ARRAYS_OVERLAP は、二つの配列に共通する要素が一つ以上あるかどうかを判定します。共通する要素があれば true、なければ false を返します。

構文

ARRAYS_OVERLAP(arr1, arr2)

パラメータ

• arr1: 一つ目の配列。型は ARRAY<T> です。

• arr2: 二つ目の配列。型は ARRAY<T> です。

  • 両方の配列の要素型 T は、同じ型であるか、相互に暗黙的に変換できる必要があります。
  • 要素型 T には、数値型、文字列型、日付・時刻型、IP 型を使用できます。

戻り値

• BOOLEAN 型を返します。

  • 二つの配列に共通する要素がある場合は true を返します。
  • 共通する要素がない場合は false を返します。

使用上の注意

1. 比較には要素の等値判定（= 演算子）を使用します。
2. この関数では NULL と NULL を等しいとみなします（使用例を参照）。
3. テーブル作成文で転置インデックスを指定すると、実行を高速化できます（使用例を参照）。
   • 関数を述語条件として使用する場合、転置インデックスによって実行が高速化されます。
   • 関数をクエリ結果に使用する場合、転置インデックスによる高速化はありません。
4. データクレンジング、タグの照合、ユーザー行動の共通部分の分析などでよく使用します。

使用例

1. 基本例

   SELECT ARRAYS_OVERLAP(ARRAY('hello', 'aloha'), ARRAY('hello', 'hi', 'hey'));
   +----------------------------------------------------------------------+
   | ARRAYS_OVERLAP(ARRAY('hello', 'aloha'), ARRAY('hello', 'hi', 'hey')) |
   +----------------------------------------------------------------------+
   |                                                                    1 |
   +----------------------------------------------------------------------+
   
   SELECT ARRAYS_OVERLAP(ARRAY('Pinnacle', 'aloha'), ARRAY('hi', 'hey'));
   +----------------------------------------------------------------+
   | ARRAYS_OVERLAP(ARRAY('Pinnacle', 'aloha'), ARRAY('hi', 'hey')) |
   +----------------------------------------------------------------+
   |                                                              0 |
   +----------------------------------------------------------------+

2. 無効な引数の型: サポートされない型を渡すと INVALID_ARGUMENT が返されます。

   SELECT ARRAYS_OVERLAP(ARRAY(ARRAY('hello', 'aloha'), ARRAY('hi', 'hey')), ARRAY(ARRAY('hello', 'hi', 'hey'), ARRAY('aloha', 'hi')));
   ERROR 1105 (HY000): errCode = 2, detailMessage = arrays_overlap does not support types: ARRAY<ARRAY<TEXT>>

3. 入力配列 ARRAY が NULL の場合、戻り値は NULL です。

   SELECT ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), NULL);
   +-----------------------------------------------+
   | ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), NULL) |
   +-----------------------------------------------+
   |                                          NULL |
   +-----------------------------------------------+
   
   SELECT ARRAYS_OVERLAP(NULL, NULL);
   +----------------------------+
   | ARRAYS_OVERLAP(NULL, NULL) |
   +----------------------------+
   |                        NULL |
   +----------------------------+

4. 入力配列 ARRAY に NULL が含まれる場合、NULL と NULL は等しいとみなされます。

    SELECT ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), ARRAY('HELLO', NULL));
    +---------------------------------------------------------------+
    | ARRAYS_OVERLAP(ARRAY('HELLO', 'ALOHA'), ARRAY('HELLO', NULL)) |
    +---------------------------------------------------------------+
    |                                                             1 |
    +---------------------------------------------------------------+
   
    SELECT ARRAYS_OVERLAP(ARRAY('PICKLE', 'ALOHA'), ARRAY('HELLO', NULL));
    +----------------------------------------------------------------+
    | ARRAYS_OVERLAP(ARRAY('PICKLE', 'ALOHA'), ARRAY('HELLO', NULL)) |
    +----------------------------------------------------------------+
    |                                                             0  |
    +----------------------------------------------------------------+
   
    SELECT ARRAYS_OVERLAP(ARRAY(NULL), ARRAY('HELLO', NULL));
    +---------------------------------------------------+
    | ARRAYS_OVERLAP(ARRAY(NULL), ARRAY('HELLO', NULL)) |
    +---------------------------------------------------+
    |                                                 1 |
    +---------------------------------------------------+

5. 転置インデックスを使用してクエリを高速化する例

   -- Create table with inverted index
   CREATE TABLE IF NOT EXISTS arrays_overlap_table (
       id INT,
       array_column ARRAY<STRING>,
       INDEX idx_array_column (array_column) USING INVERTED -- only non-tokenized inverted indexes are allowed
   ) ENGINE=OLAP
   DUPLICATE KEY(id)
   DISTRIBUTED BY HASH(id) BUCKETS 1
   PROPERTIES (
   "replication_num" = "1"
   );
   
   -- Insert two rows
   INSERT INTO arrays_overlap_table (id, array_column) VALUES (1, ARRAY('HELLO', 'ALOHA')), (2, ARRAY('NO', 'WORLD'));

• 関数を述語条件として使用する場合、転置インデックスによって実行が高速化されます。

  SELECT * from arrays_overlap_table WHERE ARRAYS_OVERLAP(array_column, ARRAY('HELLO', 'PICKLE')); 
  +------+--------------------+
  | id   | array_column       |
  +------+--------------------+
  |    1 | ["HELLO", "ALOHA"] |
  +------+--------------------+
  

• When the function is used in the query result, the inverted index will not accelerate execution

  SELECT ARRAYS_OVERLAP(array_column, ARRAY('HELLO', 'PICKLE')) FROM arrays_overlap_table;
  +--------------------------------------------------------+
  | ARRAYS_OVERLAP(array_column, ARRAY('HELLO', 'PICKLE')) |
  +--------------------------------------------------------+
  |                                                      1 |
  |                                                      0 |
  +--------------------------------------------------------+