arrayJoin 함수
arrayJoin 함수
배열을 입력받아 각 배열 요소에 대해 원본 행을 여러 행으로 펼쳐내는(unfold) 매우 특별한 함수예요. 배열 요소마다 하나의 행을 만들어 다른 컬럼 값은 그대로 복사해요.
출처: 문서
본문
arrayJoin은 매우 특별한 함수예요. 일반 함수는 행 집합을 바꾸지 않고 각 행의 값만 바꿔요(map). 집계 함수는 행 집합을 압축해요(fold 또는 reduce). arrayJoin 함수는 각 행을 받아 행 집합을 생성해요(unfold). 이 함수는 배열을 인자로 받아, 배열의 요소 수만큼 원본 행을 여러 행으로 펼쳐요. 다른 컬럼의 모든 값은 단순히 복사되지만, 이 함수가 적용된 컬럼의 값은 해당 배열 값으로 대체돼요.
배열이 비어 있으면 arrayJoin은 행을 생성하지 않아요. 배열 타입의 기본값을 담은 단일 행을 반환하려면 emptyArrayToSingle로 감싸면 돼요. 예: arrayJoin(emptyArrayToSingle(...)).
unnest(버전 26.5부터)는 함수 호출 형태(SELECT unnest(arr))에서 arrayJoin의 대소문자 구분 없는 별칭이에요. PostgreSQL 테이블 소스 구문(FROM unnest(...), CROSS JOIN UNNEST(...), LATERAL)은 지원되지 않아요. 그런 쿼리에는 ARRAY JOIN 절을 사용하세요.
예를 들어:
쿼리:
SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src
응답:
┌─dst─┬─'Hello'─┬─src─────┐
│ 1 │ Hello │ [1,2,3] │
│ 2 │ Hello │ [1,2,3] │
│ 3 │ Hello │ [1,2,3] │
└─────┴───────────┴─────────┘
arrayJoin 함수는 WHERE 섹션을 포함한 쿼리의 모든 섹션에 영향을 줘요. 아래 쿼리의 결과가 서브쿼리가 1행을 반환했음에도 2인 것에 주목하세요.
쿼리:
SELECT sum(1) AS impressions
FROM
(
SELECT ['Istanbul', 'Berlin', 'Babruysk'] AS cities
)
WHERE arrayJoin(cities) IN ['Istanbul', 'Berlin'];
응답:
┌─impressions─┐
│ 2 │
└─────────────┘
한 가지 예외가 있어요: arrayJoin(그 unnest 별칭 포함)은 조인 중 평가되는 JOIN ON 조건에서는 허용되지 않아요. 그런 조건은 행 수를 보존해야 하고 조인된 행의 각 배치에 대해 별도로 평가되기 때문이에요. 한 쪽에만 적용되는 조건과 arrayJoin에 대한 등가 키는 조인 전에 추출되므로 영향을 받지 않아요. 비배타적(disjunctive가 아닌) ALL INNER JOIN 조건도 조인 후에 조건이 적용되므로 영향을 받지 않아요. 확장이 한 쪽에만 의존한다면 조인 전에 서브쿼리의 ARRAY JOIN으로 옮겨 넣으세요. arrayJoin 인자가 양쪽 컬럼을 읽는 조건은 재구성해야 해요.
쿼리는 여러 개의 arrayJoin 함수를 사용할 수 있어요. 이 경우 변환이 여러 번 수행되고 행이 곱해져요. 예를 들어:
쿼리:
SELECT
sum(1) AS impressions,
arrayJoin(cities) AS city,
arrayJoin(browsers) AS browser
FROM
(
SELECT
['Istanbul', 'Berlin', 'Babruysk'] AS cities,
['Firefox', 'Chrome', 'Chrome'] AS browsers
)
GROUP BY
2,
3
응답:
┌─impressions─┬─city─────┬─browser─┐
│ 2 │ Istanbul │ Chrome │
│ 1 │ Istanbul │ Firefox │
│ 2 │ Berlin │ Chrome │
│ 1 │ Berlin │ Firefox │
│ 2 │ Babruysk │ Chrome │
│ 1 │ Babruysk │ Firefox │
└─────────────┴──────────┴─────────┘
모범 사례 (Best practice)
동일한 표현식으로 여러 개의 arrayJoin을 사용하면 공통 부분식(common subexpression) 제거로 인해 예상한 결과가 나오지 않을 수 있어요. 그런 경우에는 반복되는 배열 표현식을 조인 결과에 영향을 주지 않는 추가 연산으로 수정하는 것을 고려하세요. 예: arrayJoin(arraySort(arr)), arrayJoin(arrayConcat(arr, []))
예시:
쿼리:
SELECT
arrayJoin(dice) AS first_throw,
/* arrayJoin(dice) as second_throw */ -- is technically correct, but will annihilate result set
arrayJoin(arrayConcat(dice, [])) AS second_throw -- intentionally changed expression to force re-evaluation
FROM (
SELECT [1, 2, 3, 4, 5, 6] AS dice
);
SELECT 쿼리의 ARRAY JOIN 구문을 참고하세요. 이것은 더 넓은 가능성을 제공해요. ARRAY JOIN은 동일한 요소 수를 가진 여러 배열을 한 번에 변환할 수 있게 해줘요. 예시:
쿼리:
SELECT
sum(1) AS impressions,
city,
browser
FROM
(
SELECT
['Istanbul', 'Berlin', 'Babruysk'] AS cities,
['Firefox', 'Chrome', 'Chrome'] AS browsers
)
ARRAY JOIN
cities AS city,
browsers AS browser
GROUP BY
2,
3
응답:
┌─impressions─┬─city─────┬─browser─┐
│ 1 │ Istanbul │ Firefox │
│ 1 │ Berlin │ Chrome │
│ 1 │ Babruysk │ Chrome │
└─────────────┴──────────┴─────────┘
또는 Tuple을 사용할 수도 있어요. 예시:
쿼리:
SELECT
sum(1) AS impressions,
(arrayJoin(arrayZip(cities, browsers)) AS t).1 AS city,
t.2 AS browser
FROM
(
SELECT
['Istanbul', 'Berlin', 'Babruysk'] AS cities,
['Firefox', 'Chrome', 'Chrome'] AS browsers
)
GROUP BY
2,
3
결과:
┌─impressions─┬─city─────┬─browser─┐
│ 1 │ Istanbul │ Firefox │
│ 1 │ Berlin │ Chrome │
│ 1 │ Babruysk │ Chrome │
└─────────────┴──────────┴─────────┘
ClickHouse에서 arrayJoin이라는 이름은 JOIN 연산과의 개념적 유사성에서 비롯됐어요. 다만 배열을 단일 행 내에서 적용해요. 전통적인 JOIN이 서로 다른 테이블의 행을 결합하는 반면, arrayJoin은 행의 배열 각 요소를 "조인"해서 배열 요소마다 하나씩 여러 행을 만들고 다른 컬럼 값은 중복시켜요. ClickHouse는 또한 ARRAY JOIN 절 구문을 제공해서, 익숙한 SQL JOIN 용어를 사용해 전통적인 JOIN 연산과의 관계를 더 명확하게 해줘요. 이 과정을 배열 "펼치기(unfolding)"라고도 부르지만, 함수 이름과 절 모두에서 "join"이라는 용어를 사용해요. 이는 테이블을 배열 요소와 조인하는 것과 닮아 데이터 집합을 JOIN 연산과 유사한 방식으로 확장하기 때문이에요.