如何在ClickHouse中简化多列argMax查询获取最新值？含表结构优化建议

这个问题我之前也碰到过——当数据列多的时候，手写一堆argMax(col, insert_ts)确实太繁琐了。下面分两种方向给你解决方案：一种是简化现有查询的写法，另一种是从表结构设计上彻底解决这个问题。

一、简化现有查询的动态SQL方案 #

ClickHouse可以通过查询系统元数据来自动生成argMax的语句，不用手动逐个列写：

1. 利用system.columns生成列表达式 #

先查询目标表中除了pk和insert_ts之外的所有数据列，自动拼接出每个列的argMax表达式：

SELECT groupConcat('argMax(', name, ', insert_ts) AS ', name, ', ') 
FROM system.columns 
WHERE database = '你的数据库名' 
  AND table = '你的表名' 
  AND name NOT IN ('pk', 'insert_ts')

这个查询会返回类似argMax(data1, insert_ts) AS data1, argMax(data2, insert_ts) AS data2, ...的字符串，你只需要把它拼到主查询里：

SELECT pk, [上面的结果] 
FROM 你的表名 
GROUP BY pk

2. 用脚本/客户端自动生成完整SQL #

如果不想手动拼接，可以用Python、Bash等脚本调用ClickHouse客户端，先获取列表达式，再自动生成并执行完整查询。比如用Python的clickhouse-driver库就能轻松实现，这样每次查询都不用重复写列名。

二、表结构优化思路（从根源避免繁琐查询） #

其实如果你的核心需求就是快速获取每个主键的最新版本数据，换个合适的表引擎会比写复杂SQL更高效：

1. ReplacingMergeTree（最推荐） #

这是ClickHouse专门为「同主键保留最新版本数据」设计的引擎，完美匹配你的场景：

CREATE TABLE your_table (
    pk Int64,
    insert_ts DateTime,
    data1 String,
    data2 Int32,
    -- 其他数据列
) ENGINE = ReplacingMergeTree(insert_ts) -- 指定用insert_ts作为版本判断依据
ORDER BY pk; -- 主键作为排序键，同pk的行会被合并

• 插入数据时正常插入即可，ClickHouse会在后台合并数据时自动保留每个pk对应最大insert_ts的行。
• 查询时如果需要强一致性的最新数据，用SELECT * FROM your_table FINAL;（注意FINAL会触发即时合并，适合小表或离线场景）；如果可以接受少量延迟，直接SELECT * FROM your_table;就能获取大部分最新数据。

2. Materialized View（预计算最新值） #

如果不想换引擎，可以建一个物化视图预计算每个主键的最新值，之后查询视图就像查普通表一样简单：

-- 先建一个存储最新值的表
CREATE TABLE latest_data (
    pk Int64,
    data1 String,
    data2 Int32,
    -- 其他数据列
) ENGINE = MergeTree() ORDER BY pk;

-- 建物化视图，自动同步最新值
CREATE MATERIALIZED VIEW mv_latest_data TO latest_data AS
SELECT 
    pk,
    argMax(data1, insert_ts) AS data1,
    argMax(data2, insert_ts) AS data2,
    -- 其他数据列的argMax
FROM your_source_table
GROUP BY pk;

之后查询直接SELECT * FROM latest_data;即可，物化视图会自动在源表插入数据时更新最新值。

3. AggregatingMergeTree（适合多聚合场景） #

如果除了取最新值，还需要其他聚合操作，可以用这个引擎把聚合逻辑预存在表里：

CREATE TABLE agg_table (
    pk Int64,
    data1 AggregateFunction(argMax, String, DateTime),
    data2 AggregateFunction(argMax, Int32, DateTime),
    -- 其他数据列的聚合函数定义
) ENGINE = AggregatingMergeTree() ORDER BY pk;

-- 插入时将数据转为聚合状态
INSERT INTO agg_table
SELECT 
    pk,
    argMaxState(data1, insert_ts),
    argMaxState(data2, insert_ts),
    -- 其他列的argMaxState
FROM your_source_table
GROUP BY pk;

-- 查询时合并聚合状态得到最新值
SELECT 
    pk,
    argMaxMerge(data1) AS data1,
    argMaxMerge(data2) AS data2,
    -- 其他列的argMaxMerge
FROM agg_table;

这个方案适合需要同时维护多种聚合结果的场景，但配置稍复杂。

内容的提问来源于stack exchange，提问作者Jonathan