题解 | #每天的日活数及新用户占比#

WITH
    daily_active AS (
        SELECT
            uid,
            DATE (in_time) act_dt
        FROM
            tb_user_log
        UNION
        SELECT
            uid,
            DATE (out_time) act_dt
        FROM
            tb_user_log
    ),
    new_users AS (
        SELECT
            uid,
            DATE (MIN(in_time)) first_dt
        FROM
            tb_user_log
        GROUP BY
            uid
    ),
    daily_act_cnt AS (
        SELECT
            act_dt,
            COUNT(*) dau
        FROM
            daily_active
        GROUP BY
            act_dt
    )

#需要考虑dau表与new表连接--》有几个连接就有几个新增
#但需要注意这里left join 会出现null的情况
SELECT 
    act_dt dt,
    dau,
    ROUND(
        COUNT(n.uid)/dau,
        2
    )
FROM daily_act_cnt d
    LEFT JOIN new_users n ON n.first_dt = d.act_dt
GROUP BY act_dt
ORDER BY dt

步骤 1：计算日活跃用户

首先，计算每天的活跃用户数，考虑 in_time 和 out_time 两个时间点。

• daily_active 子查询：选择每个用户的 in_time 和 out_time，并将它们转换为日期格式，得到用户的活跃日期 act_dt。使用 UNION 合并 in_time 和 out_time 的结果，去除重复的日期。

步骤 2：计算新增用户

接下来，计算每个用户的首次活跃日期，即新增用户的日期。

• new_users 子查询：选择每个用户的 in_time 最小值作为用户的首次活跃日期 first_dt。使用 DISTINCT 和 GROUP BY 去除重复的用户，确保每个用户只有一个首次活跃日期。

步骤 3：计算每天的日活跃用户数

然后，计算每天的活跃用户数（DAU）。

• daily_act_cnt 子查询：对 daily_active 的结果进行分组，按活跃日期 act_dt 统计每天的活跃用户数 dau。

步骤 4：计算新增用户在 DAU 中的比例

最后，计算每天新增用户在日活跃用户数（DAU）中的比例。

• 主查询：将 daily_act_cnt 和 new_users 进行左连接，连接条件是 new_users 的首次活跃日期 first_dt 等于 daily_act_cnt 的活跃日期 act_dt。由于使用左连接，有可能出现 new_users 表中没有匹配记录的情况，导致 new_users 表中的 uid 为 NULL。选择每天的活跃日期 act_dt 和活跃用户数 dau。计算每天新增用户数 COUNT(n.uid) 并除以 dau 得到比例，使用 ROUND 函数保留两位小数。按 act_dt 进行分组，并按日期排序。

关键点

• 处理 NULL 值：在进行左连接时，可能会出现 new_users 表中没有匹配记录的情况，导致 n.uid 为 NULL。这种情况下，COUNT(n.uid) 只会计算非 NULL 值，因此能够正确统计新增用户数。
• 比例计算：计算比例时，先计算每天的新增用户数 COUNT(n.uid)，再除以每天的活跃用户数 dau，得到新增用户在 DAU 中的比例。