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

【解题思路】

1. 最近一个月发布的视频
1. datediff(max(end_time),release_time)<=29
2. 最近无播放天数
1. 按视频id分组，max(end_time)找出最近播出时间
2. datediff(视频最近播出时间，最近一个视频播放时间)算出最近无播放天数
3. 视频完播率
1. if(播放结束时间-开始时间大于等于视频时长,1,0) 记为完播
2. 按视频分组，count（start_time）计算播放次数
3. sum（完播次数）/播放次数=视频完播率
4. 其它以此类推

select
  video_id,
  round((100*(sum(if_finish)/max(playnum))+5*max(likenum)+3*max(commentnum)+2*max(retweetnum))*(1/(datediff((select max(date(end_time))
  from tb_user_video_log),max(lastdate))+1)),0) as hot_index
from
(
    select
      tl.video_id,
      if(timestampdiff(second,start_time,end_time)>=ti.duration,1,0) as if_finish,
      max(date(end_time)) over(
        partition by video_id
        order by
          end_time
      ) as lastdate,
      count(start_time) over(partition by video_id order by end_time) as playnum,
      sum(if_like)over(partition by video_id order by end_time) as likenum,
      sum(if(comment_id>0,1,0)) over(partition by video_id order by end_time) as commentnum,
      sum(if_retweet)over(partition by video_id order by end_time) as retweetnum
    from
      tb_user_video_log tl
      join tb_video_info ti using(video_id)
    where
      datediff(
        (
          select
            max(date(end_time))
          from
            tb_user_video_log
        ),
        date(release_time)
      ) <= 29
  ) t1
  group by video_id
  order by hot_index desc
  limit 3

select t1.video_id
,round(
((sum(case when timestampdiff(second,start_time,end_time)>=duration then 1 else 0 end ))/count(*)*100
+sum(if_like)*5
+count(comment_id)*3
+sum(if_retweet)*2)
*
1/(DATEDIFF((select date(max(end_time)) from tb_user_video_log),date(max(end_time)))+1)
) as hot_index
from tb_user_video_log t1,tb_video_info t2
where t1.video_id=t2.video_id
and DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)),DATE(release_time)) <= 29
group by t1.video_id
order by hot_index desc
limiT 3