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

1. 计算每个视频的完播率，点赞数，评论数，转发数，新鲜度,生成子表a

• 联结两张表：join using()
• 完播率：判断时间差是否大于等于视频时长，if(timestampdiff(second,end_time,star_time)>=duration,1,0) sum(if(timestampdiff(second,star_time,end_time)>=duration,1,0))/count(*) as like_rate
• 点赞数：sum(if_like) as like_cnt
• 评论数：count(comment_id) as comment_cnt
• 转发数：sum(if_retweet) as retweet_cnt
• 新鲜度：
  • 新鲜度=1/(最近无播放天数+1)
  • 最近无播放天数***（卡在这里了）***
    • 现在的日期-最后一次播放的日期
      • 现在的日期：表中最大的日期
      • 最后一次播放的日期，按照video_id分组后，每组最大的日期
    • 或者是现在的日期-发布日期？(一次都没有播放过？)
    • if(start_time is null, datediff(max(date(start_time)),date(release_time)), datediff((select cur_day from t),max(date(start_time)) )
• 条件：最近一个月，datediff(表的最大日期，发布日期)< 30
• 分组：video_id

with t as (
select max(date(start_time)) as cur_day
from tb_user_video_log
)   
select 
    video_id,
    sum(if(timestampdiff(second,star_time,end_time)>=duration,1,0))/count(*) as like_rate,
    sum(if_like) as like_cnt,
    count(comment_id) as comment_cnt,
    sum(if_retweet) as retweet_cnt,
    1/(if(start_time is null,
             datediff(max(date(start_time)),date(release_time)),
             datediff((select cur_day from t),max(date(start_time))
            )+1) as fre
from tb_user_video_log 
left join tb_video_info using(video_id)
where datediff(select cur_day from t,release_time)<30
group by video_id

2. 从子表中计算所需
   • 条件：无
   • 不用分组
   • 排序 ：
   • limit 3

select 
    video_id,
    (100*like_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt) as hot_index
from a
order by hot_index desc 
limit 3

3. 代入

with t as (
select max(date(start_time)) as cur_day
from tb_user_video_log
)      
       
select 
    video_id,
    (100*like_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt)*fre as hot_index
from(
    select 
        video_id,
        sum(if(timestampdiff(second,star_time,end_time)>=duration,1,0))/count(*) as like_rate,
        sum(if_like) as like_cnt,
        count(comment_id) as comment_cnt,
        sum(if_retweet) as retweet_cnt,
        1/(if(start_time is null,
                 datediff(max(date(start_time)),date(release_time)),
                 datediff((select cur_day from t),max(date(start_time)))+1) as fre
    from tb_user_video_log 
    left join tb_video_info using(video_id)
    where datediff(select cur_day from t,release_time)<30
    group by video_id
)a
order by hot_index desc 
limit 3

有人帮忙看下这个吗，不知道错在哪里