MySQL 对每组的最后 X 行求和

Question

我有以下表格:

player_records

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

玩家

int     id          (pk)
varchar name

回合

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

我需要为他们参加的最后 10 轮中的每个玩家选择杀戮/小时(SUM(杀戮)/SUM(持续时间/60/60))比率，前提是不是每个玩家参加一轮比赛。

我发现的所有类似问题都指向 this article ，但我无法将其技巧应用于上述需求，因为我必须在分组前将记录限制为 10。

为了进一步说明，如果我只需要一个玩家，我会这样做:

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;

更新为 fiddle (这 3 个查询代表每个玩家的预期结果。我只需要知道如何在同一个查询中获得所有这些结果)。

Answer 1

您可以使用排名查询 mysql 没有针对此类结果的窗口函数来获取每组 n 条记录

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

上面的查询将对属于同一 player_id 组的记录进行排名，在父查询中，您可以将每个 player_id 的记录限制为 10，并执行聚合逻辑，请注意 ORDER BY r.player_id,r.id DESC 重要的是按玩家排序结果，然后以降序方式记录，如果您需要玩家名称，则在父查询中加入玩家