I have a table
users and other table
user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG
user_score by only considering latest 10 records of
user_scores table. I have tried following two queries but not successful with these.
UPDATE user SET user.user_score = ( SELECT AVG(score) as avg_score FROM ( SELECT score FROM user_scores WHERE user_id = user.id ORDER BY id DESC LIMIT 0,10)as x)
I am facing error
Unknown column 'user.id' in 'where clause' cuz I am not able to pass variable to subquery of the query
- Other thing I have tried by JOIN but in JOIN I am not able to calculate AVG(score) of each user based on latest 10 records.
I may have a solution, but first please see if this computes the avg_scores correctly:
SELECT user_id, AVG(score) AS avg_score FROM ( SELECT @prev := 0, @n := 0 ) init JOIN ( SELECT @n := if(user_id != @prev, 1, @n + 1) AS n, @prev := user_id, user_id, score, id FROM user_scores ORDER BY user_id DESC, id DESC ) x WHERE n <= 10 GROUP BY user_id;
INDEX(user_id, id, score) on
If that works, then a multi-table
UPDATE may finish the task:
UPDATE user JOIN ( the above ) AS y USING(user_id) SET user.user_score = y.avg_score;
I created one function to handle this part, this way I could pass value into function and get user_score from function