题解 | #平均播放进度大于60%的视频类别#

with q0 as (
SELECT
tag,duration,
CASE WHEN timestampdiff(second,start_time,end_time)>duration THEN duration 
ELSE  timestampdiff(second,start_time,end_time) END as t_duration
FROM tb_user_video_log t
LEFT JOIN tb_video_info i
ON t.video_id = i.video_id
)

SELECT tag,
concat(round(avg(t_duration/duration)*100,2),'%')avg_play_progress

FROM q0 
GROUP BY tag
HAVING avg(t_duration/duration)>0.6
ORDER BY 2 DESC

.1. 关联表，先求出实际播放时长 2. 再group by 求解 3. 筛选 可以用一个查询解决，但是代码太长，麻烦