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

# 查询用户最近活跃的日期，计算目标区间
# 分组计算每类的转发量和播放量，最终计算出转发率

# 临时表，查询出用户最近活跃的日期，只要有记录就是播放，就是活跃
# 往前找30天，使用DATE_SUB函数
WITH temp_0 AS(
SELECT MAX(end_time) last_time, DATE_SUB(MAX(end_time), INTERVAL 30 DAY) first_time
FROM tb_user_video_log
)

# 查询类型，转发总数，转发率
SELECT tag, SUM(if_retweet) re_count, ROUND(SUM(if_retweet) / COUNT(*), 3) retweet_rate
FROM tb_video_info 
JOIN tb_user_video_log USING(video_id)
# 限制日期范围，这里使用了两次子查询，有没有更优雅的方式
WHERE end_time BETWEEN (SELECT first_time FROM temp_0) AND (SELECT last_time FROM temp_0)
GROUP BY tag
ORDER BY retweet_rate DESC

踩的坑：1.不会日期减去指定天数，误用了timediff

2.把最大的时间开始写作了firsttime，往前三十天的写成了lasttime，这样怎么比较区间也无法匹配到记录

思路见注释。

新知识：主要是日期函数

MySQL

使用 DATE_SUB 函数：

SELECT DATE_SUB('2024-01-23', INTERVAL 30 DAY) AS DateBefore30Days;

这里 DATE_SUB 函数从 '2024-01-23' 减去 30 天。

在 MySQL 中，TIMEDIFF 的基本语法是：

TIMEDIFF(time1, time2)

这里 time1 和 time2 是时间值，函数返回 time1 和 time2 之间的差异。返回的时间格式通常是 HH:MM:SS，或者如果差异超过 24 小时，则是 -HH:MM:SS。