Kategorien:

Funktionen für semistrukturierte und strukturierte Daten (Array/Objekt)

ARRAY_INTERSECTION

Gibt ein Array zurück, das die übereinstimmenden Elemente in den beiden Eingabearrays enthält.

Die Funktion ist NULL-sicher, d. h. sie behandelt NULL-Werte beim Vergleichen der Gleichheit als bekannte Werte.

Siehe auch:

ARRAY_EXCEPT, ARRAYS_OVERLAP

Syntax

ARRAY_INTERSECTION( <array1> , <array2> )

Argumente

array1

Ein Array, das zu vergleichende Elemente enthält.

array2

Ein Array, das zu vergleichende Elemente enthält.

Rückgabewerte

Diese Funktion gibt ein ARRAY zurück, das die übereinstimmenden Elemente der Eingabearrays enthält.

Wenn sich keine Elemente überlappen, gibt die Funktion ein leeres Array zurück.

Wenn eines oder beide Argumente NULL sind, gibt die Funktion NULL zurück.

Die Reihenfolge der Werte innerhalb des zurückgegebenen Arrays ist nicht angegeben.

Nutzungshinweise

  • Beim Vergleichen von Daten des Typs OBJECT müssen die Objekte identisch sein, um als übereinstimmend angesehen zu werden. Weitere Details dazu finden Sie unter Beispiele (unter diesem Thema).

  • Der Unterschied zwischen ARRAY_INTERSECTION und der zugehörigen ARRAYS_OVERLAP-Funktion besteht darin, dass die ARRAYS_OVERLAP-Funktion einfach TRUE oder FALSE zurückgibt, während ARRAY_INTERSECTION die sich tatsächlich überlappenden Werte zurückgibt.

  • In Snowflake sind Arrays Multimengen, keine Mengen. Mit anderen Worten, Arrays können mehrere Kopien desselben Werts enthalten. ARRAY_INTERSECTION vergleicht Arrays mithilfe der Multimengen-Semantik (manchmal auch als „Bag-Semantik“ bezeichnet). Dies bedeutet, dass die Funktion mehrere Kopien desselben Werts zurückgeben kann. Wenn ein Array N Kopien eines Werts und das andere Array M Kopien desselben Werts hat, ist die Anzahl der Kopien im zurückgegebenen Array kleiner als N oder M. Wenn beispielsweise N = 4 und M = 2 ist, dann enthält der zurückgegebene Wert 2 Kopien.

  • Wenn Sie strukturierte ARRAYs übergeben:

    • Die Funktion gibt ein ARRAY eines Typs zurück, der beide Eingabetypen aufnehmen kann.

    • Das ARRAY im zweiten Argument muss vergleichbar mit dem ARRAY im ersten Argument sein.

Beispiele

Das folgende Beispiel zeigt die einfache Verwendung der Funktion:

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

Die Sätze können mehr als einen übereinstimmenden Wert haben:

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"                                                |
| ]                                                    |
+------------------------------------------------------+

Möglicherweise gibt es mehr als eine Instanz desselben übereinstimmenden Werts. In der folgenden Abfrage enthält ein Array beispielsweise drei Kopien des Buchstabens „B“, und das andere Array enthält zwei Kopien des Buchstabens „B“. Das Ergebnis enthält zwei Übereinstimmungen:

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"                                                         |
| ]                                                             |
+---------------------------------------------------------------+

Im nächsten Beispiel wird eine größere Menge von Daten verwendet:

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

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    | ]      |                        |                                    |
|    | ]      |        |                        |                                    |
+----+--------+--------+------------------------+------------------------------------+

Das folgende Beispiel zeigt die Verwendung mit NULL-Werten:

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        |               | ]                                  |
|    | ]            | ]            |               |                                    |
+----+--------------+--------------+---------------+------------------------------------+

Das folgende Beispiel zeigt die Verwendung mit dem Datentyp 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                         |
|    |   }         |   }         |                                                             |   }                                |
|    | ]           | ]           |                                                             | ]                                  |
+----+-------------+-------------+-------------------------------------------------------------+------------------------------------+

Obwohl NULL-Werte in einem Array als vergleichbare Werte behandelt werden, lautet das Ergebnis NULL, wenn Sie NULL anstelle eines Arrays übergeben:

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