某音短视频——SQL编程真题练习

最新推荐文章于 2024-07-19 13:24:27 发布

查平平

最新推荐文章于 2024-07-19 13:24:27 发布

阅读量1.8k

点赞数 51

文章标签：音视频 sql 数据库数据分析

本文链接：https://blog.csdn.net/zfyzfw/article/details/139065210

版权

1.各个视频的平均完播率

用户-视频互动表tb_user_video_log如下

短视频信息表tb_video_info如下

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

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

题目分析：

1.理解题目意思：

有播放记录的视频的id号和其完播率

--------->有播放记录（只要有开始时间即有播放记录，所以连接两表应该是左内连接LEFT JOIN）

条件是2021年和根据其完播率降序排序

--------->年份，通过year（）可限制，即限制start_time/end_time都可---->year(start_time)=2021

2.拆解复杂指标：完播率

（1）结束时间-开始时间>=总时长就是完播，不仅仅是二者相等才算完播

--------->结束时间-开始时间若>=视频时常，就记为1，否则记为0，再把所有1相加，即得视频完播的总次数

-------->用到sum(if(end_time-start_time>=duration,1,0))，也可用TIMESTAMPDIFF(second, start_time, end_time)计算两个时间的差值，if 也可以用case when （end_time-start_time>=duration ）then 1 else 0 end 代替

这里使用sum而~~不使用count~~的原因是，count不会对null值行计数

（2）总播放次数：只要有start_time就记为一次播放，不管是否完播----count(start_time)

（3）完播率：ROUND（视频完播总次数/总播放次数，3），使用round函数保留3位小数

SELECT a.video_id ,
	   round(sum(if(end_time - start_time >= duration, 1, 0))/count(start_time ),3) as avg_comp_play_rate
FROM tb_user_video_log a
LEFT JOIN tb_video_info b
on a.video_id = b. video_id
WHERE year(start_time) = 2021
GROUP BY a.video_id 
ORDER BY avg_comp_play_rate DESC;

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

依旧是基于这两个表进行查询：用户-视频互动表tb_user_video_log和短视频信息表tb_video_info

问题：

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

注：

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

1.理解题目意思：

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

2.问题分解：

关联用户-视频互动记录和短视频信息表： JOIN tb_video_info USING(video_id)；
按视频类别分组：GROUP BY tag
计算每个类别的平均播放进度：

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

播放时长=TIMESTAMPDIFF(SECOND, start_time, end_time)；特殊情况：播放时长大于视频时长时，播放进度为100%（加个IF判断、或者case when then）~~此处不要用end_time-start_time得播放时长，容易报错，有时候1分钟被算出100秒了~~

平均进度=AVG(每个进度)

结果保留2位小数：ROUND(x, 2)

百分比格式化：CONCAT(x, '%')

计算公式：

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

筛选播放进度>60%的视频类别：HAVING avg_play_progress > 60

以下是我自己编写的代码，非常基础，没有优化，有点笨重但非常容易理解

SELECT b.tag,
CONCAT(ROUND(AVG(
    CASE WHEN TIMESTAMPDIFF(second, start_time, end_time) > b.duration 
    THEN 100
    ELSE (TIMESTAMPDIFF(second, start_time, end_time)) / b.duration * 100 END), 2), '%') AS avg_play_progress
FROM tb_user_video_log a 
LEFT JOIN tb_video_info b ON a.video_id = b.video_id
GROUP BY b.tag
HAVING AVG(
    CASE WHEN TIMESTAMPDIFF(second, start_time, end_time) > b.duration 
    THEN 100 
    ELSE TIMESTAMPDIFF(second, start_time, end_time) / b.duration * 100 
    END
) > 60
ORDER BY avg_play_progress DESC;

以下是比较简洁、高级的写法

SELECT tag, CONCAT(avg_play_progress, "%") as avg_play_progress
FROM (
    SELECT tag, 
        ROUND(AVG(
            IF(TIMESTAMPDIFF(SECOND, start_time, end_time) > duration, 1,
               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
    ORDER BY avg_play_progress DESC
) as t_progress;

此处用到了子查询，以及表连接USING的写法，应该多多尝试使用子查询的方法，而不是一步一步跟着写，太麻烦了，而且括号太多也容易出错！

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

依旧是基于这两个表进行查询：用户-视频互动表tb_user_video_log和短视频信息表tb_video_info

问题：

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

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

1.理解题目意思：

计算每类视频在有用户互动的最近一个月（按包含当天在内的近30天算）中的转发量和转发率。结果按转发率降序排序

①有用户互动怎么衡量？②最近的一个月，那么要找最大日期，再减去30天

2.问题分解：

关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)；
筛选最近30天的记录：
- 找到今天的日期：MAX(DATE(start_time)
- 往过去推移30天：DATE_SUB(MAX(DATE(start_time)), INTERVAL 30 DAY)-----DATE_SUB函数是用来在日期上进行减法运算的函数。
  - DATE_SUB(date, INTERVAL expr unit)
    - date：要进行减法运算的日期。可以是一个具体的日期或者一个日期时间字段；
    - expr：要进行减法运算的数值。可以是一个整数或者一个数值字段；
    - unit：要进行减法运算的单位。可以是YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND等单位；
- 筛选最近的：
- WHERE DATE(start_time) > (SELECT DATE_SUB(MAX(DATE(start_time)), INTERVAL 30 DAY) FROM tb_user_video_log)

按视频类别分组：GROUP BY tag
计算每个类别的转发量：SUM(if_retweet)
计算每个类别的转发率（转发率＝转发量÷播放量）：SUM(if_retweet) / COUNT(1)
保留3位小数：ROUND(x, 3)
按转发率倒序排序：ORDER BY retweet_rate DESC;

SELECT tag, SUM(if_retweet) as retweet_cnt,
    ROUND(SUM(if_retweet) / COUNT(1), 3) as retweet_rate
FROM tb_user_video_log
JOIN tb_video_info USING(video_id)
WHERE DATE(start_time) > (
    SELECT DATE_SUB(MAX(DATE(start_time)), INTERVAL 30 DAY)
    FROM tb_user_video_log
)
GROUP BY tag
ORDER BY retweet_rate DESC;
#在where语句中用到了子查询 date_sub(日期，interval 减去多少数量的时间  单位）

第二种写法如下：

SELECT b.tag, SUM(if_retweet) retweet_cnt, 
ROUND(SUM(if_retweet)/COUNT(*), 3) retweet_rate
FROM tb_user_video_log a
LEFT JOIN tb_video_info b
ON a.video_id = b.video_id
WHERE DATEDIFF(DATE((select max(start_time) FROM tb_user_video_log)), DATE(a.start_time)) <= 29
GROUP BY b.tag
ORDER BY retweet_rate desc;

#通过date（max（））找到最近的日期
#通过datediff（）<=29找到最近一个月的数据
#在where语句，查询最近日期时用到了子查询

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

依旧是基于这两个表进行查询：用户-视频互动表tb_user_video_log和短视频信息表tb_video_info

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

注：涨粉率=(加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序。

if_follow-是否关注为1表示用户观看视频中关注了视频创作者，

为0表示此次互动前后关注状态未发生变化，

为2表示本次观看过程中取消了关注。

1.理解题目意思：

计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量，涨粉率=(加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序

2.问题分解：

关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)；
筛选2021年的记录：where YEAR(start_time) = 2021
计算每个创作者每个月的涨粉量和播放量
- 按创作者和月份分组：group by author, DATE_FORMAT(start_time, "%Y-%m")
- 计算涨粉量（特殊情况if_follow=2时表示掉粉）：sum(if(if_follow=2, -1, if_follow)) as fans_add_cnt
- 计算播放量（每条记录就是一次播放）：count(1) as play_cnt
计算每个创作者每个月的涨粉量和播放量：
- 计算涨粉率，保留3位小数：round(fans_add_cnt / play_cnt, 3)
- 计算截止当月的总粉丝量（累积求和）：sum(fans_add_cnt) over(partition by author order by month)

select author, `month`, round(fans_add_cnt / play_cnt, 3) as fans_growth_rate,
    sum(fans_add_cnt) over(partition by author order by `month`) as total_fans
from (
    select author,
        DATE_FORMAT(start_time, "%Y-%m") as `month`,
        sum(if(if_follow=2, -1, if_follow)) as fans_add_cnt,
        count(1) as play_cnt
    from tb_user_video_log
    join tb_video_info USING(video_id)
    where YEAR(start_time) = 2021
    group by author, `month`
) as t_author_monthly_fans_play_cnt
order by author, total_fans;

！当月总粉丝量用到了窗口函数，涨粉量直接用sum+if条件语句，month用到了DATE_FORMAT函数，用于以不同的格式显示日期/时间数据。

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

依旧是基于这两个表进行查询：用户-视频互动表tb_user_video_log和短视频信息表tb_video_info

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

1.理解题目意思：

统计每类视频，2021年10月1号到3号，这三天，每天往前7天的总点赞量，以及7天内【单天转发量】的最大值

涉及开窗函数滑动窗口统计

#当前行，往前2行+当前行+当前行往后2行（一共5行）
rows between 2 perceding and 2 following

#当前行的后1——>后3（共3行）
rows between 1 following 3 following

#从第一行到当前行
rows between unbounded preceding and current row

2.问题分解：

(1) 先按天进行聚合统计

因为原数据是以天为单位的统计数据，每一天都会有多条if_like和if_retweet记录，所以先要按照tag，date进行统计，得到每天的总点赞量like_cnt，和总转发量retweet_cnt

(2)滑动窗口的设置(ROWS BETWEEN CURRENT ROW AND 6 PRECEDING)

思路：在09.25-10.03这个区间内，按tag聚合，dt逆序，统计得到CURRENT ROW及前6行的点赞量统计sum_like_cnt_7d，和转发量sum_retweet_cnt_7d

注意点：不可以按dt升序，ROW 6 PRECEDING输出，会报错。因为开窗函数进行滑动统计时，起点或终点必须非空。在本题中，如果按照dt升序，第一行数据前面根本不存在前6行，所以会报错

（3）记录的筛选

最后，在外面再套一层SELECT，取出所有字段，按照tag, dt聚合，HAVING限定日期为10月1号到3号，按照题目要求排序就大功告成啦。

SELECT
  *
FROM (
  SELECT
    tag,
    dt,
    SUM(like_cnt) OVER w sum_like_cnt_7d,
    MAX(retweet_cnt) OVER w sum_retweet_cnt_7d
  FROM (
    SELECT
      tag,
      DATE(start_time) dt,
      SUM(if_like) like_cnt,
      SUM(if_retweet) retweet_cnt
    FROM tb_video_info
    LEFT JOIN tb_user_video_log USING(video_id)
    WHERE DATE(start_time) BETWEEN '2021-09-25' AND '2021-10-03'
    group by 1,2) t1
  WINDOW w AS (PARTITION BY tag ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING)
) t2
GROUP BY 1, 2
HAVING dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY 1 DESC, 2

窗口定义：窗口 w：按 tag 分区，按 dt 降序排列，范围是当前行及接下来的6行（即7天内）。

SQL查询中，GROUP BY 1, 2 和 ORDER BY 1 DESC, 2 是简写形式，分别表示按查询结果中的第1列和第2列进行分组和排序

其中内层查询（t1），中层查询（t2），外层查询，共三层查询

！涉及累计量通常会用到窗口函数

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

依旧是基于这两个表进行查询：用户-视频互动表tb_user_video_log和短视频信息表tb_video_info

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

注：

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

1.理解题目意思：

最近一个月怎么表示，热度最高的3个视频，视频完播率怎么定义，最近无播放天数怎么定义

2.问题分解：

SQL查询的粒度,其实就是你想要输出的数据集的聚合级别。

（1）热度最高粒度：视频id+最近一个月

热度=(100*视频完播率+5*点赞数+3*评论数+2*转发数)*新鲜度

视频完播率：

sum(if(end_time-start_time-duration>=0,1,0))/count(video_id)

点赞数：

sum(if_like)

评论数：

sum(if(comment_id is not null,1,0))

转发数：

sum(if_retweet)

新鲜度=1/(最近无播放天数+1)

以2004为例，9-5发布（release_time-发布时间），整体视频的当前日期如下

 select max(end_time) from tb_user_video_log

当播放次数为0时，最近无播放天数=当前日期-发布日期；

当播放次数不为0时，最近无播放天数=当前日期-最近一次播放日期

 if(count(video_id)=0,date(select max(end_time) from tb_user_video_log)-release_time,
    date(select max(end_time) from tb_user_video_log)-max(date(end_time)))

（2）top3视频

limit函数：limit 0,3

（3）近一个月

detediff(最新日期-发布日期)<=29

SELECT video_id,
       round((100*finish_rate+5*like_index+3*comment_index+2*retweet_index)/(fresh_index+1),0) hot_index
FROM(
SELECT a.video_id,
       sum(if(timestampdiff(second,a.start_time,a.end_time)-b.duration>=0,1,0))/count(a.video_id) finish_rate,
       sum(a.if_like) like_index,
       sum(if(a.comment_id is not null,1,0)) comment_index,
       sum(a.if_retweet) retweet_index,
       if(count(a.video_id)=0,datediff(date((select max(end_time) from tb_user_video_log)),date(b.release_time)),
          datediff(date((select max(end_time) from tb_user_video_log)),max(date(a.end_time)))) fresh_index
FROM tb_user_video_log a
    left join tb_video_info b
    on a.video_id=b.video_id
where DATEDIFF(date((select max(end_time) from tb_user_video_log)),DATE(b.release_time))<=29
group by a.video_id) fir_sheet
order by hot_index DESC
limit 0,3

注意timestampdiff，和datediff的区别

timestampdiff是计算时间差，然后四舍五入到day上，而datediff是计算的日期差

例如：2022-05-08 21:16 和2022-05-09 21:15

使用timestampdiff的话就是0，相差不到一天，用datediff话是1，后者只返回二者相差的天数，