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

# Q:统计每天的日活数及新用户占比
# 字段：dt	、dau	、uv_new_ratio(new_uv/dau)

# 标记新用户 if&min over
with table1 as(
    select distinct uid,
    date_format(in_time,'%Y-%m-%d') as dt,
    if(in_time = min(in_time)over(partition by uid),1,0) as if_new
    from tb_user_log
    UNION
    select distinct uid,
    date_format(out_time,'%Y-%m-%d') as dt,
    if(out_time = min(in_time)over(partition by uid),1,0) as if_new
    from tb_user_log
)

select dt,
count(distinct uid) as dau, # 日活
# sum(if_new) as new_uv,
round(sum(if_new) / count(distinct uid),2) as uv_new_ratio
from table1
group by dt


# select date_format(in_time,'%Y-%m-%d') as dt,
# count(distinct uid) as dau, # 日活
# count(case when in_date = out_date then distinct uid else) as 
# # sum(if_new) as new_uv,
# round(sum(if_new) / count(distinct uid),2) as uv_new_ratio
# from table1
# group by date_format(in_time,'%Y-%m-%d')


# 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



# 注意点： 
# 1.标记新用户常规操作: 
# if(start_time = min(start_time)over(partition by uid))
# 2.跨天，判断是否存在跨天操作，存在则添加一个