병합 테이블 함수
병합 테이블 함수 (Merge Table Function)
merge 테이블 함수를 사용하면 여러 테이블을 병렬로 쿼리할 수 있어요. 이 함수는 임시 Merge 테이블을 만들고, 컬럼들의 합집합과 공통 타입 추론을 통해 이 테이블의 구조를 도출해요.
출처: 문서
본문
테이블 설정
TennisMyLife의 ATP 매치 데이터베이스를 활용해 이 함수를 사용하는 법을 배워 볼게요. 1960년대까지 거슬러 올라가는 매치가 담긴 CSV 파일을 처리할 거지만, 각 10년마다 약간 다른 스키마를 만들 거예요. 1990년대에는 추가 컬럼 몇 개도 더할 거예요. import 문은 아래와 같아요.
CREATE OR REPLACE TABLE atp_matches_1960s ORDER BY tourney_id AS
SELECT tourney_id, surface, winner_name, loser_name, winner_seed, loser_seed, score
FROM url('https://raw.githubusercontent.com/Tennismylife/TML-Database/refs/heads/master/{1968..1969}.csv')
SETTINGS schema_inference_make_columns_nullable=0,
schema_inference_hints='winner_seed Nullable(String), loser_seed Nullable(UInt8)';
CREATE OR REPLACE TABLE atp_matches_1970s ORDER BY tourney_id AS
SELECT tourney_id, surface, winner_name, loser_name, winner_seed, loser_seed, splitByWhitespace(score) AS score
FROM url('https://raw.githubusercontent.com/Tennismylife/TML-Database/refs/heads/master/{1970..1979}.csv')
SETTINGS schema_inference_make_columns_nullable=0,
schema_inference_hints='winner_seed Nullable(UInt8), loser_seed Nullable(UInt8)';
CREATE OR REPLACE TABLE atp_matches_1980s ORDER BY tourney_id AS
SELECT tourney_id, surface, winner_name, loser_name, winner_seed, loser_seed, splitByWhitespace(score) AS score
FROM url('https://raw.githubusercontent.com/Tennismylife/TML-Database/refs/heads/master/{1980..1989}.csv')
SETTINGS schema_inference_make_columns_nullable=0,
schema_inference_hints='winner_seed Nullable(UInt16), loser_seed Nullable(UInt16)';
CREATE OR REPLACE TABLE atp_matches_1990s ORDER BY tourney_id AS
SELECT tourney_id, surface, winner_name, loser_name, winner_seed, loser_seed, splitByWhitespace(score) AS score,
toBool(arrayExists(x -> position(x, 'W/O') > 0, score))::Nullable(bool) AS walkover,
toBool(arrayExists(x -> position(x, 'RET') > 0, score))::Nullable(bool) AS retirement
FROM url('https://raw.githubusercontent.com/Tennismylife/TML-Database/refs/heads/master/{1990..1999}.csv')
SETTINGS schema_inference_make_columns_nullable=0,
schema_inference_hints='winner_seed Nullable(UInt16), loser_seed Nullable(UInt16), surface Enum(\'Hard\', \'Grass\', \'Clay\', \'Carpet\')';
여러 테이블의 스키마
다음 쿼리로 각 테이블의 컬럼을 타입과 함께 나란히 나열해서 차이점을 쉽게 볼 수 있어요.
SELECT * EXCEPT(position) FROM (
SELECT position, name,
any(if(table = 'atp_matches_1960s', type, null)) AS 1960s,
any(if(table = 'atp_matches_1970s', type, null)) AS 1970s,
any(if(table = 'atp_matches_1980s', type, null)) AS 1980s,
any(if(table = 'atp_matches_1990s', type, null)) AS 1990s
FROM system.columns
WHERE database = currentDatabase() AND table LIKE 'atp_matches%'
GROUP BY ALL
ORDER BY position ASC
)
SETTINGS output_format_pretty_max_value_width=25;
┌─name────────┬─1960s────────────┬─1970s───────────┬─1980s────────────┬─1990s─────────────────────┐
│ tourney_id │ String │ String │ String │ String │
│ surface │ String │ String │ String │ Enum8('Hard' = 1, 'Grass'⋯│
│ winner_name │ String │ String │ String │ String │
│ loser_name │ String │ String │ String │ String │
│ winner_seed │ Nullable(String) │ Nullable(UInt8) │ Nullable(UInt16) │ Nullable(UInt16) │
│ loser_seed │ Nullable(UInt8) │ Nullable(UInt8) │ Nullable(UInt16) │ Nullable(UInt16) │
│ score │ String │ Array(String) │ Array(String) │ Array(String) │
│ walkover │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ Nullable(Bool) │
│ retirement │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ Nullable(Bool) │
└─────────────┴──────────────────┴─────────────────┴──────────────────┴───────────────────────────┘
차이점들을 살펴볼게요.
- 1970s는
winner_seed의 타입을Nullable(String)에서Nullable(UInt8)로,score를String에서Array(String)로 변경해요. - 1980s는
winner_seed와loser_seed를Nullable(UInt8)에서Nullable(UInt16)으로 변경해요. - 1990s는
surface를String에서Enum('Hard', 'Grass', 'Clay', 'Carpet')로 변경하고,walkover와retirement컬럼을 추가해요.
merge로 여러 테이블 쿼리하기
John McEnroe가 시드(seed) 1번인 상대를 이긴 매치를 찾는 쿼리를 작성해 볼게요.
SELECT loser_name, score
FROM merge('atp_matches*')
WHERE winner_name = 'John McEnroe'
AND loser_seed = 1;
┌─loser_name────┬─score───────────────────────────┐
│ Bjorn Borg │ ['6-3','6-4'] │
│ Bjorn Borg │ ['7-6','6-1','6-7','5-7','6-4'] │
│ Bjorn Borg │ ['7-6','6-4'] │
│ Bjorn Borg │ ['4-6','7-6','7-6','6-4'] │
│ Jimmy Connors │ ['6-1','6-3'] │
│ Mats Wilander │ ['6-2','6-4'] │
│ Ivan Lendl │ ['6-2','4-6','6-3','6-7','7-6'] │
│ Ivan Lendl │ ['6-3','3-6','6-3','7-6'] │
│ Ivan Lendl │ ['6-1','6-3'] │
│ Stefan Edberg │ ['6-2','6-3'] │
│ Stefan Edberg │ ['7-6','6-2'] │
│ Ivan Lendl │ ['7-6','1-4','WEA'] │
│ Stefan Edberg │ ['6-2','6-2'] │
│ Jakob Hlasek │ ['6-3','7-6'] │
└───────────────┴─────────────────────────────────┘
이제 McEnroe가 3번 이하 시드인 매치만 필터링하고 싶다고 가정해 볼게요. winner_seed가 여러 테이블에서 서로 다른 타입을 사용하므로 좀 더 까다롭답니다.
SELECT loser_name, score, winner_seed
FROM merge('atp_matches*')
WHERE winner_name = 'John McEnroe'
AND loser_seed = 1
AND multiIf(
variantType(winner_seed) = 'UInt8', variantElement(winner_seed, 'UInt8') >= 3,
variantType(winner_seed) = 'UInt16', variantElement(winner_seed, 'UInt16') >= 3,
variantElement(winner_seed, 'String')::Nullable(UInt16) >= 3
);
행마다 winner_seed의 타입을 확인하기 위해 variantType 함수를 사용하고, 그런 다음 variantElement로 기저 값을 추출해요. 타입이 String이면 숫자로 캐스팅한 뒤 비교해요. 쿼리 실행 결과는 아래와 같아요.
┌─loser_name────┬─score───────────────┬─winner_seed─┐
│ Bjorn Borg │ ['6-3','6-4'] │ 3 │
│ Mats Wilander │ ['6-2','6-4'] │ 3 │
│ Stefan Edberg │ ['6-2','6-3'] │ 6 │
│ Stefan Edberg │ ['7-6','6-2'] │ 4 │
│ Ivan Lendl │ ['7-6','1-4','WEA'] │ 4 │
│ Stefan Edberg │ ['6-2','6-2'] │ 7 │
└───────────────┴─────────────────────┴─────────────┘
merge 사용 시 행이 어느 테이블에서 오는가?
행이 어떤 테이블에서 왔는지 알고 싶다면 어떻게 할까요? 다음 쿼리처럼 _table 가상 컬럼을 사용할 수 있어요.
SELECT _table, loser_name, score, winner_seed
FROM merge('atp_matches*')
WHERE winner_name = 'John McEnroe'
AND loser_seed = 1
AND multiIf(
variantType(winner_seed) = 'UInt8', variantElement(winner_seed, 'UInt8') >= 3,
variantType(winner_seed) = 'UInt16', variantElement(winner_seed, 'UInt16') >= 3,
variantElement(winner_seed, 'String')::Nullable(UInt16) >= 3
);
┌─_table────────────┬─loser_name────┬─score───────────────┬─winner_seed─┐
│ atp_matches_1970s │ Bjorn Borg │ ['6-3','6-4'] │ 3 │
│ atp_matches_1980s │ Mats Wilander │ ['6-2','6-4'] │ 3 │
│ atp_matches_1980s │ Stefan Edberg │ ['6-2','6-3'] │ 6 │
│ atp_matches_1980s │ Stefan Edberg │ ['7-6','6-2'] │ 4 │
│ atp_matches_1980s │ Ivan Lendl │ ['7-6','1-4','WEA'] │ 4 │
│ atp_matches_1980s │ Stefan Edberg │ ['6-2','6-2'] │ 7 │
└───────────────────┴───────────────┴─────────────────────┴─────────────┘
이 가상 컬럼을 쿼리의 일부로 사용해 walkover 컬럼의 값을 셀 수도 있어요.
SELECT _table, walkover, count()
FROM merge('atp_matches*')
GROUP BY ALL
ORDER BY _table;
┌─_table────────────┬─walkover─┬─count()─┐
│ atp_matches_1960s │ ᴺᵁᴸᴸ │ 7319 │
│ atp_matches_1970s │ ᴺᵁᴸᴸ │ 39404 │
│ atp_matches_1980s │ ᴺᵁᴸᴸ │ 36097 │
│ atp_matches_1990s │ true │ 129 │
│ atp_matches_1990s │ false │ 37023 │
└───────────────────┴──────────┴─────────┘
walkover 컬럼이 atp_matches_1990s를 제외한 모든 것에서 NULL인 걸 볼 수 있어요. walkover 컬럼이 NULL이면 score 컬럼에 W/O 문자열이 포함되어 있는지 확인하도록 쿼리를 업데이트해야 해요.
SELECT _table,
multiIf(
walkover IS NOT NULL,
walkover,
variantType(score) = 'Array(String)',
toBool(arrayExists(
x -> position(x, 'W/O') > 0,
variantElement(score, 'Array(String)')
)),
variantElement(score, 'String') LIKE '%W/O%'
),
count()
FROM merge('atp_matches*')
GROUP BY ALL
ORDER BY _table;
score의 기저 타입이 Array(String)이면 배열을 돌며 W/O를 찾아야 하고, 타입이 String이면 문자열에서 그냥 W/O를 검색하면 돼요.
┌─_table────────────┬─multiIf(isNo⋯, '%W/O%'))─┬─count()─┐
│ atp_matches_1960s │ true │ 296 │
│ atp_matches_1960s │ false │ 7023 │
│ atp_matches_1970s │ true │ 583 │
│ atp_matches_1970s │ false │ 38821 │
│ atp_matches_1980s │ true │ 117 │
│ atp_matches_1980s │ false │ 35980 │
│ atp_matches_1990s │ true │ 129 │
│ atp_matches_1990s │ false │ 37023 │
└───────────────────┴──────────────────────────┴─────────┘