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

### 整体的思路是新增一列标注为是否是当天的新用户active_user 当天是新用户：1，当天不是新用户：0
### 每天的日活数 = SUM(active_user)
### 每天的新用户数 = COUNT(active_user)
### 确实是否是当天的新用户时，需要使用窗口函数进行分组比较，而不是使用GROUP BY直接进行分组

# SELECT uid, DATE_FORMAT(in_time,'%Y-%m-%d') AS dt
# FROM tb_user_log
# UNION
# SELECT uid,DATE_FORMAT(out_time,'%Y-%m-%d') AS dt
# FROM tb_user_log

# IF(MIN(dt) OVER(PARTITION BY uid ORDER BY dt) = dt,1,0) AS active_user

# SELECT uid,dt,IF(MIN(dt) OVER(PARTITION BY uid ORDER BY dt) = dt,1,0) AS active_user
# FROM(
#      SELECT uid,DATE_FORMAT(in_time,'%Y-%m-%d') AS dt
#      FROM tb_user_log
#      UNION
#      SELECT uid,DATE_FORMAT(out_time,'%Y-%m-%d') AS dt
#      FROM tb_user_log
# )t1

SELECT dt,COUNT(active_user) AS dau, ROUND(SUM(active_user)/COUNT(active_user),2) AS uv_new_ratio
FROM(
    SELECT uid,dt,IF(MIN(dt) OVER(PARTITION BY uid) = dt,1,0) AS active_user
    FROM(
        SELECT uid,DATE_FORMAT(in_time,'%Y-%m-%d') AS dt
        FROM tb_user_log
        UNION
        SELECT uid,DATE_FORMAT(out_time,'%Y-%m-%d') AS dt
        FROM tb_user_log
    )t1
)r2
GROUP BY dt

？？？？？？为什么如果用户不是SUM(active_user)？？？？？好奇怪

明白了！！dau是当日活跃用户数，不是当日新增数！！不要混掉