ARRAY_INTERSECTION
ARRAY_INTERSECTION (배열 교집합)
두 입력 배열에서 일치하는 요소를 포함하는 배열을 반환해요.
이 함수는 NULL-safe로, 동등성 비교를 위해 NULL을 알려진 값으로 취급해요.
참조: ARRAY_EXCEPT, ARRAYS_OVERLAP
본문
구문
ARRAY_INTERSECTION( <array1>, <array2> )
인자
array1
비교할 요소를 포함하는 배열이에요.
array2
비교할 요소를 포함하는 배열이에요.
반환 값
이 함수는 입력 배열의 일치하는 요소를 포함하는 ARRAY를 반환해요.
요소가 겹치지 않으면 함수는 빈 배열을 반환해요.
인자 중 하나 또는 둘 다 NULL이면 함수는 NULL을 반환해요.
반환된 배열 안의 값 순서는 지정되지 않아요.
사용상 주의사항
- OBJECT 타입의 데이터를 비교할 때 객체가 일치하는 것으로 간주되려면 동일해야 해요. 자세한 내용은 예시(이 주제에서)를 참고해요.
- ARRAY_INTERSECTION과 관련 ARRAYS_OVERLAP 함수의 차이는, ARRAYS_OVERLAP 함수는 단순히 TRUE 또는 FALSE를 반환하는 반면 ARRAY_INTERSECTION은 실제 겹치는 값을 반환한다는 점이에요.
- Snowflake에서 배열은 집합이 아니라 다중 집합(multi-set)이에요. 즉, 배열은 같은 값의 여러 복사본을 포함할 수 있어요. ARRAY_INTERSECTION은 다중 집합 의미론(때로 "bag semantics"라고 함)을 사용해 배열을 비교하므로, 함수는 같은 값의 여러 복사본을 반환할 수 있어요. 한 배열에 값의 복사본이 N개 있고 다른 배열에 같은 값의 복사본이 M개 있으면, 반환 배열의 복사본 수는 N과 M 중 더 작은 값이에요. 예를 들어 N이 4이고 M이 2이면 반환된 값은 복사본 2개를 포함해요.
- 두 인자는 모두 구조적(structured) ARRAY이거나 반구조적(semi-structured) ARRAY여야 해요.
- 구조적 ARRAY를 전달하는 경우: 함수는 두 입력 타입을 모두 수용할 수 있는 타입의 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 |
+------------------------------------------------+
더 알아보기
- Semi-structured and structured data functions (Array/Object) — 배열/객체 함수 모음
- ARRAYS_OVERLAP — 배열 겹침 여부