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

with
    temp as (
	    #根据最近的天数过滤数据
        select
            video_id,
            if_retweet
        from
            tb_user_video_log,
			# 求最近的天数
            (
                select
                    end_time max_end_time
                from
                    tb_user_video_log
                order by
                    end_time desc
                limit
                    1
            ) as max_time
        where
            end_time >= date_sub(max_end_time, interval 30 day)
            and end_time <= max_end_time
    )
#联表求结果
select
    tag,
    sum(if_retweet) retweet_cut,
    round(sum(if_retweet) / count(*), 3) retweet_cut
from
    temp
    join tb_video_info using (video_id)
group by
    tag
order by
    retweet_cut desc