- 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:
Syntax¶
ARRAY_INTERSECTION( <array1> , <array2> )
Argumente¶
array1Ein Array, das zu vergleichende Elemente enthält.
array2Ein 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
OBJECTmü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_INTERSECTIONund der zugehörigenARRAYS_OVERLAP-Funktion besteht darin, dass dieARRAYS_OVERLAP-Funktion einfachTRUEoderFALSEzurückgibt, währendARRAY_INTERSECTIONdie 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_INTERSECTIONvergleicht 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.
Beide Argumente müssen entweder strukturierte ARRAYs oder semistrukturierte ARRAYs sein.
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 | +------------------------------------------------+