题解 | #近一个月发布的视频中热度最高的top3视频#

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

http://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff

解决以下两个难点，题目就毫无难度了~~~~~~

明确【最近日期】后，才能确定近一个月
计算【最近无播放天数】

首先，😃😃😃😃😃难点：确定数据筛选的时间段 -- 近一个月，究竟指何时间段？

【思路】求解近一个月，需要知道最近日期cur_day。最近日期如何定义？取最近播放日期，即end_time。

同时需知最近播放日期，是所有品类最近播放的日期max(end_time)，因此代码如下

select DATE_FORMAT(max(end_time),'%Y-%m-%d') from tb_user_video_log

或者

select date(max(end_time)) from tb_user_video_log

知道最近日期，则最近一个月迎刃而解，使用date_add()或date_sub()函数即可

SELECT DATE_ADD(DATE_FORMAT(max(end_time),'%Y-%m-%d'),INTERVAL -29 day)
from tb_user_video_log

其次，敲定热度计算公式里涉及的各指标如何计算

😃😃😃😃😃1、难点！！！！！【no_watch_day】最近无播放天数需要分情况计算

视频发布后无播放，最近无播放天数=最近日期-发布日期
视频发布后有播放，最近无播放天数=最近日期-最后播放日期

需要知道

【最近日期】-- 第一步，已经确定cur_day

【发布日期】-- release_time

【最后播放日期】-- 按照video_id分组后，可求max(end_time)

【日期之差】可用datediff(结束日期，开始日期)求得

因此，代码可使用case when分情况写得

select from
case 
when count(l.video_id)=0 then DATEDIFF((select max(end_time) from tb_user_video_log),i.release_time)
else DATEDIFF((select max(end_time) from tb_user_video_log),max(l.end_time)) 
end no_watch_day from tb_user_video_log l LEFT JOIN tb_video_info i on l.video_id=i.video_id
group by l.video_id

2、【watch_rate】视频完播率=完成播放次数/总播放次数的比例

【完成播放次数】定义为结束观看时间end_time与开始播放时间start_time的差>=视频时长时duration的次数，时间差值使用timestampdiff()函数

故，某视频完成播放的次数使用case when函数来计算

sum(case 
when TIMESTAMPDIFF(second,l.start_time,l.end_time)>=i.duration then 1 
else 0 end)

【总播放次数】

count(video_id)

因此，完播率为

sum(case 
when TIMESTAMPDIFF(second,l.start_time,l.end_time)>=i.duration then 1 
else 0 end)/count(l.video_id) watch_rate

3、点赞数、转发数、评论数用sum\count计算即可

综上，将思路进行拼接

select #完播率,#点赞数,#评论数,#转发数 from <合并表>

where 发布日期>最近一个月

<各指标代码>如下↓↓↓

SELECT l.video_id,
sum(case 
when TIMESTAMPDIFF(second,l.start_time,l.end_time)>=i.duration then 1 
else 0 end)/count(l.video_id) watch_rate,  #完播率
sum(l.if_like) like_cnt, # 点赞数
count(l.comment_id) comment_cnt, #评论数
sum(if_retweet) retweet_cnt, #转发数
case 
when count(l.video_id)=0 then DATEDIFF((select max(end_time) from tb_user_video_log),i.release_time)
else DATEDIFF((select max(end_time) from tb_user_video_log),max(l.end_time)) 
end no_watch_day #最近无播放天数
from tb_user_video_log l
LEFT JOIN tb_video_info i
on l.video_id=i.video_id
where i.release_time>=(SELECT DATE_ADD(DATE_FORMAT(max(end_time),'%Y-%m-%d'),INTERVAL -29 day) dt
from tb_user_video_log)
group by l.video_id

最后，将各指标带入公式计算出热度，并按要求排序，选出top3,使用limit 3

select t.video_id,
round((100*t.watch_rate+5*t.like_cnt+3*t.comment_cnt+2*t.retweet_cnt)/(1+t.no_watch_day),0) hot_index 
from
(
各指标代码
)t
ORDER BY hot_index DESC
limit 3;