一些补充知识
replace
replace函数可以将表里面的某个字段值替换成另一个字符,批量操作时直接用sql会比程序来判断执行更快
replace(str,str1,str2);
str:源字符串
str1:待替换字符串
str2:替换后的字符串
例题1.平均播放进度大于60%的视频类别
问题分析
(1)播放进度——
播放时长:timestampdiff(second,start_time,end_time) watch_time
视频时长:duration
(timestampdiff(second,start_time,end_time)/duration)*100
(2)如果大于视频时长播放进度记为100%——
if(watch_time>duration,100,(watch_time/duration)*100)
(3)加%拼接:concat((timestampdiff(second,start_time,end_time)/duration)*100,’%’)
(4)保留两位小数:round(,2)
(5)进度大于60%输出——
一开始并不知道replace函数所以在与%拼接前又嵌套了一层筛选,用where筛选了大于60的;
(6)平均——avg();
(7)排序——ORDER BY ;
过程重写
不用replace
SELECT tag,CONCAT(avg_play_progress1,'%') avg_play_progress
FROM
(SELECT tag,round(play_progress,2) avg_play_progress1
FROM
(SELECT t1.tag,
avg(if(watch_time>duration,100,(watch_time/duration)*100)) play_pr