Getting previous row in MySQL

Question

I'm stucked in a MySQL problem that I was not able to find a solution yet. I have the following query that brings to me the month-year and the number new users of each period in my platform:

select  
    u.period ,
    u.count_new as new_users
from 
(select DATE_FORMAT(u.registration_date,'%Y-%m') as period, count(distinct u.id) as count_new from users u group by DATE_FORMAT(u.registration_date,'%Y-%m')) u
order by period desc;

The result is the table:

period,new_users
2016-10,103699
2016-09,149001
2016-08,169841
2016-07,150672
2016-06,148920
2016-05,160206
2016-04,147715
2016-03,173394
2016-02,157743
2016-01,173013

So, I need to calculate for each month-year the difference between the period and the last month-year. I need a result table like this:

period,new_users
2016-10,calculate(103699 - 149001)
2016-09,calculate(149001- 169841)
2016-08,calculate(169841- 150672)
2016-07,So on...
2016-06,...
2016-05,...
2016-04,...
2016-03,...
2016-02,...
2016-01,...

Any ideas: =/

Thankss

You repeat the query that is in the derived table once more and you join these 2 on each other, using a month earlier period from the 2nd derived table. — Shadow, Oct 20 '16 at 14:15

score 1 · Answer 1 · edited May 23 '17 at 10:32

You should be able to use a similar approach as I posted in another S/O question. You are on a good track to start. You have your inner query get the counts and have it ordered in the final direction you need. By using inline mysql variables, you can have a holding column of the previous record's value, then use that as computation base for the next result, then set the variable to the new balance to be used for each subsequent cycle.

The JOIN to the SqlVars alias does not have any "ON" condition as the SqlVars would only return a single row anyhow and would not result in any Cartesian product.

select  
    u.period,
    if( @prevCount = -1, 0, u.count_new - @prevCount ) as new_users,
    @prevCount := new_users as HoldColumnForNextCycle    
   from 
      ( select 
              DATE_FORMAT(u.registration_date,'%Y-%m') as period, 
              count(distinct u.id) as count_new 
           from 
              users u 
           group by 
              DATE_FORMAT(u.registration_date,'%Y-%m') ) u
      JOIN ( select @prevCount := -1 ) as SqlVars
   order by 
      u.period desc;

You may have to play with it a little as there is no "starting" point in counts, so the first entry in either sorted direction may look strange. I am starting the "@prevCount" variable as -1. So the first record processed gets a new user count of 0 into the "new_users" column. THEN, whatever was the distinct new user count was for the record, I then assign back to the @prevCount as the basis for all subsequent records being processed. yes, it is an extra column in the result set that can be ignored, but is needed. Again, it is just a per-line place-holder and you can see in the result query how it gets its value as each line progresses...

@Polly, glad it did. Also, please take a look at help/tour for site etiquette. Marking up on answers that help, clicking checkbox when solved helps others know what worked under similar issues they are encountering. — DRapp, Oct 20 '16 at 16:48

vhr · Answer 2 · 2016-10-20T12:52:39.933

0

I would create a temp table with two columns and then fill it using a cursor that does something like this (don't remember the exact syntax - so this is just a pseudo-code):

@val = CURSOR.col2 - (select col2 from OriginalTable t2 where (t2.Period = (CURSOR.Period-1) ))) 

INSERT tmpTable (Period, NewUsers) Values ( CURSOR.Period, @val)

edited Oct 20 '16 at 12:52

answered Oct 20 '16 at 12:47

vhr

1,528
1
13
21

Getting previous row in MySQL

2 Answers2