Jump to content

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


frankchester

Recommended Posts

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. 

 

 

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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 |
+-----------+------------+-----------+------+
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.