每组最近N条记录的平均值 [英] Average of latest N records per group

问题描述

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

My current application calculates a point average based on all records for each user:

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

业务需求已更改，我需要根据每个用户的最近30条记录来计算平均值.

The business requirement has changed and I need to calculate the average based on the last 30 records for each user.

相关表具有以下结构:

表:玩家；列:player_id，user_id，match_id，积分

table: players; columns: player_id, user_id, match_id, points

表:用户；列:user_id

table: users; columns: user_id

以下查询不起作用，但确实演示了我尝试实现的逻辑.

The following query does not work, but it does demonstrate the logic that I am trying to implement.

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条记录来计算平均值?

Is there a fairly efficient way to calculate the averages based on the latest 30 records for each user?

推荐答案

尝试一下:

SELECT user_id, AVG(points) AS pts 
FROM (SELECT user_id, IF(@uid = (@uid := user_id), @auto:=@auto + 1, @auto := 1) autoNo, points
      FROM players, (SELECT @uid := 0, @auto:= 1) A 
      WHERE points != 0 
      ORDER BY user_id, match_id DESC
     ) AS A 
WHERE autoNo <= 30
GROUP BY user_id;

这篇关于每组最近N条记录的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！