如何在ClickHouse中聚合数组值且不产生行重复？

核心思路 #

利用ClickHouse的数组原生聚合函数直接在数组上计算，避免arrayJoin展开导致的行重复；结合arrayFilter实现数组元素过滤；用arrayReduce实现通用聚合逻辑，同时保证行级列（如line_id）的聚合准确性。

一、优化底层数据存储（减少后续开销） #

你当前将数组拼接为JSON字符串存储，后续解析会产生额外性能损耗。建议直接存储数组类型：

select
  line_id,
  start_timestamp,
  end_timestamp,
  groupArray(metadata) as metadata_array  -- 直接存储数组，无需拼接JSON字符串
from
...
group by line_id, start_timestamp, end_timestamp

若metadata是JSON格式字符串，ClickHouse支持Array(String)或Array(JSON)类型存储，后续操作无需额外解析。

二、满足需求的具体方案 #

1. 同时实现数组聚合与行级聚合（无行重复） #

直接使用数组聚合函数计算数组内元素的统计值，外层行级聚合不受影响：

SELECT
  -- 行级聚合：统计唯一line_id数量，无重复计数
  count(DISTINCT line_id) AS distinct_line_count,
  -- 数组聚合：先计算每行数组内value的总和，再累加所有行的结果
  sum(arraySum(x -> JSONExtractFloat(x, 'value'), metadata_array)) AS total_value_sum,
  -- 数组聚合：计算所有数组元素的平均值（通过arrayJoin展开但不影响行级统计）
  avg(arrayJoin(arrayMap(x -> JSONExtractFloat(x, 'value'), metadata_array))) AS total_value_avg
FROM my_table

2. 单行列+数组聚合结果（每行唯一） #

为每个line_id返回唯一行，同时携带数组内元素的聚合值：

SELECT
  line_id,
  start_timestamp,
  end_timestamp,
  -- 数组内value求和
  arraySum(x -> JSONExtractFloat(x, 'value'), metadata_array) AS value_sum,
  -- 数组内value平均值
  arrayAvg(x -> JSONExtractFloat(x, 'value'), metadata_array) AS value_avg,
  -- 过滤数组元素后统计数量（仅保留value>100的元素）
  arrayCount(x -> JSONExtractFloat(x, 'value') > 100, metadata_array) AS high_value_count
FROM my_table

3. 过滤数组元素后聚合 #

先用arrayFilter筛选符合条件的数组元素，再执行聚合：

SELECT
  line_id,
  -- 仅对value>50的元素求和
  arraySum(x -> JSONExtractFloat(x, 'value'), 
           arrayFilter(x -> JSONExtractFloat(x, 'value') > 50, metadata_array)) AS filtered_value_sum
FROM my_table

4. 通用聚合（无需提前指定聚合类型） #

使用arrayReduce函数（ClickHouse 21.8+支持），动态传入聚合函数名称实现通用逻辑：

SELECT
  line_id,
  -- 动态指定sum聚合
  arrayReduce('sum', arrayMap(x -> JSONExtractFloat(x, 'value'), metadata_array)) AS value_sum,
  -- 动态指定avg聚合
  arrayReduce('avg', arrayMap(x -> JSONExtractFloat(x, 'value'), metadata_array)) AS value_avg,
  -- 动态指定max聚合
  arrayReduce('max', arrayMap(x -> JSONExtractFloat(x, 'value'), metadata_array)) AS value_max
FROM my_table

5. 必须展开数组时的折中方案 #

若需将数组元素作为独立行处理，同时保证行级聚合准确，可通过子查询分离逻辑：

WITH array_elements AS (
  SELECT
    line_id,
    JSONExtractFloat(arrayJoin(metadata_array), 'value') AS value
  FROM my_table
)
SELECT
  -- 直接从原表统计唯一line_id，避免重复计数
  (SELECT count(DISTINCT line_id) FROM my_table) AS distinct_line_count,
  sum(value) AS total_value_sum,
  avg(value) AS total_value_avg
FROM array_elements

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