题解 | #对比一周的播放完成率变化#

with t1 as (select weekth,avg(wbl) as avg_ratio from (select weekth,(case when times>time_len then 1 else times/time_len end) as wbl from(select uid,u.vid,timestampdiff(second,start_time,end_time) as times,time_len,weekday(start_time)+1 as weekth from user_play_log_tb as u left join video_info_tb as v using(vid)) t)k group by weekth)
select t1.weekth,concat(round(100*(avg_ratio1/avg_ratio-1),1),'%') as play_ratio_delta from t1 inner join (select case when weekth=0 then 7 else weekth end as weekth,avg_ratio as avg_ratio1 from (select (weekth-1)%7 as weekth,avg_ratio from t1) t2) t3 using (weekth) order by weekth