ARRAY JOIN
ARRAY JOIN
배열 컬럼이 있는 테이블에 대해, 해당 초기 컬럼의 각 개별 배열 요소를 가진 행을 만들고 다른 컬럼의 값은 복제하는 새 테이블을 생성하는 것은 일반적인 연산입니다. 이것이 ARRAY JOIN 절이 하는 기본적인 경우입니다.
그 이름은 배열 또는 중첩 데이터 구조와 JOIN을 실행하는 것으로 볼 수 있다는 사실에서 유래했습니다. 의도는 arrayJoin 함수와 유사하지만, 절의 기능이 더 넓습니다.
PostgreSQL FROM unnest(...), CROSS JOIN UNNEST(...), LATERAL은 지원되지 않습니다. 대신 ARRAY JOIN을 사용하세요. unnest 이름(버전 26.5부터)은 테이블 함수가 아니라 arrayJoin의 함수 호출 별칭입니다(SELECT unnest(arr)).
Syntax:
SELECT <expr_list>
FROM <left_subquery>
[LEFT] ARRAY JOIN <array>
[WHERE|PREWHERE <expr>]
...
지원되는 ARRAY JOIN 타입은 아래에 나열됩니다:
ARRAY JOIN- 기본 경우에서 빈 배열은JOIN결과에 포함되지 않습니다.LEFT ARRAY JOIN-JOIN결과는 빈 배열이 있는 행을 포함합니다. 빈 배열의 값은 배열 요소 타입의 기본값(보통 0, 빈 문자열 또는 NULL)으로 설정됩니다.
Basic ARRAY JOIN Examples
ARRAY JOIN and LEFT ARRAY JOIN
아래 예시들은 ARRAY JOIN과 LEFT ARRAY JOIN 절의 사용을 보여줍니다. Array 타입 컬럼이 있는 테이블을 만들고 값을 삽입해 보겠습니다:
CREATE TABLE arrays_test
(
s String,
arr Array(UInt8)
) ENGINE = Memory;
INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);
┌─s───────────┬─arr─────┐
│ Hello │ [1,2] │
│ World │ [3,4,5] │
│ Goodbye │ [] │
└─────────────┴─────────┘
아래 예시는 ARRAY JOIN 절을 사용합니다:
SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;
┌─s─────┬─arr─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ World │ 3 │
│ World │ 4 │
│ World │ 5 │
└───────┴─────┘
다음 예시는 LEFT ARRAY JOIN 절을 사용합니다:
SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;
┌─s───────────┬─arr─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ World │ 3 │
│ World │ 4 │
│ World │ 5 │
│ Goodbye │ 0 │
└─────────────┴─────┘
ARRAY JOIN and arrayEnumerate function
이 함수는 보통 ARRAY JOIN과 함께 사용됩니다. ARRAY JOIN을 적용한 후 각 배열에 대해 무언가를 한 번만 셀 수 있게 해줍니다. 예:
SELECT
count() AS Reaches,
countIf(num = 1) AS Hits
FROM test.hits
ARRAY JOIN
GoalsReached,
arrayEnumerate(GoalsReached) AS num
WHERE CounterID = 160656
LIMIT 10
┌─Reaches─┬──Hits─┐
│ 95606 │ 31406 │
└─────────┴───────┘
이 예시에서 Reaches는 전환 수(ARRAY JOIN 적용 후 받은 문자열)이고, Hits는 페이지뷰 수(ARRAY JOIN 전의 문자열)입니다. 이 특정 경우에는 더 쉬운 방법으로 같은 결과를 얻을 수 있습니다:
SELECT
sum(length(GoalsReached)) AS Reaches,
count() AS Hits
FROM test.hits
WHERE (CounterID = 160656) AND notEmpty(GoalsReached)
┌─Reaches─┬──Hits─┐
│ 95606 │ 31406 │
└─────────┴───────┘
ARRAY JOIN and arrayEnumerateUniq
이 함수는 ARRAY JOIN을 사용하고 배열 요소를 집계할 때 유용합니다.
이 예시에서 각 goal ID는 전환 수(Goals 중첩 데이터 구조의 각 요소는 도달된 목표이며, 이를 전환이라고 합니다)와 세션 수 계산을 가집니다. ARRAY JOIN이 없으면 세션 수를 sum(Sign)으로 계산했을 것입니다. 하지만 이 특정 경우 행은 중첩 Goals 구조로 곱해졌으므로, 이후 각 세션을 한 번씩 세기 위해 arrayEnumerateUniq(Goals.ID) 함수의 값에 조건을 적용합니다.
SELECT
Goals.ID AS GoalID,
sum(Sign) AS Reaches,
sumIf(Sign, num = 1) AS Visits
FROM test.visits
ARRAY JOIN
Goals,
arrayEnumerateUniq(Goals.ID) AS num
WHERE CounterID = 160656
GROUP BY GoalID
ORDER BY Reaches DESC
LIMIT 10
┌──GoalID─┬─Reaches─┬─Visits─┐
│ 53225 │ 3214 │ 1097 │
│ 2825062 │ 3188 │ 1097 │
│ 56600 │ 2803 │ 488 │
│ 1989037 │ 2401 │ 365 │
│ 2830064 │ 2396 │ 910 │
│ 1113562 │ 2372 │ 373 │
│ 3270895 │ 2262 │ 812 │
│ 1084657 │ 2262 │ 345 │
│ 56599 │ 2260 │ 799 │
│ 3271094 │ 2256 │ 812 │
└─────────┴─────────┴────────┘
Using Aliases
ARRAY JOIN 절에서 배열에 별칭을 지정할 수 있습니다. 이 경우 배열 항목은 이 별칭으로 접근할 수 있지만, 배열 자체는 원래 이름으로 접근됩니다. 예:
SELECT s, arr, a
FROM arrays_test
ARRAY JOIN arr AS a;
┌─s─────┬─arr─────┬─a─┐
│ Hello │ [1,2] │ 1 │
│ Hello │ [1,2] │ 2 │
│ World │ [3,4,5] │ 3 │
│ World │ [3,4,5] │ 4 │
│ World │ [3,4,5] │ 5 │
└───────┴─────────┴───┘
별칭을 사용해 외부 배열로 ARRAY JOIN을 수행할 수 있습니다. 예를 들어:
SELECT s, arr_external
FROM arrays_test
ARRAY JOIN [1, 2, 3] AS arr_external;
┌─s───────────┬─arr_external─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ Hello │ 3 │
│ World │ 1 │
│ World │ 2 │
│ World │ 3 │
│ Goodbye │ 1 │
│ Goodbye │ 2 │
│ Goodbye │ 3 │
└─────────────┴──────────────┘
ARRAY JOIN 절에서 여러 배열은 쉼표로 구분할 수 있습니다. 이 경우 JOIN은 그들과 동시에 수행됩니다(데카르트 곱이 아니라 직접 합). 기본적으로 모든 배열은 같은 크기를 가져야 함을 주의하세요. 예:
SELECT s, arr, a, num, mapped
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num, arrayMap(x -> x + 1, arr) AS mapped;
┌─s─────┬─arr─────┬─a─┬─num─┬─mapped─┐
│ Hello │ [1,2] │ 1 │ 1 │ 2 │
│ Hello │ [1,2] │ 2 │ 2 │ 3 │
│ World │ [3,4,5] │ 3 │ 1 │ 4 │
│ World │ [3,4,5] │ 4 │ 2 │ 5 │
│ World │ [3,4,5] │ 5 │ 3 │ 6 │
└───────┴─────────┴───┴─────┴────────┘
아래 예시는 arrayEnumerate 함수를 사용합니다:
SELECT s, arr, a, num, arrayEnumerate(arr)
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num;
┌─s─────┬─arr─────┬─a─┬─num─┬─arrayEnumerate(arr)─┐
│ Hello │ [1,2] │ 1 │ 1 │ [1,2] │
│ Hello │ [1,2] │ 2 │ 2 │ [1,2] │
│ World │ [3,4,5] │ 3 │ 1 │ [1,2,3] │
│ World │ [3,4,5] │ 4 │ 2 │ [1,2,3] │
│ World │ [3,4,5] │ 5 │ 3 │ [1,2,3] │
└───────┴─────────┴───┴─────┴─────────────────────┘
다른 크기의 여러 배열은 SETTINGS enable_unaligned_array_join = 1을 사용해 조인할 수 있습니다. 예:
SELECT s, arr, a, b
FROM arrays_test ARRAY JOIN arr AS a, [['a','b'],['c']] AS b
SETTINGS enable_unaligned_array_join = 1;
┌─s───────┬─arr─────┬─a─┬─b─────────┐
│ Hello │ [1,2] │ 1 │ ['a','b'] │
│ Hello │ [1,2] │ 2 │ ['c'] │
│ World │ [3,4,5] │ 3 │ ['a','b'] │
│ World │ [3,4,5] │ 4 │ ['c'] │
│ World │ [3,4,5] │ 5 │ [] │
│ Goodbye │ [] │ 0 │ ['a','b'] │
│ Goodbye │ [] │ 0 │ ['c'] │
└─────────┴─────────┴───┴───────────┘
ARRAY JOIN with Nested Data Structure
ARRAY JOIN은 nested data structures와도 동작합니다:
CREATE TABLE nested_test
(
s String,
nest Nested(
x UInt8,
y UInt32)
) ENGINE = Memory;
INSERT INTO nested_test
VALUES ('Hello', [1,2], [10,20]), ('World', [3,4,5], [30,40,50]), ('Goodbye', [], []);
┌─s───────┬─nest.x──┬─nest.y─────┐
│ Hello │ [1,2] │ [10,20] │
│ World │ [3,4,5] │ [30,40,50] │
│ Goodbye │ [] │ [] │
└─────────┴─────────┴────────────┘
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest;
┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │ 1 │ 10 │
│ Hello │ 2 │ 20 │
│ World │ 3 │ 30 │
│ World │ 4 │ 40 │
│ World │ 5 │ 50 │
└───────┴────────┴────────┘
ARRAY JOIN에서 중첩 데이터 구조의 이름을 지정하면, 그 이름이 구성하는 모든 배열 요소와 함께 ARRAY JOIN을 하는 것과 같은 의미입니다. 예시는 아래에 나열됩니다:
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`, `nest.y`;
┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │ 1 │ 10 │
│ Hello │ 2 │ 20 │
│ World │ 3 │ 30 │
│ World │ 4 │ 40 │
│ World │ 5 │ 50 │
└───────┴────────┴────────┘
이 변형도 의미가 있습니다:
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`;
┌─s─────┬─nest.x─┬─nest.y─────┐
│ Hello │ 1 │ [10,20] │
│ Hello │ 2 │ [10,20] │
│ World │ 3 │ [30,40,50] │
│ World │ 4 │ [30,40,50] │
│ World │ 5 │ [30,40,50] │
└───────┴────────┴────────────┘
JOIN 결과 또는 원본 배열 중 하나를 선택하기 위해 중첩 데이터 구조에 별칭을 사용할 수 있습니다. 예:
SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest AS n;
┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┐
│ Hello │ 1 │ 10 │ [1,2] │ [10,20] │
│ Hello │ 2 │ 20 │ [1,2] │ [10,20] │
│ World │ 3 │ 30 │ [3,4,5] │ [30,40,50] │
│ World │ 4 │ 40 │ [3,4,5] │ [30,40,50] │
│ World │ 5 │ 50 │ [3,4,5] │ [30,40,50] │
└───────┴─────┴─────┴─────────┴────────────┘
arrayEnumerate 함수 사용 예시:
SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`, num
FROM nested_test
ARRAY JOIN nest AS n, arrayEnumerate(`nest.x`) AS num;
┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┬─num─┐
│ Hello │ 1 │ 10 │ [1,2] │ [10,20] │ 1 │
│ Hello │ 2 │ 20 │ [1,2] │ [10,20] │ 2 │
│ World │ 3 │ 30 │ [3,4,5] │ [30,40,50] │ 1 │
│ World │ 4 │ 40 │ [3,4,5] │ [30,40,50] │ 2 │
│ World │ 5 │ 50 │ [3,4,5] │ [30,40,50] │ 3 │
└───────┴─────┴─────┴─────────┴────────────┴─────┘
Implementation Details
ARRAY JOIN을 실행할 때 쿼리 실행 순서가 최적화됩니다. ARRAY JOIN은 항상 쿼리에서 WHERE/PREWHERE 절보다 먼저 지정되어야 하지만, 기술적으로는 ARRAY JOIN 결과가 필터링에 사용되지 않는 한 어떤 순서로도 수행될 수 있습니다. 처리 순서는 쿼리 최적화 도구가 제어합니다.
Incompatibility with short-circuit function evaluation
Short-circuit function evaluation은 if, multiIf, and, or 같은 특정 함수에서 복잡한 표현식의 실행을 최적화하는 기능입니다. 이 함수들 실행 중 0으로 나누기 같은 잠재적 예외가 발생하는 것을 방지합니다.
arrayJoin은 항상 실행되며 short circuit function evaluation을 지원하지 않습니다. 이는 쿼리 분석과 실행 중 다른 모든 함수와 분리되어 처리되는 독특한 함수이고, short circuit 함수 실행과 동작하지 않는 추가 로직이 필요하기 때문입니다. 그 이유는 결과의 행 수가 arrayJoin 결과에 의존하고, arrayJoin의 지연 실행을 구현하는 것은 너무 복잡하고 비용이 크기 때문입니다.