题解 | 最近7天每天的人均停留时长和次均有效时长

with tmp
as (
    select 
    date(start_time) dt
    ,sum(timestampdiff(second , start_time , end_time))  / count(1) tm11
    from user_play_log_tb a join (select max(date(end_time)) dt1 from user_play_log_tb) as b
    where date(start_time) between date_sub(dt1 , interval 6 day) and dt1 and timestampdiff(second , start_time , end_time) >= 3
    group by 1
)
select 
a.dt
,avg_stay_time_len_user
,ifnull(round(tm11, 1), 0) avg_stay_time_len_once
from
(   select 
    date(start_time) dt
    ,round(sum(timestampdiff(second , start_time , end_time)) / count(distinct uid) , 1) avg_stay_time_len_user
    from user_play_log_tb a join (select max(date(end_time)) dt1 from user_play_log_tb) as b
    where date(start_time) between date_sub(dt1 , interval 6 day) and dt1
    group by 1
) a left join tmp on tmp.dt = date(a.dt)