牛客 SQL 156~160

最新推荐文章于 2024-05-21 18:34:19 发布
最新推荐文章于 2024-05-21 18:34:19 发布
阅读量112 收藏
点赞数
文章标签： sql ffmpeg 音视频
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/peimina/article/details/129799846
版权

156——计算完播率

用户-视频互动表tb_user_video_log

iduidvideo_idstart_timeend_timeif_followif_likeif_retweetcomment_id
110120012021-10-01 10:00:002021-10-01 10:00:30011NULL
210220012021-10-01 10:00:002021-10-01 10:00:24001NULL
310320012021-10-01 11:00:002021-10-01 11:00:340101732526
410120022021-09-01 10:00:002021-9-01 10:00:42101NULL
510220022021-10-01 11:00:002021-10-01 10:00:30101NULL

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

短视频信息表tb_video_info

idvideo_idauthortagdurationrelease_time
12001901影视302021-01-01 07:00:00
22002901美食602021-01-01 07:00:00
32003902旅游902021-01-01 07:00:00

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

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

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

输出示例：

示例数据的结果如下：

video_idavg_comp_play_rate
20010.667
20020.000

解释：

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

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

select
    a.video_id,
    round(
        sum(
            if ((end_time - start_time - b.duration) >= 0, 1, 0)
        ) / count(a.video_id),
        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

round函数

round(value,n)：用于对某个数值（字段）保留指定小数位数（四舍五入）

SQL执行顺序

一般的执行顺序：

  1. from
  2. join
  3. on
  4. where
  5. group by
  6. avg, sum等聚合函数
  7. having
  8. select
  9. distinct
  10. order by
  11. limit

157——计算各类视频平均播放速度

用户-视频互动表tb_user_video_log

iduidvideo_idstart_timeend_timeif_followif_likeif_retweetcomment_id
110120012021-10-01 10:00:002021-10-01 10:00:30011NULL
210220012021-10-01 10:00:002021-10-01 10:00:21001NULL
310320012021-10-01 11:00:502021-10-01 11:01:200101732526
410220022021-10-01 11:00:002021-10-01 11:00:30101NULL
510320022021-10-01 10:59:052021-10-01 11:00:05101NULL

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

短视频信息表tb_video_info

idvideo_idauthortagdurationrelease_time
12001901影视302021-01-01 07:00:00
22002901美食602021-01-01 07:00:00
32003902旅游902021-01-01 07:00:00

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

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

注：

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

输出示例：

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

tagavg_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%（保留两位小数）；

select tag,concat(avg_play_progress,"%") as avg_play_progress 
from(
select tag,round(avg(if(TIMESTAMPDIFF(second,start_time,end_time)>duration,100,TIMESTAMPDIFF(second,start_time,end_time)/duration*100)),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 tag
having avg_play_progress > 60
order by avg_play_progress desc)t


-- 运行结果一样，但是提交结果没通过
select tag,concat(avg_play_progress,"%") as avg_play_progress 
from(
select tag,round(avg(if((end_time-start_time)/duration>=1,100,(end_time-start_time)/duration*100)),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 tag
having avg_play_progress > 60
order by avg_play_progress desc)t

小数点转化为百分比的方法:concat(value,“%”)

TIMESTAMPDIFF函数：

TIMESTAMPDIFF(unit,begin,end)
TIMESTAMPDIFF函数返回begin-end的结果，其中begin和end是DATE或DATETIME表达式。

TIMESTAMPDIFF函数允许其参数具有混合类型，例如，begin是DATE值，end可以是DATETIME值。 如果使用DATE值，则TIMESTAMPDIFF函数将其视为时间部分为“00:00:00”的DATETIME值。

unit参数是确定(end-begin)的结果的单位，表示为整数。 以下是有效单位：

MICROSECOND 微秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 周
MONTH 月份
QUARTER
YEAR 年份

158——统计在有用户互动的最近一个月每类视频转发率

用户-视频互动表tb_user_video_log

iduidvideo_idstart_timeend_timeif_followif_likeif_retweetcomment_id
110120012021-10-01 10:00:002021-10-01 10:00:20011NULL
210220012021-10-01 10:00:002021-10-01 10:00:15001NULL
310320012021-10-01 11:00:502021-10-01 11:01:150101732526
410220022021-09-10 11:00:002021-09-10 11:00:30101NULL
510320022021-10-01 10:59:052021-10-01 11:00:05100NULL

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

短视频信息表tb_video_info

idvideo_idauthortagdurationrelease_time
12001901影视302021-01-01 07:00:00
22002901美食602021-01-01 07:00:00
32003902旅游902020-01-01 07:00:00

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

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

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

输出示例：

示例数据的输出结果如下

tagretweet_cutretweet_rate
影视20.667
美食10.500

解释：

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

select tag,sum(if_retweet) retweet_cut,round((sum(if_retweet)/count(a.video_id)),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

-- 用video_id分组结果提交不通过
select tag,sum(if_retweet) retweet_cut,round((sum(if_retweet)/count(a.video_id)),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 a.video_id
order by retweet_rate desc

难点：如何计算30天内

-- 方法一：先取出最大的开始观看时间，转换成date格式，然后跟每一行的开始时间作对比
datediff(date((select max(start_time) from tb_user_video_log)),date(a.start_time))<=29

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

用户-视频互动表tb_user_video_log

iduidvideo_idstart_timeend_timeif_followif_likeif_retweetcomment_id
110120012021-09-01 10:00:002021-09-01 10:00:20011NULL
210520022021-09-10 11:00:002021-09-10 11:00:30101NULL
310120012021-10-01 10:00:002021-10-01 10:00:20111NULL
410220012021-10-01 10:00:002021-10-01 10:00:15001NULL
510320012021-10-01 11:00:502021-10-01 11:01:151101732526
610620022021-10-01 10:59:052021-10-01 11:00:05200NULL

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

短视频信息表tb_video_info

idvideo_idauthortagdurationrelease_time
12001901影视302021-01-01 07:00:00
22002901美食602021-01-01 07:00:00
32003902旅游902020-01-01 07:00:00
42004902美女902020-01-01 08:00:00

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

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

注：

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

输出示例：

示例数据的输出结果如下

authormonthfans_growth_ratetotal_fans
9012021-090.5001
9012021-100.2502

解释：

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

(ps:total_fans是截止当前的总粉丝数，是要加上前面9月份的，要看清楚题目，所以需要用到开窗函数计算累积粉丝数)

select author, date_format(start_time,'%Y-%m') month,
round(sum(case when if_follow = 2 then -1 when if_follow = 1 then 1 else 0 end)/count(a.video_id),3) as fans_growth_rate,
sum(sum(case when if_follow = 2 then -1 when if_follow = 1 then 1 else 0 end)) over (partition by author order by date_format(start_time,'%Y-%m')) as total_fans
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 author,month
order by b.author,total_fans

case when的使用方法

Case具有两种格式。简单Case函数和Case搜索函数。

  • 第一种 格式 : 简单Case函数 :

格式说明

case 列名

when 条件值1 then 选项1

when 条件值2 then 选项2…

else 默认值 end

  • 第二种 格式 :Case搜索函数

格式说明

case

when 列名= 条件值1 then 选项1

when 列名=条件值2 then 选项2…

else 默认值 end

date_format(date,format)函数

  • 1、date：代表具体时间字段，也可以为now()查询当前时间;
  • 2、format：DATE_FORMAT将传来的Date类型数据转为自己需要的格式，如%Y-%m-%d %H:%i:%s会将传来的Time数据转为"yyyy-MM-dd HH:mm:ss"格式

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

用户-视频互动表tb_user_video_log

iduidvideo_idstart_timeend_timeif_followif_likeif_retweetcomment_id
110120012021-09-24 10:00:002021-09-24 10:00:20110NULL
210520022021-09-25 11:00:002021-09-25 11:00:30001NULL
310220022021-09-25 11:00:002021-09-25 11:00:30111NULL
410120022021-09-26 11:00:002021-09-26 11:00:30101NULL
510120022021-09-27 11:00:002021-09-27 11:00:30110NULL
610220022021-09-28 11:00:002021-09-28 11:00:30101NULL
710320022021-09-29 11:00:002021-10-02 11:00:30101NULL
810220022021-09-30 11:00:002021-09-30 11:00:30111NULL
910120012021-10-01 10:00:002021-10-01 10:00:20110NULL
1010220012021-10-01 10:00:002021-10-01 10:00:15001NULL
1110320012021-10-01 11:00:502021-10-01 11:01:151101732526
1210620022021-10-02 10:59:052021-10-02 11:00:05201NULL
1310720022021-10-02 10:59:052021-10-02 11:00:05101NULL
1410820022021-10-02 10:59:052021-10-02 11:00:05111NULL
1510920022021-10-03 10:59:052021-10-03 11:00:05010NULL

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

短视频信息表tb_video_info

idvideo_idauthortagdurationrelease_time
12001901旅游302020-01-01 07:00:00
22002901旅游602021-01-01 07:00:00
32003902影视902020-01-01 07:00:00
42004902美女902020-01-01 08:00:00

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

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

输出示例：

示例数据的输出结果如下

tagdtsum_like_cnt_7dmax_retweet_cnt_7d
旅游2021-10-0152
旅游2021-10-0253
旅游2021-10-0363

解释：

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

tagdtlike_cntretweet_cnt
旅游2021-09-2512
旅游2021-09-2601
旅游2021-09-2710
旅游2021-09-2801
旅游2021-09-2901
旅游2021-09-3011
旅游2021-10-0121
旅游2021-10-0213
旅游2021-10-0310

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

with t1 as(
select tag,date_format(start_time,'%Y-%m-%d') dt,
sum(sum(if_like)) over (partition by tag order by date_format(start_time,'%Y-%m-%d') rows 6 preceding ) sum_like_cnt_7d,
max(sum(if_retweet)) over (partition by tag order by date_format(start_time,'%Y-%m-%d')  rows 6 preceding) max_retweet_cnt_7d
from tb_user_video_log a
left join tb_video_info b on a.video_id = b.video_id
WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
group by dt,tag)
select * from t1 where dt between '2021-10-01' and '2021-10-03' order by tag desc ,dt asc;

with as

with as 也叫子查询，用来定义一个sql片段，且该片段会被整个sql语句反复使用很多次，这个sql片段就相当于是一个公用临时表

如：with tmp as （select * from B）

​ select * from tmp

先执行select * from B拿到一个结果，将这个结果记录为tmp，再执行 select * from tmp

窗口函数

函数名称([字段名]) OVER([PARTITION BY <分组字段>][ORDER BY <排序字段> [DESC]] [<FRAME字句>])

partition by字句：按照指定字段进行分区，两个分区由边界分割，即所谓的"窗口"。如果没有指定，那么它把整个结果集作为一个分组。窗口函数在不同的分区内分别执行，在跨越分区边界时重新初始化。
order by子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和PARTITION BY子句配合使用，也可以单独使用。默认为升序排列。
FRAME子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。
ROWS语法：
ROWS BETWEEN (expr 为指定数值)
UNBOUNDED PRECEDING边界是分区中的第一行
PRECEDING边界是当前行减去expr的值
FOLLOWING边界是当前行加上expr的值
CURRENT ROW边界是当前行
UNBOUNDED FOLLOWING边界是分区中的最后一行

默认：ROWS BETWEEN UNBOUNDED PRECENDING AND CURRENT ROW
在这里插入图片描述

逗×
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
牛客SQL刷题 Day-07
12-14
目录 1、获取select * from employees对应的执行计划 2、将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分 3、创建一个actor表，包含如下列信息 4、对于表actor批量插入如下...
牛客SQL题解 – 从titles表获取按照title进行分组
12-14
题目描述 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。 CREATE TABLE IF NOT EXISTS “titles” ( `emp_no` int(11) NOT NULL, `title` varchar(50) NOT NULL, ...
牛客网sql自刷题
weixin_44883563的博客
07-23 274
初级数据分析师的成长之路
牛客SQL1~23
weixin_45155046的博客
07-28 83
牛客SQL1~23 --SQL1 select * from employees order by hire_date desc limit 1; --SQL2 select * from employees order by hire_date desc limit 2,1; --SQL3 select s.*, d.dept_no from dept_manager d, salaries s where s.emp_no = d.emp_no order by s.emp_no asc; --SQL
牛客SQL28~29
weixin_45155046的博客
08-08 118
SQL28 查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部 --SQL28 select t2.name, count(t1.film_id) from film t1 join category t2 join film_category t3 on t1.film_id
牛客sql
qq_35599414的博客
01-26 702
一、查找最晚入职员工的所有信息 描述 有一个员工employees表简况如下: 请你查找employees里最晚入职员工的所有信息，以上例子输出如下: 最优解法 select * from employees where hire_date = (select max(hire_date) from employees); 当然会有order by排序后，用limit获取第一个元素，但这必须要在没有相同数据的情况下才可以 学习：select 后要查询出聚合函数+字段..
牛客SQL记录
南风知我意
02-07 264
牛客SQL刷题记录
牛客SQL题答案（持续更新）
qq_45580637的博客
06-26 724
牛客较难、困难题自己的解法，如有错误，欢迎指正，大家一起进步！
牛客SQL练习第52题
双人与余的博客
09-07 125
题目描述 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列 CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date NO
牛客sql练习 - 60
theodorewy的博客
06-23 90
描述 求salary的累计和running_total CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`)); 考点 把sum聚合函数作为窗口函数使用 SELECT emp_no, salary SUM(salary
牛客SQL练习（一）
ljylc128的博客
05-05 116
SQL1 查找最晚入职员工的所有信息 语句 #方法一 select * from employees where hire_date=(select MAX(hire_date) from employees) limit 1 #方法二 select * from employees order by hire_date desc limit 1 知识点 order by 排序 desc desc （descend）降序排列数据：select * from 表 order by 字段 des
牛客sql练习 - 69
theodorewy的博客
07-03 120
描述 牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数 drop table if exists login; CREATE TABLE `login` ( `id` int(4) NOT NULL, `user_id` int(4) NOT NULL, `client_id` int(4) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`)); 考点 分组计数除了用count group by也可以用 sum(case ) # 对满足
【牛客SQL】01查找最晚入职员工的所有信息
12-14
查找最晚入职员工的所有信息 文章目录查找最晚入职员工的所有信息
牛客网站数据库SQL实战题目
05-17
题目涵盖了广泛的SQL知识点，包括基本查询、复杂查询、聚合函数、子查询、联接（JOIN）、视图、存储过程和触发器等。 通过多样的题目类型，用户可以全面掌握SQL的不同应用场景。 实战性： 题目设计紧密结合实际业务...
父子级分类统计分类下数量sql
pscool的博客
05-19 66
【代码】父子级分类统计分类下数量sql。
小白也会SQL：大模型改变交互方式（中）
鲁班模锤
05-17 746
在人工智能与自然语言处理交汇点，有一种技术正悄然改变与数据交互的方式——将日常语言转化为精准SQL查询。这一“text-to-sql”转换任务，使非专业人士也能轻松驾驭复杂的数据库操作，极大地拓宽了数据应用的边界。
idea创建springboot项目报错:Initialization failed for ‘https://start.spring.io‘
爱吃Java的羊的博客
05-18 129
>勾选Automatic proxy configuration URL: 复选框，并在输入框中录入。->勾选Auto-detect proxy settings复选框。->点击Appearance & Behavior菜单。idea创建springboot项目报错。->点击HTTP Proxy菜单。打开Settings界面。->点击OK报错退出。
写SQL的心得
weixin_64842400的博客
05-11 620
接着，系统按照 GROUP BY 子句中的指定字段分组，并对每个分组进行计算，生成虚拟的分组结果表。执行顺序 ：from > on > where > group by > having > select > distinct > order by > top。4、聚合函数本质：对指定的列进行聚合，如果我们用了group by，我们可以对每个分组应用内聚合函数。在分组内部，聚合函数会自动处理所有重复的行。具体来说，在执行 SELECT 子句时，系统先计算 SELECT 中的列表达式和函数等，然后。
公用表表达式（CTE）详解：针对 MySQL 和 SQL Server 数据库
最新发布
nbsaas-boot基于Request-Response的企业级快速开发框架
05-21 323
CTE 是一个命名的临时结果集，它在查询的执行范围内有效。CTE 的定义在WITH子句中，紧随其后的是查询主体。非递归 CTE：用于定义一次性计算结果的临时表。递归 CTE：用于处理递归查询，如处理层次结构数据（例如组织结构图）。
找到昵称以"牛客"+纯数字+"号"或者纯数字组成的用户对于字母c开头的试卷类别（如C,C++,c#等）的已完成的试卷ID和平均得分。写出sqlserver中的查询语句
07-14
你可以使用 SQL Server 的 `LIKE` 和 `PATINDEX` 函数结合起来实现这个查询。下面是一个示例的查询语句： ```sql SELECT p.paper_id, AVG(s.score) AS average_score FROM papers p JOIN scores s ON p.paper_id = ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值