如何用ClickHouse按时间范围准确统计新增与回访用户

问题说明 #

现有活跃用户表结构如下：

需求为按指定时间范围统计区间内的新增用户数、回访用户数，当前存在回访用户维度下user_id重复计数的问题。
示例数据如下：

校验规则：统计时间范围为2021年12月15日至2021年12月20日时，预期结果为2个新增用户、2个回访用户。
当前使用ClickHouse存储数据，通过Superset做可视化，原有尝试的SQL无法输出正确结果，原有代码如下：

WITH data AS ( 
    SELECT user_id, 
           created_time, 
           MIN(created_time) OVER (PARTITION BY user_id) AS first_time, 
           ROW_NUMBER() OVER(PARTITION BY user_id) AS no_login 
    FROM default.active_user
), data2 AS ( 
    SELECT *, 
          (CASE WHEN no_login=1 THEN 'new' ELSE 'returning' END) AS category_user 
    FROM data
) 
SELECT user_id, 
       COUNT(distinct user_id) AS active_user, 
       category_user,
       created_time 
FROM data2 
GROUP BY user_id, 
         category_user, 
         created_time 

原有代码问题 #

• 用户类型判定逻辑错误：没有基于用户全量历史首次活跃时间判定，仅用窗口函数排序的行号判断，在时间范围过滤场景下会把早于统计区间首次登录的用户误判为新增
• 分组逻辑错误：按created_time分组会把同一用户在不同日期的活跃记录拆分为多条，导致回访用户重复计数
• 聚合逻辑错误：同时SELECT user_id和COUNT(DISTINCT user_id)，分组粒度为单个用户+单天，无法得到区间维度的汇总统计值

正确SQL实现 #

WITH user_first_active AS (
    -- 第一步：计算每个用户全量历史的首次活跃时间，作为新老用户判定依据
    SELECT 
        user_id,
        MIN(created_time) AS first_active_time
    FROM default.active_user
    GROUP BY user_id
),
range_active AS (
    -- 第二步：筛选统计区间内的活跃用户，关联首次活跃时间打用户类型标签
    SELECT DISTINCT
        a.user_id,
        CASE
            -- 首次活跃时间落在统计区间内即为新增用户
            WHEN u.first_active_time BETWEEN '2021-12-15 00:00:00' AND '2021-12-20 23:59:59' THEN 'new'
            -- 区间内有活跃、但首次活跃早于区间起点即为回访用户
            ELSE 'returning'
        END AS user_category
    FROM default.active_user a
    INNER JOIN user_first_active u ON a.user_id = u.user_id
    -- 统计时间范围过滤，接入Superset时可替换为平台时间筛选宏
    WHERE a.created_time BETWEEN '2021-12-15 00:00:00' AND '2021-12-20 23:59:59'
)
-- 第三步：按用户类型聚合，统计去重后的用户数
SELECT
    user_category,
    COUNT(DISTINCT user_id) AS user_count
FROM range_active
GROUP BY user_category

如果需要按日/周/月等细粒度时间维度统计，只需要在range_active CTE中增加对应粒度的时间字段（例如toDate(a.created_time) AS stat_date），最终GROUP BY增加stat_date字段即可，不会出现回访用户重复计数的问题。

内容的提问来源于stack exchange，提问作者Nguyễn Thành Luân