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

WITH tongji AS (
    SELECT 
    a.uid,
    a.video_id,
    a.start_time,
    a.end_time,
    CASE WHEN (a.end_time - a.start_time >= b.duration) THEN 1 ELSE 0 END AS "is_wanbo"
FROM tb_user_video_log as a
LEFT JOIN tb_video_info as b
ON a.video_id = b.video_id
WHERE 1=1 
# AND YEAR(b.release_time) = 2021
AND YEAR(a.start_time) = 2021
AND YEAR(a.end_time) = 2021

)
, juhe AS
(
    SELECT 
        video_id,
        COUNT(*) AS shuliang,
        SUM(is_wanbo) AS wanboshuliang
    FROM tongji
    GROUP BY video_id
)
SELECT 
    video_id,
    ROUND(wanboshuliang/shuliang,3) AS avg_comp_play_rate
FROM juhe
ORDER BY avg_comp_play_rate DESC ;
主要分为三步：
1.表连接并且查询出播放数据在21年的
2.创建辅助列来判断一个视频是否完成了播放
3.进行聚合