[SOLVED] Aggregate Functions - a twist

[midgeling7]

MySQL 5.0.2.2:

 

CREATE TABLE `ranking` (
  `playerid` int(12) NOT NULL,
  `rank` int(12) NOT NULL,
  `score` decimal(8,1) NOT NULL,
  `timestamp` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

Every day I add to this table with a new score and rank for each player. I want to write an SQL statement that finds how much each player has moved since last time.

 

Currently I use php to: (for player 1)

SELECT * FROM `ranking` WHERE `playerid` = '1' ORDER BY `timestamp` DESC LIMIT 2

Then take (rows[2]['rank'] - rows[1]['rank']) as that player's movement.

 

There has to be a way of doing this in MySQL, and to complete it for every player at once, rather than having to iterate through them in php (got 350k+ players, this would take a while...)

 

Thought I could try something like this:

SELECT `rank` FROM `ranking` WHERE `timestamp` IN (SELECT DISTINCT `timestamp` FROM `ranking` ORDER BY `timestamp` DESC LIMIT 2) GROUP BY `playerid`

But not only does that not work (it seems you can't use IN with a sub select that uses LIMIT), there's no aggregate functions I can find that perform their functions in a specific order.

Eg: a player moves from 4th to 7th the two hits would be 7,4 (in that order) I need to do 4 - 7 = -3 places - none of the aggregate functions allow subtraction and the GROUP BY manual page states that the order in which results are returned inside a group is unpredictable.

 

Please someone help!

I'd rather not keep separate tables to work all this out - especially as I really only want to find the 'movers and shakers' from the list - so only the top 10 and bottom ten off the table of player's movements ordered by how far they've moved.

 

Please let me know any of your thoughts on this! Cheers,

JP

[midgeling7]

A friend of mine has pointed out that I neglected to mention that I'm trying to establish the change in a players rank - described above as movement - cheers Dave

[Wildbug]

My simplified mockup and the solution:

 

mysql> DESCRIBE ranks;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| playerid | tinyint(4) | YES  |     | NULL    |       |
| rank     | tinyint(4) | YES  |     | NULL    |       |
| day      | date       | YES  |     | NULL    |       |
+----------+------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> SELECT * FROM ranks;
+----------+------+------------+
| playerid | rank | day        |
+----------+------+------------+
|        1 |    1 | 2007-06-20 |
|        2 |    3 | 2007-06-20 |
|        3 |    2 | 2007-06-20 |
|        1 |    3 | 2007-06-21 |
|        2 |    1 | 2007-06-21 |
|        3 |    2 | 2007-06-21 |
|        4 |    4 | 2007-06-21 |
+----------+------+------------+
7 rows in set (0.00 sec)

mysql> SELECT today.playerid,yesterday.rank-today.rank AS movement FROM
(SELECT * FROM ranks WHERE day=CURDATE()) AS today
JOIN
(SELECT * FROM ranks WHERE day=CURDATE()-INTERVAL 1 DAY) AS yesterday
ON today.playerid=yesterday.playerid;
+----------+----------+
| playerid | movement |
+----------+----------+
|        1 |       -2 |
|        2 |        2 |
|        3 |        0 |
+----------+----------+
3 rows in set (0.00 sec)

[midgeling7]

An elegant solution! Fantastic!

 

There is one small problem, which I neglected to mention: I'd like to retain the option to alter the period between additions to the `ranking` (or `ranks` in your case) table.

 

Using your simplified version, I suppose I could do the following:

 

SET @latestdate = (SELECT MAX(`day`) FROM `rank`);
SET @previousdate = (SELECT MAX(`day`) FROM `rank` WHERE `day` != @latestdate);

SELECT today.playerid,yesterday.rank-today.rank AS movement FROM
(SELECT * FROM ranks WHERE day=@latestdate) AS today
JOIN
(SELECT * FROM ranks WHERE day=@previousdate) AS yesterday
ON today.playerid=yesterday.playerid;

 

That seems a bit clunky though - what do you think?

 

Again many thanks for your prompt reply!

JP

[Wildbug]

That works.  You could also replace the session variables with subqueries if you want to do it all in one shot.

 

SELECT today.playerid, yesterday.rank - today.rank AS movement FROM
(SELECT * FROM ranking WHERE day=(SELECT MAX(day) FROM ranking)) AS today JOIN
(SELECT * FROM ranking WHERE day=(SELECT DISTINCT day FROM ranking ORDER BY day DESC LIMIT 1,1) AS yesterday ON today.playerid=yesterday.playerid;