题解 | #各个视频的平均完播率#

with t1 as (
    select vl.uid, vl.video_id, unix_timestamp(vl.start_time) start_time, unix_timestamp(vl.end_time) end_time, vi.duration
    from tb_user_video_log vl join  tb_video_info vi on vl.video_id=vi.video_id
    where date_format(vl.start_time,'%Y') = 2021
), t2 as (
    select video_id,end_time-start_time ts,duration
    from t1
), t3 as (
    select video_id, 
        sum(case when ts >= duration then 1 else 0 end) over(partition by video_id) /
            count(1) over(partition by video_id) avg_comp_play_rate
    from t2
), t4 as (
    select distinct video_id, cast(avg_comp_play_rate as decimal(16,3)) avg_comp_play_rate
    from t3
    order by avg_comp_play_rate desc

)
select * from t4