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

# 转发率＝转发量÷播放量
# where子句里不能使用聚合函数
# date_sub(date,interval value unit) 可求出日期减去value unit单位的日期数
select
	tag,
	sum(if(if_retweet = 1,1,0)) as retweet_cut,
	round(sum(if(if_retweet = 1,1,0)) / count(*),3) as retweet_rate
from
	tb_user_video_log log
join
	tb_video_info info
on 
	log.video_id = info.video_id
where
	date(start_time) > 
	(select date_sub(max(date(start_time)),interval 30 day)
	from tb_user_video_log)
group by
	1
order by
	3 desc