【MySQL习题】各个视频的平均完播率【全网最详细教学】

目录

数据表描述

问题描述

输出示例

解题思路【重点】

正解代码 

---

数据表描述

有以下两张表：

表1：用户-视频互动表tb_user_video_log

数据举例：

 说明：

• uid-用户ID,
• video_id-视频ID
•  start_time-开始观看时间
• end_time-结束观看时间
•  if_follow-是否关注
•  if_like-是否点赞
• if_retweet-是否转发
•  comment_id-评论ID

表2：短视频信息表tb_video_info

数据举例：

说明：

• video_id-视频ID
• author-创作者ID
• tag-类别标签
• duration-视频时长（秒）
• release_time-发布时间

---

问题描述

        计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序

注：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。

---

输出示例

说明：

        视频id2001在2021年10月有3次播放记录，观看时长分别为30秒、24秒、34秒，视频时长30秒，因此有两次是被认为完成播放了的，故完播率为0.667；

        视频id2002在2021年9月和10月共2次播放记录，观看时长分别为42秒、30秒，视频时长60秒，故完播率为0.000。

---

解题思路【重点】

        首先，我们来分解问题：

各个要求如何解：

1. 要求1:2021年：我们在代码中就需要要求年份，而我们看看上面的表，他并没有年份这一列，但是在表1中他有一个用户开始观看时间和用户结束观看世界，以及表2中有一个视频的发布时间。在这三个时间中，他都是以年月日、时分秒来记录的，那我们就可以使用year函数，year函数就是从日期中截取年（同理，其实后面的月、日、时分秒都可以使用这类函数来截取）。现在知道怎么取年这个数据了，最后一个从哪儿取呢？正解：从表1的开始观看时间中截取
2. 要求2：有播放记录的：有播放记录的，换句话说就是没有播放记录的就不用统计了。（这个要求，我们其实不用过多考虑，因为如果没有播放记录的话，表1中就没有这个数据）
3. 要求3：每个视频的完播率：每个视频意思就是要按照视频id来进行分组，group by.
4. 要求4：结果保留3位小数：需要我们使用一个round函数，这个函数就有两个参数，第一个参数是你给的数据，第二个参数是需要保留的小数位数。
5. 要求5：按完播率降序排序。最终的结果降序，order by desc.

回顾，MySQL中各个关键字的执行顺序：

from>on>join>where>group by>with>having>select>distinct>order by>limit 

        我们接下来，就根据这个关键字顺序来写：

• 第一个是from：就是查什么表嘛，我们在这里是查，两张表：用户-视频互动表和短视频信息表。代码：

from tb_user_video_log a,tb_video_info b
#顺便给重命名了一下

• 第二、三个是on、join，咱们现在先不看了
• 第四个是where：就是给出两张表汇总后的限制条件，第一个就是两张表笛卡尔积后，只取数据的视频id相等的数据；根据上述分析要求观看年份是2021的数据。代码：

from tb_user_video_log a,tb_video_info b
where a.video_id = b.video_id and year(start_time) = 2021

• 第五个是group by：根据上述分析，要求要按照不同的视频id进行分组。代码：

from tb_user_video_log a,tb_video_info b
where a.video_id = b.video_id and year(start_time) = 2021
group by a.video_id

• 第六、七个是 with、having，咱们就不用看了
• 第八个是select：select后面跟的是要输出的字段是什么？我们根据上面的输出示例可知，输出字段有两个，一个是视频id，另一个是该视频对应的完播率。视频id好说，那视频完播率我们怎么计算呢？首先我们上面已经写出来的代码，我们可以知道我们已经把两个表的数据整合到一起了，并且按照视频id已经做出了分组。我们现在就只需要计算每个组的完播率的视频个数除以这个组视频的总数，就是这个组的完播率了。计算完播率个数，我们可以使用sum函数，sum函数中再嵌套一个if关键字【mysql的if关键字的使用：if(参1，参2，参3)参数1就是一个判断，为真还是假；为真时取第二个参数的值，为假时取第三个参数的值】；计算这个组的总视频个数使用count(*)就可以了。计算出这个组的完播率后，我们在最外层套一个round函数，就可以了。最后给计算出完播率的这一部分取个名字，就是as avg_comp_play_rate。代码：

select a.video_id,
    round(sum(if(end_time - start_time >= duration,1,0))/count(*),3)
 as avg_comp_play_rate
from tb_user_video_log a,tb_video_info b
where a.video_id = b.video_id and year(start_time) = 2021
group by a.video_id

• 第九个distinct就不看了
• 第十个是order by：排序，根据上述要求需要对最终的结果按照完播率来降序排序。代码：

select a.video_id,
    round(sum(if(end_time - start_time >= duration,1,0))/count(*),3)
 as avg_comp_play_rate
from tb_user_video_log a,tb_video_info b
where a.video_id = b.video_id and year(start_time) = 2021
group by a.video_id
order by avg_comp_play_rate desc;

• 第十一个是limit，这里就不看了，用不上~

所有代码就结束了，拿去运行就可以了~

---

正解代码

select a.video_id,
    round(sum(if(end_time - start_time >= duration,1,0))/count(*),3)
 as avg_comp_play_rate
from tb_user_video_log a,tb_video_info b
where a.video_id = b.video_id and year(start_time) = 2021
group by a.video_id
order by avg_comp_play_rate desc;

或者： 

select a.video_id,
    round(sum(if(end_time - start_time >= duration,1,0))/count(*),3)
 as avg_comp_play_rate
from tb_user_video_log a left join tb_video_info b on a.video_id = b.video_id
where year(start_time) = 2021
group by a.video_id
order by avg_comp_play_rate desc;

好啦，本期就到这里了，下期见~