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

select
    dt,
    round(sum(times) / count(distinct uid),1) as avg_stay_time_len_user,
    case when max(times)<3 then 0.0 else (round(sum(case when times>3 then times end) / count(case when times>3 then uid end),1)) end as avg_stay_time_len_once
from
    (
        select
            uid,
            vid,
            date_format (start_time, '%Y-%m-%d') as dt,
            timestampdiff(day,start_time,(select max(start_time) from user_play_log_tb)) as days,
            timestampdiff (second, start_time, end_time) as times
        from
            user_play_log_tb
    ) t
where days<7
group by
    dt