CODEWITHSUNDEEP
mysqlsql
user3768071
user3768071

Reputation: 887

Calculate percent increase/decrease from previous row value

I have a table that looks something like this:

|date_start  | date_end    |amount | 
+------------+-------------+-------+
|2015-02-23  | 2015-03-01  |50     |
|2015-03-02  | 2015-03-08  |50     |
|2015-03-09  | 2015-03-15  |100    |
|2015-03-16  | 2015-03-22  |800    |
|2015-03-23  | 2015-03-29  |50     |

and I'd like to work out the percent increase/decrease for column amount, from the previous date. For example the result would be something like this,

|date_start  | date_end    |amount | perc_change | 
+------------+-------------+-------+-------------+
|2015-02-23  | 2015-03-01  |50     | 
|2015-03-02  | 2015-03-08  |50     | 0
|2015-03-09  | 2015-03-15  |100    | 50
|2015-03-16  | 2015-03-22  |800    | 700
|2015-03-23  | 2015-03-29  |50     | -750

I've searched and racked my brain for a couple of days now. Usually, I simply do this using server side code but now I need to contain it all within the query.

Upvotes: 5

Views: 18380

Answers (6)

Amira Shaker
Amira Shaker

Reputation: 11

You can use the lag() window function to make it be easier:

select date_start,
       date_end,
       amount,
       LAG(amount, 1, 0) OVER (ORDER BY date_start) as previous_amount,
       amount - LAG(amount, 1, 0) OVER (ORDER BY date_start) as amount_diff,
       ((amount - LAG(amount, 1, 0) OVER (ORDER BY date_start)) / (amount - LAG(amount, 1, 0) OVER (ORDER BY date_start))) * 100 amount_diff_percentage
from your_table_name

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

If we assume that the previous row always ends exactly one day before the current begins (as in your sample data), then you can use a join. The percentage increase would be:

select t.*,
       100 * (t.amount - tprev.amount) / tprev.amount
from atable t left join
     atable tprev
     on tprev.date_end = t.date_start - interval 1 day;

However, your results seem to just have the difference, which is easier to calculate:

select t.*,
       (t.amount - tprev.amount) as diff
from atable t left join
     atable tprev
     on tprev.date_end = t.date_start - interval 1 day;

Upvotes: 3

ekans
ekans

Reputation: 1714

You case use the mysql var system : 

SELECT date_start, date_end, IF(@last IS NOT NULL,ammount - @last , '' ) as perc_change, @last := amount as amount 
From table 
ORDER BY date_start;

the var @last is set at each passage, so column order between perc_change and amount is important

Upvotes: 0

Tim3880
Tim3880

Reputation: 2583

Try this:

 SELECT t.*,
 amount - (SELECT amount FROM transactions prev WHERE prev.date_end     < t.date_start ORDER BY date_start DESC LIMIT 1) AS changes
 FROM   transactions t

Upvotes: 5

The Reason
The Reason

Reputation: 7973

It looks something like this, i dont have environment to test this select but i think it should work

    SELECT t1.date_start, t1.date_end, t1.amount, (t1.amount - t2.amount)  as  perc_change
    FROM table1 t1 
      JOIN table1 t2  ON t1.id = t2.id + 1 // here your join to 
   //get next row and calculate this perc_change field

Upvotes: 0

AdamMc331
AdamMc331

Reputation: 16691

I started by joining each row in the table with the one that comes after it, like this:

SELECT m.date_start AS mStart, mt.date_start AS mtStart
FROM myTable m
JOIN myTable mT ON m.date_start < mt.date_start AND mt.date_start = (SELECT MIN(date_start) FROM myTable WHERE date_start > m.date_start);

This will join the tables so that each row can be seen with the following date if there is one. If there's not, the date is not returned.

Once you have those values, you can adjust the SELECT query to show you the percent change from the date before, like this:

SELECT 
  mT.date_start AS secondDate, 
  mT.amount - m.amount AS percentChange
FROM myTable m
JOIN myTable mT ON m.date_start < mT.date_start AND mt.date_start = (SELECT MIN(date_start) FROM myTable WHERE date_start > m.date_start);

I would like to make a note here that while you say 'percent difference' in your question, your expected results have nothing to do with percentage, but just difference in value. If you need to calculate this a different way, you can just adjust the select query above to meet your needs.

The last thing you will have to do is join this back to your original table to see all of the values together. This has to be done using a left join, in order for the first date of the table to be seen. Here is the final query:

SELECT m.date_start, m.date_end, m.amount, tmp.percentChange
FROM myTable m
LEFT JOIN(
  SELECT 
    mT.date_start AS secondDate, 
    mT.amount - m.amount AS percentChange
  FROM myTable m
  JOIN myTable mT ON m.date_start < mT.date_start AND mt.date_start = (SELECT MIN(date_start) FROM myTable WHERE date_start > m.date_start)
) tmp ON tmp.secondDate = m.date_start;

And here is an SQL Fiddle example.

Upvotes: 0

Related Questions