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

with cte as (
select uid,a.vid,time_len,weekday(start_time)+1 as weekth,
           unix_timestamp(end_time)-unix_timestamp(start_time) as seconds
from user_play_log_tb a left join video_info_tb b on a.vid=b.vid ),
cte1 as (
select weekth, avg(if(seconds<time_len,seconds/time_len,1)) as play_ratio
from cte
group by weekth
order by weekth
)
select weekth,
concat(round((lead(play_ratio,1,(select play_ratio from cte1 where weekth=1))over(order by weekth)-play_ratio)/play_ratio*100,1),'%') as play_ratio_delta
from cte1

两个率不应该这么减