转发量/率计算

• 注意点
  • 定义有用户互动的最近一个月，当前最近播放的时间的近一个月，即先查询所有记录中end_time的最大值，然后计算日期差datediff小于30（这里的日期差是包括了当天，因此不是小于等于）

select 
    tvi.tag,
    sum(tuvl.if_retweet) as retweet_cnt,
    round(sum(tuvl.if_retweet) / count(tuvl.video_id), 3) as retweet_rate
from (
    select 
        video_id,
        if_retweet,
        datediff(max(end_time) over(), end_time) as diff
    from tb_user_video_log
) as tuvl
    inner join tb_video_info as tvi on (tuvl.video_id = tvi.video_id and tuvl.diff < 30)
group by tvi.tag
order by retweet_rate desc;