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

select tag,sum(if_retweet) retweet_cnt,round(sum(if_retweet)/count(*),3) retweet_rate from (
select tag,start_time,if_retweet
,max(start_time)over(order by tag) max_time
,date_sub(start_time,interval 29 day) pr_time
,max(date_sub(start_time,interval 29 day))over(order by tag) max_pr_time
from tb_user_video_log t1 
left join tb_video_info t2 
on t1.video_id = t2.video_id
) a
where start_time between max_pr_time and max_time
group by tag
order by retweet_rate desc;