MYSQL Update data based on top 10 records of each user

Posted on

Question :

I have a table users and other table user_scores. In 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_score in 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 = ORDER BY id DESC LIMIT 0,10)as x)

I am facing error Unknown column '' in 'where clause' cuz I am not able to pass variable to subquery of the query

  1. 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.

Please suggest.

Answer :

I may have a solution, but first please see if this computes the avg_scores correctly:

        user_id, AVG(score) AS avg_score
      ( SELECT  @prev := 0, @n := 0 ) init
      ( 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;

Recommend INDEX(user_id, id, score) on user_scores .

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

Leave a Reply

Your email address will not be published. Required fields are marked *