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

select t1.tag,sum(t1.if_retweet) as retweet_cnt, round(sum(t1.if_retweet)/count(*),3) as retweet_rate 
# 提取整体最大的日期作为最近的日期
from (
select u1.uid,u1.video_id,u1.start_time,u1.if_retweet,v1.tag, 
(select max(u2.start_time) from tb_user_video_log as u2) as max_date 
from  
tb_user_video_log as u1 left join tb_video_info as v1 on u1.video_id=v1.video_id ) t1 
# 算最近30天的数据
where datediff(t1.max_date,t1.start_time) <=29
group by t1.tag order by sum(t1.if_retweet)/count(*) desc