牛客网SQL实战某音短视频系列

题目来源：牛客网

01 各个视频的平均完播率
- 分析思路
02 平均播放进度大于60%的视频类别
- 分析思路
03 每类视频近一个月的转发量/率
- 分析思路
04 每个创作者每月的涨粉率及截止当前的总粉丝量
- 分析思路
05 国庆期间每类视频点赞量和转发量
- 分析思路
6 近一个月发布的视频中热度最高的top3视频
- 分析思路

01 各个视频的平均完播率

描述

用户-视频互动表tb_user_video_log

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

（uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID）

短视频信息表tb_video_info

id	video_id	author	tag	duration	release_time
1	2001	901	影视	30	2021-01-01 07:00:00
2	2002	901	美食	60	2021-01-01 07:00:00
3	2003	902	旅游	90	2021-01-01 07:00:00

（video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长（秒）, release_time-发布时间）

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

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

输出示例：

示例数据的结果如下：

video_id	avg_comp_play_rate
2001	0.667
2002	0.000

解释：

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

视频2002在2021年9月和10月共2次播放记录，观看时长分别为42秒、30秒，视频时长60秒，故完播率为0.000。

分析思路

2021年有播放记录

选择 year(start_time) = 2021（没有说明，也可以用end_time）
完播率

完播判断：timestampdiff(second, start_time, end_time) >= duration

获取完播率考虑用if函数将是否完播转换为0-1值，使用avg获得均值即完播率，round保留三位小数。
按完播率降序

order by rate desc

select tb1.video_id, 
round(avg(if(timestampdiff(second, start_time, end_time) >= tb2.duration, 1, 0)),3) avg_comp_play_rate
from tb_user_video_log tb1
join tb_video_info tb2
on tb1.video_id = tb2.video_id
where year(start_time) = 2021
group by tb1.video_id
order by avg_comp_play_rate desc;

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

描述

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

注：

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

输出示例：

示例数据的输出结果如下：

tag	avg_play_progress
影视	90.00%
美食	75.00%

解释：

影视类视频2001被用户101、102、103看过，播放进度分别为：30秒（100%）、21秒（70%）、30秒（100%），平均播放进度为90.00%（保留两位小数）；

美食类视频2002被用户102、103看过，播放进度分别为：30秒（50%）、60秒（100%），平均播放进度为75.00%（保留两位小数）；

分析思路

各类视频 按tag分组，group by tag
平均播放进度

播放时长：timestampdiff(second, start_time, end_time)

播放时长>=视频时长，播放进度为1

播放时长<视频时长，播放进度为：timestampdiff(second, start_time, end_time) / duration

使用if()函数转换为0-1问题，avg获得均值，百分比形式需要 *100，但是后续要筛选进度值，暂不添加百分号
平均播放进度>60% having xxx > 60
保留两位小数 round(xxx, 2)
按播放进度倒序排序 order by xxx desc
添加百分号% concat(xxx, %)

select tag, concat(avg_progress, '%') 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) avg_progress 
from tb_user_video_log t1
INNER join tb_video_info t2 on t1.video_id = t2.video_id
group by tag
having avg_progress > 60
order by avg_progress desc) t;

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

描述

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

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

输出示例：

示例数据的输出结果如下

tag	retweet_cut	retweet_rate
影视	2	0.667
美食	1	0.500

解释：

由表tb_user_video_log的数据可得，数据转储当天为2021年10月1日。近30天内，影视类视频2001共有3次播放记录，被转发2次，转发率为0.667；美食类视频2002共有2次播放记录，1次被转发，转发率为0.500。

分析思路

有用户互动的最近一个月

查询最近一次互动时间，使用datediff将播放记录限制在30天内

where datediff((select max(end_time) from tb_user_video_log), end_time) < 30
转发量和转发率

由于if_retweet只有0-1值，可使用聚合函数sum和avg获得转发量和转发量，需要保留三位小数，按转发率降序。

select tag, sum(if_retweet) retweet_cut, round(avg(if_retweet), 3) retweet_rate
from tb_user_video_log tb1 
join tb_video_info tb2
on tb1.video_id = tb2.video_id
where datediff((select max(end_time) from tb_user_video_log), end_time) < 30
group by tag
order by retweet_rate desc;

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

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

注：

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

输出示例：

示例数据的输出结果如下

author	month	fans_growth_rate	total_fans
901	2021-09	0.500	1
901	2021-10	0.250	2

解释：

示例数据中表tb_user_video_log里只有视频2001和2002的播放记录，都来自创作者901，播放时间在2021年9月和10月；其中9月里加粉量为1，掉粉量为0，播放量为2，因此涨粉率为0.500（保留3位小数）；其中10月里加粉量为2，掉份量为1，播放量为4，因此涨粉率为0.250，截止当前总粉丝数为2。

分析思路

时间格式

month格式为2021-09，可使用substr()提取 substr(start_time, 1, 7)
每个创作者每月涨粉率

按创作者和月份分组，使用if函数处理if_follow的值：if(if_follow=2, -1, if_follow)，

使用avg和sum可以获得每月涨粉率（保留3位小数）和每月粉丝数变化量month_change，
总粉丝量

使用聚合窗口函数，获取总粉丝量sum(month_change) over (partition by author order by month)
年份选择

需求统计2021年数据，由于总粉丝量是累计的，应该在最外层的表中选择2021年，再排序。

select author, month, fans_growth_rate,
sum(month_change) over (partition by author order by month) as total_fans
from
(
	select author, 
	substr(start_time, 1, 7) month, 
	round(avg(if(if_follow=2, -1, if_follow)), 3) fans_growth_rate, 
	sum(if(if_follow=2, -1, if_follow)) month_change
	from tb_user_video_log tb1 
	join tb_video_info tb2
	on tb1.video_id = tb2.video_id
	GROUP BY author, month
) t0
where substr(month, 1, 4) = 2021
ORDER BY author, total_fans;

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

用户-视频互动表tb_user_video_log

id	uid	video_id	start_time	end_time	if_follow	if_like	if_retweet	comment_id
1	101	2001	2021-09-24 10:00:00	2021-09-24 10:00:20	1	1	0	NULL
2	105	2002	2021-09-25 11:00:00	2021-09-25 11:00:30	0	0	1	NULL
3	102	2002	2021-09-25 11:00:00	2021-09-25 11:00:30	1	1	1	NULL
4	101	2002	2021-09-26 11:00:00	2021-09-26 11:00:30	1	0	1	NULL
5	101	2002	2021-09-27 11:00:00	2021-09-27 11:00:30	1	1	0	NULL
6	102	2002	2021-09-28 11:00:00	2021-09-28 11:00:30	1	0	1	NULL
7	103	2002	2021-09-29 11:00:00	2021-10-02 11:00:30	1	0	1	NULL
8	102	2002	2021-09-30 11:00:00	2021-09-30 11:00:30	1	1	1	NULL
9	101	2001	2021-10-01 10:00:00	2021-10-01 10:00:20	1	1	0	NULL
10	102	2001	2021-10-01 10:00:00	2021-10-01 10:00:15	0	0	1	NULL
11	103	2001	2021-10-01 11:00:50	2021-10-01 11:01:15	1	1	0	1732526
12	106	2002	2021-10-02 10:59:05	2021-10-02 11:00:05	2	0	1	NULL
13	107	2002	2021-10-02 10:59:05	2021-10-02 11:00:05	1	0	1	NULL
14	108	2002	2021-10-02 10:59:05	2021-10-02 11:00:05	1	1	1	NULL
15	109	2002	2021-10-03 10:59:05	2021-10-03 11:00:05	0	1	0	NULL

（uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID）

短视频信息表tb_video_info

id	video_id	author	tag	duration	release_time
1	2001	901	旅游	30	2020-01-01 07:00:00
2	2002	901	旅游	60	2021-01-01 07:00:00
3	2003	902	影视	90	2020-01-01 07:00:00
4	2004	902	美女	90	2020-01-01 08:00:00

（video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长, release_time-发布时间）

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

输出示例：

示例数据的输出结果如下

tag	dt	sum_like_cnt_7d	max_retweet_cnt_7d
旅游	2021-10-01	5	2
旅游	2021-10-02	5	3
旅游	2021-10-03	6	3

解释：

由表tb_user_video_log里的数据可得只有旅游类视频的播放，2021年9月25到10月3日每天的点赞量和转发量如下：

tag	dt	like_cnt	retweet_cnt
旅游	2021-09-25	1	2
旅游	2021-09-26	0	1
旅游	2021-09-27	1	0
旅游	2021-09-28	0	1
旅游	2021-09-29	0	1
旅游	2021-09-30	1	1
旅游	2021-10-01	2	1
旅游	2021-10-02	1	3
旅游	2021-10-03	1	0

因此国庆头3天（10.01_{10.03）里10.01的近7天（9.25}10.01）总点赞量为5次，单天最大转发量为2次（9月25那天最大）；同理可得10.02和10.03的两个指标。

分析思路

每类视频每天的点赞量和转发量

按视频类别和日期分组，sum(if_like)和sum(if_retweet)分别单日点赞量like_cnt和转发量retweet_cnt
近一周总点赞量和一周内最大单天转发量

由于每天都有播放记录，可以直接使用窗口函数指定当前行和前六行数据rows 6 preceding

一周总点赞量 sum_like_cnt_7d：

sum(sum(if_like)) over (PARTITION BY tag ORDER BY date(start_time) ROWS 6 PRECEDING)

一周最大单天转发量 max_retweet_cnt_7d：

max(sum(if_retweet)) over (PARTITION BY tag ORDER BY date(start_time) ROWS 6 PRECEDING)
时间选择和排序

国庆前三天：WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'

排序：ORDER BY tag DESC, dt

select * 
from (	
    select tag, date(start_time) dt, 
        sum(sum(if_like)) over (PARTITION BY tag ORDER BY date(start_time)
                            ROWS 6 PRECEDING) as sum_like_cnt_7d,
        max(sum(if_retweet)) over (PARTITION BY tag ORDER BY date(start_time)
                            ROWS 6 PRECEDING) as max_retweet_cnt_7d
    from tb_user_video_log tb1 
    join tb_video_info tb2
    on tb1.video_id = tb2.video_id
    GROUP BY tag, dt
) t0
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY tag DESC, dt;

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

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

注：

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

输出示例：

示例数据的输出结果如下

video_id	hot_index
2001	122
2002	56
2003	1

解释：

最近播放日期为2021-10-03，记作当天日期；近一个月（2021-09-04及之后）发布的视频有2001、2002、2003、2004，不过2004暂时还没有播放记录；

视频2001完播率1.0（被播放次数4次，完成播放4次），被点赞3次，评论1次，转发2次，最近无播放天数为0，因此热度为：(100*1.0+5*3+3*1+2*2)/(0+1)=122

同理，视频2003完播率0，被点赞数1，评论和转发均为0，最近无播放天数为3，因此热度为：(100*0+5*1+3*0+2*0)/(3+1)=1（1.2保留为整数）。

分析思路

最近播放日期：select date(max(end_time)) from tb_user_video_log
最近无播放天数（按video_id分组）：最近播放日期 - 该视频最近播放日期

datediff((select date(max(end_time)) from tb_user_video_log), date(max(end_time))) freshness
完播率

round(avg(if(timestampdiff(second, start_time, end_time) >= tb2.duration, 1, 0)),3) avg_comp_play_rate
视频发布时间：一个月内

where datediff((select date(max(end_time)) from tb_user_video_log), date(release_time)) < 30
热度：round(xxx, 0)，降序，前三

(100 * avg_comp_play_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt)/ (freshness + 1)

select video_id, 
    round((100 * avg_comp_play_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt)/ (freshness + 1), 0) as hot_index
from (
    select tb1.video_id, 
        round(avg(if(timestampdiff(second, start_time, end_time) >= tb2.duration, 1, 0)),3) avg_comp_play_rate,
        sum(if_like) like_cnt, 
        count(comment_id) comment_cnt, 
        sum(if_retweet) retweet_cnt, 
        datediff((select date(max(end_time)) from tb_user_video_log), date(max(end_time))) freshness
    from tb_user_video_log tb1 
    join tb_video_info tb2
    on tb1.video_id = tb2.video_id
    where datediff((select date(max(end_time)) from tb_user_video_log), date(release_time)) < 30
    group by tb1.video_id) t0
order by hot_index desc
limit 3;

posted @ 2022-03-23 23:48 ikventure 阅读(304) 评论(0) 编辑收藏举报

刷新页面返回顶部

ikventure

牛客网SQL实战 某音短视频系列

01 各个视频的平均完播率

分析思路

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

分析思路

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

分析思路

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

分析思路

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

分析思路

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

分析思路

公告

牛客网SQL实战某音短视频系列