SELECT t1.dt AS dt, COUNT(t1.uid) AS dau, ROUND(COUNT(t2.uid) / COUNT(t1.uid), 2) AS uv_new_ratio 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 LEFT JOIN ( SELECT uid, DATE(MIN(in_time)) dt FROM tb_user_log GROUP BY uid )t2 ON t1.dt = t2.dt AND t1.uid = t2.uid GROUP BY t1.dt ORDER BY dt
本题的妙处在于,可以通过使用COUNT函数分别统计不同表中的同一字段来达到题目要求。
首先准备两个子表,左表为所有用户活跃过的时间,右表为用户第一次登录的时间。使用左连接将这两个表连接起来,那么t2.uid这一字段在用户“不是第一次登录”时值就为NULL。使用COUNT函数分别统计t1.uid和t2.uid,则前者统计的是某日总活跃人数,后者统计的是该日的新用户人数。