如何在ClickHouse中按数组列元素分组求和另一数组列

针对你的需求，我们可以通过数组展开+分组聚合+数组重组的方式实现，具体SQL如下：

完整实现SQL #

WITH sensor_agg AS (
    SELECT
        timestamp,
        sortArray(groupArray(sensor_type)) AS sensor_type_sorted,
        groupArray(sum_val) AS sum_val_by_sensor_type
    FROM (
        SELECT
            timestamp,
            sensor_type,
            SUM(values) AS sum_val
        FROM your_table
        ARRAY JOIN sensor_type, priority, values  -- 同步展开三个等长数组
        GROUP BY timestamp, sensor_type
    )
    GROUP BY timestamp
),
priority_agg AS (
    SELECT
        timestamp,
        sortArray(groupArray(priority)) AS priority_sorted,
        groupArray(sum_val) AS sum_val_by_priority
    FROM (
        SELECT
            timestamp,
            priority,
            SUM(values) AS sum_val
        FROM your_table
        ARRAY JOIN sensor_type, priority, values
        GROUP BY timestamp, priority
    )
    GROUP BY timestamp
)
SELECT
    s.timestamp,
    s.sensor_type_sorted,
    s.sum_val_by_sensor_type,
    p.priority_sorted,
    p.sum_val_by_priority
FROM sensor_agg s
INNER JOIN priority_agg p ON s.timestamp = p.timestamp
ORDER BY s.timestamp;

代码说明 #

1. 数组展开：使用ARRAY JOIN将每个timestamp对应的三个数组（sensor_type、priority、values）同步拆分为单行数据，确保每个传感器类型、优先级与对应数值一一对应。
2. 分组求和：在子查询中按timestamp + sensor_type、timestamp + priority分别分组，计算对应values的总和。
3. 数组重组与排序：通过groupArray将分组后的维度值（sensor_type/priority）和求和结果重新组合为数组，再用sortArray对维度数组进行排序，保证结果数组的顺序一致。
4. 结果关联：将两个维度的聚合结果通过timestamp关联，得到最终的合并结果。

注意事项 #

• 确保sensor_type、priority、values三个数组的长度完全一致，否则ARRAY JOIN会导致数据匹配错误。
• 若表数据量较大，可利用timestamp的单调递增特性，结合ClickHouse的分区索引优化查询性能。

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