前言
题目来源于牛客网sql企业题库第6题(困难):
近一个月发布的视频中热度最高的top3视频
问题:
代码如下:
1)需要筛选数据,按照近一个月内:
//作为where条件
datediff(date((select max(end_time)from tb_user_video_log)), date(t2.release_time))<=29
2)以video_id为group by条件,计算点赞数、转发数、评论数、视频完播率
//点赞数
sum(if_like) likes
//转发数
sum(if_retweet) retweets
//评论数
count(comment_id) comments
//视频完播率
avg(case when timestampdiff(second, start_time, end_time)>=duration then 1 else 0 end) play_rate
//最近无播放天数
datediff((select max(end_time) from tb_user_video_log), max(t1.end_time)) day_new
注意两个用法:
timestampdiff(second,start_time,end_time):返回两个datetime类型数据之间相差的秒数
datediff(a,b):a,b为sql语句时需要加括号
总代码如下:
//代码来源牛客题解区大佬,自己写的又臭又长
select
a.video_id,
round((100*play_rate + 5*likes + 3*comments + 2*retweets)*(1/(day_new+1)),0) hot_index
from
(select
t1.video_id,
avg(case when timestampdiff(second, start_time, end_time)>=duration then 1 else 0 end) play_rate,
sum(if_like) likes,
count(comment_id) comments,
sum(if_retweet) retweets,
datediff((select max(end_time) from tb_user_video_log), max(t1.end_time)) day_new
from
tb_user_video_log t1
join
tb_video_info t2
on
t1.video_id=t2.video_id
where
datediff(date((select max(end_time)from tb_user_video_log)), date(t2.release_time))<=29
group by
t1.video_id
) a
ORDER BY
hot_index DESC
LIMIT
3
easy ~~