mysql - 每组最新N条记录的平均值

Question

我当前的应用程序根据每个用户的所有记录计算平均分:

SELECT `user_id`, AVG(`points`) AS pts 
FROM `players` 
WHERE `points` != 0 
GROUP BY `user_id`

业务需求发生变化，我需要根据每个用户的最近 30 条记录计算平均值。

相关表的结构如下:

表:玩家；列:player_id, user_id, match_id, points

表:用户；列:user_id

以下查询不起作用，但它确实展示了我正在尝试实现的逻辑。

SELECT @user_id := u.`id`, (
    -- Calculate the average for last 30 records
    SELECT AVG(plr.`points`) 
    FROM (
        -- Select the last 30 records for evaluation
        SELECT p.`points` 
        FROM `players` AS p 
        WHERE p.`user_id`=@user_id 
        ORDER BY `match_id` DESC 
        LIMIT 30
    ) AS plr
) AS avg_points 
FROM `users` AS u

是否有一种相当有效的方法可以根据每个用户的最新 30 条记录计算平均值？

Answer 1

没有理由重新发明轮子并冒着错误、次优代码的风险。您的问题是普通 per group limit problem 的微不足道的扩展.已经有 tested and optimized solutions to solve this problem ，并且从这个资源中，我建议从以下两种解决方案中进行选择。这些查询为每个玩家生成最新的 30 条记录(为您的表重写):

select user_id, points
from players
where (
   select count(*) from players as p
   where p.user_id = players.user_id and p.player_id >= players.player_id
) <= 30;

(只是为了确保我理解您的结构:我想 player_id 是玩家表中的唯一键，并且一个用户可以作为多个玩家出现在该表中。)

第二 tested and optimized solution就是使用MySQL变量:

set @num := 0, @user_id := -1;

select user_id, points,
      @num := if(@user_id = user_id, @num + 1, 1) as row_number,
      @user_id := user_id as dummy
from players force index(user_id) /* optimization */
group by user_id, points, player_id /* player_id should be necessary here */
having row_number <= 30;

第一个查询不会是最优的(是二次的)，而第二个查询是最优的(一次通过)，但只能在 MySQL 中工作。这个选择由你。如果您选择第二种技术，请注意并使用您的 key 和数据库设置正确测试它； they suggest in some circumstances it might stop working .

您的最终查询很简单:

select user_id, avg(points)
from ( /* here goes one of the above solutions; 
          the "set" commands should go before this big query */ ) as t
group by user_id

请注意，我没有在您的第一个查询 (points != 0) 中加入您的条件，因为我不太了解您的要求(您没有描述)，而且我还认为这个答案应该足够通用，以帮助其他有类似问题的人。