题解 | #(常规思路)平均播放进度大于60%的视频类别#

select
c.tag,
concat(cast(avg(c.jindu) as decimal(10,2)),'%') as avg_play_progress
from
(select
b.tag as tag,
case when (( unix_timestamp(a.end_time) - unix_timestamp(a.start_time) ) > b.duration) then 100
else cast(((( unix_timestamp(a.end_time) - unix_timestamp(a.start_time) ) / b.duration) * 100) as decimal(10,2)) end as jindu
from tb_user_video_log a
left join tb_video_info b
on a.video_id=b.video_id)c
group by c.tag
having cast(avg(c.jindu) as decimal(10,2)) > 60.00
order by avg_play_progress desc

1 mysql时间跨度怎么相互减去 ，使用unix_timetamp()进行datetime格式转换成时间戳
unix_timestamp(a.end_time) - unix_timestamp(a.start_time)
2 百分比怎么算，配合group by avg 获取平均值，再用concat进行拼接百分符号

1 第一个子查询，先获取需要 tag列，以及需要做计算的列即进度列
2 第二个子查询配合group by 使用 avg聚合函数+concat进行计算