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

SELECT tag,CONCAT(ROUND(AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>duration,100,TIMESTAMPDIFF(SECOND,start_time,end_time)/duration*100)),2) ,'%') AS avg_play_progress
FROM tb_user_video_log JOIN tb_video_info USING(video_id)
GROUP BY tag
HAVING SUBSTRING_INDEX(avg_play_progress,"%",1)>60
ORDER BY avg_play_progress DESC;

本题要点：

1.计算不同情况下的平均完播率

还是使用IF函数为AVG生成不同情况下的完播率列，进而可以计算平均完播率。

需要注意的是SQL中没有直接对应百分数的函数，因此要想得到百分数，需要进行字符串拼接

2.CONCAT字符串拼接

拼接时注意百分号是字符，需要用单引号引起来，拼接后为字符串

而我们要拿来比较大小的应该是拼接前的数值，因此还需要使用SUBSTRING_INDEX函数进行分割

3.SUBSTRING_INDEX(str, delim, count)：该函数以特定字符为参考进行分割

其中，str 是要处理的原始字符串；delim 是分隔符，可以是单个字符或字符串；count 表示拆分的次数，它决定了返回的子字符串是分隔符出现指定次数之前还是之后的部分。当 count 为正数时，返回从左到右，分隔符出现 count 次之前的子字符串；当 count 为负数时，返回从右到左，分隔符出现 abs(count) 次之后的子字符串。

4.SUBSTRING：该函数以特定位置为参考进行分割，从1开始

-- 形式一：指定起始位置和长度 SUBSTRING(str, pos, len) -- 形式二：只指定起始位置，截取从起始位置到字符串末尾的子串 SUBSTRING(str, pos)

5.HAVING子句

对聚合以后的结果进行筛选需要用HAVING子句而不是WHERE