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

# SELECT tb_user_video_log
# ## uid，video_id,start_time,end_time, if_follow,
# start_time, release_time, duration,tag,author,video_id

## 最近一个月：首先倒序排列出最后一个日期(使用窗口函数)：ORDER BY DATE_FORMAT(start_time,'%Y%m%d') LIMIT 1 AS last_time
## 转发率 = 转发量/播放量
## 播放量：有start_time即认为有1个播放量
## DATE_SUB(时间，INTERVAL n 时间单位)：返回减去n个时间单位后的日期
## DATE_SUB(last_time, INTERVAL 29 day)

SELECT tag,SUM(if_retweet) AS retweet_cut,
ROUND(SUM(if_retweet)/COUNT(if_retweet),3) AS retweet_rate
FROM tb_user_video_log a
LEFT JOIN tb_video_info b
ON a.video_id = b.video_id
WHERE DATE(start_time) >
(SELECT DATE_SUB(MAX(DATE(start_time)), INTERVAL 30 DAY) FROM tb_user_video_log)
GROUP BY tag
ORDER BY retweet_rate DESC
##