Mysql 解法, 执行用时: 36ms, 内存消耗: 6412KB, 提交时间: 2022-01-27

WITH 11_new_user AS (
    -- 2021年11月新用户
    SELECT
        uid 11_new_user,
        first_log
    FROM(
        -- 所有用户第一次登录时间
        SELECT
            uid,
            DATE(MIN(in_time)) first_log
        FROM tb_user_log
    GROUP BY uid
    ) t1
    WHERE DATE_FORMAT(first_log,'%Y%m') = '202111'
),all_user AS (
    -- 所有用户登录日期
    SELECT
        DISTINCT uid, DATE(in_time) log_time
    FROM tb_user_log
    UNION
    SELECT
        DISTINCT uid, DATE(out_time)
    FROM tb_user_log
)
-- 关联新用户和所有用户
SELECT
    n.first_log dt,
    -- count(DISTINCT 11_new_user) cnt_new_log,
    -- count(DISTINCT if(datediff(log_time,first_log) = 1,uid,null)) cnt_new_log
    round(count(DISTINCT if(datediff(log_time,first_log) = 1,uid,null)) /
        count(DISTINCT 11_new_user),2) uv_left_rate
FROM 11_new_user n,all_user a
WHERE uid = 11_new_user
GROUP BY n.first_log
ORDER BY n.first_log;

Mysql 解法, 执行用时: 36ms, 内存消耗: 6516KB, 提交时间: 2022-01-23

select a.first_day, round(count(distinct b.uid)/count(distinct a.uid),2) as rate
from (
    select uid, date(min(in_time)) as first_day
    from tb_user_log 
    -- where date_format(in_time, '%Y-%m') = '2021-11'
    group by uid
    having date_format(min(in_time), '%Y-%m') = '2021-11') a 
left join (
    select uid, date(in_time) as dt 
    from tb_user_log
    union 
    select uid, date(out_time) as dt
    from tb_user_log) b 
on a.uid = b.uid and datediff(b.dt, a.first_day) = 1
group by a.first_day
order by a.first_day

Mysql 解法, 执行用时: 36ms, 内存消耗: 6528KB, 提交时间: 2022-01-27

#新用户首次登录
with t1 as (
    select uid,dt,rank()over(partition by uid order by dt asc) as rk
    from (
select uid,date_format(in_time,'%Y-%m-%d') as dt
    from tb_user_log
    union
    select uid,date_format(out_time,'%Y-%m-%d') as dt
        from tb_user_log
        )temp
),
t2 as (
select a.uid,a.dt as dt1,a.rk as rk1,b.dt as dt2,b.rk as rk2
from t1 a 
join t1 b on a.uid=b.uid
where a.rk=1
and timestampdiff(day, a.dt,b.dt)=1
    and a.dt like '2021-11%'
    )
select t1.dt,round(ifnull(count(distinct t2.uid)/count(distinct t1.uid),0),2) as rate
from t1
left join t2 on t1.uid=t2.uid and t1.dt=t2.dt1
where t1.dt like '2021-11%'
and t1.rk=1
group by t1.dt
order by dt

Mysql 解法, 执行用时: 37ms, 内存消耗: 6296KB, 提交时间: 2021-12-01

select
date(t.in_time) dt
,round(sum(case when t.b_in_time is not null then 1 else 0 end)/count(*),2) uv_left_rate
from
(
    select
    a.uid
    ,a.in_time
    ,a.out_time
    ,b.in_time b_in_time
    ,b.out_time b_out_time
    ,row_number()over(partition by a.uid order by b.in_time) t_rank
    from
    tb_user_log a
    left join
    tb_user_log b
    on a.uid=b.uid and date_add(date(b.out_time),interval -1 day)=date(a.in_time)
    where
    (a.uid,date(a.out_time)) in (select uid,min(date(out_time)) from tb_user_log group by uid)
    and year(a.in_time)='2021' and month(a.in_time)='11'
) t
where 
t.t_rank=1
group by
date(t.in_time)
order by
dt

Mysql 解法, 执行用时: 37ms, 内存消耗: 6308KB, 提交时间: 2021-12-16

select a.dt, 
    round(count(distinct b.uid)/count(distinct a.uid),2) uv_left_rate
from (
    select uid , min(date(in_time)) dt
    from tb_user_log 
    group by uid ) a
left join 
     (select uid,date(in_time) dt
    from tb_user_log
    union 
    select uid,date(out_time) dt
    from tb_user_log) b
on DATE_ADD(a.dt,INTERVAL 1 DAY) = b.dt and a.uid=b.uid
where a.dt like '2021-11%'
group by a.dt 
order by a.dt