 表函数同时查询多张 schema 不一致的表)
如何用 ClickHouse 的 merge() 表函数同时查询多张 schema 不一致的表
【免费下载链接】ClickHouseClickHouse® is a real-time analytics database management system项目地址: https://gitcode.com/GitHub_Trending/cli/ClickHouse
当你同一个数据库里有多张结构相近但不完全一致的表——同一列在不同表里类型不同，或者部分表多了若干列——用普通 SQL 很难把它们当成一张表来查。ClickHouse 的merge()表函数就是为这个场景设计的：它创建一个临时的 Merge 表，把匹配到的表当成一个整体来读，结果列取各表列名的并集，列类型由底层表推导。当某列的类型在各表间不一致时，该列会表现为Variant，需要配合variantType/variantElement函数按行取出对应类型的值。
本文的操作路径基于官方文档中的完整示例（按年代拆分的网球比赛数据），从建表、比对 schema 到查询与验证。涉及variantType（v24.2.0 引入）和variantElement（v25.2.0 引入），按文中示例执行需要 v25.2.0 或更新的 ClickHouse。
merge() 的语法与参数
语法见 merge 表函数参考：
merge(['db_name',] 'tables_regexp')| 参数 | 说明 |
|---|---|
db_name | 可选，默认currentDatabase()。可以是数据库名、返回数据库名常量的表达式，或REGEXP(expression)用来匹配多个库名 |
tables_regexp | 匹配表名的正则表达式。正则基于 re2（支持 PCRE 子集），大小写敏感 |
临时 Merge 表不提供写入能力；读取自动并行化，且底层表已建索引时会被使用。与 Merge 引擎相同，查询时可以用到两个虚拟列：_table（数据实际读自哪张表）和_database（来自哪个库）。对_table加过滤（例如WHERE _table='xyz'）时只会读取满足条件的表。
准备示例数据：四张 schema 各不相同的表
下面的建表命令来自 官方数据建模指南。它从文档给出的 Jeff Sackmann 网球数据集地址下载 1960s–1990s 四个年代的 CSV 并建表，执行前需要有外网访问权限。注意每个年代刻意用了不同的 schema：
winner_seed类型逐年变化：Nullable(String)→Nullable(UInt8)→Nullable(UInt16)；- 1960s 的
score是String，其余年代用splitByWhitespace拆成Array(String)； - 1990s 把
surface设为Enum，并额外增加walkover、retirement两列。
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/JeffSackmann/tennis_atp/refs/heads/master/atp_matches_{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/JeffSackmann/tennis_atp/refs/heads/master/atp_matches_{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/JeffSackmann/tennis_atp/refs/heads/master/atp_matches_{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/JeffSackmann/tennis_atp/refs/heads/master/atp_matches_{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\')';如果你已有自己的表，无需使用这套数据，只要保证它们能被同一个正则匹配到（本例中是atp_matches*），后续查询逻辑通用。
先用 system.columns 看清各表的差异
查询前把每张表同名列的类型并排列出来，便于判断哪些列会形成Variant：
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) │ └─────────────┴──────────────────┴─────────────────┴───────────────────────────┴───────────────────┘可以确认三处差异：winner_seed从Nullable(String)一路变成Nullable(UInt16)；score在 1960s 是String、其余是Array(String)；1990s 独有的walkover、retirement列在其余表中为 NULL（即该表没有这列）。
用 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'] │ │ 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'] │ │ Stefan Edberg │ ['6-2','6-2'] │ │ Jakob Hlasek │ ['6-3','7-6'] │ └───────────────┴─────────────────────────────────┘merge('atp_matches*')没有指定库名，默认在当前库中按正则atp_matches*匹配上述四张表，一次查询覆盖全部年代。
列类型不一致时：用 variantType + variantElement 分支取值
接着在上一步结果上再加条件：McEnroe 的winner_seed排在第 3 位或更后。由于winner_seed在各表类型不同，合并后是Variant，不能直接拿数值比较。文档给出的做法是用 multiIf 按行分派：先用variantType判断该行存储的具体类型，再用variantElement取出该类型的值；String分支需要先转成数值再比较：
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')::UInt16 >= 3 );文档示例输出：
┌─loser_name────┬─score─────────┬─winner_seed─┐ │ Bjorn Borg │ ['6-3','6-4'] │ 3 │ │ Stefan Edberg │ ['6-2','6-3'] │ 6 │ │ Stefan Edberg │ ['7-6','6-2'] │ 4 │ │ Stefan Edberg │ ['6-2','6-2'] │ 7 │ └───────────────┴───────────────────────────┴───────────┘两个函数的行为见 variantType / variantElement 参考：variantType返回每行的变体类型名（NULL 行为None）；variantElement(variant, type_name[, default_value])取出指定类型的子列，第三个可选参数default_value在变体中不存在该类型时作为默认值。Variant本身的定义与子列读取方式见 Variant 数据类型。
用 _table 虚拟列确认行来自哪张表
把_table加入 SELECT 即可知道每行读自哪张底表（结果与上一查询对应）：
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')::UInt16 >= 3 );文档示例输出：
┌─_table────────────┬─loser_name────┬─score─────────┬─winner_seed─┐ │ atp_matches_1970s │ Bjorn Borg │ ['6-3','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 │ Stefan Edberg │ ['6-2','6-2'] │ 7 │ └───────────────────┴───────────────┴───────────────────────────┴───────────┘_table还能用来判断“哪些表缺列”。先按表统计walkover的取值分布：
SELECT _table, walkover, count() FROM merge('atp_matches*') GROUP BY ALL ORDER BY _table;文档示例输出：
┌─_table────────────┬─walkover─┬─count()─┐ │ atp_matches_1960s │ ᴺᵁᴸᴸ │ 7542 │ │ atp_matches_1970s │ ᴺᵁᴸᴸ │ 39165 │ │ atp_matches_1980s │ ᴺᵁᴸᴸ │ 36233 │ │ atp_matches_1990s │ true │ 128 │ │ atp_matches_1990s │ false │ 37022 │ └───────────────────┴──────────┴─────────┘可以看到只有atp_matches_1990s有walkover的真实值，其余表的行全是 NULL——这正是“缺列”表在合并结果中的表现。如果要在统计中把缺列的行也算进来，需要回落到score列自行判断W/O，而score的类型同样是Variant，所以按类型分派：Array(String)行遍历数组找W/O，String行直接做字符串匹配：
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;文档示例输出：
┌─_table────────────┬─multiIf(isNo⋯, '%W/O%'))─┬─count()─┐ │ atp_matches_1960s │ true │ 242 │ │ atp_matches_1960s │ false │ 7300 │ │ atp_matches_1970s │ true │ 422 │ │ atp_matches_1970s │ false │ 38743 │ │ atp_matches_1980s │ true │ 92 │ │ atp_matches_1980s │ false │ 36141 │ │ atp_matches_1990s │ true │ 128 │ │ atp_matches_1990s │ false │ 37022 │ └───────────────────┴───────────────────────────┴─────────┘此时 1960s–1980s 各表也有true行，说明缺列的行已通过score补上了判断。
验证方式与适用边界
- 验证：按上文的顺序执行——先查
system.columns确认各表实际类型差异，再跑merge()查询，对照文档示例输出核对行数与类型列的表现（缺列表为 NULL、类型列为Variant）。 - 版本：
variantType自 v24.2.0、variantElement自 v25.2.0 可用；本文示例整体需要 v25.2.0+。 - 正则匹配：
tables_regexp大小写敏感且基于 re2；Merge 表自身即使匹配到正则也不会被选入读取，避免循环。 - 只读：
merge()生成的临时 Merge 表不支持写入；查询过滤放在WHERE中即可，对_table的过滤还能把读取范围限定到满足条件的底表。
更多上下文可继续查阅仓库中的 merge 表函数参考、Merge 表引擎 和 Merge table function 指南。
【免费下载链接】ClickHouseClickHouse® is a real-time analytics database management system项目地址: https://gitcode.com/GitHub_Trending/cli/ClickHouse
创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
