MySQL query to get sum of differences between column and AVG of same column
I have a table which contains the scores by user, for a game:
UserID (Integer)
MatchId (Integer)
Score (Double)
I'd like to getter sum each user's "points above average" (PAA) - the amount by which a user's score was above or below the average.
So you'd need to calculate the average of 'Score' for each 'MatchId', then for each row in the table calculate the amount by which the 'Score' differs from the match average. And then sum that PAA value by user.
Is it possible to do this via a MySQL query? Or do I need PHP? If it can be done by query, what would that query look like?
plan
setup
create table scores
(
UserID integer not null,
MatchId integer not null,
Score decimal(5, 2) not null,
primary key ( UserID, MatchId )
);
insert into scores
( UserID, MatchId, Score )
values
( 1, 1, 22.1 ),
( 2, 1, 36.0 ),
( 3, 1, 35.3 ),
( 1, 2, 50.0 ),
( 2, 2, 39.8 ),
( 3, 2, 42.0 )
;
query
select s.UserID, sum(s.Score - avgs.avg_score) as paa
from scores s
inner join
(
select MatchId, avg(Score) as avg_score
from scores
group by MatchId
) avgs
on s.MatchId = avgs.MatchId
group by s.UserID
;
output
+--------+-----------+
| UserID | paa |
+--------+-----------+
| 1 | -2.966666 |
| 2 | 0.733334 |
| 3 | 2.233334 |
+--------+-----------+
sqlfiddle
链接地址: http://www.djcxy.com/p/63776.html上一篇: 如何从资源创建Drawable