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

2023-10-29

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

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

离线数仓

数据仓库

sql

牛客网SQL刷题二-某音短视频的相关文章

jDBI中如何进行内查询？

我怎样才能在 jDBI 中执行这样的事情 SqlQuery select id from foo where name in
Postgresql：SERIAL 在约束 INSERT 失败时递增

有一个像这样的简单表结构 CREATE TABLE test id INT PRIMARY KEY sid SERIAL 我注意到如果我尝试插入一行但它未通过约束测试即主键约束 SERIAL计数器无论如何都会增加所以下一次成功插入 si
从两个表中搜索然后删除

我有两个包含成员数据的表与 member id 列链接我需要搜索所有记录email列以 pl 结尾然后我需要为此删除两个表中的所有记录基于 member id 是否可以通过一条 SQL 语句完成此操作 SELECT member
MySQL如何根据字段是否存在来插入新记录或更新字段？

我正在尝试实现一个评级系统在数据库表中保留以下两个字段评级当前评级 num rates 迄今为止提交的评分数量 UPDATE mytable SET rating rating num rates theRating num rate
SQL 更新数据集中的位置

id1 id2 bool 1 1 F 1 2 F 2 1 F UPDATE table name SET bool T WHERE id1 id2 IN 1 1 2 1 Need work here 所以基本上我想选择条件为 id1 id2
SQL使用多个/相关列计算项目频率？

我对 SQL 完全陌生并且阅读了有关 SQL 的 StackOverflow 帖子来尝试弄清楚这一点以及其他来源但无法在 SQL 中执行此操作开始我有一个 3 列和数千行的表其中包含前 2 列的数据第三列当前为空我需要根据第
Oracle：如何查找模式中上次更新（任何表）的时间戳？

有一个Oracle数据库模式数据很小但仍然有10 15个表左右它包含一种配置路由表有一个应用程序必须不时轮询此架构不得使用通知如果架构中没有数据更新应用程序应使用其当前的内存版本如果任何表有任何更新应用程序应将所有表重新
基于列顺序的查询速度

数据库中列类型的顺序对查询时间有影响吗例如具有混合排序 INT TEXT VARCHAR INT TEXT 的表的查询速度是否会比具有连续类型 INT INT VARCHAR TEXT TEXT 的表慢答案是肯定的这确实很重要而且
如何在SQL Server数据库表列中存储图像[重复]

这个问题在这里已经有答案了我有一张名为FEMALE在我的数据库中它有ID as Primary Key 它有一个Image column 我的问题是如何使用 SQL 查询存储图像尝试一下 insert into tableName I
整理有关 QueryDSL-JPA 的提示

有没有办法用 QueryDSL 来执行它粗体部分从地点选择其中名称如 cafe 整理 Latin1 general CI AI 我将 JPA 与 hibernate 一起使用您可以使用addFlag QueryFlag Positi
在同一个表上组合两个 SQL SELECT 语句

我想结合这两个 SQL 查询 SELECT FROM Contracts WHERE productType RINsell AND clearTime IS NULL AND holdTime IS NOT NULL ORDER BY g
MySQL 中的断言

我有一个针对大型数据库运行的 SQL 脚本我想在开始时提出几个简单的查询作为健全性检查有没有办法在MySQL中写断言或者任何类型的选择如果它与该值不匹配则中止整个脚本一些疯狂的代码要点是 SET可能会引发 mysql 变量
从存储过程中的动态 SQL 获取结果

我正在编写一个存储过程需要在过程中动态构造 SQL 语句以引用传入的表名称我需要让这个 SQL 语句返回一个结果然后我可以在整个过程的其余部分中使用该结果我尝试过使用临时表和所有内容但我不断收到一条消息提示我需要声明变量等例如
SQL 大表中的随机行（使用 where 子句）

我有一个网站人们可以在其中对汽车进行投票向用户展示 4 辆汽车他她可以投票选出他们最喜欢的汽车桌子cars有重要的列 car id int 10 not auto increment so has gaps views int 7
SQL Server 使用通配符加入并在第一个匹配处停止

IF OBJECT ID tempdb TABLE1 IS NOT NULL DROP TABLE TABLE1 IF OBJECT ID tempdb TABLE2 IS NOT NULL DROP TABLE TABLE2 CREATE
是否允许在流水线 PL/SQL 表函数中使用 SELECT？

管道函数的文档指出在 SQL 语句通常是SELECT 并且在大多数示例中管道函数用于数据生成或转换接受客户作为参数但不发出任何 DML 语句现在从技术上讲可以使用 SELECT 而不会出现 Oracle 中的任何错误 ORA
SQL Server 相当于 MySQL 的 USING

在 MySQL 中当您连接不同表中具有相同名称的列时可以在连接中使用关键字 USING 例如这些查询产生相同的结果 SELECT FROM user INNER JOIN perm USING uid SELECT FROM user
消息 102，级别 15，状态 1，第 1 行“ ”附近的语法不正确

我试图从临时表中查询但不断收到此消息 Msg 102 Level 15 State 1 Line 1 Incorrect syntax near 有人能告诉我问题是什么吗是因为要转换吗查询是 select compid 2 conve
SQL Server 转换选择一列并将其转换为字符串

是否可以编写一条从表中选择列并将结果转换为字符串的语句理想情况下我希望有逗号分隔的值例如假设 SELECT 语句看起来像这样 SELECT column FROM table WHERE column lt 10 结果是一列包含值的
将多行合并为一行并根据行数附加列

我正在尝试将同一个表的多行合并为一个我有一个像这样的示例表 Col1 Col2 Col3 Col4 Col5 Col6 1 BH1 CB 12 CC CC Conveyor Mal 1 BH1 CB 104 ZC ZC Full Emp

随机推荐

c语言中宏定义的关键字,C语言中，宏定义“define”的一种有趣的用法

在C语言中宏定义也是一种很重要的概念可以使用 define 来定义一个常量也可以用 define 来实现一些功能这一篇文章中我们将介绍一下在C语言中使用宏定义 define 实现的一个小的例子我们可以借助 define 将很
汇编：按给定格式在屏幕中间显示字符串

代码如下 assume cs code ds data ss stac data segment db welcome to masm db 02h 24h 71h data ends stac segment dw 0 0 0 0 0 0
Lion：闭源大语言模型的对抗蒸馏

Lion 闭源大语言模型的对抗蒸馏 Lion 由香港科技大学提出的针对闭源大语言模型的对抗蒸馏框架成功将 ChatGPT 的知识转移到了参数量 7B的 LLaMA 模型命名为 Lion 在只有 70k训练数据的情况下实现了近 95 的
两台服务器文件镜像,两台云服务器镜像

两台云服务器镜像内容精选换一换请您在购买前确保已完成注册和充值详细操作请参见如何注册公有云管理控制台的用户登录管理控制台单击管理控制台左上角的选择区域和项目选择计算 gt 弹性云服务器单击购买弹性云服务器系统进入购买
删除单链表中的指定节点

题目1 编写在带头结点的单链表L中删除一个最小值结点的高效算法假设最小值结点是唯一的时间复杂度为O n 空间复杂度为O 1 问题解答算法思想用p从头至尾扫描单链表 pre指向 p结点的前驱用minp保存值最小的结点指针初值为p
那些方法可以绕过服务器对文件内容的检测,文件上传漏洞

文件上传文件上传就是在一些web应用中允许用户上传图片文本等相应文件到服务器指定的位置而文件上传漏洞就是利用这些可以上传的地方将恶意代码植入到服务器中之后通过url去访问以执行代码达到攻击的目的可以成功攻击的条件 1 存放上传文件
Linux网络编程：IO多路复用——epoll

服务器端代码 epoll event poll include
mysql sqlite 语法_浅谈sqlite与mysql的数据库语法差异_沃航科技

由于一些原因小沃将自己的官方网站从原来的阿里云迁移到了justg的vps上服务器配置也从原理的1核1G变为了1核512M 虽然服务器性能所有下降但是毕竟小沃的官网是使用了百度的cdn的因此应付高并发还是没有什么问题的考虑到服务器性
Go框架之Gin应用到原理源码

Go框架之Gin应用到原理源码基本概览目前 golang使用最广泛的Web 微框架之一具有高性能的优点基于 httprouter 它提供了类似martini但更好性能路由性能约快40倍的API服务本文将从基本应用快速上手到中间
C++中unique函数

C 中unique函数文章一 unique是STL中很实用的函数之一需要 include 感谢各位提醒下面来简单介绍一下它的作用 unique的作用是去掉容器中相邻元素的重复元素这里去掉要加一个引号为什么呢是因为它实质上是一
Java面试题全集（下）

这部分主要是开源Java EE框架方面的内容包括hibernate MyBatis spring Spring MVC等由于Struts 2已经是明日黄花在这里就不讨论Struts 2的面试题如果需要了解相关内容可以参考我的另一篇
封装高德地图定位组件
回顾2019，展望2020

同2018一年一样 2019年同样是充实的一年回顾一下2019年做出总结不足的地方在未来的日子里改进顺便展望2020年给自己一个比较清晰的目标 2019年工作之外的时间基本都花在学习英语和编程相关的技术上学习英语花费的时间多于学
使用Git LFS上传大文件到GitHub教程，以及可能会遇到的坑（使用了Git LFS却依然传不上超过100M的文件；framework库如何添加等）

什么是Git LFS Git LFS Large File Storage 是 Github 开发的一个 Git 的扩展用于实现 Git 对大文件的支持简单的说就是如果你想传超过100M的二进制文件到GitHub 你就要用Git LF
obs上传文件到服务器,OBS服务应用于互联网数据上传时使用POST 实现服务端和客户端权限控制和数据上传分离的方法...

1背景采用JavaScript SDK 等客户端直接签名时 AccessKeyID和AcessKeySecret会暴露在前端页面因此存在严重的安全隐患因此 OBS提供了服务端签名后直传的方案解决此问题问题代码如下所示需要在前端使
牛客——数字权重（组合数学）

数字权重题目描述小a有一个n位的数字但是它忘了各个位上的数是什么现在请你来确定各个位上的数字满足以下条件设第 i i i位的数为 a i
关于WEB 性能优化（摘抄）

压缩源代码和图片 JavaScript文件源代码可以采用混淆压缩的方式 CSS文件源代码进行普通压缩 JPG图片可以根据具体质量来压缩为50 到70 PNG可以使用一些开源压缩软件来压缩比如24色变成8色去掉一些PNG格式信息等选择合
R语言：以多列标准筛选特定行

作者村长数据科学指弹吉他及录音工程爱好者浙大金融学博士在读在data table包和MongoDB的使用上有较多经验写在前面本期我们大猫二人组的村长在新的一年首先回归为大家带来新的推送在本期我们会运用一个病例数据为大家
avrdude: stk500v2_ReceiveMessage(): timeout， arduino mega2560 上传出错; arduino nano刷bootload后上传出错

avrdude stk500v2 ReceiveMessage timeout 此方法适用于其他各型号Arduino 1 将C Program Files x86 Arduino hardware arduino avr boards tx
牛客网SQL刷题二-某音短视频

SQL1 各个视频的平均完播率数据 DROP TABLE IF EXISTS tb user video log tb video info CREATE TABLE tb user video log id INT PRIMARY KE

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

SQL1 各个视频的平均完播率

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

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

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

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

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

牛客网SQL刷题二-某音短视频 的相关文章

随机推荐

热门标签

牛客网SQL刷题二-某音短视频的相关文章