如何使用uniqCombinedMerge计算ClickHouse两张表的用户交集数量

你原来的写法存在三个核心问题：

• 语法错误：WITH子句中别名的位置错误，应该放在子查询括号外侧，而非WHERE条件末尾
• 逻辑错误：test_a的value字段本身已经是uniqCombinedState类型，不需要再套一层uniqCombinedState()，应该用uniqCombinedMerge()聚合所有符合条件行的state，得到该时间范围内的全局HLL状态
• 函数使用错误：uniqCombinedMerge()只能合并多个state得到并集用户数，无法直接计算交集，计算交集需要使用uniqCombinedIntersectMerge()函数

全量交集查询写法 #

高版本ClickHouse（21.8+）写法 #

直接传入两个全局状态即可计算：

WITH
    -- 聚合得到test_a指定时间范围的全局用户HLL状态
    (SELECT uniqCombinedMerge(value) FROM test_db.test_a WHERE timestamp BETWEEN 1637505413789 AND 1637505431789) AS a_state,
    -- 聚合得到test_b指定时间范围的全局用户HLL状态
    (SELECT uniqCombinedMerge(value) FROM test_db.test_b WHERE timestamp BETWEEN 1637505413789 AND 1637505431789) AS b_state
-- 计算两个HLL的交集用户数
SELECT uniqCombinedIntersectMerge(a_state, b_state) AS intersect_user_count;

低版本兼容写法 #

将两个状态合并到同一个结果集后聚合计算：

WITH
    (SELECT uniqCombinedMerge(value) FROM test_db.test_a WHERE timestamp BETWEEN 1637505413789 AND 1637505431789) AS a_state,
    (SELECT uniqCombinedMerge(value) FROM test_db.test_b WHERE timestamp BETWEEN 1637505413789 AND 1637505431789) AS b_state
SELECT uniqCombinedIntersectMerge(state) AS intersect_user_count
FROM (
    SELECT a_state AS state
    UNION ALL
    SELECT b_state AS state
) t;

按version分组的交集查询写法 #

如果需要按version维度对齐，计算每个version下的用户交集，可使用以下写法：

SELECT
    version_n,
    uniqCombinedIntersectMerge(state) AS intersect_user_count
FROM (
    SELECT version_n, value AS state FROM test_db.test_a WHERE timestamp BETWEEN 1637505413789 AND 1637505431789
    UNION ALL
    SELECT version_n, value AS state FROM test_db.test_b WHERE timestamp BETWEEN 1637505413789 AND 1637505431789
) t
GROUP BY version_n;

注意事项 #

• 两张表的uniqCombinedState生成参数必须完全一致（比如均为对uid字段统计、精度参数相同），否则交集计算结果会出现偏差
• 如果需要计算并集，将uniqCombinedIntersectMerge替换为uniqCombinedMerge即可；如果需要计算差集，替换为uniqCombinedExcludeMerge即可

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