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

with t as (
    SELECT 
      t1.video_id,
      CASE WHEN 
         # end_time-start_time >= duration
        timestampdiff (second,start_time,end_time) >= duration
      THEN 1 ELSE 0 END as flag
    FROM tb_user_video_log t1
    LEFT JOIN tb_video_info t2
    ON t1.video_id = t2.video_id
    WHERE YEAR(t1.start_time) = 2021
) 
SELECT 
    video_id, ROUND(avg(flag), 3) as avg_comp_play_rate
    #avg的用法：直接除计算次数了，用1则直接省去了除以分母。假如是 1 1 0 1 0总共五个，那avg函数会直接返回（1+1+1）/5 = 0.6,注意，avg会忽略null值。 
from t
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC