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

SELECT 
b.tag,
sum(case when a.if_retweet =1 then 1 else 0 end) as retweet_cnt,
-- count(case when a.if_retweet=1 then 1 else NULL end) as cnt,
ROUND(sum(case when a.if_retweet =1 then 1 else 0 end) /count(*),3) retweet_rate

from tb_user_video_log a
left join tb_video_info b
on a.video_id=b.video_id
where  DATE_FORMAT(a.start_time,'%Y-%m-%d')>= 
(
		SELECT DATE_SUB(max(DATE_format(end_time,'%Y-%m-%d')),interval 29 DAY) 
		from tb_user_video_log
)

GROUP BY 1
order by 3 desc

1、注意count和SUM区别，SUM把满足条件加上，COUNT就是技数1,0都算，NULL不计算

2、DATE_SUB（日期，interval 29 DAY） DAY 大写

3、DATE_FORMAT(日期，'%y-%m-%d')