select t1.tag, sum(t.if_retweet) as retweet_cut,-- 转发量 round(sum(t.if_retweet)/count(t.video_id),3) as retweet_rate from tb_video_info t1 left join tb_user_video_log t on t.video_id=t1.video_id where date(start_time)>(select date_sub(MAX(start_time),interval 30 day) from tb_user_video_log) group by t1.tag order by retweet_rate desc;
解题思路:
转发量:sum(if_reweet)
转发率:转发量/播放量。也就是sum(if_reweet)/count(video_id)。结果保留3位小数,就是用ROUND(转发率,3)即可
最近30天:
写法①、date_sub函数(时间向前推移函数)
(select date_sub(MAX(start_time),interval 30 day) from tb_user_video_log ------这一步的意思是以最新的播放日期为准,向前推移30天。
写法②、timestampdiff函数(时间差函数)
timestampdiff(date,t.start_time,(select max(t.start_time) from tb_user_video_log)<30