*****题解 | #平均播放进度大于60%的视频类别#

问题分解：

• 关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)；
• 按视频类别分组：GROUP BY tag
• 计算每个类别的平均播放进度：播放进度=播放时长÷视频时长*100%播放时长=TIMESTAMPDIFF(SECOND, start_time, end_time)；特殊情况：播放时长大于视频时长时，播放进度为100%（加个IF判断）平均进度=AVG(每个进度)结果保留2位小数：ROUND(x, 2)百分比格式化：CONCAT(x, '%')计算公式：

• 筛选播放进度>60%的视频类别：HAVING avg_play_progress > 60

细节问题：

• 表头重命名：as
• 按播放进度倒序排序：ORDER BY avg_play_progress DESC;
• 百分比的表示则先把avg*100 再用concat和%链接即可 因为是计算出答案后在对答案进行筛选 所以用having

知识点梳理

1）字符串拼接concat(字段名，‘拼接内容’) 

2）字符串检索剪切substring_index(字段名,‘检索符号’,相对位置)  

3）timediff()函数和timestampdiff()函数的使用区别

• timestampdiff(时间格式，开始时间，结束时间)求时间差，按照格式可以返回为以下任意格式（second，minute，hour，day，week，month，year...）
• timediff(结束时间，开始时间)一般直接返回day格式如果小于day则返回00:00:00格式。

# # 
# 问题：计算各类视频的平均播放进度，将进度大于60%的类别输出。
# 注：
# 播放进度=播放时长÷视频时长*100%，当播放时长大于视频时长时，播放进度均记为100%。
# 结果保留两位小数，并按播放进度倒序排序。

# 播放时长 timestampdiff(second,start_time,end_time) 
# 视频时长 duration 
# 如果低于视频时长 要除以 timestampdiff(second,start_time,end_time) / duration  *100%

/*
# 没使用concat连接% 这个是错的 
select tag,
round (
    sum(
        if(timestampdiff(second,start_time,end_time)>duration ,1, timestampdiff(second,start_time,end_time) /   duration )
        )
/ count(uid)  ,2 )
as 	avg_play_progress

from tb_user_video_log a, tb_video_info b
where a.video_id=b.video_id 
group by tag
order by avg_play_progress desc;
*/


# 且不能将1改为00% 这样也是错的
/*
select tag,
concat(
round (
    sum(
        if(timestampdiff(second,start_time,end_time)>duration ,1, timestampdiff(second,start_time,end_time) /   duration )
        )
/ count(uid)  ,2 )*100 #应该先乘100 再取 2，自己实验过了 count(uid)  ,2 )* 是错的
,'%')
as 	avg_play_progress

from tb_user_video_log a, tb_video_info b
where a.video_id=b.video_id 
group by tag
having avg_play_progress>60
order by avg_play_progress desc;
*/



# 且不能将1改为00% 这样也是错的
# 正确
select tag,
concat(
round (
   ( sum(
        if(timestampdiff(second,start_time,end_time)>duration ,1, timestampdiff(second,start_time,end_time) /   duration )
        )
/ count(uid) )*100 ,2 ) 

#应该先乘100 再取 2，自己实验过了 count(uid)  ,2 )* 是错的
,'%')
as 	avg_play_progress

from tb_user_video_log a, tb_video_info b
where a.video_id=b.video_id 
group by tag
having avg_play_progress>60
order by avg_play_progress desc;

# 除了使用sum/count(uid) 还可以直接使用AVG函数，这个是直接取平均 会比上面简单很多

select tag,
concat(
round (
   ( AVG(
        if(timestampdiff(second,start_time,end_time)>duration ,1,
        timestampdiff(second,start_time,end_time) / duration )
   ) *100),2)
,'%')
as 	avg_play_progress

from tb_user_video_log a, tb_video_info b
where a.video_id=b.video_id 
group by tag
having avg_play_progress>60
order by avg_play_progress desc;