SQL练习—某音短视频1（easy）

目录

1.各个视频的平均完播率

2. 平均播放进度大于60%的视频类别

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

---

1.各个视频的平均完播率

select video_id,round(sum(sign)/count(sign),3)as avg_comp_play_rate
from(
select uid,t.video_id ,
if(timestampdiff(second,start_time,end_time)>= duration,1,0) as sign 
-- 对完整播放的打上标签1，未完整播放为0，这样完播率=sum/count
from tb_user_video_log as t 
left join tb_video_info as v 
on t.video_id=v.video_id
-- 正常表连接
where year(start_time)=2021
)a 
group by video_id
order by avg_comp_play_rate desc 
-- 别忘记筛选条件

2. 平均播放进度大于60%的视频类别

select tag,avg_play_progress
from(
select tag,
concat(round(avg(play_progress)*100,2) ,'%')  as avg_play_progress
-- 计算平均播放进度   
from (
select u.video_id,tag,duration,
if(timestampdiff(second,start_time,end_time)>duration,1,timestampdiff(second,start_time,end_time)/duration) as play_progress
-- 如果播放时长>视频时长 记为1，否则记下播放占比
from tb_user_video_log as u 
left join tb_video_info as v 
on u.video_id=v.video_id
) t1 
group by tag 
order by avg_play_progress desc 
) t2
where avg_play_progress>60
-- 筛选条件

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

 

 

SELECT tag, SUM(if_retweet), ROUND(SUM(if_retweet)/ COUNT(if_retweet),3) retweet_rate
FROM tb_user_video_log u 
LEFT JOIN tb_video_info v 
ON u.video_id=v.video_id 
WHERE TIMESTAMPDIFF(DAY, start_time, (SELECT MAX(start_time) FROM tb_user_video_log)) < 30
-- 限制条件是近一个月内
GROUP BY tag
ORDER BY retweet_rate DESC;