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

//思路：
1.先计算出总的播放次数（注意筛选条件是2021年）
SELECT video_id,COUNT(video_id) FROM tb_user_video_log where year(start_time)=2021 GROUP BY video_id ；
2.计算出每个视频被播放了多少秒（同时也要注意筛选条件是2021）
SELECT video_id,TIME_TO_SEC(TIMEDIFF(end_time,start_time)) as Btime FROM tb_user_video_log where year(start_time)=2021;
3.计算出每一个视频被完整播放完的次数
SELECT tb_video_info.video_id,sum(if(tb_video_info.duration<=T2.Btime,1,0)) as FinshClick
FROM tb_video_info natural join (SELECT video_id,TIME_TO_SEC(TIMEDIFF(end_time,start_time)) as Btime FROM tb_user_video_log where year(start_time)=2021) as T2 GROUP BY tb_video_info.video_id;
4.计算平均完播率
SELECT
    T1.video_id,
    ROUND(FinshClick / ALLClick, 3) AS avg_comp_play_rate
FROM
    (
        SELECT
            video_id,
            COUNT(video_id) AS ALLClick
        FROM
            tb_user_video_log
        where year(start_time)=2021
        GROUP BY
            video_id
    ) AS T1
    NATURAL join (
        SELECT
            tb_video_info.video_id,
            sum(if (tb_video_info.duration <= T2.Btime, 1, 0)) as FinshClick
        FROM
            tb_video_info
            natural join (
                SELECT
                    video_id,
                    TIME_TO_SEC (TIMEDIFF (end_time, start_time)) as Btime
                FROM
                    tb_user_video_log
                WHERE YEAR(start_time)=2021
            ) as T2
        GROUP BY
            tb_video_info.video_id
    ) AS T3
ORDER BY
    avg_comp_play_rate DESC;