MySQL sum last X rows per group

Question

I have the following tables:

player_records

int     id          (pk)
int     player_id   (fk)
int     round_id    (fk)
int     kills     

players

int     id          (pk)
varchar name

rounds

int     id          (pk)
int     duration            // round duration in seconds

I need to select kills/hour (SUM(kills)/SUM(duration/60/60)) ratio for each player from the last 10 rounds they've participated, provided not every player takes part in a round.

All similar questions that I've found point to this article, but I haven't been able to apply its tricks to my needs above as I have to limit the records to 10 before grouping.

To further illustrate it, this is how I'd be doing if I needed just a single player:

SELECT SUM(t1.kills)/SUM(t1.duration)*60*60 kills_hour_last_10
FROM (
    SELECT records.kills, rounds.duration
    FROM records
    JOIN rounds ON rounds.id = records.round_id
    WHERE records.player_id = 1
    ORDER BY records.id DESC
    LIMIT 10
) t1;

Updated with fiddle (Those 3 queries represent the expected results for each player. All I need is to know how to get all of them in the same query).

Answer 1

You can use a rank query mysql does not have window functions for this type of results to get n records per group

SELECT t.player_id,
SUM(t.kills)/SUM(t.duration)*60*60 kills_hour_last_10    
FROM (
SELECT *,
@r:= CASE WHEN @g = player_id THEN @r + 1 ELSE 1 END rownum,
@g:= player_id 
FROM (
SELECT r.player_id,r.id,r.kills, rs.duration
 FROM  records r
 JOIN rounds rs ON rs.id = r.round_id
 ORDER BY r.player_id ASC,r.id DESC
) t1
 CROSS JOIN (SELECT @g:=NULL,@r:=NULL) t2
) t
WHERE rownum <= 10
GROUP BY t.player_id 

---

Fiddle Demo

Above query will give ranks to the record which belongs to same player_id group and in parent query you can limit records to 10 for each player_id and the perform your aggregation logic note that ORDER BY r.player_id,r.id DESC is important to order results by player and then its records in descending manner,if you need player names then join players in parent query