组中限制在mysql中

Question

我有一个关于mysql的问题。假设我有一张桌子，如下所示。数据库img

在这个表中，我有三个参数:用户、视频和favorite_speed。

现在的问题是，我想得到每个用户在每门课程中看到的最后3段视频的平均速度。要将视频连接到课程，我们必须在课程与主题和视频之间有两个连接。

例如，使用下面的代码，我得到了课程中每个用户的平均速度。但问题是，我如何获得最后3段视频的平均值？

SELECT avg(favorite_speed),favorite_speed.user_id,topics.course_id 
FROM favorite_speed,videos,topics 
WHERE favorite_speed.video_id=videos.id AND topics.id=videos.topic_id 
GROUP BY user_id,videos.topic_id  
ORDER BY `avg(favorite_speed)`  DESC

Answer 1

现在的问题是，我想得到每个用户在每门课程中看到的最后3段视频的平均速度。

您可以使用窗口函数来识别最近的三个视频。假设它是基于favorite_speed.id

SELECT fs.user_id, v.course_id, avg(fs.favorite_speed), 
FROM (SELECT fs.user_id, fs.favorite_speed, v.course_id,
             ROW_NUMBER() OVER (PARTITON BY fs.user_id, v.course_id ORDER BY fs.id DESC) as seqnum
      FROM favorite_speed fs JOIN
           videos v 
           ON fs.video_id = v.id 
     ) fsv
WHERE seqnum <= 3
GROUP BY fs.user_id, v.course_id; 
ORDER BY avg(fs.favorite_speed) DESC;

注意，不需要topics表，因为信息在videos表中。