CODEWITHSUNDEEP
mysql
Soni007
Soni007

Reputation: 103

MySQL : How to keep track of change in value using its trend?

MYSQL : How to keep track of change in value using its trend, if value decreases then trend would be -1, if increases then 1 and 0 for same

For example

id_indicator    value      Trend                
    1             0           0   
    1             1           1                                        
    1             5           1                               
    1             4           -1                                 
    2             1           0                      
    2             -8          -1                               
    2             0           1

How i can get trend column? Can i add temporary column in Select statement that will hold trend value?

Upvotes: 0

Views: 258

Answers (1)

csminb
csminb

Reputation: 2382

use a myslq variable to keep track of previous rows

SET @prev = 0;

SELECT 
    id_indicator, 
    value, 
    -- IF(value > @prev, 1, IF(value < @prev , -1, 0)) AS trend,
    SIGN(value-@prev) AS trend, -- nicer solution thx to @spencer7593
    @prev:=value FROM `the_table`

the fourth column's only purpose is to assign the current value into @prev so you can use it in the next row iteration.
having both current and previous values, you can pretty much write any expression you want with them

Upvotes: 1

Related Questions