Merge 테이블 함수

merge 테이블 함수를 사용하면 여러 테이블을 병렬로 쿼리할 수 있습니다. 이 함수는 임시 Merge 테이블을 생성한 다음, 각 테이블의 컬럼을 합치고 공통 타입을 추론하여 이 테이블의 구조를 결정합니다.

테이블 설정

TennisMyLife’s ATP match database를 활용해 이 함수를 사용하는 방법을 살펴보겠습니다. 1960년대까지 거슬러 올라가는 경기 데이터가 담긴 CSV 파일을 처리하되, 각 10년 단위로 스키마를 조금씩 다르게 만들겠습니다. 또한 1990년대 데이터에는 추가 컬럼 몇 개를 더하겠습니다. 가져오기 SQL 문은 아래와 같습니다:

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

차이점을 살펴보겠습니다:

1970년대 데이터에서는 winner_seed의 유형이 Nullable(String)에서 Nullable(UInt8)로, score는 String에서 Array(String)로 변경됩니다.
1980년대 데이터에서는 winner_seed와 loser_seed가 Nullable(UInt8)에서 Nullable(UInt16)로 변경됩니다.
1990년대 데이터에서는 surface가 String에서 Enum('Hard', 'Grass', 'Clay', 'Carpet')로 변경되고, walkover와 retirement 컬럼이 추가됩니다.

merge를 사용해 여러 테이블 쿼리하기

John McEnroe가 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이면 숫자로 CAST한 다음 비교합니다. 쿼리 실행 결과는 아래와 같습니다.

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

ClickHouse 가이드

사용 사례 가이드

테이블 설정

여러 테이블의 스키마

merge를 사용해 여러 테이블 쿼리하기

merge를 사용할 때 행은 어느 테이블에서 가져옵니까?

​테이블 설정

​여러 테이블의 스키마

​merge를 사용해 여러 테이블 쿼리하기

​merge를 사용할 때 행은 어느 테이블에서 가져옵니까?

테이블 설정

여러 테이블의 스키마

merge를 사용해 여러 테이블 쿼리하기

merge를 사용할 때 행은 어느 테이블에서 가져옵니까?