- 类别：
半结构化和结构化数据函数 （数组/对象）
ARRAY_INTERSECTION¶
返回一个数组，其中包含两个输入数组中的匹配元素。
该函数为 NULL-safe，意味着它将 NULLs 视作用于比较相等性的已知值。
语法¶
ARRAY_INTERSECTION( <array1> , <array2> )
实参¶
array1包含要比较的元素的数组。
array2包含要比较的元素的数组。
返回¶
此函数返回一个 ARRAY，其中包含匹配的输入数组的元素。
如果没有元素重叠，该函数会返回空数组。
如果一个或两个实参都是 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。
第二个实参中的 ARRAY 必须与第一个实参中的 ARRAY 属于 同类。
示例¶
此示例演示了该函数的简单用法：
SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B'), ARRAY_CONSTRUCT('B', 'C')); +------------------------------------------------------+ | ARRAY_INTERSECTION(ARRAY_CONSTRUCT('A', 'B'), | | ARRAY_CONSTRUCT('B', 'C')) | |------------------------------------------------------| | [ | | "B" | | ] | +------------------------------------------------------+
这些集合可能具有多个匹配值：
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" | | ] | +------------------------------------------------------+
可能存在多个相同匹配值的实例。例如，在下面的查询中，一个数组具有字母“B”的三个副本，另一个数组具有字母“B”的两个副本。结果包含两个匹配项：
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" | | ] | +---------------------------------------------------------------+
此示例使用更大的数据量：
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 | ] | | | | | ] | | | | +----+--------+--------+------------------------+------------------------------------+
此示例展示了带有 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 | | ] | | | ] | ] | | | +----+--------------+--------------+---------------+------------------------------------+
此示例展示了 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 | | | } | } | | } | | | ] | ] | | ] | +----+-------------+-------------+-------------------------------------------------------------+------------------------------------+
尽管数组 中 的 NULL 值被视为同类值，但如果传递 NULL 而不是 数组，则结果为 NULL：
SELECT array_intersection(ARRAY_CONSTRUCT('A', 'B'), NULL); +------------------------------------------------+ | ARRAY_INTERSECTION(ARRAY_CONSTRUCT('A', 'B'), | | NULL) | |------------------------------------------------| | NULL | +------------------------------------------------+