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

/*select date_sub(max(date(end_time)),interval 29 day)
from tb_user_video_log
where if_retweet = 1
*/
select tag
        ,sum(if_retweet) AS retweet_cnt
        ,round(sum(if_retweet) / count(*),3) AS retweet_rate
from tb_user_video_log tl
inner join tb_video_info ti on tl.video_id = ti.video_id
/*where datediff(date((select max(start_time) from tb_user_video_log where if_retweet=1)),date(start_time))<=29*/
where start_time >= (select date_sub(date(max(start_time)),interval 29 day) from tb_user_video_log where if_retweet=1)
group by tag
order by retweet_cnt desc

mysql中select date('2021-10-31 21:02:20') -30 AS new_date的结果是¹：

| new_date || 1991-10-31 |

这是因为mysql会把date('2021-10-31 21:02:20')转换为一个整数，然后减去30，再转换回日期。如果想要从日期中减去30天，可以使用DATE_SUB函数²³，例如：

SELECT DATE_SUB (date('2021-10-31 21:02:20'), INTERVAL 30 DAY) AS new_date

这样的结果是：

| new_date || 2021-10-01 |