SQL学习（2）：大厂真题实例-视频平均播放进度

SQL学习（2）：大厂真题实例-视频平均播放进度

题目来源：《牛客题霸：SQL大厂真题》： 01 某音短视频 SQL2 平均播放进度大于60%的视频类别

1.题目描述

用户-视频互动表tb_user_video_log：

uid-用户ID, 
video_id-视频ID, 
start_time-开始观看时间（精确至秒）, 
end_time-结束观看时间（精确至秒）, 
if_follow-是否关注, 
if_like-是否点赞, 
if_retweet-是否转发, 
comment_id-评论ID

短视频信息表tb_video_info

video_id-视频ID, 
author-创作者ID, 
tag-类别标签, 
duration-视频时长（秒）, 
release_time-发布时间

问题：计算各类视频的平均播放进度，将进度大于60%的类别输出。

注：播放进度=播放时长÷视频时长*100%，当播放时长大于视频时长时，播放进度均记为100%。结果保留两位小数，并按播放进度倒序排序。 平均播放进度命名为 avg_play_progress。

2.问题分析

细节分析过程可以参考上一篇文章，在不熟练时可以将分析过程书写在草稿纸上或画出思维导图，直到代码完整后再输入电脑。

通过练习之后可以省略书写过程，直接在SQL操作界面进行以下省略操作。

1. 提炼题目，书写出简单框架。

一边书写SQL基本框架，一边梳理题目，明确每一句SQL语句对应的问题要求。对于需要进一步进行公式分解、函数参数输入的部分，可以仅书写列名、函数名和函数内外结构即可。

具体梳理框架代码：

SELECT tag, avg_play_progress
#先不进行公式拆解，直接对需要提取的内容进行命名即可(即使题目没有要求，命名一方面可以使思路清晰，另一方面结果也好看)
FROM tb_user_video_log JOIN tb_video_info USING (video_id)
#暂时无法确定是否需要外连接时，可以仅书写JOIN或者自己的特殊符号提醒进行检查
GROUP BY tag
HAVING avg_play_progress>60%#存在统计函数，不能放在WHERE处
ORDER BY avg_play_progress DESC

2. 公式拆解和函数补充

根据已有框架进行补充，步骤依旧可以和第一步一样，先构建函数、公式等框架，再最后填入具体内容

• 拆解补充

1. avg_play_progress拆解：

   • 平均：AVG() GROUP BY tag。不要忘记对什么进行拆解，题目给出的公式可能不是我们最终需要输出的数据而是中间值、条件的计算公式。

   • 播放进度=播放时长÷视频时长*100%

     播放时长：end_time-start_time，使用TIMESTAMPDIFF，单位SECOND
     视频时长：duration
     100%：小数转化为百分比（%是取余运算符号，不能直接使用），可以使用CONCAT拼接为字符串

   • 当播放时长大于视频时长时，播放进度均记为100%。

     判断结构:
     IF(播放进度>=1，记作100%，记作本身)

   • 结果保留两位小数

     ROUND(<>,2)

2. HAVING补充：
   由于选用CONCAT拼接显示百分比，输出实际为字符串，需要对数据类型进行处理。

• 框架构建

3. 由于需要拼接，考虑拼接位置

   为了使得判断语句简洁，我们考虑最后进行百分比拼接，因此CONCAT在最外侧

4. 接下来进行内部构建

   1. 首先构建公式最内侧的播放进度：

      TIMESTAMPDIFF(SECOND,start_time,end_time)/duration

   2. 其次，进行判断语句构建
      需要注意：由于最后进行百分号拼接和分子保留两位小数，判断语句的输出结果需要扩大100倍

      #为了方便看清结构，书写进行了分段
      IF(
      	TIMESTAMPDIFF(SECOND,start_time,end_time)/duration>1,
      	100,
      	TIMESTAMPDIFF(SECOND,start_time,end_time)/duration*100)
      

   3. 接下来，求平均值

      AVG(
      	IF(TIMESTAMPDIFF(SECOND,start_time,end_time)/duration>1,100,TIMESTAMPDIFF(SECOND,start_time,end_time)/duration*100)
      	)
      

   4. 然后，保留2位小数

      ROUND(
      	AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)/duration>1,100,TIMESTAMPDIFF(SECOND,start_time,end_time)/duration*100))
      	,2)
      

   5. 最后，进行CONCAT拼接

      CONCAT(
      	ROUND(AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)/duration>1,100,TIMESTAMPDIFF(SECOND,start_time,end_time)/duration*100)),2)
      	,'%')
      

5. HAVING语句处理
   需要注意：前期数字进行了处理，在对比出也需要进行相同处理，否则会报错(请查看字符串大小比较知识点)

   HAVING avg_play_progress>'60.00%'

3.完整代码和检查

尤其需要检查JOIN部分

SELECT tag, CONCAT(ROUND(AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)/duration>1,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 avg_play_progress>'60.00%'
ORDER BY avg_play_progress DESC

3.知识点总结：

1.CONCAT()用法

CONCAT()输入参数位字符串，但MySQL允许我们将数字参数传递给CONCAT()函数（即可以不使用引号将其传递），比如CONCAT(10,11)输出为字符串1011；也可以传递一个字符串参数和一个数字参数，比如CONCAT('A',10)输出为字符串A10；还可以输入字段(列名)为参数，比如使用CONCAT(NAME,‘1’）进行拼接，并输出字符串类型
但输出结果始终为字符串；如有任何一个参数为NULL ，则返回值为 NULL。

拓展：GROUP_CONCAT([DISTINCT] 连接字段 [ORDER BY 排序字段 ASC/DESC] [SEPERATORE ‘分隔符’])
函数返回带有来自一个组的连接的非NULL值的字符串结果。通俗的讲，函数会根据分组标准，将连接字段连接之后显示出来。分组标准则根据group by指定的列进行分组，因此常和GROUP BY一起使用。
如果仅SELECT GROUP_CONCAT()，那么不使用GROUP BY不会出错，返回值为整个表所有连接字段连成的一个字符串。如果SELECT包含其他部分而不使用GROUP BY会出错
例如：表table

id	price
1	10
1	10
1	20
2	20

代码：SELECT GROUP_CONCAT(DISTINCT price ORDER BY id DESC SEPERATORE ';') AS group_price FROM table GROUP BY id返回

id	group_price
1	10; 20
2	20

代码：SELECT GROUP_CONCAT(DISTINCT price) FROM table能够返回字符串10,20
代码：SELECT id, GROUP_CONCAT(price) FROM table则报错

2.字符串运算

原则上，谨慎做法转化为数字再比较

• 大小比较：
  MySQL中，字符串可以通过直接通过>、<等比较运算符进行直接比较，原理上则是比较ASCII码。

  • 逐个对比。
    含有字符串的比较是按照从左到右的字符顺序逐个对比，而不是像两个数字一样。

  • 比较的时候，字符串一般是会被转为数字的。

  • 默认情况下，数字在从左到右同个位置，即难以出现'a1b'和'1ab'比较的情况。

    字符和数字：字符串会自动丢弃数字后面所有部分（非数字部分和后续数字部分），再转化为数字形式进行数字之间的比较。对于没有数字的那些字符串，则默认转化为数字0。比如0='abc'返回1；‘0’=‘abc’返回0；0=‘0abc’返回1；1<‘0a1’`返回0
    【百分符号也会去除！！！】

    字符和字符：
    从左向右比较相同位置的字符。
    比较时若字符是数字，则直接比较；若字符是非数字那么会转换为ascii码进行比较。
    若在某位置上已经有大小之分，那么就不会再继续进行比较。
    如果字符串长度不相等，而能够对比的所有位置都相同，则更长的字符串更大。因此，题目中保留d位小数的情况时，WHERE语句需要注意位数。比如'abcd'<'abcde'返回1；'60.00'>'60'返回1而60.00=60返回1。

• 加减乘除
  MySQL中,字符串进行加减乘除运算时，截取方法和大小比较相同：从左到右截取首先出现数字的部分，转化为数字形式运算。如果字符串前面没有数字,那么就默认数值为0,加减结果位0,乘除结果是NULL。
  同样默认情况下，数字在从左到右同个位置，即难以出现'a1b'和'1ab'比较的情况。
  比如，'1ab'+'4cc'+'abc'返回5

• 转换类型：
  转化规则和上述相同。
  转化方式：
  1. 字符串+0：转化为数字形式，同样丢弃非数字部分，包括%符号
  2. CAST( AS SIGNED/UNSIGNED/DECIMAL)：将字符串转化为整数/无符号整数/浮点数类型
  3. CONVERT(, SIGNED/UNSIGNED/DECIMAL)

3.精度问题

• ROUND(x,d)：返回四舍五入小数点后d位的数字x，对应数学中的保留d位小数。
• TRUNCATE(x,d)：返回直接舍去至小数点后d位的数字x。若d的值为0,则结果不带小数部分。d可为负数，表示要舍去数字x的小数点左起第d位开始后面所有的值，所有数字的舍入方向都接近于零（即用0填充）。
• FLOOR(x)：返回小于或等于 x 的最大整数。

例如：

ROUND(18.6676,3)
18.668

TRUNCATE(18.6676,3)
18.667

TRUNCATE(18.6676,-1)
10

FLOOR(18.6676)
18

4.小数显示为百分比：使用CONCAT(str,‘%’)

5.时间变化：运算符和函数

• 时间变化推荐全部使用函数。

• 时间数字形式可以直接运算，但是此时进行的是10进制运算。而时间实际上是24小时60分钟/秒制度，因此直接运算需要再进行一次转换。函数形式则不需要自己转换。

  例如：同样以秒为单位，‘2018-09-01 10:04:00’-‘2018-09-01 10:03:00’

    直接运算得到20180901100400-20180901100300=100的数字，并不是实际的秒数
    函数TIMESTAMPDIFF(SECOND)则会得到60(秒)
  

• 拓展：

  1. MySQL中的时间类型有DATE，TIME，DATETIME，TIMESTAMP，YEAR五种

     其中，
     1.DATE类型，支持的范围 ‘1000-01-01’ to ‘9999-12-31’，MySQL以’YYYY-MM-DD’格式显示，但允许使用字符串或数字将值赋给DATE列。

     字面常量转换规则如下：

      1.格式为'YYYY-MM-DD'或'YY-MM-DD'的字符串。任何标点字符都可以用作日期部分之间的分隔符。例如，'2012-12-31'、'2012/12/31'、'2012^12^31'和'2012@12@31'是等价的。
      2.格式为'YYYYMMDD'或'YYMMDD'的没有分隔符的字符串，只要该字符串具有日期的意义。例如，'20070523'和'070523'被解释为'2007-05-23'，但'071332'是非法的(它有无意义的月和日部分)，会变成'0000-00-00'。
      3.作为YYYYMMDD或YYMMDD格式的数字，只要该数字作为日期有意义。例如，19830905和830905被解释为“1983-09-05”。
     

     2.TIME类型。支持的范围’-838:59:59.000000’ to ‘838:59:59.000000’，MySQL以’hh:mm:ss[.fraction]'格式显示，但允许使用字符串或数字向TIME列赋值。
     字面常量转换规则如下：

      1.MySQL将带有冒号的TIME值缩写为一天中的时间，例如：'11:12'表示'11:12:00'，而不是 '00:11:12'；
      2.MySQL将没有冒号的TIME缩写值最右边的两个数字看成秒，例如：'1112'和1112都表示'00:11:12'
      3.在时间部分和小数秒部分之间识别的唯一分隔符是小数点；
     

  2. 时间变化函数的输入参数除特殊强调外不需要形式一致，即不需要都为date形式或都为time形式，date形式会自动补充00:00:00。