1

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.

1.

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

  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.

  • I think there is a rule that prohibits passing a column through more than one level of subqueries. – Rick James Mar 12 '18 at 21:46
0

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;

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;
0

I created one function to handle this part, this way I could pass value into function and get user_score from function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.