一、明确需求：

输出平均播放进度>60%的类别

二、需求分解

根据题例可知，计算互动表video_log每条记录的观看时长，联结信息表video_info获得每条记录对应的tag视频类别。 alt

三、实现代码

with 
#对互动表和信息表进行去重处理
a as 
(select distinct * from tb_user_video_log)
, b as
(select distinct * from tb_video_info)

# 此处唯一要注意的是观看时间的计算要用时间函数timestampdiff，不能(end_time-start_time)直接相减
select tag
    ,concat(round((sum(play_progress)/count(distinct uid)) *100,2),'%') avg_play_progress
from 
    (select a.uid,b.tag
        ,if(timestampdiff(second,a.start_time,a.end_time) > b.duration,1
                , timestampdiff(second,a.start_time,a.end_time) / b.duration) play_progress
    from  a left join b on a.video_id = b.video_id
    )x
group by tag 
having avg(play_progress) > 0.6
order by avg_play_progress desc

四、拓展知识

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

  • unit：year,month,day,week,hour,minute,second
  • datetime_expr1:start_time
  • datetime_expr2:endtime 链接官方文档
  • 时间格式datetime之间相减的是类似个十百千格式的个数相减，不会进行分秒的换算。