- Catégories :
Fonctions de données semi-structurées et structurées (tableau/objet)
ARRAY_INTERSECTION¶
Renvoie un tableau contenant les éléments correspondants dans les deux tableaux en entrée.
La fonction est NULL-safe, ce qui signifie qu’elle considère les NULLs comme des valeurs connues permettant de comparer l’égalité.
- Voir aussi :
Syntaxe¶
ARRAY_INTERSECTION( <array1> , <array2> )
Arguments¶
array1Tableau qui contient des éléments à comparer.
array2Tableau qui contient des éléments à comparer.
Renvoie¶
Cette fonction renvoie un tableau (ARRAY) contenant les éléments des tableaux d’entrée correspondants.
Si aucun élément ne se chevauche, la fonction renvoie un tableau vide.
Si l’un des arguments ou les deux sont NULL, la fonction renvoie NULL.
L’ordre des valeurs dans le tableau renvoyé n’est pas spécifié.
Notes sur l’utilisation¶
Lors de la comparaison de données de type
OBJECT, les objets doivent être identiques pour être considérés comme correspondants. Pour plus de détails, voir Exemples (dans ce chapitre).La différence entre la fonction
ARRAY_INTERSECTIONet la fonctionARRAYS_OVERLAPassociée est que la fonctionARRAYS_OVERLAPrenvoie simplementTRUEouFALSE, tandis queARRAY_INTERSECTIONrenvoie les valeurs qui se chevauchent.Dans Snowflake, les tableaux sont des ensembles multiples et non des ensembles simples. En d’autres termes, les tableaux peuvent contenir plusieurs copies de la même valeur.
ARRAY_INTERSECTIONcompare les tableaux à l’aide d’une sémantique à plusieurs ensembles (parfois appelée « sémantique de sac »), ce qui signifie que la fonction peut renvoyer plusieurs copies de la même valeur. Si un tableau a N copies d’une valeur et l’autre tableau M copies de la même valeur, le nombre de copies dans le tableau renvoyé est le plus petit de N ou M. Par exemple, si N est 4 et M est 2, alors la valeur renvoyée contient 2 copies.
Les deux arguments doivent être soit des ARRAYs structurés, soit des ARRAYs semi-structurés.
Si vous transmettez des ARRAYs structurés :
La fonction renvoie un ARRAY d’un type qui peut prendre en charge les deux types d’entrée.
Le ARRAY du deuxième argument doit être comparable au ARRAY du premier argument.
Exemples¶
Cet exemple montre une utilisation simple de la fonction :
SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B'), ARRAY_CONSTRUCT('B', 'C')); +------------------------------------------------------+ | ARRAY_INTERSECTION(ARRAY_CONSTRUCT('A', 'B'), | | ARRAY_CONSTRUCT('B', 'C')) | |------------------------------------------------------| | [ | | "B" | | ] | +------------------------------------------------------+
Les ensembles peuvent avoir plus d’une valeur correspondante :
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" | | ] | +------------------------------------------------------+
Il pourrait y avoir plusieurs instances de la même valeur correspondante. Par exemple, dans la requête ci-dessous, un tableau a trois copies de la lettre « B » et l’autre tableau a deux copies de la lettre « B ». Le résultat contient deux correspondances :
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" | | ] | +---------------------------------------------------------------+
Cet exemple utilise une plus grande quantité de données :
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 | ] | | | | | ] | | | | +----+--------+--------+------------------------+------------------------------------+
Ceci montre l’utilisation avec les valeurs 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 | | ] | | | ] | ] | | | +----+--------------+--------------+---------------+------------------------------------+
Cet exemple montre une utilisation avec le type de données 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 | | | } | } | | } | | | ] | ] | | ] | +----+-------------+-------------+-------------------------------------------------------------+------------------------------------+
Bien que des valeurs NULL dans un tableau soient traitées comme des valeurs comparables, si vous passez NULL au lieu d’un tableau, le résultat est NULL :
SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B'), NULL); +------------------------------------------------+ | ARRAY_INTERSECTION(ARRAY_CONSTRUCT('A', 'B'), | | NULL) | |------------------------------------------------| | NULL | +------------------------------------------------+