ClickHouse中AggregatingMergeTree支持嵌套字段聚合吗？如何写物化视图？

ClickHouse完全支持对Nested类型字段进行这类聚合操作，但需要注意字段类型匹配以及聚合函数的正确使用方式，以下是具体实现步骤：

1. 修正目标聚合表结构 #

你的目标表testing_agg中client_id定义为UInt32，但原表testing的client_id是UInt64，可能会导致数据截断或溢出，建议统一类型为UInt64。修正后的表创建语句：

CREATE TABLE testing_agg
(
    client_id UInt64,
    records AggregateFunction(count, UInt32),
    nested_field Nested(
        key String,
        value1 AggregateFunction(sum, UInt32),
        value2 AggregateFunction(sum, UInt32)
    )
) ENGINE = AggregatingMergeTree
PRIMARY KEY (client_id);

2. 创建正确的物化视图 #

要实现按client_id聚合，并对Nested字段中每个key对应的value1/value2求和，需要先通过ARRAY JOIN展开Nested字段，再分组聚合后重新组织成Nested结构。物化视图语句如下：

CREATE MATERIALIZED VIEW testing_agg_mv TO testing_agg
AS
SELECT
    client_id,
    countState(toUInt32(1)) AS records,
    arrayAgg(key) AS nested_field.key,
    arrayAgg(sumState(value1)) AS nested_field.value1,
    arrayAgg(sumState(value2)) AS nested_field.value2
FROM testing
ARRAY JOIN nested_field
GROUP BY client_id;

语句说明： #

• ARRAY JOIN nested_field：将原表的Nested类型字段展开为多行，使每个key、value1、value2成为独立的行记录，确保能针对每个key做聚合。
• countState(toUInt32(1))：生成count聚合的状态值，对应目标表的records字段（count函数需要传入UInt32类型参数，这里用toUInt32(1)作为占位符）。
• arrayAgg(sumState(value1))：对同一client_id下的相同key的value1求和，再用arrayAgg将聚合结果按key的顺序组织成数组，对应Nested字段的value1数组；value2和key的处理逻辑同理。

3. 查询聚合结果 #

要获取最终的聚合数值，需要使用Merge系列函数解析聚合状态：

SELECT
    client_id,
    countMerge(records) AS total_records,
    arrayMap(
        (k, v1, v2) -> (k, sumMerge(v1), sumMerge(v2)),
        nested_field.key,
        nested_field.value1,
        nested_field.value2
    ) AS aggregated_nested
FROM testing_agg
GROUP BY client_id;

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