カテゴリ:

半構造化データ関数と構造化データ関数 (配列/オブジェクト)

ARRAY_INTERSECTION

2つの入力配列の一致する要素を含む配列を返します。

この関数は NULL-safeです。つまり、 NULLs を等値比較の既知の値として扱います。

こちらもご参照ください。

ARRAY_EXCEPTARRAYS_OVERLAP

構文

ARRAY_INTERSECTION( <array1> , <array2> )
Copy

引数

array1

比較する要素を含む配列。

array2

比較する要素を含む配列。

戻り値

この関数は、一致する入力配列の要素を含む ARRAY を返します。

重複する要素がない場合、関数は空の配列を返します。

1つまたは両方の引数が NULLの場合、関数は NULLを返します。

返される配列内の値の順序は指定されていません。

使用上の注意

  • OBJECT のデータ型を比較する場合、オブジェクトは一致すると見なされるために同一でなければなりません。詳細については、このトピックの をご参照ください。

  • ARRAY_INTERSECTION と、関連する ARRAYS_OVERLAP 関数の違いは、 ARRAYS_OVERLAP 関数は TRUE または FALSE を返すのに対して、 ARRAY_INTERSECTION は実際に重複する値を返すことです。

  • Snowflakeでは、配列はセットではなくマルチセットです。つまり、配列には同じ値の複数のコピーを含めることができます。 ARRAY_INTERSECTION は、マルチセットセマンティクス(「バッグセマンティクス」とも呼ばれる)を使用して配列を比較します。つまり、関数は同じ値の複数のコピーを返すことができます。一方の配列に値のN個のコピーがあり、もう一方の配列に同じ値のM個のコピーがある場合、返される配列のコピー数はNまたはMのうち小さい方です。例えば、Nが4およびMが2の場合、返される値には2個のコピーが含まれます。

  • どちらの引数も 構造化 ARRAYs または 半構造化 ARRAYs のいずれかである必要があります。

  • 構造化 ARRAYs を渡す場合:

    • この関数は、両方の入力型に対応できる型の ARRAY を返します。

    • 第2引数の ARRAY は、第1引数の ARRAY と 比較可能 である必要があります。

この例は、関数の簡単な使用法を示しています。

SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B'), 
                          ARRAY_CONSTRUCT('B', 'C'));
+------------------------------------------------------+
| ARRAY_INTERSECTION(ARRAY_CONSTRUCT('A', 'B'),        |
|                           ARRAY_CONSTRUCT('B', 'C')) |
|------------------------------------------------------|
| [                                                    |
|   "B"                                                |
| ]                                                    |
+------------------------------------------------------+
Copy

セットには複数の一致する値がある場合があります。

SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B', 'C'), 
                          ARRAY_CONSTRUCT('B', 'C'));
+------------------------------------------------------+
| ARRAY_INTERSECTION(ARRAY_CONSTRUCT('A', 'B', 'C'),   |
|                           ARRAY_CONSTRUCT('B', 'C')) |
|------------------------------------------------------|
| [                                                    |
|   "B",                                               |
|   "C"                                                |
| ]                                                    |
+------------------------------------------------------+
Copy

同じ一致値のインスタンスが複数存在する場合があります。例えば、次のクエリでは、1つの配列に文字「B」のコピーが3つあり、もう1つの配列に文字「B」のコピーが2つあります。結果には2つの一致が含まれます。

SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B', 'B', 'B', 'C'), 
                          ARRAY_CONSTRUCT('B', 'B'));
+---------------------------------------------------------------+
| ARRAY_INTERSECTION(ARRAY_CONSTRUCT('A', 'B', 'B', 'B', 'C'),  |
|                           ARRAY_CONSTRUCT('B', 'B'))          |
|---------------------------------------------------------------|
| [                                                             |
|   "B",                                                        |
|   "B"                                                         |
| ]                                                             |
+---------------------------------------------------------------+
Copy

この例では、大量のデータを使用しています。

CREATE OR REPLACE TABLE array_demo (ID INTEGER, array1 ARRAY, array2 ARRAY, tip VARCHAR);

INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 1, ARRAY_CONSTRUCT(1, 2), ARRAY_CONSTRUCT(3, 4), 'non-overlapping';
INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 2, ARRAY_CONSTRUCT(1, 2, 3), ARRAY_CONSTRUCT(3, 4, 5), 'value 3 overlaps';
INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 3, ARRAY_CONSTRUCT(1, 2, 3, 4), ARRAY_CONSTRUCT(3, 4, 5), 'values 3 and 4 overlap';
INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 4, ARRAY_CONSTRUCT(NULL, 102, NULL), ARRAY_CONSTRUCT(NULL, NULL, 103), 'NULLs overlap';
INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 5, array_construct(object_construct('a',1,'b',2), 1, 2),
              array_construct(object_construct('a',1,'b',2), 3, 4), 
              'the objects in the array match';
INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 6, array_construct(object_construct('a',1,'b',2), 1, 2),
              array_construct(object_construct('b',2,'c',3), 3, 4), 
              'neither the objects nor any other values match';
INSERT INTO array_demo (ID, array1, array2, tip)
    SELECT 7, array_construct(object_construct('a',1, 'b',2, 'c',3)),
              array_construct(object_construct('c',3, 'b',2, 'a',1)), 
              'the objects contain the same values, but in different order';
Copy
SELECT ID, array1, array2, tip, ARRAY_INTERSECTION(array1, array2) 
    FROM array_demo
    WHERE ID <= 3
    ORDER BY ID;
+----+--------+--------+------------------------+------------------------------------+
| ID | ARRAY1 | ARRAY2 | TIP                    | ARRAY_INTERSECTION(ARRAY1, ARRAY2) |
|----+--------+--------+------------------------+------------------------------------|
|  1 | [      | [      | non-overlapping        | []                                 |
|    |   1,   |   3,   |                        |                                    |
|    |   2    |   4    |                        |                                    |
|    | ]      | ]      |                        |                                    |
|  2 | [      | [      | value 3 overlaps       | [                                  |
|    |   1,   |   3,   |                        |   3                                |
|    |   2,   |   4,   |                        | ]                                  |
|    |   3    |   5    |                        |                                    |
|    | ]      | ]      |                        |                                    |
|  3 | [      | [      | values 3 and 4 overlap | [                                  |
|    |   1,   |   3,   |                        |   3,                               |
|    |   2,   |   4,   |                        |   4                                |
|    |   3,   |   5    |                        | ]                                  |
|    |   4    | ]      |                        |                                    |
|    | ]      |        |                        |                                    |
+----+--------+--------+------------------------+------------------------------------+
Copy

これは、 NULL 値の使用法を示しています。

SELECT ID, array1, array2, tip, ARRAY_INTERSECTION(array1, array2) 
    FROM array_demo
    WHERE ID = 4
    ORDER BY ID;
+----+--------------+--------------+---------------+------------------------------------+
| ID | ARRAY1       | ARRAY2       | TIP           | ARRAY_INTERSECTION(ARRAY1, ARRAY2) |
|----+--------------+--------------+---------------+------------------------------------|
|  4 | [            | [            | NULLs overlap | [                                  |
|    |   undefined, |   undefined, |               |   undefined,                       |
|    |   102,       |   undefined, |               |   undefined                        |
|    |   undefined  |   103        |               | ]                                  |
|    | ]            | ]            |               |                                    |
+----+--------------+--------------+---------------+------------------------------------+
Copy

この例は、 OBJECT データ型での使用法を示しています。

SELECT ID, array1, array2, tip, ARRAY_INTERSECTION(array1, array2) 
    FROM array_demo
    WHERE ID >= 5 and ID <= 7
    ORDER BY ID;
+----+-------------+-------------+-------------------------------------------------------------+------------------------------------+
| ID | ARRAY1      | ARRAY2      | TIP                                                         | ARRAY_INTERSECTION(ARRAY1, ARRAY2) |
|----+-------------+-------------+-------------------------------------------------------------+------------------------------------|
|  5 | [           | [           | the objects in the array match                              | [                                  |
|    |   {         |   {         |                                                             |   {                                |
|    |     "a": 1, |     "a": 1, |                                                             |     "a": 1,                        |
|    |     "b": 2  |     "b": 2  |                                                             |     "b": 2                         |
|    |   },        |   },        |                                                             |   }                                |
|    |   1,        |   3,        |                                                             | ]                                  |
|    |   2         |   4         |                                                             |                                    |
|    | ]           | ]           |                                                             |                                    |
|  6 | [           | [           | neither the objects nor any other values match              | []                                 |
|    |   {         |   {         |                                                             |                                    |
|    |     "a": 1, |     "b": 2, |                                                             |                                    |
|    |     "b": 2  |     "c": 3  |                                                             |                                    |
|    |   },        |   },        |                                                             |                                    |
|    |   1,        |   3,        |                                                             |                                    |
|    |   2         |   4         |                                                             |                                    |
|    | ]           | ]           |                                                             |                                    |
|  7 | [           | [           | the objects contain the same values, but in different order | [                                  |
|    |   {         |   {         |                                                             |   {                                |
|    |     "a": 1, |     "a": 1, |                                                             |     "a": 1,                        |
|    |     "b": 2, |     "b": 2, |                                                             |     "b": 2,                        |
|    |     "c": 3  |     "c": 3  |                                                             |     "c": 3                         |
|    |   }         |   }         |                                                             |   }                                |
|    | ]           | ]           |                                                             | ]                                  |
+----+-------------+-------------+-------------------------------------------------------------+------------------------------------+
Copy

配列 の NULL 値は比較可能な値として扱われますが、配列の 代わりに NULL を渡すと、結果は NULLになります。

SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B'), 
                          NULL);
+------------------------------------------------+
| ARRAY_INTERSECTION(ARRAY_CONSTRUCT('A', 'B'),  |
|                           NULL)                |
|------------------------------------------------|
| NULL                                           |
+------------------------------------------------+
Copy