题解 | #每天的日活数及新用户占比#
每天的日活数及新用户占比
http://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
和之前一样的逻辑，理顺了之后越做越快，6行代码搞定。
一、题目理解
- 统计每天的日活数及新用户占比
- 新用户占比=当天的新用户数÷当天活跃用户数（日活数）。
- 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过。
- 新用户占比保留2位小数，结果按日期升序排序。
这几句话给的核心信息是，in_time和out_time都算作是活跃日，同时要把用户首次登录的日期找出来，最后再计算新用户占比。
二、解题步骤
1）老方法，先建立一张拥有基本信息的用户活跃基础表
- 这张表要包含用户id，活跃日，成为新用户的日期。因为用户可能1天活跃N次，所以要做去重处理。
- 活跃日直接并联in_time和out_time
- 成为新用户日期，用窗口函数来取：MIN(DATE(in_time))OVER(PARTITION BY uid) AS new_dt
代码如下：
SELECT DISTINCT uid,DATE(in_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log UNION SELECT DISTINCT uid,DATE(out_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log
2）定义新用户
如果dt=new_dt那这天就是用户首次登录成为新用户的日子啦~
WITH t1 AS( SELECT DISTINCT uid,DATE(in_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log UNION SELECT DISTINCT uid,DATE(out_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log ) SELECT uid,dt,IF(dt=new_dt,1,0) '是否为新用户（是为1，不是为0）' FROM t1;
3）计算新用户占比，结果按照日期升序，输出结果。
- 日活：COUNT(1)
- 新用户数：SUM（是否为新用户）
- 新用户占比：ROUND(SUM(新用户)/COUNT(1),2)
WITH t1 AS( SELECT DISTINCT uid,DATE(in_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log UNION SELECT DISTINCT uid,DATE(out_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log ) SELECT dt,COUNT(1) dau,ROUND(SUM(IF(dt=new_dt,1,0))/COUNT(1),2) uv_new_ratio FROM t1 GROUP BY dt ORDER BY dt ASC;
完成啦~是不是很好理解~
SQL解题集 文章被收录于专栏
这是牛客SQL相关的解题集
投递快看等公司6个岗位
