找出播放率较低的视频

美句共勉：你若盛开，蝴蝶自来。The better you become, the better you attract。

某视频播放平台把已发布的短视频数据记录成了短视频信息表( tb_video_info)，把用户观看视频的数据记录成了用户-视频互动表(tb_user_video_log)。2022年为提高资源利用率需下架一批观看率不高的短视频，因此运营总监要求计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序。

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

 完播率举例：

   视频2001在2021年10月有3次播放记录，观看时长分别为30秒、24秒、34秒，视频时长30秒，因此有两次是被认为完成播放了的，故完播率为0.667；若视频时长为60秒，则完播率为0.000。

输出示例结果:

video_id  avg_comp_play_rate
2001        0.667
2002        0.000

用户-视频互动表   tb_user_video_log

序号 用户ID  视频ID  开始播放时间 结束播放时间 是否关注 是否点赞 是否转发 评论ID
id  uid  video_id  start_time  end_time  if_follow  if_like  if_retweet  comment_id
1  101  2001  2021-10-01  10:00:00  2021-10-01  10:00:30  0  1  1  NULL
2  102  2001  2021-10-01  10:00:00  2021-10-01  10:00:24  0  0  1  NULL
3  103  2001  2021-10-01  11:00:00  2021-10-01  11:00:34  0  1  0  1732526
4  101  2002  2021-09-01  10:00:00  2021-9-01  10:00:42  1  0  1  NULL
5  102  2002  2021-10-01  11:00:00  2021-10-01  10:00:30  1  0  1  NULL

短视频信息表  tb_video_info

序号  视频ID   创作者ID  类别标签  视频时长（秒）   发布时间
id  video_id  author    tag    duration       release_time
1    2001    901      影视    30          2021/1/1 7:00
2    2002    901     美食    60          2021/1/1 7:00
3    2003    902      旅游    90          2021/1/1 7:00

注：以上两表来自牛客网，本文只用做学习讲解。

逻辑思路（login thinking）：

（1）清晰业务明了表关系。

短视频信息表，描述短视频基本情况的表，唯一标识视频ID。

用户视频-互动表，描述用户在某个时间点点击了哪个视频，用户ID+视频ID+时间戳为唯一标识。

（2）自底向上制定核心逻辑。

需要将播放时长与(结束播放时间-开始播放时间)做比较，因两个字段不在一起需要考虑表关联。用户视频-互动表中的视频ID均来自短视频信息表且业务需求是视频播放次数，因此考虑左连接(left join)。

（3）细枝末节保障结果准确。

强调视频的播放时间2021，完播率保留3位小数，完播率降序排序。

案例展示（case show）：

案例一：

select
  m1.video_id,
  cast(
    sum(if(m1.duration_user >= m2.duration, 1, 0)) / count(1) as decimal(10, 3)
  ) as avg_comp_play_rate
from
  (
    select
      video_id,
      start_time,
      end_time,
      (end_time - start_time) as duration_user
    from `tb_user_video_log`
    where
      year(end_time) = '2021'
  ) m1
  left join (
    select
      video_id,
      duration
    from`tb_video_info`
  ) m2 on m1.video_id = m2.video_id
group by
  m1.video_id
order by
  sum(if(m1.duration_user >= m2.duration, 1, 0)) / count(1) desc

案例二：

SELECT
  video_id,
  ROUND(
    AVG(
      IF(
        TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration,
        1,
        0
      )
    ),
    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;

一学一结（learn a knot）：

嗷呜！！到了learn a knot的时候了，昱潼总结本次有三种语法点需要掌握：（1）条件判断；（2）类型转换函数；（3）日期函数。

（1）条件判断：IF/CASE WHEN。

IF函数：如果condition为true，返回value_if_true，否则返回value_if_false。

IF(condition, value_if_true, value_if_false)

示例：

SELECT IF(today='周日', '不去上班', '去上班');

CASE WHEN语句：a、枚举这个字段所有可能的值。

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

示例：

SELECT NAME '食物名称',
      CASE NAME
           WHEN '苹果' THEN '水果'
           WHEN '香蕉' THEN '水果'
           WHEN '白菜' THEN '蔬菜'
           WHEN '山药' THEN '蔬菜'
           ELSE '谷物'
      END '食物分类'
FROM food_info;

CASE WHEN语句：b、只返回符合条件的第一个值。

CASE WHEN [expr] THEN [result1]…ELSE [default] END

示例：

SELECT name '学科'
      ,score '成绩'
      ,CASE WHEN score< 60 THEN '不及格'
            WHEN score< 80 THEN  '良好'
            WHEN score>= 80 AND score < 90 THEN '优秀'
            ELSE '超级优秀'
      END '等级'
FROM subject_info;

（2）类型转换函数：cast/round。

1、round(x,d),x指要处理的数，d是指保留几位小数。
2、round(x),指的是round(x,0),默认d为0。
3、cast(value AS datatype)value指要处理的数，datatype要转换成的数据类型

（3）日期函数。

TIMESTAMPDIFF(unit,begin,end)，函数返回begin-end的结果。其中unit参数是确定(end-begin)的结果的单位，可以是：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR。
示例：TIMESTAMPDIFF(MONTH, '2022-06-01', '2022-09-01')，返回3。

DATEDIFF(end,begin),计算两个日期之间的天数等同于TIMESTAMPDIFF(DAY,end,begin)，注意end与begin的位置，end-begin>0的值。
YEAR(date),YEAR()函数返回一个指定日期的年份值，范围为1000到9999，如果日期为零，YEAR()函数返回0。
示例：year('2022-09-18'),返回2022；year(now()),返回2022。

MONTH(DATE/DATETIME),接受一个DATE或DATETIME值的参数。它返回1到12之间的整数，范围从1到12。
示例：month('2022-09-18'),返回9。
DAY(date)函数接受一个参数，它的含义同dayofmonth。如果date参数为零，例如'0000-00-00'，则DAY函数返回0，如果日期为NULL，则DAY函数返回NULL值。
示例：DAY('2018-01-15')，返回15。

WEEK(date, mode),date是要获取周数的日期;mode是一个可选参数，用于确定周数计算的逻辑。
模式 一周的第一天 范围
0 星期日 0-53
1 星期一 0-53
2 星期日 1-53
3 星期一 1-53
4 星期日 0-53
5 星期一 0-53
6 星期日 1-53
7 星期一 1-53
模式：366/7或者365/7得出一周的第一天是0-7中的哪一个。
范围：从1月1日起到下一个周日或周一的天数大于3，则这几天算第1周；如果小于3则算第0周。

以下语句可在mysql中使用。

1、建表语句

DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    video_id INT NOT NULL COMMENT '视频ID',
    start_time datetime COMMENT '开始观看时间',
    end_time datetime COMMENT '结束观看时间',
    if_follow TINYINT COMMENT '是否关注',
    if_like TINYINT COMMENT '是否点赞',
    if_retweet TINYINT COMMENT '是否转发',
    comment_id INT COMMENT '评论ID'
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_video_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    video_id INT UNIQUE NOT NULL COMMENT '视频ID',
    author INT NOT NULL COMMENT '创作者ID',
    tag VARCHAR(16) NOT NULL COMMENT '类别标签',
    duration INT NOT NULL COMMENT '视频时长(秒数)',
    release_time datetime NOT NULL COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;

2、插入语句

INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES
  (101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:30', 0, 1, 1, null),
  (102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:24', 0, 0, 1, null),
  (103, 2001, '2021-10-01 11:00:00', '2021-10-01 11:00:34', 0, 1, 0, 1732526),
  (101, 2002, '2021-09-01 10:00:00', '2021-09-01 10:00:42', 1, 0, 1, null),
  (102, 2002, '2021-10-01 11:00:00', '2021-10-01 11:00:30', 1, 0, 1, null);

INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES
  (2001, 901, '影视', 30, '2021-01-01 7:00:00'),
  (2002, 901, '美食', 60, '2021-01-01 7:00:00'),
  (2003, 902, '旅游', 90, '2021-01-01 7:00:00');