MySQL中GROUP BY分组后如何取每个分组内最近3条记录计算平均值

实现该需求的前提是favorite_speed表存在记录观看时间的字段，以下方案统一假设该字段名为watch_time，请根据实际字段名替换。
另外你原有SQL中按videos.topic_id分组存在逻辑问题：如果多个主题(topic)归属同一门课程(course)，会把同一课程的统计结果拆分到不同主题下，以下方案已修正为按course_id分组，匹配你按课程统计的需求。

方案1：MySQL 8.0及以上版本（支持窗口函数，推荐） #

通过窗口函数实现分组排序取TopN，性能和可读性更高：

SELECT 
    user_id,
    course_id,
    AVG(favorite_speed) AS avg_recent_3_speed
FROM (
    SELECT 
        fs.user_id,
        t.course_id,
        fs.favorite_speed,
        -- 按用户+课程分组，观看时间倒序编号，最近的记录编号为1
        ROW_NUMBER() OVER (PARTITION BY fs.user_id, t.course_id ORDER BY fs.watch_time DESC) AS rn
    FROM favorite_speed fs
    JOIN videos v ON fs.video_id = v.id
    JOIN topics t ON v.topic_id = t.id
) temp
WHERE rn <= 3
GROUP BY user_id, course_id
ORDER BY avg_recent_3_speed DESC;

方案2：MySQL 5.x版本（无窗口函数兼容方案） #

通过关联子查询实现分组排序逻辑，兼容低版本MySQL：

SELECT 
    fs.user_id,
    t.course_id,
    AVG(fs.favorite_speed) AS avg_recent_3_speed
FROM favorite_speed fs
JOIN videos v ON fs.video_id = v.id
JOIN topics t ON v.topic_id = t.id
WHERE (
    SELECT COUNT(*) 
    FROM favorite_speed fs2
    JOIN videos v2 ON fs2.video_id = v2.id
    JOIN topics t2 ON v2.topic_id = t2.id
    -- 统计同用户同课程下，比当前记录观看时间更晚的记录数
    WHERE fs2.user_id = fs.user_id 
      AND t2.course_id = t.course_id 
      AND fs2.watch_time >= fs.watch_time
) <= 3 -- 筛选出最近3条记录
GROUP BY fs.user_id, t.course_id
ORDER BY avg_recent_3_speed DESC;

内容的提问来源于stack exchange，提问作者Eghbal Shirasb