问题
问题解析
问题分解:
- 关联用户-视频互动记录和短视频信息表:LEFT JOIN tb_video_info USING(video_id);
- 按视频类别分组:GROUP BY tag
- 计算每个类别的平均播放进度:
- 播放进度=播放时长÷视频时长*100%
- 播放时长=TIMESTAMPDIFF(SECOND, start_time, end_time);特殊情况:播放时长大于视频时长时,播放进度为100%(加个IF判断或者用case)
- 平均进度=AVG(每个进度)
- 结果保留2位小数:ROUND(x, 2)
- 百分比格式化:CONCAT(x, '%')
- 播放进度计算
CONCAT(
ROUND(
AVG(
case when TIMESTAMPDIFF(SECOND,start_time,end_time) > duration then 1 else TIMESTAMPDIFF(SECOND,start_time,end_time)/duration
end) *100,2),'%)
完整代码:
select t2.tag,
concat(
round(
avg(
case when TIMESTAMPDIFF(SECOND, t1.start_time, t1.end_time)>=t2.
duration then 1 else TIMESTAMPDIFF(SECOND, t1.start_time, t1.
end_time)/t2.duration end) *100,2),'%') as avg_play_progress
from tb_user_video_log t1
left join tb_video_info t2 on t1.video_id=t2.video_id
group by t2.tag
having round(
avg(
case when TIMESTAMPDIFF(SECOND, t1.start_time, t1.end_time)>t2.
duration then 1 else TIMESTAMPDIFF(SECOND, t1.start_time, t1.
end_time)/t2.duration end)*100,2)>60
order by avg_play_progress desc