MySQL: Last 10 entries per user?
I have a table storing transactional data for users. To find a users rating you take the average score of the last 10 entries for that user. Is there a way to get that with SQL?
I need to be able to work it out for a single user given their ID. And to get a list of all users ordered by their score.
Currently I'm working it out outside MySQL and storing it in another col for each user so I can ORDER_BY that.
# returns average for all transactions.
SELECT user_id, AVG(score) FROM transactions WHERE user_id = 1
# returns scores for last 10 transactions.
S开发者_高级运维ELECT user_id, score FROM transactions WHERE user_id = 1 ORDER_BY date DESC LIMIT 10
Use:
SELECT x.user_id,
AVG(x.score)
FROM (SELECT t.user_id,
t.score
FROM TRANSACTIONS t
WHERE t.user_id = ?
ORDER BY t.date
LIMIT 10) x
GROUP BY x.user_id
Just combine the two queries already in use:
SELECT user_id, AVG(score)
FROM transactions
WHERE rowid in (
SELECT rowid
FROM transactions
WHERE user_id = 1
ORDER_BY date DESC
LIMIT 10)
where rowid
is whatever the identifying I.D. is.
精彩评论