题目描述

本题需计算2021年里有播放记录的每个视频的完播率，并按完播率降序排序，结果保留三位小数。涉及两张数据表：

  • tb_user_video_log（用户 - 视频互动表）：记录了用户ID（uid）、视频 ID（video_id）、开始观看时间（start_time）、结束观看时间（end_time）等用户与视频的互动信息。
  • tb_video_info（短视频信息表）：包含视频ID（video_id）、创作者ID（author）、视频时长（duration，单位：秒）等视频相关信息。

题目中明确视频完播率是指完成播放次数占总播放次数的比例，且当结束观看时间与开始播放时间的差大于或等于视频时长时，视为完成播放。

题目分析

本题核心在于多表连接、时间差计算以及聚合运算。首先要将用户-视频互动表和短视频信息表通过视频ID连接起来；接着计算每次观看的时长，并与视频实际时长对比，判断是否完成播放；最后，对每个视频分组，计算完播率并按要求排序输出。关键在于准确使用时间函数计算观看时长，以及合理运用聚合函数计算完播率。

代码详解

SELECT v.video_id,
ROUND(AVG(IF(TIMESTAMPDIFF(SECOND, start_time, end_time)>=duration, 1, 0)), 3) 
AS avg_comp_play_rate
FROM tb_user_video_log AS uv
JOIN tb_video_info AS v
ON (uv.video_id = v.video_id)
WHERE YEAR(start_time) = 2021
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC

关键点

  1. 多表连接：利用video_id将tb_user_video_log表（别名uv）和tb_video_info表（别名v）连接；
  2. 时间差计算与判断：
    • 通过TIMESTAMPDIFF(SECOND, start_time, end_time)计算每次观看视频的时长（以秒为单位），并用IF函数判断观看时长是否大于或等于视频的实际时长duration；
    • 计算观看时长时，注意不能使用TIMEDIFF函数，因为TIMEDIFF函数返回的是HH:MM:SS这样的时间格式，只是表示两个时间的差值形式，不方便直接与以秒为单位的duration字段进行数值比较；
  3. 聚合运算与完播率计算：通过GROUP BY video_id对每个视频进行分组，再用AVG函数计算每个视频的平均完成播放情况（即完播率）。最后，借助ROUND函数将完播率结果保留三位小数。