ClickHouse AggregatingMergeTree带TTL聚合表配置问题咨询

尝试创建带TTL的聚合表时，无法为AggregateFunction类型的列设置新值，同时不带TTL时n_rate计算异常。相关配置与问题如下：

基础表 #

DROP TABLE IF EXISTS basic_test_table;
CREATE TABLE basic_test_table (
    ts DateTime comment 'timestamp',
    n UInt32 comment 'generated integer',
    s String comment 'generated 1-letter string',
    t String comment 'TOP|RARE'
)
Engine = MergeTree
ORDER BY ts;

聚合表与物化视图 #

DROP TABLE IF EXISTS agg_basic_table;
CREATE TABLE agg_basic_table (
    ts DateTime,
    n_avg AggregateFunction(avg, Float32) comment 'simple average',
    n_sum SimpleAggregateFunction(sumWithOverflow, UInt32) comment 'simple sum',
    n_rate Float32 materialized n_sum / 60.0 comment 'its not working',
    s_uniq AggregateFunction(uniq, String) comment 'count of uniq strings',
    t_top AggregateFunction(anyHeavy, String) comment 'most frequent string'
)
engine AggregatingMergeTree()
order by (toStartOfTenMinutes(ts), ts)
ttl ts + interval 10 minute
group by toStartOfTenMinutes(ts) set
    ts = min(toStartOfTenMinutes(ts)),
    n_avg = avg(n_avg), //problem here
    n_sum = sum(n_sum), //it works because we use SimpleAggregateFunction
    n_rate = avg(n_rate),//problem here
    s_uniq = uniqMerge(s_uniq), //problem here
    t_top = anyHeavyMerge(t_top); //problem here

DROP TABLE IF EXISTS mv_agg_basic;
create materialized view if not exists mv_agg_basic to agg_basic_table
    as select
        toStartOfMinute(b.ts) as ts,
        avgState(b.n::Float32) as n_avg,
        sum(b.n::UInt32) as n_sum,
        uniqState(b.s) as s_uniq,
        anyHeavyState(b.t) as t_top
    from basic_test_table b
    group by (ts)
    order by (ts);

数据插入语句 #

insert into basic_test_table (ts, n, s, t)
select now() + interval (5000 - number) second, intDiv(number, 1000) + 1, randomFixedString(1), if(number % 10 >= 4, 'TOP', 'RARE') from numbers(10000) settings max_block_size=10000;

查询语句 #

select ts, avgMerge(n_avg), sum(n_sum), avg(n_rate), uniqMerge(s_uniq), anyHeavyMerge(t_top) from agg_basic_table group by ts order by ts;

预期结果 #

时间戳（近期数据1分钟间隔，其他数据10分钟间隔）、n的平均值、n的总和、n的速率、s中唯一字符串数量、t中最热门的字符串。

现存问题 #

1. 带TTL时，AggregateFunction类型列（n_avg、s_uniq、t_top）无法设置新值，n_rate也存在同样问题；仅SimpleAggregateFunction类型的n_sum能正常工作。
2. 不带TTL时可运行，但n_rate计算异常：每秒插入1个值为1的数据时显示1，插入两次后仍显示1，预期应为2。
3. 疑问：使用带-State和-Merge函数的AggregateFunction是创建聚合表的唯一方式吗？

1. TTL合并时AggregateFunction列的赋值修正 #

在AggregatingMergeTree的TTL合并规则中，AggregateFunction类型列必须使用对应的-Merge函数进行合并，不能用普通聚合函数：

• n_avg需替换为avgMerge(n_avg)，而非avg(n_avg)
• s_uniq的uniqMerge(s_uniq)、t_top的anyHeavyMerge(t_top)是正确用法
• 物化列n_rate无法在TTL合并时自动更新，建议改为查询时计算，避免存储失效

修正后的聚合表配置：

DROP TABLE IF EXISTS agg_basic_table;
CREATE TABLE agg_basic_table (
    ts DateTime,
    n_avg AggregateFunction(avg, Float32) comment 'simple average',
    n_sum SimpleAggregateFunction(sumWithOverflow, UInt32) comment 'simple sum',
    s_uniq AggregateFunction(uniq, String) comment 'count of uniq strings',
    t_top AggregateFunction(anyHeavy, String) comment 'most frequent string'
)
engine AggregatingMergeTree()
order by (toStartOfTenMinutes(ts), ts)
ttl ts + interval 10 minute
group by toStartOfTenMinutes(ts) set
    ts = min(toStartOfTenMinutes(ts)),
    n_avg = avgMerge(n_avg),
    n_sum = sum(n_sum),
    s_uniq = uniqMerge(s_uniq),
    t_top = anyHeavyMerge(t_top);

对应的查询语句（实时计算n_rate）：

select 
    ts, 
    avgMerge(n_avg), 
    sum(n_sum), 
    sum(n_sum)/60.0 as n_rate, 
    uniqMerge(s_uniq), 
    anyHeavyMerge(t_top) 
from agg_basic_table 
group by ts 
order by ts;

2. n_rate计算异常的原因与解决 #

之前将n_rate设为物化列，仅在数据插入时计算一次；后续TTL合并更新n_sum时，物化列不会重新计算，导致数值滞后。改为查询时基于最新的n_sum计算，即可得到正确结果。

3. 聚合表创建方式说明 #

带-State和-Merge函数的AggregateFunction是AggregatingMergeTree的标准通用方案，但不是唯一的聚合表创建方式：

• 若仅需求和类预聚合，可使用SummingMergeTree，功能单一但性能高效
• 普通MergeTree配合视图也能实现实时聚合，但性能远不如预聚合的AggregatingMergeTree
• 对于复杂多维度聚合，AggregatingMergeTree配合-State/-Merge函数仍是最优选择

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