题解 | #每类视频近一个月的转发量/率#

关键点在于 有互动的最近一个月，是指tb_user_video_log表中最大的时间

观察数据看到由转发为1，没有转发为0，用SUM()计算出有转发的量，用COUNT()计算总量

select 
    tv.tag
    ,sum(if_retweet) reweet_cnt
    ,round(sum(if_retweet) / count(if_retweet),3) retweet_rate
from tb_user_video_log tu
left join tb_video_info tv
on tu.video_id = tv.video_id
where TIMESTAMPDIFF(day,tu.start_time,(select max(start_time) max_date from tb_user_video_log)) <= 29
group by tv.tag
order by retweet_rate desc