题解|窗口函数WHERE的位置|#各个视频的平均完播率#

## 先增加一列作为是否完播,然后计数：完播的数量/总数量
## 结束时间-开始时间：TIMESTAMPDIFF(second,start_time,end_time)
## duration >= TIMESTAMPDIFF(second,start_time,end_time)
## IF((end_diff>duration,'%Y%m')1,0) AS end_video

SELECT video_id,ROUND(SUM(end_video)/COUNT(end_video),3) AS avg_comp_play_rate
FROM(
    SELECT video_id, IF(end_diff >= duration,1,0) AS end_video
    FROM(
        SELECT uid,a.video_id,start_time,end_time,duration,TIMESTAMPDIFF(second,start_time,end_time) AS end_diff
        FROM tb_user_video_log a
        LEFT JOIN tb_video_info b
        ON a.video_id = b.video_id
        )t1
    WHERE YEAR(start_time) = 2021
    )t2
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC