How to calculate percent change between two values in the same column-CodePudding

I want to calculate a percent change between two values in the same column in a specific form and I have no idea if what I’m trying to do is even possible.

I have a table with 3 fields

Month, Country, Value

order_month	country	value
2021-01	UK	10
2022-02	UK	20
2021-01	France	20
2022-02	France	18
2021-01	Italy	25
2021-02	Italy	35

What I struggle to get :

order_month	country	value
2021-01	UK	10
2022-02	UK	20
diff	UK	10
2021-01	France	20
2022-01	France	18
diff	France	-2
2021-01	Italy	25
2022-02	Italy	35
diff	Italy	10

I tried many things without success. Thanks a lot if you can help me on this.

CodePudding user response：

You can use the LEAD/LAG window functions for this. I'd propose using this to create a new column for the difference, rather than hoping to add in a new row into the result to get the difference of the two rows above it.

Schema (MySQL v8.0)

CREATE TABLE data (
  `order_month` date,
  `country` VARCHAR(6),
  `value` INTEGER
);

INSERT INTO data
  (`order_month`, `country`, `value`)
VALUES
  ('2021-01-01', 'UK', '10'),
  ('2022-02-01', 'UK', '20'),
  ('2021-01-01', 'France', '20'),
  ('2022-02-01', 'France', '18'),
  ('2021-01-01', 'Italy', '25'),
  ('2022-02-01', 'Italy', '35');

Query #1

select *,
VALUE - Lead(VALUE) OVER (PARTITION BY COUNTRY ORDER BY ORDER_MONTH DESC) as Month_vs_Month
from data;

order_month	country	value	Month_vs_Month
2022-02-01	France	18	-2
2021-01-01	France	20
2022-02-01	Italy	35	10
2021-01-01	Italy	25
2022-02-01	UK	20	10
2021-01-01	UK	10

View on DB Fiddle

CodePudding user response：

You need to create two subqueries or CTEs that isolate the values to the months you're analyzing.

Subquery example

select
  country,
  value as jan_value
from {{table}}
where order_month = '2022-01'

Do the same for Februrary then join the tables to create a new data set with county, jan_value, and feb_value. From this dataset you can determine difference in the values.