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

SELECT t0.video_id
     , ROUND((100 * AVG(IF(TIMESTAMPDIFF(SECOND, start_time, end_time) >= t1.duration, 1, 0)) +
              5 * SUM(t0.if_like) +
              3 * COUNT(t0.comment_id) +
              2 * SUM(t0.if_retweet)) /
             (DATEDIFF((SELECT MAX(end_time) FROM tb_user_video_log), MAX(end_time)) + 1), 0) AS hot_index
FROM tb_user_video_log t0
         LEFT JOIN tb_video_info t1
                   ON t0.video_id = t1.video_id
WHERE DATEDIFF((SELECT MAX(end_time) FROM tb_user_video_log), t1.release_time) <= 29
GROUP BY t0.video_id
ORDER BY hot_index DESC
LIMIT 3;

with 
t as #信息总表
(
select 
    video_id
    , if_like
    , if_retweet
    , case when timestampdiff(second, start_time, end_time)>=duration then 1 else 0 end as if_finish
    , comment_id
from tb_user_video_log  inner join tb_video_info using(video_id)
where datediff((select max(end_time) from tb_user_video_log), end_time)<=29
    and datediff((select max(end_time) from tb_user_video_log), release_time)<=29
),
t1 as #分组统计完播率、点赞数、评论数
(
select 
    video_id
    , avg(if_finish) as fsh_r
    , sum(if_like) as lk_n
    , count(comment_id) as cm_n
    , sum(if_retweet) as rt_n
    
from t
group by video_id
),
t2 as #最近无播放天数
(
select 
    video_id
    , datediff((select max(end_time) from tb_user_video_log), max(end_time)) as nv
from tb_user_video_log  inner join tb_video_info using(video_id)
group by video_id
)

select 
    video_id
    , round((100*fsh_r + 5*lk_n + 3*cm_n + 2*rt_n) / (nv+1)) as hot_index
from t1 join t2 using(video_id)
order by hot_index desc
limit 3
;

SELECT 
T1.video_id,
ROUND((100*T1.完播率+5*T1.点赞数+3*T1.评论数+2*T1.转发数)/(datediff(T2.最大视频时间,T1.单视频最近时间)+1),0) AS hot_index
FROM
    (
        -- 取出进一个月发布的视频的：点赞数，评论数，转发数，完播率，单视频最近播放时间
        select 
        t1.video_id,
        sum(t1.if_like) as 点赞数,
        count(t1.comment_id) as 评论数,
        sum(t1.if_retweet) as 转发数,
        max(date(t1.end_time)) 单视频最近时间,
        avg(if(TIMESTAMPDIFF(SECOND,t1.start_time,t1.end_time)>=t2.duration,1,0)) as 完播率
        from tb_user_video_log as t1
        left join tb_video_info as t2
        on t1.video_id = t2.video_id
        where TIMESTAMPDIFF(day,t1.end_time,(select max(release_time) from tb_video_info)) <= 29
        group by t1.video_id
    ) T1

    left join
    (   -- 匹配全部视频的最近播放时间
        select
        video_id,
        date(end_time) as 单视频最近时间,
        max(date(end_time)) over() as 最大视频时间
        from tb_user_video_log 
    ) T2
    on T1.video_id = T2.video_id and T1.单视频最近时间= T2.单视频最近时间
ORDER BY hot_index DESC limit 3

select 
    video_id,
    round((100*com_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt)/fresh_index,0) hot_index
from (
    select 
        t1.video_id,
        sum(if(timestampdiff(second,t1.start_time,t1.end_time) >= t2.duration,1,0))/  count(t1.video_id) com_rate,
        sum(if_like) like_cnt,
        count(comment_id) comment_cnt,
        sum(if_retweet) retweet_cnt,
        if(count(end_time)=0,timestampdiff(day,release_time,(select max(end_time) from tb_user_video_log)),timestampdiff(day,max(end_time),(select max(end_time) from tb_user_video_log)))+1 fresh_index
    from tb_user_video_log t1
        left join tb_video_info t2
        on t1.video_id = t2.video_id
    where timestampdiff(day,t2.release_time,(select max(end_time) from tb_user_video_log)) <= 29
    group by t1.video_id) table1
order by hot_index desc
limit 0,3

with tt as(
    select 
        t1.video_id,
        sum(t1.if_like) as count_like,
        count(t1.comment_id) as count_comment_id,
        sum(t1.if_retweet) as count_retweet	,
        count(if(timestampdiff(second,t1.start_time,t1.end_time)>=t2.duration,1,null))/count(t1.start_time) as wanbo,
        datediff((select max(end_time) from tb_user_video_log),max(end_time)) as no_day
    from tb_user_video_log t1
        left join tb_video_info t2
        using(video_id)
    where datediff((select  max(end_time) from tb_user_video_log) ,end_time) <=29
        and t2.release_time>date_sub((select max(end_time) from tb_user_video_log) ,interval 29 day)
    group by t1.video_id)

select 
    tt.video_id,
   round((tt.wanbo*100+tt.count_like*5+tt.count_comment_id*3+count_retweet*2)*(1/(no_day+1)),0) as hot_index
from tt
order by hot_index desc
limit 3;

select t0.video_id,
round((100*complete_rate+5*likes+3*comments+2*retweets)*(1/(no_play+1)),0) as hot_index
from
(select t1.video_id,
count(if(duration - (select
case
when minute(end_time) = minute(start_time) then second(end_time) - second(start_time)
else second(end_time)+60-second(start_time)
end)<=0,1,null)) / count(t1.video_id) as complete_rate,
sum(if_like) as likes,
count(comment_id) as comments,
sum(if_retweet) as retweets,
datediff((select max(end_time) from tb_user_video_log), max(end_time)) as no_play
from tb_user_video_log as t1
join tb_video_info as t2
on t1.video_id = t2.video_id
where datediff((select max(end_time) from tb_user_video_log), t2.release_time) <= 29
group by t1.video_id) as t0
order by hot_index desc
limit 3

# 问题：找出近一个月发布的视频中热度最高的top3视频。

# 注：
# 热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；
# 新鲜度=1/(最近无播放天数+1)；
# 当前配置的参数a,b,c,d分别为100、5、3、2。
# 最近播放日期以end_time-结束观看时间为准，假设为T，则最近一个月按[T-29, T]闭区间统计。
# 结果中热度保留为整数，并按热度降序排序。
-- 注意：timestampdiff 与datediff 正好相反，参数大小方向；
-- 注意：最近90天，使用发布日期

with log as (
select t.*, max(date(end_time))over() as dt from tb_user_video_log t
)

select 
video_id,
round ((rate*100+5*like_num+3*comment_num+2*retweet_num)/(cnt+1),0) as hot_index
from (
select 
 t1.video_id,
 date(t1.end_time) as dt, -- 统计日期
 avg(if(timestampdiff(second,start_time,end_time)>=t2.duration,1,0)) over(partition by t1.video_id) as rate, -- 频完播率
 sum(if(if_like =1,1,0)) over(partition by t1.video_id) as like_num, -- 点赞数
 sum(if(comment_id is not null,1,0)) over(partition by t1.video_id) as comment_num, -- 评论数
 sum(if(if_retweet =1,1,0)) over(partition by t1.video_id) as retweet_num, -- 转发数
 row_number() over(partition by t1.video_id order by end_time) as rn , -- 排序
 min(datediff(dt,date(t1.end_time))) over(partition by t1.video_id ) as cnt -- 播放天数
from log t1 
inner join tb_video_info t2
on t1.video_id = t2.video_id
where datediff(dt,date(t2.release_time)) between 0 and 29
  -- where datediff(date((select max(end_time) from tb_user_video_log)), date(release_time)) <= 29
) tt 
where rn =1 
-- and video_id=2002
order by 2 desc
limit 3
;

select
    vi.video_id,
    round((100*avg(if(timestampdiff(second,start_time,end_time) >= duration,1,0)) + 
           5*sum(if_like) + 
           3*count(comment_id) + 
           2*sum(if_retweet)) / (datediff(max(date(max(end_time))) over(),date(max(end_time))) + 1)) hot_index
from tb_user_video_log uvl
join tb_video_info vi
on uvl.video_id = vi.video_id
where date(release_time) > (select date_sub(date(max(end_time)),interval 30 day) from tb_user_video_log)
group by vi.video_id
order by hot_index desc
limit 3;