ClickHouse에서 조인 사용하기

ClickHouse는 표준 SQL 조인을 완벽하게 지원하므로 효율적인 데이터 분석이 가능합니다. 이 가이드에서는 정규화된 IMDB 데이터셋(출처: relational dataset repository)을 기반으로, 자주 사용되는 조인 유형 몇 가지와 이를 Venn 다이어그램과 예제 쿼리를 통해 사용하는 방법을 살펴봅니다.

테스트 데이터와 리소스

테이블을 생성하고 로드하는 방법에 대한 안내는 여기에서 확인할 수 있습니다. 테이블을 로컬에 생성하고 로드하지 않으려는 경우, 해당 데이터셋은 playground에서도 사용할 수 있습니다.

예제 데이터셋에서 다음 네 개의 테이블을 사용합니다:

이 네 개의 테이블에 있는 데이터는 하나 이상의 장르를 가질 수 있는 영화들을 나타냅니다. 영화에서의 역할은 배우들이 맡습니다.

위 다이어그램의 화살표는 외래 키와 기본 키 간의 관계를 나타냅니다. 예를 들어, genres 테이블의 한 행에 있는 movie_id 컬럼에는 movies 테이블의 한 행에 있는 id 값이 들어 있습니다.

영화와 배우 사이에는 다대다 관계가 있습니다. 이 다대다 관계는 roles 테이블을 사용하여 두 개의 일대다 관계로 정규화됩니다. roles 테이블의 각 행에는 movies 테이블과 actors 테이블의 id 컬럼 값이 포함되어 있습니다.

ClickHouse에서 지원하는 조인 유형

ClickHouse는 다음과 같은 조인 유형을 지원합니다:

INNER JOIN
OUTER JOIN
CROSS JOIN
SEMI JOIN
ANTI JOIN
ANY JOIN
ASOF JOIN

다음 절에서는 위에 나열한 각 JOIN 유형에 대한 예제 쿼리를 살펴봅니다.

INNER JOIN

INNER JOIN은 조인 키가 일치하는 각 행 쌍에 대해, 왼쪽 테이블의 행에서 가져온 컬럼 값과 오른쪽 테이블의 행에서 가져온 컬럼 값을 결합하여 반환합니다. 어떤 행이 둘 이상의 행과 일치하는 경우, 모든 조합이 반환되며(즉, 조인 키가 일치하는 행들에 대해 데카르트 곱이 생성됩니다).

이 쿼리는 movies 테이블과 genres 테이블을 조인하여 각 영화의 장르를 찾습니다:

SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
INNER JOIN genres AS g ON m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;

┌─name───────────────────────────────────┬─genre─────┐
│ Harry Potter and the Half-Blood Prince │ Action    │
│ Harry Potter and the Half-Blood Prince │ Adventure │
│ Harry Potter and the Half-Blood Prince │ Family    │
│ Harry Potter and the Half-Blood Prince │ Fantasy   │
│ Harry Potter and the Half-Blood Prince │ Thriller  │
│ DragonBall Z                           │ Action    │
│ DragonBall Z                           │ Adventure │
│ DragonBall Z                           │ Comedy    │
│ DragonBall Z                           │ Fantasy   │
│ DragonBall Z                           │ Sci-Fi    │
└────────────────────────────────────────┴───────────┘

참고

INNER 키워드는 생략할 수 있습니다.

INNER JOIN의 동작은 다음 조인 유형 중 하나를 사용하여 확장하거나 변경할 수 있습니다.

(LEFT / RIGHT / FULL) OUTER JOIN

LEFT OUTER JOIN은 기본적으로 INNER JOIN과 동일하게 동작하지만, 일치하는 행이 없는 왼쪽 테이블의 행에 대해서는 오른쪽 테이블 컬럼에 대해 ClickHouse가 기본값을 반환합니다.

RIGHT OUTER JOIN 쿼리도 유사하게 동작하며, 오른쪽 테이블에서 일치하는 행이 없는 행의 값과 함께 왼쪽 테이블 컬럼에 대한 기본값을 반환합니다.

FULL OUTER JOIN 쿼리는 LEFT와 RIGHT OUTER JOIN을 결합한 것으로, 왼쪽 및 오른쪽 테이블에서 일치하는 행이 없는 모든 행의 값을 반환하며, 각각 오른쪽 및 왼쪽 테이블 컬럼에 대한 기본값도 함께 반환합니다.

참고

ClickHouse는 기본값 대신 NULL을 반환하도록 설정할 수 있습니다(하지만 성능상의 이유로 권장되지 않습니다).

다음 쿼리는 genres 테이블에 일치하는 행이 없는 movies 테이블의 모든 행을 조회하여, 장르가 없는 모든 영화를 찾습니다. 이로 인해 movie_id 컬럼은 (쿼리 시점에) 기본값인 0을 갖게 됩니다:

SELECT m.name
FROM movies AS m
LEFT JOIN genres AS g ON m.id = g.movie_id
WHERE g.movie_id = 0
ORDER BY
    m.year DESC,
    m.name ASC
LIMIT 10;

┌─name──────────────────────────────────────┐
│ """Pacific War, The"""                    │
│ """Turin 2006: XX Olympic Winter Games""" │
│ Arthur, the Movie                         │
│ Bridge to Terabithia                      │
│ Mars in Aries                             │
│ Master of Space and Time                  │
│ Ninth Life of Louis Drax, The             │
│ Paradox                                   │
│ Ratatouille                               │
│ """American Dad"""                        │
└───────────────────────────────────────────┘

참고

OUTER 키워드는 생략해도 됩니다.

CROSS JOIN

CROSS JOIN은 조인 키를 고려하지 않고 두 테이블의 전체 데카르트 곱을 생성합니다. 왼쪽 테이블의 각 행이 오른쪽 테이블의 각 행과 결합됩니다.

따라서 다음 쿼리는 movies 테이블의 각 행을 genres 테이블의 각 행과 조합합니다:

SELECT
    m.name,
    m.id,
    g.movie_id,
    g.genre
FROM movies AS m
CROSS JOIN genres AS g
LIMIT 10;

┌─name─┬─id─┬─movie_id─┬─genre───────┐
│ #28  │  0 │        1 │ Documentary │
│ #28  │  0 │        1 │ Short       │
│ #28  │  0 │        2 │ Comedy      │
│ #28  │  0 │        2 │ Crime       │
│ #28  │  0 │        5 │ Western     │
│ #28  │  0 │        6 │ Comedy      │
│ #28  │  0 │        6 │ Family      │
│ #28  │  0 │        8 │ Animation   │
│ #28  │  0 │        8 │ Comedy      │
│ #28  │  0 │        8 │ Short       │
└──────┴────┴──────────┴─────────────┘

앞선 예시의 쿼리만으로는 큰 의미가 없지만, 일치하는 행을 연결하기 위해 WHERE 절을 추가하면 각 영화에 대한 장르를 찾기 위한 INNER JOIN 동작을 그대로 재현할 수 있습니다.

SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
CROSS JOIN genres AS g
WHERE m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;

CROSS JOIN에 대한 대체 구문으로, FROM 절에서 여러 테이블을 쉼표로 구분하여 지정하는 방법이 있습니다.

ClickHouse는 쿼리의 WHERE 절에 조인 표현식이 있는 경우 CROSS JOIN을 INNER JOIN으로 재작성합니다.

예시 쿼리의 경우 EXPLAIN SYNTAX를 사용하여 이를 확인할 수 있습니다(쿼리가 실행되기 전에 재작성되는, 구문적으로 최적화된 버전을 반환합니다).

EXPLAIN SYNTAX
SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
CROSS JOIN genres AS g
WHERE m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;

┌─explain─────────────────────────────────────┐
│ SELECT                                      │
│     name AS name,                           │
│     genre AS genre                          │
│ FROM movies AS m                            │
│ ALL INNER JOIN genres AS g ON id = movie_id │
│ WHERE id = movie_id                         │
│ ORDER BY                                    │
│     year DESC,                              │
│     name ASC,                               │
│     genre ASC                               │
│ LIMIT 10                                    │
└─────────────────────────────────────────────┘

구문상 최적화된 CROSS JOIN 쿼리 버전에서 INNER JOIN 절에는 ALL 키워드가 포함되어 있으며, 이는 CROSS JOIN을 INNER JOIN으로 다시 쓸 때에도 CROSS JOIN의 데카르트 곱 의미론이 유지되도록 명시적으로 추가된 것입니다. INNER JOIN의 경우 데카르트 곱은 비활성화할 수 있습니다.

ALL

그리고 위에서 설명했듯이 RIGHT OUTER JOIN에서는 OUTER 키워드를 생략할 수 있고, 선택적인 ALL 키워드를 추가할 수 있으므로, ALL RIGHT JOIN으로 작성해도 정상적으로 동작합니다.

(LEFT / RIGHT) SEMI JOIN

LEFT SEMI JOIN 쿼리는 오른쪽 테이블에서 조인 키가 최소 한 번 이상 일치하는 왼쪽 테이블의 각 행에 대해 컬럼 값을 반환합니다. 첫 번째로 발견된 일치 항목만 반환되며(카티션 곱은 비활성화됨) 그 이상은 반환되지 않습니다.

RIGHT SEMI JOIN 쿼리도 비슷하며, 왼쪽 테이블에서 최소 한 번 이상 일치하는 값이 있는 오른쪽 테이블의 모든 행에 대해 값을 반환하지만, 이 경우에도 첫 번째로 발견된 일치 항목만 반환합니다.

이 쿼리는 2023년에 영화에 출연한 모든 배우를 찾습니다. 일반적인 (INNER) 조인을 사용하면, 동일한 배우가 2023년에 여러 역할을 맡은 경우 여러 번 나타난다는 점에 유의하십시오:

SELECT
    a.first_name,
    a.last_name
FROM actors AS a
LEFT SEMI JOIN roles AS r ON a.id = r.actor_id
WHERE toYear(created_at) = '2023'
ORDER BY id ASC
LIMIT 10;

┌─first_name─┬─last_name──────────────┐
│ Michael    │ 'babeepower' Viera     │
│ Eloy       │ 'Chincheta'            │
│ Dieguito   │ 'El Cigala'            │
│ Antonio    │ 'El de Chipiona'       │
│ José       │ 'El Francés'           │
│ Félix      │ 'El Gato'              │
│ Marcial    │ 'El Jalisco'           │
│ José       │ 'El Morito'            │
│ Francisco  │ 'El Niño de la Manola' │
│ Víctor     │ 'El Payaso'            │
└────────────┴────────────────────────┘

(LEFT / RIGHT) ANTI JOIN

LEFT ANTI JOIN은 왼쪽 테이블에서 매칭되지 않는 모든 행의 컬럼 값을 반환합니다.

마찬가지로, RIGHT ANTI JOIN은 오른쪽 테이블에서 매칭되지 않는 모든 행의 컬럼 값을 반환합니다.

앞서 살펴본 외부 조인 예제 쿼리는 데이터셋에서 장르가 없는 영화를 찾기 위해 ANTI JOIN을 사용하는 방식으로도 표현할 수 있습니다:

SELECT m.name
FROM movies AS m
LEFT ANTI JOIN genres AS g ON m.id = g.movie_id
ORDER BY
    year DESC,
    name ASC
LIMIT 10;

┌─name──────────────────────────────────────┐
│ """Pacific War, The"""                    │
│ """Turin 2006: XX Olympic Winter Games""" │
│ Arthur, the Movie                         │
│ Bridge to Terabithia                      │
│ Mars in Aries                             │
│ Master of Space and Time                  │
│ Ninth Life of Louis Drax, The             │
│ Paradox                                   │
│ Ratatouille                               │
│ """American Dad"""                        │
└───────────────────────────────────────────┘

(LEFT / RIGHT / INNER) ANY JOIN

LEFT ANY JOIN은 LEFT OUTER JOIN과 LEFT SEMI JOIN을 결합한 것으로, ClickHouse는 왼쪽 테이블의 각 행에 대해 컬럼 값을 반환합니다. 이때 오른쪽 테이블에 일치하는 행이 있으면 그 행의 컬럼 값과 결합하여 반환하고, 일치하는 행이 없으면 오른쪽 테이블의 기본 컬럼 값과 결합하여 반환합니다. 왼쪽 테이블의 하나의 행이 오른쪽 테이블에서 둘 이상의 행과 일치할 경우, ClickHouse는 처음으로 발견된 일치 행과의 결합 컬럼 값만 반환합니다(데카르트 곱(cartesian product)은 비활성화됨).

마찬가지로, RIGHT ANY JOIN은 RIGHT OUTER JOIN과 RIGHT SEMI JOIN을 결합한 것입니다.

그리고 INNER ANY JOIN은 데카르트 곱(cartesian product)이 비활성화된 INNER JOIN입니다.

다음 예시는 두 개의 임시 테이블(left_table 및 right_table)을 사용한 단순화된 예제에서 LEFT ANY JOIN을 보여 줍니다. 이 임시 테이블은 values 테이블 함수로 생성됩니다.

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
LEFT ANY JOIN right_table AS r ON l.c = r.c;

┌─l_c─┬─r_c─┐
│   1 │   0 │
│   2 │   2 │
│   3 │   3 │
└─────┴─────┘

다음은 RIGHT ANY JOIN을 사용한 동일한 쿼리입니다:

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
RIGHT ANY JOIN right_table AS r ON l.c = r.c;

┌─l_c─┬─r_c─┐
│   2 │   2 │
│   2 │   2 │
│   3 │   3 │
│   3 │   3 │
│   0 │   4 │
└─────┴─────┘

다음은 INNER ANY JOIN이 포함된 쿼리입니다:

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
INNER ANY JOIN right_table AS r ON l.c = r.c;

┌─l_c─┬─r_c─┐
│   2 │   2 │
│   3 │   3 │
└─────┴─────┘

ASOF JOIN

ASOF JOIN은(는) 근사 일치 기능을 제공합니다. 왼쪽 테이블의 행에 오른쪽 테이블에서 정확히 일치하는 행이 없으면, 오른쪽 테이블에서 가장 가까운 행이 대신 매칭에 사용됩니다.

이는 시계열 분석에 특히 유용하며 쿼리 복잡성을 크게 줄일 수 있습니다.

다음 예시는 주식 시장 데이터의 시계열 분석을 수행합니다. quotes 테이블에는 하루 중 특정 시점을 기준으로 한 종목 심볼 시세가 저장됩니다. 예시 데이터에서 가격은 10초마다 한 번씩 갱신됩니다. trades 테이블에는 종목 거래 내역이 나열되며, 특정 시점에 특정 종목이 일정 수량만큼 매수된 거래를 나타냅니다:

각 거래의 정확한 비용을 계산하려면, 거래를 그와 가장 가까운 시점의 시세와 매칭해야 합니다.

이는 ASOF JOIN을 사용하면 간단하고 간결하게 처리할 수 있습니다. ON 절로는 정확 일치 조건을 지정하고, AND 절로는 가장 가까운 일치 조건을 지정합니다. 즉, 특정 심볼(정확 일치)에 대해 해당 심볼의 거래 시점(근사 일치)과 같거나 그 이전 시점 중에서 quotes 테이블에서 시간 값이 가장 ‘가까운’ 행을 찾는 방식입니다:

SELECT
    t.symbol,
    t.volume,
    t.time AS trade_time,
    q.time AS closest_quote_time,
    q.price AS quote_price,
    t.volume * q.price AS final_price
FROM trades t
ASOF LEFT JOIN quotes q ON t.symbol = q.symbol AND t.time >= q.time
FORMAT Vertical;

Row 1:
──────
symbol:             ABC
volume:             200
trade_time:         2023-02-22 14:09:05
closest_quote_time: 2023-02-22 14:09:00
quote_price:        32.11
final_price:        6422

Row 2:
──────
symbol:             ABC
volume:             300
trade_time:         2023-02-22 14:09:28
closest_quote_time: 2023-02-22 14:09:20
quote_price:        32.15
final_price:        9645

참고

ASOF JOIN에서 ON 절은 필수이며, AND 절에서 지정하는 비정확 일치 조건 외에 사용할 정확 일치 조건을 지정합니다.

요약

이 가이드는 ClickHouse가 모든 표준 SQL 조인(join) 유형뿐만 아니라 분석 쿼리를 위한 특수 조인도 어떻게 지원하는지 설명합니다. 조인에 대한 더 자세한 내용은 JOIN SQL 문에 대한 문서를 참조하십시오.

테스트 데이터와 리소스​

ClickHouse에서 지원하는 조인 유형​

INNER JOIN​

(LEFT / RIGHT / FULL) OUTER JOIN​

CROSS JOIN​

(LEFT / RIGHT) SEMI JOIN​

(LEFT / RIGHT) ANTI JOIN​

(LEFT / RIGHT / INNER) ANY JOIN​

ASOF JOIN​

요약​