SQL学习（1）：宏观到微观的题目解答 | 大厂真题实例-视频平均完播率

SQL学习（1）：宏观到微观的题目解答 | 大厂真题实例-视频平均完播率

题目来源：《牛客题霸：SQL大厂真题》： 01 某音短视频 SQL1 各个视频的平均完播率

前言：宏观到微观——简单题目的解题思路和代码框架

本章详细讲解一种宏观到微观的解题思路（先书写select-from-where代码框架，再分解题目，逐步填充），适合思路清晰的简单题目和部分中等题目，解答清晰、书写方便、不容易出现语法遗漏问题。
之后几章将以题目为例进行该方法的应用，问题分析将逐步简化，帮助熟悉熟练，以达到简单快速书写代码和解决问题的目的。

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-发布时间

问题：计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序。完播率命名为 avg_comp_play_rate。

注：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。

2. 问题分析

第一步：拆解问题，找到SQL对应部分，形成宏观框架。

• SQL常用的主体框架为：

  SELECT <列名>
  FROM <表名>
  WHERE <表达式>
  GROUP BY <列名>
  HAVING <表达式>
  ORDER BY <列名>
  

  根据问题在此框架基础上再进行join操作、union联合操作、统计函数窗口函数等函数操作、case when/if（）等判断操作、子查询（或者叫嵌套查询、内查询）等多种其他操作

• 具体拆分：

    2021年有播放记录——where 筛选 start_time 在2021
    每个视频——group by video_id
    完播率——select选择
    (结果保留三位小数)——select 部分
    按完播率降序排序——order by 完播率 desc
    完播率命名为 	avg_comp_play_rate——select选择video_id，完播率 as avg_comp_play_rate
  

• 重组框架结构

    SELECT video_id, 完播率(保留3位小数) AS avg_comp_play_rate
    FROM 表名
    WHERE start_time有2021
    GROUP BY video_id
    ORDER BY avg_comp_play_rate DESC
  

第二步：根据重组框架拆分空缺，选择函数、操作或判断等结构

观察上一步得到的结构，我们能够看到，空缺的地方有：

1.完播率
2.保留3位小数
3.表名
4.WHERE处表达式

1. 完播率有两种方法进行补充

• 方法一：公式拆解
  完播率=完整播放次数/总播放次数

   完整播放次数计算：结束观看时间-开始观看时间>=视频时长。
   使用sum(if())、count(if())、sum(case when)、count(case when )等统计函数+判断结构形式求解
   时间差由于需要精确到秒，使用timestampdiff()求得时间差
   
   总播放次数：count(video_id)或者count(start_time)
  

  具体代码为：

  SUM(IF(TIMESTAMPDIIF(SECOND,start_time,end_time)>=duration,1,0))
  COUNT(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,NULL))
  SUM(CASE WHEN TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration THEN 1 ELSE 0 END)
  COUNT(CASE WHEN TIMESTAMPDIFF(SECOND,start_time,end_time) THEN 1 ELSE NULL END)
  

• 方法二：直接使用AVG()求解
  需要注意的是，这种除法公式得到的数据指标并不都适用于AVG()函数，例如分母需要distinct end_time。因此如果为求正确率，建议统一使用方法一。

  和方法一相同，结合判断语句进行，选择AVG(IF())或者AVG(CASE WHEN)

2. 保留3位小数
   在完播率外层再嵌套一个函数ROUND(<列名>，保留位数)

3. 表名
   由于完整率的判断条件中既有表1的start_time, end_time，也有表2的duration，FROM部分需要使用JOIN连接2表。
   由于在该问题中NULL不是需要的信息，因此直接使用(INNER) JOIN即可，连接条件为video_id。由于连接列名相同，简洁的做法是使用USING (video_id).

4. WHERE表达式：
   可以直接使用时间函数：YEAR(start_time)=2021
   也可以使用字符串函数：SUBSTRING(start_time,0,4)=‘2021’、LEFT(start_time,4)

第三步：查缺补漏。填充空缺，检查框架并进行适当修改

需要注意的是，直接填充空缺有可能存在错误，比如JOIN ON操作可能使得列名变化。需要重新检查填充后的框架，并根据实际进行一定的修改。

完整代码：

SELECT video_id,
ROUND(SUM(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0))/COUNT(video_id),3) 
AS avg_comp_play_rate
FROM tb_user_video_log JOIN tb_video_info USING (video_id)
WHERE YEAR(start_time)=2021
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC

3.知识点总结和扩展

1. ROUND(<列名>，N)

表示对<列名>的值取N位小数

2. 统计函数对于NULL值得处理

	SUM：
	当单列中含有NULL值的时候，SUM会忽略该NULL值进行求和。
	如果涉及多列的求和运算，某一列的值为NULL，会忽略该组合列(多个参与求和的列)，也即忽略该行。
		例如： SUM(A+B+C)，A、B、C 为三个列，
		如果某行记录中A列值为NULL，且没有分组求和GROUP BY，则该行不参与求和，会返回一个求和数。
		如果某行记录中A列值为NULL，且有分组求和（比如主键id），则该行不参与求和，同时该行会返回null。
	AVG：和SUM一样
	
	COUNT：
		    COUNT(*)或COUNT(1)：对表中的行数进行统计，不管某一行是否有NULL值
		    COUNT(字段名)：对特定列进行统计，会忽略NULL值进行统计

3. 统计函数和条件表达式

• 最常见的类型是使用COUNT/SUM(IF/CASE WHEN)

• 最常见的应用是统计某字段满足某条件的记录条数。此时SUM的判断结果为1/0，COUNT的判断结果为TRUE/NULL或1/NULL。

  需要注意的是对于任意数字N，COUNT(N)效果和COUNT(1)相同，不会跳过NULL。因此COUNT的判断结果不能为1/0，会忽略条件统计全部。

    e.g. SUM(user_id IS NULL)、SUM(IF(num>10,1,0))、COUNT(CASE WHEN 'A' THEN 1 ELSE NULL END)
  

4. 判断语句

MySQL种条件语句主要有:

• 双结果：IF(<条件>，TRUE时结果，FALSE时结果)

• 多结果：

  CASE WHEN 条件1 THEN 结果1
  	 WHEN 条件2 THEN 结果2
  	 ...
  	 ELSE 结果n
  	 END
  

  对于具体应用来说，多结果有2种等价表达，例如

  SELECT CASE grade
  	   WHEN 'A' THEN 'EXCELLENT'
  	   WHEN 'B' THEN 'GOOD'
  	   WHEN 'C' THEN 'PASS'
  	   ELSE 'RETAKE'
  	   END
  或者
  SELECT CASE WHEN grade='A' THEN 'EXCELLENT'
  			WHEN grade='B' THEN 'GOOD'
  			WHEN grade='C' THEN 'PASS'
  			ELSE 'RETAKE'
  			END
  

• 拓展和相关

    IFNULL(exp1,exp2)：如果exp1不为空，返回exp1的值，否则返回exp2的值
    ISNULL(exp)：如果exp为空，返回1，否则返回0
    NULLIF(exp1,exp2)：如果exp1= exp2 成立，返回NULL，否则返回 expr1。
  

5.字符串函数

1.字符集和校对函数：CHARSET( )、COALESCE( )、COERCIBILITY( )、COLLATION( )
2.类型转换函数：ASCII( )、BIN( )、BINARY( )、CAST( )、CHAR( )、COMPRESS( )、CONVERT( )、EXPORT_SET( )、HEX( )、 MAKE_SET( )、ORD( )、SOUNDEX( )、UNCOMPRESS( )、UNHEX( )
3.格式化函数：CONCAT( )、CONCAT_WS( )、LCASE( )、LENGTH( )、LOWER( )、LPAD( )、LTRIM( )、OCTET_LENGTH( )、QUOTE( )、RPAD( )、RTRIM( )、SPACE( )、TRIM( )、UCASE( )、UPPER( )
4.表达式函数：BIT_ LENGTH( )、CRC32( )、CHAR_ LENGTH( )、CHARACTER_ LENGTH( )、ELT( )、FIELD( )、FIND_ IN_ SET( )、INSTR( )、INTERVAL( )、LOCATE( )、MATCH( )AGAINST( )、POSITION( )、STRCMP( )、UNCOMPRESSED _LENGTH( )
5.提取函数：LEFT( )、LOAD_ FILE( )、MID( )、RIGHT( )、SUBSTR( )、SUBSTRING( )、SUBSTRING_ INDEX( )
6.字符串操纵函数：INSERT( )、REPEAT( )、REPLACE( )、REVERSE( )

本文重点讲解格式化和提取函数

• 格式化函数：

  1. CONCAT(str1,str2,…)/CONCAT_WS(sep,str1,str2,…)：
     (使用sep符号)将str连接成新的字符串。其中sep需要加引号形成字符串形式。sep=’NULL’，返回NULL；str=’NULL’，忽略此字符，其余正常连接

  2. LOWER(str)/UPPER(str)：
     别名LCASE(str)/UCASE(str)，将字符串全部小写/大写

  3. LENGTH(str)/OCTET_LENGTH(str)：
     返回str的字符数，用字节/比特来度量。
     在如今的大部分情况下，两函数等价，且LENGTH()更常使用。

  4. LPAD(str,len,padstr)/RPAD(str,len,padstr):
     用字符串padstr左/右填补str到len字符长度。
     len控制返回字符串最大长度：如果str长度>len，则填充至 len 停止；如果str长度<len，不进行填充，返回str从左到右截取到len(注意：此时都是截取最前面的len个字符)。

  5. TRIM(str)/ LTRIM(str)/RTRIM(str)：
     删除str两侧/左侧/右侧所有空格

     TRIM(BOTH/LEADING/TRAILING remstr FROM str)：
     删除str两侧/左侧/右侧所有的remstr，remstr为字符串形式

  6. QUOTE(str):
     返回带有正确转义的数据值的字符串str结果。返回的字符串用单引号引起来(表格中也会显示外层单引号，而正常字符串不会显示引号)，并带有反斜杠(\)，单引号(')，ASCII NULL和Control + Z的每个实例，并加一个反斜杠。如果参数为NULL，则返回值是单词“NULL”，不包含单引号。(在编程环境中，某些符号存在特定意义，如果想加入字符串中作为内容，需要在其前方加入\)

     例如：QUOTE('greeks''for''greaks')，返回’greeks’for’greaks’，成功将字符串中的单引号增加反斜杠，变为正确转移符号，可以正常在后续SQL语句中使用。

  7. SPACE(N)：
     返回由N个空格间隔符组成的字符串。
     常和CONCAT()一起使用，比如CONCATE('A',SPACE(3),'01')返回字符串A 01

• 提取函数

  1. LEFT(str,len)/RIGHT(str,len)：
     返回str前/后len个字符。（从左到右/从右向左）

  2. MID(str, start[,len])/SUBSTR(str,start[,len])/SUBSTRING(str,start[,len])：
     返回str从start位置开始len长的字符，此时从左到右的位置从1开始。len>0，从左到右提取；len<0，从右向左提取。省略len默认取到结尾。

     需要注意的时：substring函数在不同数据库中名字不太一样

      MySQL: SUBSTR()、SUBSTRING()
      Oracle: SUBSTR()
      SQL Server: SUBSTRING()
     

     其次，SUBSTRING(str FROM start [FOR len])是函数更规范的一种写法

  3. SUBSTRING_ INDEX(str, delim,num)：
     返回指定num数量的分隔符delim出现之前的字符串str的子字符串
     例如，SUBSTRING_INDEX('www.cctv.001.com','.',2)返回字符串www.cctv

6.时间函数

在SQL中，时间不是字符串类型，而是单独的事件类型，形式通常为’YYYY-MM-DD HOUR:MIN:SECOND’

• 时间获取

  1. YEAR(date)/MONTH(date)/WEEK(date)/HOUR(date)/MINITE(date)/QUARTER(date)：
     返回年、月、一年中的第几周、小时、分钟、一年中的第几季度，返回数字或字符串形式

     MONTHNAME(date):
     返回月份的英文名字，为字符串形式

     例如YEAR(’2021-10-11‘)返回2021，QUARTRE('2021-10-11')返回4，MONTH(‘2021-06-05’)返回6，MONTHNAME(‘2020-06-11’)返回June

     DATE(date)/TIME(date)：
     返回从年到日/从小时到微秒的时间，结果既可以是数字也可以是字符串

  2. 当前日期：
     CURDATE()：
     别名：CURRENT_DATE()，返回当前从年到日的日期，为 “YYYY-MM-DD”（字符串）或YYYYMMDD（数字）

     CURTIME():
     别名：CURRENT_TIME()，返回当前从时到秒的时间，为 “HH-MM-SS”（字符串）或HHMMSS.uuuuuu（数字）

     NOW()/LOCALTIME()：
     等价于CURRENT_TIMESTAMP()/LOCALTIMESTAMP()，返回当前日期和时间，为 “YYYY-MM-DD HH-MM-SS”（字符串）或YYYYMMDDHHMMSS.uuuuuu

• 时间改变

  1. 返回值可以为数字的时间获取函数直接进行数字运算即可，例如YEAR(‘2021-01-02’)+1返回2022。
     如果使用MONTHNAME()进行运算，会忽略函数结果，只返回其他部分的运算结果。
     时间数据直接进行加减运算只会在year部分进行加减，返回year数字

  2. DATE_ADD(date, INTERVAL value addunit)：
     别名ADDSATE(date, INTERVAL value addunit)，返回以addunit为单位在date上增加value的新时间。value可以为正或负。
     addunit可以为

      MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR
      SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
     

     ADDDATE(date,days): 默认为添加days天数

  3. ADDTIME(datetime, time)/SUBTIME(datetime,time)：
     在datetime上添加/减去time长度的时间，time形式为hh:mm:ss.microsecond，可以适当省略。返回值表现为时间形式，也可以作为数字直接运算。例如ADDTIME(‘2021-01-11 10:27:56’,3)返回2021-01-11 10:27:59

  4. DATE_SUB(date, INTERVAL value interval)：
     原理和DATE_ADD相同，此函数为减去时间，别名SUBDATE(date, INTERVAL value addunit)

  5. TIMESTAMP(date[,time]):
     如果使用此函数指定了两个参数，则首先将第二个参数添加到第一个参数，然后返回datetime值。如果只有一个参数，date为日期时间值，返回date；date为日期值，默认添加“00:00:00".

• 时间差:
  不同的数据库可能存在函数名不一样或时间相减顺序不同的情况，以下为MySQL库的使用

  1. TIMEDIFF(time1, time2)：
     time1 和 time2 应采用相同的格式，并进行计算 time1 - time2,返回以时间**"HH-MM-SS"或HHMMSS.uuuuuu**形式的时间差距（如果有日期差距，转化为时间），例如TIMEDIFF("2017-06-25 13:10:11", "2017-06-15 13:10:10")+1返回2400002

  2. DATEDIFF(date1, date2)：
     计算 date1 - date2,返回以时间**"YY-MM-DD"或YYMMDD**形式的日期差距，例如DATEDIFF("2017-06-25 09:34:21", "2017-06-15 15:25:35")+1返回11

  3. TIMESTAMPDIFF(dataunit, time1,time2)：
     计算time2-time1，dataunit为计算差值的时间单位（例如HOUR为单位，差1天，返回24），主要有DAY/HOUR/MINUTE/SECOND

  拓展部份：如出现混淆，请忽略此部分，专注一个数据库
  SQL Server中datediff和MySQL的TIMESTAMPDIFF()使用规则和形式一致

• 时间格式化
  DATE_FORMAT(date,format):
  format形式：

    	%a 	缩写星期名
    	%b 	缩写月名
    	%c 	月，数值
    	%D 	带有英文前缀的月中的天   
    	%d 	月的天，数值（00-31）
    	%e 	月的天，数值（0-31）
    	%f 	微秒
    	%H 	小时（00-23）
    	%h 	小时（01-12）
    	%I 	小时（01-12）
    	%i 	分钟，数值（00-59）
    	%j 	年的天（001-366）
    	%k 	小时（0-23）
    	%l 	小时（1-12）
    	%M 	月名
    	%m 	月，数值（00-12）
    	%p 	AM 或 PM
    	%r 	时间，12-小时（hh:mm:ss AM 或 PM）
    	%S 	秒（00-59）
    	%s 	秒（00-59）
    	%T 	时间, 24-小时（hh:mm:ss）
    	%U 	周（00-53）星期日是一周的第一天
    	%u 	周（00-53）星期一是一周的第一天
    	%V 	周（01-53）星期日是一周的第一天，与 %X 使用
    	%v 	周（01-53）星期一是一周的第一天，与 %x 使用
    	%W 	星期名
    	%w 	周的天（0=星期日, 6=星期六）
    	%X 	年，其中的星期日是周的第一天，4 位，与 %V 使用
    	%x 	年，其中的星期一是周的第一天，4 位，与 %v 使用
    	%Y 	年，4 位
    	%y 	年，2 位