ClickHouse中按指定Key统计数组元素出现次数的最优实现方法

嘿，这个需求我之前也碰到过，用ClickHouse的数组展开+分组聚合再重组的思路就能完美搞定，给你一步步拆解：

首先，如果你还没有测试数据，可以先创建表并插入示例数据：

CREATE TABLE test_table (
    key String,
    seq String,
    words Array(String)
) ENGINE = MergeTree()
ORDER BY key;

INSERT INTO test_table VALUES
('1', '123', ['AAA', 'BBB']),
('1', '456', ['AAA', 'CCC']);

然后执行下面的查询语句就能得到你想要的结果：

SELECT
    key,
    groupArray((word, count)) AS result_words
FROM (
    SELECT
        key,
        word,
        count(*) AS count
    FROM test_table
    ARRAY JOIN words AS word  -- 把数组拆成单行元素
    WHERE key = '1'           -- 过滤目标key
    GROUP BY key, word        -- 按key和单词分组统计次数
    ORDER BY count DESC, word -- 可选：按次数降序、单词排序，让结果更规整
)
GROUP BY key;

逻辑拆解： #

• ARRAY JOIN：这一步是核心，把每个数组里的元素拆分成独立的行，原来的两行数据会变成四行：('1','123','AAA'), ('1','123','BBB'), ('1','456','AAA'), ('1','456','CCC')；
• 分组统计：过滤出key='1'的数据后，按key和word分组，用count(*)算出每个单词的出现次数；
• 重组数组：最后再按key分组，用groupArray把（单词，次数）的键值对打包成数组，就得到了你期望的格式。

这个方案逻辑清晰，充分利用了ClickHouse的数组处理和聚合能力，性能也很优秀，适合这种数组元素统计的场景。

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