MySQL - 平均其他表中的最新列

Question

我有两个表:“servers”和“stats”

服务器有一个名为“id”的列，它会自动递增。stats 有一个名为“server”的列，它对应于服务器表中的一行，一个名为“time”的列表示它被添加的时间，还有一个名为“votes”的列，我想得到它的平均值。

我想获取所有服务器 (SELECT * FROM servers) 以及对应于每个服务器的最近 24 行的平均投票。我相信这是一个“每组最多 n 个”的问题。

这是我尝试做的，但它给了我总共 24 行，而不是每组 24 行:

SELECT servers.*,
       IFNULL(AVG(stats.votes), 0) AS avgvotes
FROM servers
LEFT OUTER JOIN
  (SELECT server,
          votes
   FROM stats
   GROUP BY server
   ORDER BY time DESC LIMIT 24) AS stats ON servers.id = stats.server
GROUP BY servers.id

就像我说的，我想获取每台服务器的 24 个最新行，而不是总共 24 个最新行。

Answer 1

感谢这个伟大的 post 。

alter table add index(server, time)
 set @num:=0, @server:='';
select servers.*, IFNULL(AVG(stats.votes), 0) AS avgvotes
from servers left outer join (
select server, 
       time,votes, 
       @num := if(@server = server, @num + 1, 1) as row_number, 
       @server:= server as dummy 
from stats force index(server) 
group by server, time 
having row_number < 25) as stats 
on servers.id = stats.server
group by servers.id

编辑1

我刚刚注意到上面的查询为每个组提供了最早的 24 条记录。

 set @num:=0, @server:='';
select servers.*, IFNULL(AVG(stats.votes), 0) AS avgvotes
from servers left outer join (
select server, 
       time,votes, 
       @num := if(@server = server, @num + 1, 1) as row_number, 
       @server:= server as dummy 
from (select * from stats order by server, time desc)  as t
group by server, time 
having row_number < 25) as stats 
on servers.id = stats.server
group by servers.id

这将给出每组 24 个最新实体的平均值

编辑2

@DrAgonmoray您可以先尝试内部查询部分，看看它是否为每个组返回最新的 24 条记录。在我的 mysql 5.5 中，它工作正常。

select server, 
       time,votes, 
       @num := if(@server = server, @num + 1, 1) as row_number, 
       @server:= server as dummy 
from (select * from stats order by server, time desc)  as t
group by server, time 
having row_number < 25