ClickHouse SQL 查询优化：计算每日活跃用户数和新增用户数

本文将介绍如何使用 ClickHouse SQL 查询来计算每日活跃用户数和新增用户数，并进行优化。

SQL 代码

SELECT ts,
  if(i >= 31, length(arrayDistinct(arrayFlatten(arraySlice(total, i-30, 31)))), length(arrayDistinct(arrayFlatten(arraySlice(total, 1, i))))) as total_num,
  -- if(i >= 31, arraySum(arraySlice(total_increase, i-30, 31)), arraySum(arraySlice(total_increase, 1, i))) as total_increase_num
FROM 
  (
    SELECT groupArray(A.Ds) as t,
      groupArray(app_id_list) as total,
      -- groupArray(app_count) as total_increase
    from (
        SELECT Ds, groupArray(AppIdentifier) as app_id_list
        FROM (
            select AppIdentifier, Ds
            from log_iMonkey_iOS_overview
            GROUP BY AppIdentifier, Ds
          )
        GROUP BY Ds
        order by Ds
      ) as A 
      -- join 
      -- (
      --   SELECT min_Ds as Ds,
      --     count(distinct AppIdentifier) as app_count
      --   FROM (
      --       select AppIdentifier,
      --         min(Ds) as min_Ds
      --       from log_iMonkey_iOS_overview
      --       GROUP BY AppIdentifier
      --     )
      --   GROUP BY min_Ds
      --   order by min_Ds
      -- ) as C on A.Ds = C.Ds
  ) ARRAY
  JOIN t as ts, arrayEnumerate(t) as i where ts >= toDate('monthly_active_new_business.start')

代码解读

数据来源: 该查询使用 log_iMonkey_iOS_overview 表作为数据源，其中包含 AppIdentifier 和 Ds 字段，分别代表应用标识符和日期。
分组: 首先，通过 GROUP BY AppIdentifier, Ds 和 GROUP BY Ds 将数据按应用标识符和日期进行分组，计算每一天每个应用的活跃用户数。
数组操作: 使用 groupArray 函数将每个日期对应的所有应用标识符集合保存到 total 数组中。
时间窗口: 使用 arraySlice 函数截取 total 数组的子数组，分别计算过去30天和当前日期之前的所有活跃用户数。
去重: 使用 arrayDistinct 函数去除数组中的重复元素。
合并: 使用 arrayFlatten 函数将二维数组转换为一维数组。
计数: 使用 length 函数计算去重后的数组长度，即为活跃用户数。

优化建议

使用预聚合: 可以使用预聚合表来减少查询时间，例如创建一张每日活跃用户数的预聚合表，然后直接查询该表。
使用索引: 为 AppIdentifier 和 Ds 字段创建索引，加快查询速度。
优化数据类型: 考虑使用更合适的字段类型，例如使用 Date 类型存储日期，以提高查询性能。

总结

本文介绍了使用 ClickHouse SQL 查询每日活跃用户数和新增用户数的方法，并给出了优化建议。通过合理使用 ClickHouse 的数据处理功能，我们可以高效地分析用户数据，为业务决策提供支持。