mysql inflation value calculation-CodePudding

I have a mysql table like this:

| data       | valore  | inflation |
 ------------ --------- ----------- 
| 2022-06-01 | 296.311 |      NULL |
| 2022-05-01 | 292.296 |      NULL |
| 2022-04-01 | 289.109 |      NULL |
| 2022-03-01 | 287.504 |      NULL |
| 2022-02-01 | 283.716 |      NULL |
| 2022-01-01 | 281.148 |      NULL |
| 2021-12-01 | 278.802 |      NULL |
| 2021-11-01 | 277.948 |      NULL |
| 2021-10-01 | 276.589 |      NULL |
| 2021-09-01 | 274.310 |      NULL |
| 2021-08-01 | 273.567 |      NULL |
| 2021-07-01 | 273.003 |      NULL |
| 2021-06-01 | 271.696 |      NULL |

I would need to insert (update) inflation value, calculated as current year value / past year value.

For examle the inflation for 2022-06-01 should be given by 296.311/271.696 * 100 - 100 (or as percentage anyway).

How can I do?

Thank You

CodePudding user response：

Use a self join in the UPDATE statement:

UPDATE tablename t1
INNER JOIN tablename t2 ON t2.data = t1.data - INTERVAL 1 YEAR
SET t1.inflation = t1.valore / t2.valore * 100 - 100;

See the demo.

CodePudding user response：

Ho usato mySQL 8.0:

CREATE TABLE inflation_tab
(data DATE, valore FLOAT, inflazione FLOAT);
INSERT INTO inflation_tab VALUES
('2022-06-01', 296.311, NULL),
('2022-05-01', 292.296, NULL),
('2022-04-01', 289.109, NULL),
('2022-03-01', 287.504, NULL),
('2022-02-01', 283.716, NULL),
('2022-01-01', 281.148, NULL),
('2021-12-01', 278.802, NULL),
('2021-11-01', 277.948, NULL),
('2021-10-01', 276.589, NULL),
('2021-09-01', 274.310, NULL),
('2021-08-01', 273.567, NULL),
('2021-07-01', 273.003, NULL),
('2021-06-01', 271.696, NULL);
SET @last_val=271.696;
WITH cte AS (
    SELECT data, valore, ROUND(valore/@last_val*100 - 100, 3) AS inflazione
    FROM inflation_tab IT
)
UPDATE inflation_tab IT INNER JOIN cte ON (IT.data=cte.data)
SET IT.inflazione=cte.inflazione

CodePudding user response：

Use interval function;

data = data - INTERVAL 1 YEAR