How to add a column of percent change (not percentage points) in MySQL?
there is a table with column of changes in percents:
+---------+
| percent |
+---------+
| -0.50 |
| 0.50 |
| 1.00 |
| -0.20 |
| 0.50 |
| -1.00 |
| -2.00 |
| 0.75 |
| 1.00 |
| 0.50 |
+---------+
How to write a query that calculates a total percent change of a value for each row so the calculated row expresses its percentage change and all previous rows of percentage change?.
expected result:
+---------+---------------+---------------+
| percent | nominal_value | total_percent |
+---------+---------------+---------------+
| -0.50 | 0.50 | -0.50 |
| 0.50 | 0.75 | -0.25 |
| 1.00 | 1.50 | 0.50 |
| -0.20 | 1.20 | 0.20 |
| 0.50 | 1.80 | 0.80 |
| -1.00 | 0.00 | -1.00 |
| -2.00 | -2.00 | -3.00 |
| 0.75 | -0.50 | -1.50 |
| 1.00 | 0.00 | -1.00 |
| 0.50 | 0.50 | -0.50 |
+---------+---------------+---------------+
Where the nominal_value
is an arbitrary value that was changed by percent
so for the first row if the nominal value was 1.0 (100%) but was changed by -0.50
(-50%
) it resulted in nominal value 0.5
.
Then at the second row percent
change was +0.50
(+50%
) so the nominal value was increased by half of it 0.5 => 0.75
but one can also say that it was just lowered by -0.25
(-25%
) from its original value since from 1.0
to 0.75
is a -0.25
(-25%
) of 1.0
.
That's exactly what I'm after a total_percent
change, the nominal_value
was just for the explanatory purpose and is not needed.
I'm using MySQL 8 so the query may use window functions / ranges etc.
here is the test table to replicate:
CREATE TABLE IF NOT EXISTS test
(
percent DECIMAL(5,2) NOT NULL
)
ENGINE = InnoDB
;
INSERT INTO test (percent) VALUES
(-0.50)
,(0.50)
,(1.00)
,(-0.20)
,(0.50)
,(-1.0)
,(-2.0)
,(0.75)
,(1.0)
,(0.50)
;
This query will give you the results you want. It uses two CTEs, the first which simply adds a row number to the data, and the second, recursive CTE which generates the
nominal_value
values from the currentpercent
and the precedingnominal_value
(where preceding is defined by row number). Finallytotal_percent
is computed from thenominal_value
.Note
To make this (and any similar) query work reliably, there has to be a
PRIMARY KEY
that the first CTE can have its results ordered by. In the demo I have added anAUTO_INCREMENT INT
columnid
for this purpose.Output:
Demo on dbfiddle
An alternate way to compute this data is using a stored procedure. The advantage of this approach is that it doesn't require a recursive CTE or variables, but the disadvantage is that it can be tricky to use the results (for example in a
JOIN
). This procedure creates a temporary table to store results before returning them; that table could be preserved instead of beingDROP
ped at the end of the procedure if further processing was needed. As with the other answers, this approach requires the data to have aPRIMARY KEY
to guarantee consistent results.Output:
Demo on dbfiddle
This is a small variation of the accepted answer due to the fact OP edited post and added additional rows of data and wanted result after accepted ans. was posted and accepted:
Note that accepted answer stops calculations after reaching zero nominal value and then no matter the percentage change makes no difference and nominal value is the same = 0. For some cases this might be the right approach. For others here's this one that continues calculation through zero or @Nick answer in case you use MySQL 8.