MySQL query to get total percentage change

问题:

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)
;

回答1:

DROP TABLE IF EXISTS test;

CREATE TABLE test
( id SERIAL PRIMARY KEY
, percent DECIMAL(5,2) NOT NULL
);

INSERT INTO test (percent) VALUES 
(-0.5)
,(0.5)
,(1)
,(-0.2)
,(0.5)
,(-1)
;

SELECT ROUND(@i:=(@i+(@i*percent)),2)n 
  FROM test
     , (SELECT @i:=1) vars 
 ORDER 
    BY id;
+------+
| n    |
+------+
| 0.50 |
| 0.75 |
| 1.50 |
| 1.20 |
| 1.80 |
| 0.00 |
+------+
6 rows in set (0.00 sec)

mysql>

回答2:

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 current percent and the preceding nominal_value (where preceding is defined by row number). Finally total_percent is computed from the nominal_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 an AUTO_INCREMENT INT column id for this purpose.

WITH RECURSIVE cte AS (
  SELECT percent, ROW_NUMBER() OVER () AS rn
  FROM test
  ORDER BY id),
cte2 AS (
  SELECT 1 + percent AS nominal_value, rn
  FROM cte
  WHERE rn = 1
  UNION ALL
  SELECT CASE WHEN nominal_value = 0 THEN percent
              ELSE nominal_value + percent * ABS(nominal_value)
              END,
         cte.rn
  FROM cte
  JOIN cte2 ON cte2.rn = cte.rn - 1
  )
SELECT percent, nominal_value, (nominal_value - 1) AS total_percent
FROM cte2
JOIN cte ON cte.rn = cte2.rn

Output:

percent nominal_value   total_percent
-0.5    0.5             -0.5
0.5     0.75            -0.25
1       1.5             0.5
-0.2    1.2             0.2
0.5     1.8             0.8
-1      0               -1
-2      -2              -3
0.75    -0.5            -1.5
1       0               -1
0.5     0.5             -0.5

Demo on dbfiddle

回答3:

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 being DROPped at the end of the procedure if further processing was needed. As with the other answers, this approach requires the data to have a PRIMARY KEY to guarantee consistent results.

DELIMITER //
CREATE PROCEDURE total_percent()
BEGIN
  DECLARE nominal_value DECIMAL(10,2) DEFAULT 1;
  DECLARE this_percent DECIMAL(5,2);
  DECLARE done INT DEFAULT 0;
  DECLARE p_cursor CURSOR FOR SELECT percent FROM test ORDER BY id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  CREATE TEMPORARY TABLE p (percent DECIMAL(5, 2),
                            nominal_value DECIMAL(10, 2),
                            total_percent DECIMAL(10, 2));
  OPEN p_cursor;
  compute: LOOP
    FETCH p_cursor INTO this_percent;
    IF done THEN
      LEAVE compute;
    END IF;
    IF nominal_value = 0 THEN
      SET nominal_value = this_percent;
    ELSE
      SET nominal_value = nominal_value + this_percent * ABS(nominal_value);
    END IF;
    INSERT INTO p VALUES (this_percent, nominal_value, nominal_value -1);
  END loop;
  SELECT * FROM p;
  DROP TABLE p;
END //
DELIMITER ;

CALL total_percent();

Output:

percent  nominal_value   total_percent
-0.5     0.5             -0.5
0.5      0.75            -0.25
1        1.5             0.5
-0.2     1.2             0.2
0.5      1.8             0.8
-1       0               -1
-2       -2              -3
0.75     -0.5            -1.5
1        0               -1
0.5      0.5             -0.5

Demo on dbfiddle

回答4:

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:

Query:

DROP TABLE IF EXISTS test;

CREATE TABLE test
( 
 id SERIAL PRIMARY KEY
 , percent DECIMAL(5,2) NOT NULL
);

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)
;

SELECT 
    percent,

    CASE @i 
        WHEN 0 THEN ROUND(@i:=(@i+(percent * 1)),2) -1
        ELSE ROUND(@i:=(@i+(percent * ABS(@i))) ,2) -1
    END total_percent

FROM 
    test
    , (SELECT @i:=1) vars         
ORDER 
    BY id; 

Result:

+---------+---------------+
| percent | total_percent |
+---------+---------------+
|   -0.50 |         -0.50 |
|    0.50 |         -0.25 |
|    1.00 |          0.50 |
|   -0.20 |          0.20 |
|    0.50 |          0.80 |
|   -1.00 |         -1.00 |
|   -2.00 |         -3.00 |
|    0.75 |         -1.50 |
|    1.00 |         -1.00 |
|    0.50 |         -0.50 |
+---------+---------------+
10 rows in set, 3 warnings (0.00 sec)

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.