在ClickHouse中使用聚合函数统计唯一元组的出现次数

要在ClickHouse中按play_id统计(game_id, level_id)唯一组合的访问次数，你可以使用groupMap系列聚合函数实现，具体步骤如下：

1. 修改物化视图Schema #

将存储聚合状态的字段定义为AggregateFunction(groupMap, Tuple(String, String), UInt64)，该类型会维护(game_id, level_id)元组到访问次数的映射状态：

CREATE MATERIALIZED VIEW mv_play_session_stats
(
    play_id UUID,
    games_levels AggregateFunction(groupMap, Tuple(String, String), UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY play_id
AS
SELECT
    toUUID(simpleJSONExtractString(gamePayload, 'play_id')) AS play_id,
    groupMapState(tuple(simpleJSONExtractString(gamePayload, 'game_id'), simpleJSONExtractString(gamePayload, 'level_id')), 1) AS games_levels
FROM your_source_event_table
GROUP BY play_id;

2. 查询聚合结果 #

方式1：获取每个会话的完整映射 #

使用groupMapMerge函数还原存储的聚合状态，得到(game_id, level_id)到访问次数的Map：

SELECT
    play_id,
    groupMapMerge(games_levels) AS game_level_visit_counts
FROM mv_play_session_stats;

返回结果示例：

方式2：展开为多行明细 #

如果需要将Map拆分为每行一个元组和对应次数的格式，可结合arrayJoin、mapKeys和mapValues：

SELECT
    play_id,
    game_level_tuple.1 AS game_id,
    game_level_tuple.2 AS level_id,
    visit_count
FROM mv_play_session_stats
ARRAY JOIN
    mapKeys(groupMapMerge(games_levels)) AS game_level_tuple,
    mapValues(groupMapMerge(games_levels)) AS visit_count;

返回结果示例：

原理说明 #

• groupMapState：写入物化视图时，将每个(game_id, level_id)元组作为key，每次事件对应数值1作为value，累加相同key的value得到访问次数，并生成聚合状态存储。
• groupMapMerge：查询时还原聚合状态，得到完整的元组-次数映射关系。

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