编程代码
select b.tag
,sum(a.if_retweet) as retweet_cut
,round(sum(a.if_retweet)/count(a.id),3) as retweet_rate
from tb_user_video_log as a
join tb_video_info as b
using (video_id)
where datediff((select max(end_time) from tb_user_video_log), a.end_time) <= 29
group by b.tag
order by retweet_rate desc;
;
知识点总结:
1、datediff函数的语法:
datediff(end_time, begin_time);
2、datediff函数与timestampdiff函数的区别
datediff函数:end_time在前, begin_time在后且只能计算日期差,计算原则: 只截取日期部分作差
timestampdiff函数:begin_time在前,end_time在后,有时间单位,可计算各种时间差,且计算原则:时间差不到24小时不算一天,不到60分钟不算一个小时,不到60秒不算一分钟。
timestampdiff函数的语法:
timestampdiff(unit, begin_time, end_time);
unit有效单位:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
3、注意:
datediff((select max(end_time) from tb_user_video_log), a.end_time) <=29
直接用select提取的单个值时,外部要带括号;
包含今天在内的近30天,是小于等于29;
获取当前时间:select now()