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

with tiaojian as (
select
weekday(start_time)+1 as weekdate,
(sum(case when timestampdiff(second,start_time,end_time)>=time_len then 1 else timestampdiff(second,start_time,end_time)/time_len end))/count(tb.id) as delta
from user_play_log_tb tb inner join 
video_info_tb vb on tb.vid=vb.vid
group by weekday(start_time)+1
order by weekday(start_time)+1
),tiaojian1 as (
select 
weekdate,delta,
lead(delta,1,(select delta from tiaojian where weekdate=1))over(order by weekdate) as m
from tiaojian 
)

select 
weekdate as weekth ,
concat(
round(
(
(m-delta)/delta)*100,1),"%") as  play_ratio_delta

from tiaojian1