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

SELECT tv.tag, SUM(IF(tu.if_retweet = 1, 1, 0)) AS retweet_cnt, ROUND(SUM(IF(tu.if_retweet = 1, 1, 0)) / COUNT(tu.start_time), 3) AS retweet_rate
FROM tb_user_video_log AS tu
LEFT OUTER JOIN tb_video_info AS tv
ON tu.video_id = tv.video_id
WHERE DATE(start_time) > DATE_SUB((SELECT DATE(MAX(tu.start_time)) FROM tb_user_video_log AS tu ), INTERVAL 30 DAY) # 筛选近一个月有用户互动的播放记录（整体视频最近的一个月）
GROUP BY tv.tag
ORDER BY retweet_rate DESC; # 进行表联结