一、知识点梳理&拓展

1）字符串拼接concat(字段名，‘拼接内容’)  
concat(100,'%')#返回100%
2）字符串检索剪切substring_index(字段名,‘检索符号’,相对位置)   
substring_index('adbbccdeeed','d',1) #返回a，解读为字符串中第2个d左边的所有字符
substring_index('adbbccdeeed','d',2) #返回adbbcc，解读为字符串中第2个d左边的所有字符
3）timediff()函数和timestampdiff()函数的使用区别
  • timestampdiff(时间格式，开始时间，结束时间)求时间差，按照格式可以返回为以下任意格式（second，minute，hour，day，week，month，year...）
  • timediff(结束时间，开始时间)一般直接返回day格式如果小于day则返回00:00:00格式。

二、解题步骤

1）计算各类视频的平均播放进度
  • 根据题意，各类视频的平均播放率=（用户1播放率+用户2播放率+...用户N播放率）/N=AVG(每个用户播放率)

2）播放进度=播放时长÷视频时长*100% 
播放时长:  TIMESTAMPDIFF(SECOND,start_time,end_time)*100 AS play_time
播放进度：play_time/duration
带入完整的play_time：TIMESTAMPDIFF(SECOND,start_time,end_time)*100/duration AS play_progess #每个视频的播放率
3）当播放时长大于视频时长时，播放进度均记为100%。
  • 当播放率>=1时，返回1；播放率<1时，返回播放率（为了后面拼接‘%’，此处需要*100） 
CASE WHEN play_progess >=100 THEN 100
ELSE play_progess END
  • 带入完整的play_progess 
CASE WHEN TIMESTAMPDIFF(SECOND,start_time,end_time)*100/duration >=100 THEN 100
ELSE TIMESTAMPDIFF(SECOND,start_time,end_time)*100/duration END
  • 按照类目求平均播放率,结果保留2位小数 
CONCAT(ROUND(AVG(case_play_progess),2),'%') avg_play_progress
  • 带入完整的 case_play_progess 
SELECT tag,     CONCAT(ROUND(AVG(CASE WHEN TIMESTAMPDIFF(SECOND,start_time,end_time)*100/duration >=100 THEN 100      ELSE TIMESTAMPDIFF(SECOND,start_time,end_time)*100/duration END),2),'%') avg_play_progress            
FROM tb_video_info a RIGHT JOIN tb_user_video_log b ON a.video_id=b.video_id
GROUP BY a.tag
#求每一类视频的平均播放率

4）将进度大于60%的类别输出。
  • SQL无法直接对%格式的数据进行比较（被定义为字符串数据），所以需要提取百分数中间的数字。 
SUBSTRING_INDEX(avg_play_progress,"%",1)>60
5）结果保留两位小数，并按播放进度倒序排序。 
SELECT tag,
	CONCAT(ROUND(AVG(CASE WHEN TIMESTAMPDIFF(SECOND,start_time,end_time)*100/duration >=100 THEN 100
	                 ELSE TIMESTAMPDIFF(SECOND,start_time,end_time)*100/duration END),2),'%') avg_play_progress            
FROM tb_video_info a RIGHT JOIN tb_user_video_log b ON a.video_id=b.video_id
GROUP BY a.tag
HAVING SUBSTRING_INDEX(avg_play_progress,"%",1)>60
ORDER BY avg_play_progress desc;