Calculate percentage increase / decrease of percentage 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 would like to handle the percentage increase / decrease for a column amount
from the previous date. For example, the result will 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 searched and endured my brain for a couple of days. I usually just do it using server side code, but now I need to contain it in the request.
source to share
Assuming the previous line always ends exactly the day before the current one (as in your sample data), you can use join
. The percentage increase will 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 a difference that 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;
source to share
It looks something like this, I don't have an environment to test this choice, 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
source to share
I started by appending each row in the table to the one that appears after it, for example:
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 merge the tables so that each row will be visible with the next date, if any. If not, no date is returned.
Once you have these values, you can customize your SELECT query to display the percentage change since the date before, for example:
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 point out here that while you say "percentage differences" in your question, your expected results have nothing to do with percentage, but simply a difference in value. If you need to calculate it differently, you can simply tweak the selection query above to suit your needs.
The last thing you need to do is join this original table to see all the values together. This needs to be done using a left join so that the first date of the table is visible. Here's the final request:
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 a SQL Fiddle example .
source to share
In case of using 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;
var @last is set in each pass, so the order of the columns between perc_change and count is important.
source to share