ARRAY JOIN 절

ARRAY JOIN 절에 대한 문서

배열 컬럼을 포함하는 테이블에서는 원래 컬럼의 각 배열 요소마다 하나의 행을 갖는 새 테이블을 만들고, 다른 컬럼의 값은 복제하는 작업이 흔히 사용됩니다. 이것이 ARRAY JOIN 절이 수행하는 가장 기본적인 형태입니다.

이 이름은 배열 또는 중첩 데이터 구조에 대해 JOIN을 수행하는 것으로 볼 수 있다는 데서 유래했습니다. 의도는 arrayJoin 함수와 비슷하지만, 절의 기능이 더 넓습니다.

구문:

SELECT <expr_list>
FROM <left_subquery>
[LEFT] ARRAY JOIN <array>
[WHERE|PREWHERE <expr>]
...

지원되는 ARRAY JOIN 유형은 다음과 같습니다:

• ARRAY JOIN - 기본적으로 빈 배열은 JOIN 결과에 포함되지 않습니다.
• LEFT ARRAY JOIN - JOIN 결과에는 빈 배열을 가진 행도 포함됩니다. 빈 배열의 값은 배열 요소 타입의 기본값(일반적으로 0, 빈 문자열 또는 NULL)으로 설정됩니다.

​

기본 ARRAY JOIN 예시

​

ARRAY JOIN 및 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 및 arrayEnumerate 함수

이 함수는 일반적으로 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 및 arrayEnumerateUniq

이 함수는 ARRAY JOIN을 사용해 배열 요소를 집계할 때 유용합니다.

이 예시에서는 각 목표 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 │
└─────────┴─────────┴────────┘

​

별칭 사용

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

ARRAY JOIN은 중첩 데이터 구조에서도 사용할 수 있습니다:

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 │
└───────┴─────┴─────┴─────────┴────────────┴─────┘

​

구현 세부 사항

ARRAY JOIN을 실행할 때 쿼리 실행 순서는 최적화됩니다. 쿼리에서는 ARRAY JOIN을 항상 WHERE/PREWHERE 절보다 앞에 지정해야 하지만, ARRAY JOIN의 결과를 필터링에 사용하지 않는다면 기술적으로는 어느 쪽을 먼저 수행해도 됩니다. 처리 순서는 쿼리 옵티마이저가 제어합니다.

​

쇼트서킷 함수 평가와의 비호환성

쇼트서킷 함수 평가는 if, multiIf, and, or와 같은 특정 함수에서 복잡한 표현식의 실행을 최적화하는 기능입니다. 이 기능은 이러한 함수를 실행하는 동안 0으로 나누기와 같은 잠재적인 예외가 발생하지 않도록 합니다.

arrayJoin은 항상 실행되며 쇼트서킷 함수 평가를 지원하지 않습니다. 이는 arrayJoin이 쿼리 분석 및 실행 과정에서 다른 모든 함수와 별도로 처리되는 고유한 함수이고, 쇼트서킷 함수 실행과는 호환되지 않는 추가 로직이 필요하기 때문입니다. 또한 결과의 행 수는 arrayJoin의 결과에 따라 달라지므로, arrayJoin의 지연 실행을 구현하는 것은 지나치게 복잡하고 비용도 많이 듭니다.

​

관련 콘텐츠

• 블로그: ClickHouse에서 시계열 데이터 다루기