ClickHouse에서 JOIN 사용하기
ClickHouse에서 JOIN 사용하기
ClickHouse는 표준 SQL 조인을 완전히 지원해서 효율적인 데이터 분석을 가능하게 해요. 이 가이드에서는 관계형 데이터셋 저장소에서 가져온 정규화된 IMDB 데이터셋에서 벤 다이어그램과 예제 쿼리로 흔히 사용되는 몇 가지 조인 타입과 그 사용법을 탐구해볼게요.
출처: 문서
본문
테스트 데이터와 리소스 (Test Data and Resources)
테이블 생성·로드 지침은 여기에서 찾을 수 있어요. 테이블을 로컬에서 만들고 로드하고 싶지 않다면 이 데이터셋은 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을 반환하도록 구성할 수 있어요(하지만 성능상의 이유로 덜 권장돼요).
이 쿼리는 movies 테이블에서 genres 테이블에 일치가 없는 모든 행을 조회해 장르가 없는 모든 영화를 찾아요. 따라서 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 │
└──────┴────┴──────────┴─────────────┘
이전 예제 쿼리 단독으로는 별 의미가 없지만, 각 영화의 장르를 찾는 INNER JOIN 동작을 재현하도록 일치 행을 연관시키는 WHERE 절로 확장할 수 있어요:
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으로 재작성될 때도 데카르트 곱 의미를 유지하기 위해 명시적으로 추가된 것이에요. 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은 모든 일치하지 않는 오른쪽 테이블 행에 대해 컬럼 값을 반환해요. 이전 outer 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는 첫 번째로 찾은 일치의 결합된 컬럼 값만 반환해요(데카르트 곱이 비활성화됨). 마찬가지로 RIGHT ANY JOIN은 RIGHT OUTER JOIN + RIGHT SEMI JOIN의 조합이에요. 그리고 INNER ANY JOIN은 데카르트 곱이 비활성화된 INNER JOIN이에요. 다음 예제는 두 개의 임시 테이블(left_table과 right_table)을 values table function으로 구성해 LEFT 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
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 절의 비정확 일치 조건 옆에 정확한 일치 조건을 지정해요.
요약 (Summary)
이 가이드는 ClickHouse가 모든 표준 SQL JOIN 타입과 분석 쿼리를 가능하게 하는 특수 조인을 어떻게 지원하는지 보여줘요. JOIN에 대한 자세한 내용은 JOIN 문 문서를 참고하세요.