MySQL - Average most recent columns in other table

Question

I have two tables: "servers" and "stats"

servers has a column called "id" that auto-increments. stats has a column called "server" that corresponds to a row in the servers table, a column called "time" that represents the time it was added, and a column called "votes" that I would like to get the average of.

I would like to fetch all the servers (SELECT * FROM servers) along with the average votes of the 24 most recent rows that correspond to each server. I believe this is a "greatest-n-per-group" question.

This is what I tried to do, but it gave me 24 rows total, not 24 rows per group:

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

Like I said, I would like to get the 24 most recent rows for each server, not 24 most recent rows total.

Answer 1

Thanks for this great 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

edit 1

I just noticed that above query gives the oldest 24 records for each groups.

 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

which will give the average of the 24 newest entity for each group

Edit2

@DrAgonmoray you can try the inner query part first and see if it returns the the newest 24 records for each group. In my mysql 5.5, it works correctly.

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