牛客网SQL刷题二-某音短视频

Jenvid.yang

已于 2022-04-04 00:28:52 修改

阅读量604

点赞数

分类专栏：离线数仓文章标签：数据仓库 sql

于 2022-04-04 00:27:06 首次发布

本文链接：https://blog.csdn.net/a53715668/article/details/123947659

版权

离线数仓专栏收录该内容

5 篇文章 1 订阅

订阅专栏

SQL1 各个视频的平均完播率

数据

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;

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');

题目

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

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

select video_id,round(count(is_compete)/count(video_id),3) rate from (
  select v.video_id,
  case
    when timestampdiff(second,start_time,end_time) >= v.duration then 1 else null
  end is_compete
  from tb_user_video_log l
  join tb_video_info v on l.video_id=v.video_id
  where year(start_time) = 2021
  ) t
group by video_id
order by rate desc

SQL2 平均播放进度大于60%的视频类别

数据

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;

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:21', 0, 0, 1, null),
  (103, 2001, '2021-10-01 11:00:50', '2021-10-01 11:01:20', 0, 1, 0, 1732526),
  (102, 2002, '2021-10-01 11:00:00', '2021-10-01 11:00:30', 1, 0, 1, null),
  (103, 2002, '2021-10-01 10:59:05', '2021-10-01 11:00:05', 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, '2020-01-01 7:00:00');

题目

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

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

  select tag,concat(comp_rate,'%') from (
  select v.tag,
    round(avg(if(timestampdiff(second,start_time,end_time) > v.duration,1,timestampdiff(second,start_time,end_time) /v.duration)*100),2)  comp_rate
  from tb_user_video_log l
  join tb_video_info v on l.video_id=v.video_id
  group by v.tag
  having comp_rate>60
  order by comp_rate desc
  )t

SQL3 每类视频近一个月的转发量/率

数据

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;

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:20', 0, 1, 1, null)
  ,(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:15', 0, 0, 1, null)
  ,(103, 2001, '2021-10-01 11:00:50', '2021-10-01 11:01:15', 0, 1, 0, 1732526)
  ,(102, 2002, '2021-09-10 11:00:00', '2021-09-10 11:00:30', 1, 0, 1, null)
  ,(103, 2002, '2021-10-01 10:59:05', '2021-10-01 11:00:05', 1, 0, 0, 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, '2020-01-01 7:00:00');

题目

问题：统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据）中，每类视频的转发量和转发率（保留3位小数）。

注：转发率＝转发量÷播放量。结果按转发率降序排序。
SQL

select v.tag,
  sum(if_retweet),
  round(sum(if_retweet)/count(*),3) retweet_rate
  from tb_user_video_log l
  join tb_video_info v on l.video_id=v.video_id
  where l.start_time >= date_sub((select date(max(start_time)) from tb_user_video_log), interval 30 day)
  and (if_follow =1 or if_like=1 or if_retweet=1 or comment_id is not null)
  group by tag
  order by retweet_rate desc;

SQL4 每个创作者每月的涨粉率及截止当前的总粉丝量

数据

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;

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-09-01 10:00:00', '2021-09-01 10:00:20', 0, 1, 1, null)
  ,(105, 2002, '2021-09-10 11:00:00', '2021-09-10 11:00:30', 1, 0, 1, null)
  ,(101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:20', 1, 1, 1, null)
  ,(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:15', 0, 0, 1, null)
  ,(103, 2001, '2021-10-01 11:00:50', '2021-10-01 11:01:15', 1, 1, 0, 1732526)
  ,(106, 2002, '2021-10-01 10:59:05', '2021-10-01 11:00:05', 2, 0, 0, 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, '2020-01-01 7:00:00')
  ,(2004, 902, '美女', 90, '2020-01-01 8:00:00');

题目

问题：计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量

注：
- 涨粉率=(加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序。
- if_follow-是否关注为1表示用户观看视频中关注了视频创作者，为0表示此次互动前后关注状态未发生变化，为2表示本次观看过程中取消了关注。
SQL

起点到当前
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

前3行到当前行
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
可以将between and 省略 等同于
ROWS  3 PRECEDING

前三行，当前行，后一行
ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING

当前行以及往后所有行
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

select author,
    date_format(start_time,'%Y-%m') per_month,
    round(sum(
      case
        when if_follow=1 then 1
        when if_follow=0 then 0
        when if_follow=2 then -1
      end
    ) /count(*),3) fans_growth_rate,
    sum(
      case
        when if_follow=1 then 1
        when if_follow=0 then 0
        when if_follow=2 then -1
      end
    ) total_fans
  from tb_user_video_log l
  join tb_video_info v on l.video_id=v.video_id
group by author,per_month

select author, per_month, fans_growth_rate,
sum(fans) over(partition by author  order by per_month asc) fanns
from
(
 select author,
    date_format(start_time,'%Y-%m') per_month,
    round(sum(
      case
        when if_follow=1 then 1
        when if_follow=0 then 0
        when if_follow=2 then -1
      end
    ) /count(*),3) fans_growth_rate,
    sum(
      case
        when if_follow=1 then 1
        when if_follow=0 then 0
        when if_follow=2 then -1
      end
    ) fans
  from tb_video_info v 
  join tb_user_video_log l on l.video_id=v.video_id
  where year(start_time)='2021'
group by author,per_month
 ) t
 order by author,fanns asc

select author, per_month, 
round(sum(month_fans)/count(*),3) fans_growth_rate
,sum(sum((month_fans))) over(partition by author  order by per_month asc) fanns
from
(
 select author,
    date_format(start_time,'%Y-%m') per_month,
    case
      when if_follow=1 then 1
      when if_follow=0 then 0
      when if_follow=2 then -1
    end month_fans
  from tb_video_info v 
  join tb_user_video_log l on l.video_id=v.video_id
  where year(start_time)='2021'
 ) t
 group by author,per_month
order by author, fanns asc

SQL5 国庆期间每类视频点赞量和转发量

数据

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;

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-09-24 10:00:00', '2021-09-24 10:00:20', 1, 1, 0, null)
  ,(105, 2002, '2021-09-25 11:00:00', '2021-09-25 11:00:30', 0, 0, 1, null)
  ,(102, 2002, '2021-09-25 11:00:00', '2021-09-25 11:00:30', 1, 1, 1, null)
  ,(101, 2002, '2021-09-26 11:00:00', '2021-09-26 11:00:30', 1, 0, 1, null)
  ,(101, 2002, '2021-09-27 11:00:00', '2021-09-27 11:00:30', 1, 1, 0, null)
  ,(102, 2002, '2021-09-28 11:00:00', '2021-09-28 11:00:30', 1, 0, 1, null)
  ,(103, 2002, '2021-09-29 11:00:00', '2021-09-29 11:00:30', 1, 0, 1, null)
  ,(102, 2002, '2021-09-30 11:00:00', '2021-09-30 11:00:30', 1, 1, 1, null)
  ,(101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:20', 1, 1, 0, null)
  ,(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:15', 0, 0, 1, null)
  ,(103, 2001, '2021-10-01 11:00:50', '2021-10-01 11:01:15', 1, 1, 0, 1732526)
  ,(106, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:05', 2, 0, 1, null)
  ,(107, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:05', 1, 0, 1, null)
  ,(108, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:05', 1, 1, 1, null)
  ,(109, 2002, '2021-10-03 10:59:05', '2021-10-03 11:00:05', 0, 1, 0, null);

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

题目

问题：统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。
SQL

-- 窗口函数不包起来，数据不准确
 select
 tag,mth,
 sum(likes) over(partition by tag order by mth desc  rows between current row and 6 following  ) sum_7likes,
 max(retweets) over(partition by tag order by mth desc rows between current row and 6 following  ) max_7retweets
 from (
   select
   tag,
   date(start_time) mth,
   sum(if_like) likes,
   sum(if_retweet) retweets
   from tb_user_video_log l
   join tb_video_info v on l.video_id=v.video_id
  group by tag,mth order by tag,mth asc
)t
where mth between '2021-10-01' and '2021-10-03'  -- 只有3天，数据少了，还需要更早天数的数据，不能先过滤，要先窗口取出来后再套一层过滤
order by tag asc, mth asc

select * from (
   select
   tag,mth,
   sum(likes) over(partition by tag order by mth desc  rows between current row and 6 following  ) sum_7likes, -- 倒序+后6行=前7天
   max(retweets) over(partition by tag order by mth desc rows between current row and 6 following  ) max_7retweets
   from (
     select
     tag,
     date(start_time) mth,
     sum(if_like) likes,
     sum(if_retweet) retweets
     from tb_user_video_log l
     join tb_video_info v on l.video_id=v.video_id
    group by tag,mth order by tag,mth asc
  )t)t
where mth between '2021-10-01' and '2021-10-03' 
order by tag desc, mth asc

SQL6 近一个月发布的视频中热度最高的top3视频

数据

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;

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-09-24 10:00:00', '2021-09-24 10:00:30', 1, 1, 1, null)
  ,(101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:31', 1, 1, 0, null)
  ,(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:35', 0, 0, 1, null)
  ,(103, 2001, '2021-10-03 11:00:50', '2021-10-03 11:01:35', 1, 1, 0, 1732526)
  ,(106, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:04', 2, 0, 1, null)
  ,(107, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:06', 1, 0, 0, null)
  ,(108, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:05', 1, 1, 1, null)
  ,(109, 2002, '2021-10-03 10:59:05', '2021-10-03 11:00:01', 0, 1, 0, null)
  ,(105, 2002, '2021-09-25 11:00:00', '2021-09-25 11:00:30', 1, 0, 1, null)
  ,(101, 2003, '2021-09-26 11:00:00', '2021-09-26 11:00:30', 1, 0, 0, null)
  ,(101, 2003, '2021-09-30 11:00:00', '2021-09-30 11:00:30', 1, 1, 0, null);

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

题目

问题：找出近一个月发布的视频中热度最高的top3视频。

注：
- 热度=(a视频完播率+b点赞数+c评论数+d转发数)*新鲜度；
- 新鲜度=1/(最近无播放天数+1)；
- 当前配置的参数a,b,c,d分别为100、5、3、2。
- 最近播放日期以end_time-结束观看时间为准，假设为T，则最近一个月按[T-29, T]闭区间统计。
- 结果中热度保留为整数，并按热度降序排序。
SQL

select  t.video_id,
round((100*full_show + 5*cnt_like + 3* cnt_comment + 2* cnt_retweet)*(1/(no_show_days+1))) hot_index
from (
  select video_id,
  sum(is_compete)/count(*) full_show,
  avg(is_compete) full_show2,
  -- 用avg求的比例结果不正确？？？！！！ -- 
  sum(if_like) cnt_like,sum(is_comment) cnt_comment,sum(if_retweet) cnt_retweet
  from
  (
  select v.video_id,
    -- 是否看完
     case
      when timestampdiff(second,start_time,end_time) >= v.duration then 1 else 0
     end is_compete,
    -- 点赞数
    if_like,
    -- 评论数
    if(comment_id,1,0) is_comment,
    -- 转发数
    if_retweet
    from tb_user_video_log l
    join tb_video_info v on l.video_id=v.video_id
    where 
    -- 近一个月发布的视频
    v.release_time >= date_sub((select date(max(end_time)) from tb_user_video_log), interval 29 day)
  ) t
  group by video_id ) t
-- 单独求每个视频最近没有播放的最大天数
join 
(
  select video_id,min(datediff((select date(max(end_time)) from tb_user_video_log), end_time2)) no_show_days from 
  (
    select v.video_id,
    last_value(end_time) over(partition by video_id order by date(end_time)) end_time2
    from tb_user_video_log l
    join tb_video_info v on l.video_id=v.video_id
    where 
    v.release_time >= date_sub((select date(max(end_time)) from tb_user_video_log), interval 29 day)
    ) t
  group by video_id 
) m on t.video_id=m.video_id
order by hot_index desc
limit 3;

    select  t.video_id,
     round((100*full_show + 5*cnt_like + 3* cnt_comment + 2* cnt_retweet)*(1/(no_show_days+1))) hot_index
     from (
    select video_id,
    sum(is_compete)/count(*) full_show,
    avg(is_compete) full_show2,
    -- 这里两个都是正确的？？？！！！
    sum(if_like) cnt_like,sum(is_comment) cnt_comment,sum(if_retweet) cnt_retweet,
    min(no_show_days) no_show_days
    from
    (
      select v.video_id,
        -- 是否看完
         case
          when timestampdiff(second,start_time,end_time) >= v.duration then 1 else 0
         end is_compete,
        -- 点赞数
        if_like,
        -- 评论数
        if(comment_id,1,0) is_comment,
        -- 转发数
        if_retweet,
        datediff((select max(date(end_time)) from tb_user_video_log), date(end_time)) as no_show_days
        from tb_user_video_log l
        join tb_video_info v on l.video_id=v.video_id
        where 
        -- 近一个月发布的视频
        v.release_time >= date_sub((select date(max(end_time)) from tb_user_video_log), interval 29 day)
      ) t
      group by video_id
  ) t
  order by hot_index desc limit 3;
  
+----------+------------+---------+------------+------------+---------------------+--------------+
| video_id | is_compete | if_like | is_comment | if_retweet | end_time            | no_show_days |
+----------+------------+---------+------------+------------+---------------------+--------------+
|     2001 |          1 |       1 |          0 |          1 | 2021-09-24 10:00:30 |           10 |
|     2001 |          1 |       1 |          0 |          0 | 2021-10-01 10:00:31 |            3 |
|     2001 |          1 |       1 |          0 |          1 | 2021-10-01 10:00:35 |            3 |
|     2001 |          1 |       1 |          1 |          0 | 2021-10-04 00:00:35 |            0 |
|     2002 |          0 |       0 |          0 |          1 | 2021-10-02 11:00:04 |            2 |
|     2002 |          1 |       0 |          0 |          0 | 2021-10-02 11:00:06 |            2 |
|     2002 |          1 |       1 |          0 |          1 | 2021-10-02 11:00:05 |            2 |
|     2002 |          0 |       1 |          0 |          0 | 2021-10-03 11:00:01 |            1 |
|     2002 |          0 |       0 |          0 |          1 | 2021-09-25 11:00:30 |            9 |
|     2003 |          0 |       1 |          0 |          1 | 2021-09-26 11:01:29 |            8 |
|     2003 |          1 |       1 |          0 |          0 | 2021-09-30 11:01:30 |            4 |
|     2004 |          1 |       1 |          0 |          0 | 2021-10-03 11:01:30 |            1 |
+----------+------------+---------+------------+------------+---------------------+--------------+

-- 大佬写的就是简洁
    select video_id,
           round((100*comp_play_rate + 5*like_cnt + 3*comment_cnt + 2*retweet_cnt) / (days_noplay + 1)) as hot_index
      from (
            select video_id,
                   sum(if(timestampdiff(second, start_time, end_time) >= duration, 1, 0)) / count(start_time) as comp_play_rate,
                   sum(if_like) as like_cnt,
                   sum(if(comment_id is not null, 1, 0)) as comment_cnt,
                   sum(if_retweet) as retweet_cnt,
                   datediff((select max(date(end_time)) from tb_user_video_log), max(date(end_time))) as days_noplay
              from tb_user_video_log
              join tb_video_info using(video_id)
             where date(release_time) >= date_sub((select max(date(end_time)) from tb_user_video_log), interval 29 day)
          group by video_id
      ) as a 
  order by hot_index desc 
     limit 3

Jenvid.yang

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
牛客网SQL刷题二-某音短视频

SQL1 各个视频的平均完播率数据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
复制链接

扫一扫