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

1.解题思路：本题目的核心关键点在于[视频的平均完播率如何计算]，以及计算所需的数据如何从表中取出。

视频完播 = 视频开播时间(start_time) - 视频播完时间(end_time) >= 视频总时长(duration)

视频平均完播率 = AVG（视频完播次数 / 视频播放总次数） 或者 视频完播率 = SUM(视频完播) / COUNT(视频播放)

得到计算公式后，就需要从表中提取数据。我们可以发现关键数据存放在两张表里，所以需要进行连表查询（Left Join）。

2.重点学习函数：

日期提取：YEAR(Date) --> 提取日期中的年；

日期计算：TIMESTAMPDIFF(YEAR/MONTH/SECOND, <start_time>, <end_time>) --> 计算时间差

控制流程函数：IF(<condition>, value_if_true, value_if_false) --> 用于判断视频是否完播

聚合函数：AVG(column); --> 计算平均值

数学函数：ROUND(number, decimal_places ) --> 用于对数字进行四舍五入保留小数，decimal_places 指定保留位数。

3.具体代码实现如下：

SELECT
    tvi.video_id,
    ROUND(
        AVG(
            IF (
                timestampdiff (SECOND, tuvl.start_time, tuvl.end_time) >= tvi.duration,
                1,
                0
            )
        ),3
    ) AS avg_comp_play_rate
FROM
    tb_video_info as tvi
    LEFT JOIN tb_user_video_log as tuvl ON tvi.video_id = tuvl.video_id
WHERE
    YEAR(tuvl.start_time) = 2021
GROUP BY
    tvi.video_id
ORDER BY
    avg_comp_play_rate DESC;