# 指标:新用户占比=当天的新用户数÷当天活跃用户数(日活数)
# 方法1:临时表+连接
# 1.日活数
# SELECT dt,count(uid) dau
# from (
# select uid,date(in_time) dt
# from tb_user_log
# union
# select uid,date(in_time)
# from tb_user_log
# ) t
# group by dt
# 2.当天新用户数
# select min_dt,count(uid)
# from (
# SELECT uid,min(dt) min_dt
# from (
# select uid,date(in_time) dt
# from tb_user_log
# union
# select uid,date(in_time)
# from tb_user_log
# ) t
# group by uid
# ) t1
# group by min_dt
#3.整合
# with dau_t as (
# SELECT dt,count(uid) dau
# from (
# select uid,date(in_time) dt
# from tb_user_log
# union
# select uid,date(out_time)
# from tb_user_log
# ) t
# group by dt
# ), -- 日活表
# new_user_t as (
# select min_dt,count(uid) new_cnt
# from (
# SELECT uid,min(date(in_time)) min_dt
# from tb_user_log
# group by uid
# ) t
# group by min_dt
# ) -- 用户第一次登陆表
# select dau_t.dt,dau,if(round(new_cnt/dau,2) is null,0.00,round(new_cnt/dau,2)) uv_new_ratio
# from dau_t
# left join new_user_t
# on dau_t.dt=new_user_t.min_dt
# 方法2:求每个用户按照日期先后的累计和或者排名
# 1.求出每个用户按照日期先后的累计和或者排名
# select *,rank() over (partition by uid order by date ) as times
# from
# (select uid,left(in_time,10) as date
# from tb_user_log
# union
# SELECT uid,left(out_time,10) as date
# from tb_user_log) tmp
# # 2.按照日期分组计算指标
# select date,count(uid) as dau,
# round( sum(if(times=1,1,0))/count(uid) ,2)
# from
# (select *,rank() over (partition by uid order by date ) as times
# from
# (select uid,left(in_time,10) as date
# from tb_user_log
# union
# SELECT uid,left(out_time,10) as date
# from tb_user_log) tmp) base
# GROUP by date
# order by date
代码借鉴@通辽可汗克鸽勃 ,在此表示感谢!