select tag, sum(if_retweet) as retweet_cnt,
round(sum(if_retweet) / count(1), 3) as retweet_rate
from tb_user_video_log
join tb_video_info using(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;
子查询:这个子查询计算了
tb_user_video_log
表中start_time
列的最大日期,然后使用DATE_SUB
函数减去30天,得到一个日期阈值。这个阈值用于在外层查询中筛选出最近30天的数据。外层查询:外层查询首先将
tb_user_video_log
表和tb_video_info
表通过video_id
列进行连接。然后,它使用WHERE
子句和子查询的结果来筛选出start_time
大于最近30天的数据。分组和聚合:外层查询使用
GROUP BY tag
来按标签分组数据。对于每个标签,它使用SUM(if_retweet)
来计算转发次数(retweet_cnt
),其中if_retweet
是一个布尔类型的列,表示视频是否被转发。计算转发率:查询使用
ROUND
函数来将retweet_cnt
除以每个标签的总视频数(COUNT(1)
),然后将结果四舍五入到小数点后三位,得到转发率(retweet_rate
)。排序:最后,查询使用
ORDER BY retweet_rate DESC
来按转发率降序排序结果。整个查询的逻辑是先计算出每个标签在最近30天内的总转发次数,然后计算出每个标签的转发率,并按照这个率进行排序。