宁波建网站可按需定制,wordpress用户批量注册,海南省住房公积金管理局官网,软件开发需要的软件目录
数据表描述
问题描述
输出示例
解题思路【重点】
正解代码 数据表描述
有以下两张表#xff1a;
表1#xff1a;用户-视频互动表tb_user_video_log
数据举例#xff1a; 说明#xff1a;
uid-用户ID,video_id-视频ID start_time-开始观看时间end_time-结束观…目录
数据表描述
问题描述
输出示例
解题思路【重点】
正解代码 数据表描述
有以下两张表
表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-视频IDauthor-创作者IDtag-类别标签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:2021年我们在代码中就需要要求年份而我们看看上面的表他并没有年份这一列但是在表1中他有一个用户开始观看时间和用户结束观看世界以及表2中有一个视频的发布时间。在这三个时间中他都是以年月日、时分秒来记录的那我们就可以使用year函数year函数就是从日期中截取年同理其实后面的月、日、时分秒都可以使用这类函数来截取。现在知道怎么取年这个数据了最后一个从哪儿取呢正解从表1的开始观看时间中截取要求2有播放记录的有播放记录的换句话说就是没有播放记录的就不用统计了。这个要求我们其实不用过多考虑因为如果没有播放记录的话表1中就没有这个数据要求3每个视频的完播率每个视频意思就是要按照视频id来进行分组group by.要求4结果保留3位小数需要我们使用一个round函数这个函数就有两个参数第一个参数是你给的数据第二个参数是需要保留的小数位数。要求5按完播率降序排序。最终的结果降序order by desc.
回顾MySQL中各个关键字的执行顺序
fromonjoinwheregroup bywithhavingselectdistinctorder bylimit 我们接下来就根据这个关键字顺序来写
第一个是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咱们就不用看了第八个是selectselect后面跟的是要输出的字段是什么我们根据上面的输出示例可知输出字段有两个一个是视频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;好啦本期就到这里了下期见~
