ClickHouse中如何对匹配指定键的MAP值求和？

场景与表结构 #

每分钟约有10000组数据流入，每组包含4624个网格参考（编号0-4623）对应的观测人数，数据存储在MergeTree引擎的grid_counters表中，表结构定义如下：

CREATE TABLE grid_counters
(
    `storeID` UInt16,
    `camIP` IPv4,
    `timestamp` DateTime,
    `grid_counts` Map(UInt16, UInt8)
)
ENGINE = MergeTree
PARTITION BY (toMonday(timestamp), storeID)
ORDER BY (timestamp, storeID)

需求 #

需要查询任意时间段（最长1天）内，指定网格参考键对应的总观测人数。尝试直接使用sumMap函数未达预期——该函数会返回包含指定键的Map结果，还需要额外解析才能得到总和，希望能直接返回最终的求和数值。

解决方案 #

通过sumMapFiltered结合arraySum实现需求，具体SQL如下：

SELECT arraySum(toSum) AS total
FROM
(
    SELECT
        (sumMapFiltered([2057, 2058, 2124, 2125, 2126, 9])(grid_counts.keys, grid_counts.values) AS r).1,
        r.2 AS toSum
    FROM grid_counters
    -- 可添加WHERE条件限定时间段，例如：WHERE timestamp BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'
)

逻辑说明 #

1. sumMapFiltered([指定键数组])接收Map的键数组和值数组，过滤出指定键对应的键值对并分别求和，返回一个包含两个数组的元组：第一个数组是匹配的键，第二个数组是对应键的累计和；
2. 提取求和后的数值数组toSum，再用arraySum计算该数组的总和，直接得到所有指定网格的观测总人数。

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