SQL158 每类视频近一个月的转发量/率

SQL158 每类视频近一个月的转发量/率

查询分析

这个SQL查询的目的是分析最近30天内不同标签(tag)下视频的转发情况，主要关注转发率和转发次数。

1. 数据来源

• 从tb_user_video_log表获取用户视频互动日志
• 通过video_id关联tb_video_info表获取视频标签信息

2. 时间范围筛选

• 使用子查询动态确定最近30天的日期范围：
  • 首先获取日志表中最大的start_time日期
  • 然后向前推29天(共30天，包含起止日期)

3. 计算指标

• retweet_cnt: 每个标签下的总转发次数(通过SUM(if_retweet)计算)
• retweet_rate: 转发率，计算方式为转发次数除以总互动次数(COUNT(if_retweet))，并保留3位小数

4. 分组与排序

• 按tag分组计算各标签的转发数据
• 结果按retweet_rate降序排列，找出转发率最高的标签

5. 应用场景

这个查询适用于视频平台分析不同内容标签的传播效果，帮助运营团队了解哪些类型的内容更容易被用户转发，从而优化内容策略。

代码

SELECTtag,SUM(if_retweet) AS retweet_cnt,ROUND(SUM(if_retweet) / COUNT(if_retweet), 3) AS retweet_rate
FROMtb_user_video_logJOIN tb_video_info USING (video_id)
WHEREDATE(start_time) BETWEEN (SELECT DATE_SUB(DATE(MAX(start_time)), INTERVAL 29 DAY)FROM tb_user_video_log) AND (SELECT DATE(MAX(start_time))FROM tb_user_video_log)
GROUP BYtag
ORDER BYretweet_rate DESC