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

SELECT video_id, ROUND(cy/ca,3) as avg_comp_play_rate
FROM(
SELECT logt.video_id as video_id, SUM(IF(TIMESTAMPDIFF(SECOND, logt.start_time, logt.end_time) >= infot.duration, 1, 0)) OVER(PARTITION BY logt.video_id ORDER BY logt.video_id) as cy, COUNT(logt.video_id) OVER(PARTITION BY logt.video_id ORDER BY logt.video_id) as ca
FROM tb_user_video_log as logt
INNER JOIN tb_video_info as infot
ON logt.video_id = infot.video_id
WHERE YEAR(logt.start_time)="2021"
) as re
GROUP BY video_id, ROUND(cy/ca,3)
ORDER BY ROUND(cy/ca,3) DESC