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

# 难点1：如果in_time和out_time跨天了，在两天里都记为该用户活跃过。
# 那就union，把两个时间合并成一个字段；因为最终是计算“每天的用户数”
# 难点2：新用户占比，每天的"新"都是相对前面的所有日期而言的
# 看到这种就想到窗口函数了，order by+日期

SELECT dt, COUNT(DISTINCT uid) dau, # 求每个dt有多少个uid，即dau
ROUND(SUM(IF(rk=1,1,0))/COUNT(DISTINCT uid),2) uv_new_ratio
# 只保留第一个日期的uid记为1，此时的uid在这个日期是新用户，sum计算总共有多少个新用户; 以dt分组，就是每个dt有多少新用户
FROM
    (
# 以uid分组，以dt排序，计算有多少dt；即每个uid在多少个dt出现；第一个日期就是新用户
# dt会有大量重复(因为我们把in_time跟out_time合并了),用count可能会计算多次呢？所以我选择row_number对dt进行不重复排序
    SELECT uid, dt, ROW_NUMBER()OVER (PARTITION BY uid ORDER BY dt) rk 
    FROM
        (
        SELECT uid, DATE(in_time) AS dt
        FROM tb_user_log
        UNION 
        SELECT uid, DATE(out_time) AS dt
        FROM tb_user_log
        ) t1
    ) t2
GROUP BY dt
ORDER BY dt