题解 | 两种方法求解#每天的日活数及新用户占比#

思路1：日活就是每天访问的不同用户数，所以我们首先要得到一张登录表，登录表记录了每天登录的用户，并按天对用户进行了去重，也就是下面的 t1。而要统计新用户的占比，我们就需要识别每天登录用户中哪些用户是新用户（即第一次登录）。一个可行的思路是，使用窗口函数对每个用户的登录日期进行排序得到下面的 t2。统计的时候进行判断，如果统计当天该用户的序号为 1，则表示用户今天是第一次登录，即为新用户。于是可以写出下面的答案：

with t1 as(		# 用户登录表，记录了用户 id 和登录时间，对每天的登录用户进行了去重
    select uid,date(in_time) dt
    from tb_user_log
    union		# union 实现去重，union all 不去重
    select uid,date(out_time) dt
    from tb_user_log
),
t2 as (		# 对每个用户的登录日期进行排序，注册日期的序号是 1
    select
        uid,dt,
        row_number() over(partition by uid order by dt) rn
    from t1
)
# 获得答案
select
    dt,
    count(uid) dau,
    round(sum(if(rn=1,1,0))/count(uid),2) uv_new_ration
from t2
group by dt
order by dt;

思路2：同样的思路得到用户登录表 t1。每个用户的注册日期肯定在登录表中是最小的，因此用 min 函数可以得到用户登录表即下面的 t2。最后在求解答案的时候，用 t1 left join t2，关联的字段是 uid 以及日期，由于使用了 left join，t1 中每个用户所有的登录日期都得到了保留，count 计数即可得到 dau，而 t2 表中不是当天注册的用户 uid 和 reg_dt 都为null，同样使用 count 计数就能得到当天的新用户。于是可以得到下面的答案：

with t1 as(
    select uid,date(in_time) dt
    from tb_user_log
    union
    select uid,date(out_time) dt
    from tb_user_log
),
t2 as (		# 得到用户注册表
    select
        uid,min(dt) reg_dt
    from t1
    group by uid
)
select
    dt,
    count(t1.uid) dau,
    round(count(t2.uid)/count(t1.uid),2) uv_new_ration
from t1 left join t2 on t1.uid=t2.uid and t1.dt=t2.reg_dt
group by dt
order by dt;

欢迎关注公众号BigDataNotes，每天分享大数据开发面经和大数据技术