Getting data from a previous row, based on the current row

[frankchester]

I'm not sure if this is a pure mySQL question or if it's more PHP but I thought this seemed like a good place to ask it.

 

I have a table which has data stored into it twice daily. The data stored is the timestamp, an account_ID, and the number of followers. 

 

I am trying to add another field of "gains_losses". This should look at the previous entry for that account_ID, find out the followers data, and then find out the difference between the data being submitted today. 

 

How would you go about doing this? I'm a bit stuck because I can't do it based on the date previously, and the times can change so I can't do a search for "Where time = this minus one day". 

 

Here's a screenie of the table, you can see the account_ID repeats often as these are entries for each of these accounts. 

 

 

[requinix]

I'm a bit stuck because I can't do it based on the date previously,

Why not? You say "should look at the previous entry" I would expect that looking at the times would be the best way to do that.

 

If you're not using an auto_increment column already then you can add one as part of a composite key like (account_id, entry): the will count from 1 per account_id, so you'll know for a fact that the previous entry was (account_id, current entry-1).

[CroNiX]

Why not? You say "should look at the previous entry" I would expect that looking at the times would be the best way to do that.

 

If you're not using an auto_increment column already then you can add one as part of a composite key like (account_id, entry): the will count from 1 per account_id, so you'll know for a fact that the previous entry was (account_id, current entry-1).

I can see that working using the auto_increment ID only if they don't allow deleting entries, otherwise I wouldn't count on (entry - 1) being the correct ID.

[Barand]

Don't add another column for the calculated gains. Derived data should not be stored but calculated when required. For example

 

Data

mysql> SELECT * FROM frankchester;
+----+-----------+------------+-----------+
| id | accountid | date       | followers |
+----+-----------+------------+-----------+
|  1 |         1 | 2014-11-10 |        50 |
|  2 |         2 | 2014-11-10 |        45 |
|  3 |         1 | 2014-11-11 |        55 |
|  4 |         2 | 2014-11-11 |        60 |
|  5 |         1 | 2014-11-12 |        65 |
|  6 |         2 | 2014-11-12 |        55 |
+----+-----------+------------+-----------+

Query

SELECT accountid, date, followers, gain
FROM (
    SELECT
     date
     , IF(@preva=accountid,followers-@prevf,NULL) as gain
     , @prevf:= followers as followers
     , @preva:= accountid as accountid
     FROM frankchester
        JOIN (SELECT @prevf:=NULL, @preva:=NULL) as init
     ORDER BY accountid, date
     ) as calc;

Results

+-----------+------------+-----------+------+
| accountid | date       | followers | gain |
+-----------+------------+-----------+------+
|         1 | 2014-11-10 |        50 | NULL |
|         1 | 2014-11-11 |        55 |    5 |
|         1 | 2014-11-12 |        65 |   10 |
|         2 | 2014-11-10 |        45 | NULL |
|         2 | 2014-11-11 |        60 |   15 |
|         2 | 2014-11-12 |        55 |   -5 |
+-----------+------------+-----------+------+