Tuesday, September 3

Insert the rank into table and more, MySql

After creating rank as in "Select the rows which have the max/min value in a table.", what I really want is to find out the changes of every record. So the first step is to actually write the rank into the table:

Alter table test add column serialnumber int, add column changefrompreviousrecord decimal(4, 2), add column changedays int;

Use variables to write the record:

update test
    set serialnumber =
         @sno := case when @pid<>personid then 0
                    else @sno+1
            end ,
        personid= @pid:=personid
order by personid, date_of_service;

The line of "personid=@pid:=personid " is a bit awkward, but that is the way to give value to @pid without affecting the logic.

After this update query, we have the real table of 

serialnumber, personid, value, date_of_service
0, 1,  23,  2013-01-05
1, 1,  29,  2013-01-05
2, 1,  27,  2013-01-04
3, 1,  28,  2013-01-04
4, 1,  26,  2013-01-03
0, 2,  25,  2013-01-02
1, 2,  24,  2013-01-01
So we can calculate "changefrompreviousrecord" and "changedays" using a self-joint:


update test bo1 join test bo2
    on bo1.personid=bo2.personidand bo1.serialnumber=bo2.serialnumber+1
set bo1.changefrompreviousrecord=(bo1.value-bo2.value)*100.0/bo2.value,
    bo1.changedays = datediff(bo1.date_of_service, bo2.date_of_service);


So we have:


serialnumber, personid, value, date_of_service, changefrompreviousrecord, changedays
0, 1,  23,  2013-01-05, -24.1, 0
1, 1,  29,  2013-01-05, 7.40, 1
2, 1,  27,  2013-01-04, -3.57, 0
3, 1,  28,  2013-01-04, 7.69, 1
4, 1,  26,  2013-01-03, null, null
0, 2,  25,  2013-01-02, 4.16, 1
1, 2,  24,  2013-01-01, null, null

Note, the "changefrompreviousrecord" is the percentage of the change from the previous record.

That is what I accomplished.

0 Comments:

Links to this post:

Create a Link

<< Home