我当前的应用程序根据每个用户的所有记录计算点平均值:
SELECT `user_id`, AVG(`points`) AS pts
FROM `players`
WHERE `points` != 0
GROUP BY `user_id`
业务需求已更改,我需要根据每个用户的最近30条记录计算平均值.
相关表格具有以下结构:
桌子:球员;列:player_id,user_id,match_id,points
表:用户; columns: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条记录计算平均值?