第二道sql题参考答案 SELECT userid, -- 完整观看率 = 至少完整观看1次的视频数 / 总去重视频数 ROUND( SUM(has_complete) / COUNT(DISTINCT video_id) * 100, -- 分母为去重视频数 2 ) AS complete_rate FROM ( -- 子查询：先按用户+视频分组，判断该视频是否至少完整观看过1次 SELECT w.userid, w.video_id, -- 若该视频有1次以上完整观看，标记为1（否则0） MAX( CASE WHEN TIMESTAMPDIFF(SECOND, w.start_time, w.end_time) >= v.duration THEN 1 ELSE 0 END ) AS has_complete FROM watch_records w JOIN users u ON w.userid = u.userid JOIN videos v ON w.video_id = v.video_id WHERE u.gender = 'female' AND w.end_time > w.start_time GROUP BY w.userid, w.video_id -- 按用户+视频去重 ) AS t GROUP BY userid HAVING COUNT(DISTINCT video_id) > 50 -- 总去重视频数超50个 ORDER BY complete_rate DESC LIMIT 10;