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

WITH t1 AS (
    SELECT uid,
           in_time
    FROM tb_user_log
    UNION ALL
    SELECT uid,
           out_time AS in_time
    FROM tb_user_log
),
t2 AS (
    SELECT uid,
           in_time,
           RANK() over (PARTITION BY uid ORDER BY uid ASC, in_time ASC ) AS num_1
    FROM t1
),
t3 AS (
    SELECT uid,
           DATE (in_time) AS dt
    FROM t2
    WHERE num_1 = 1
),
t4 AS (
    # 新用户
    SELECT dt,
           count(uid) AS new_num
    FROM t3
    GROUP BY dt
),
t5 AS (
    SELECT uid,
           DATE (in_time) AS dt
    FROM t2
),
t6 AS (
    SELECT dt,
           count(DISTINCT uid) AS dau
    FROM t5
    GROUP BY dt
)
SELECT t6.dt,
       t6.dau,
       IFNULL(ROUND(t4.new_num / t6.dau ,2),0.00) AS uv_new_ratio
       FROM t6
LEFT JOIN t4
ON t6.dt = t4.dt
ORDER BY dt;

# 关键在于 IFNULL 进行 none 值转化为 0 值