MySQL - Calculate percentage change between latest 2 available year

kms 0
Viewed 59

I have a table in MySQL 8.0 with the following structure.

CREATE TABLE `rg` ( `name` text, 
                    `zip` text,
                    `Year` bigint,
                    `r` double,
                    `geom` GEOMETRY
                  );

INSERT INTO `rg` (name, zip, Year, r, geom) 

VALUES 

('AB, NM', '87105', '2015', '10', 'POLYGON ((35.066062 -106.700005))'),

('AB, NM', '87105', '2016', '20', 'POLYGON ((35.067912 -106.700884))'),

('AB, NM', '87105', '2017', '30', 'POLYGON ((35.067912 -106.700884))')

How do I write a query that returns the percentage difference of r between the latest 2 available years? I group by name, zip and geom columns.

select name, zip, ST_AsText(geom) as geom, ....
from rg
GROUP BY name, zip, geom

Expected output:

AB, NM 87105 POLYGON ((35.067912 -106.700884)) 50

i.e difference between r values 20, 30 of 2016 and 2017 is 50.

mysql sql
2 Answers

Use two CTE's, one to get the previous max year, and the other for the max year. JOIN them together using name and zip, and run the difference calculation (mentioned as your expected output above) to get the percentage change between latest two available years.

Calculation:

((maxYear.r-previousMaxYear.r)/previousMaxYear.r * 100)

WITH 
previousMaxYear (name, zip, geom, r) AS (
SELECT name, zip, geom, r FROM rg WHERE year = (SELECT MAX(year) - 1 FROM rg LIMIT 1)
),
maxYear (name, zip, geom, r) AS (
SELECT name, zip, geom, r FROM rg WHERE year = (SELECT MAX(year) FROM rg LIMIT 1)
)
SELECT a.name, a.zip, ST_AsText(a.geom) as geom, CONCAT(ROUND((a.r-b.r)/b.r * 100), '%') AS 'Difference'
FROM maxYear a
  INNER JOIN previousMaxYear b ON
  a.name = b.name
  AND 
  a.zip = b.zip

Result:

name zip geom Difference
AB, NM 87105 POINT(35.067912 -106.700884) 50%

db<>fiddle here.

UPDATE:

Since, as you stated, the previous year may not always be MAX(year) - 1, you can use one CTE to partition it by year DESC in order to get the sequential number for each row.

Then, JOIN the CTE with itself (self join):

  • JOIN the max date (alias a) WHERE row number = 1 (RN=1).
  • JOIN the next max date (alias b) WHERE row number = 2 (RN=2).

Finally, LIMIT 1 row to return the values from alias a.

WITH CTE AS (SELECT name, zip, geom, r, ROW_NUMBER() OVER (ORDER BY year DESC) AS RN FROM rg)
SELECT
  a.name, 
  a.zip, 
  ST_AsText(a.geom) AS geom,
  CONCAT(ROUND((a.r-b.r)/b.r * 100), '%') AS 'difference'
FROM CTE
INNER JOIN (CTE AS a)
    ON (a.name = CTE.name 
        AND a.zip = CTE.zip 
        AND a.RN = 1)
INNER JOIN (CTE as b)
    ON (b.name = CTE.name 
        AND b.zip = CTE.zip 
        AND b.RN = 2)
LIMIT 1

db<>fiddle here.

GRIV0

Your math is a bit off

vut the query would look like, but 50 i can't get

The window function LEAD gives you the next value in Line. you can use it to make mathematical operation

Row_number gives you the row number also for the parttiion and order given in the window function.

As you are only interested in the last two years, we sort the rows by the YEAR and want the partition of name and zip

The calculation of the percentage is

r / r(from the last year) * 100

To get your 50 pro cent growth you need to subtract 100 from the actual per centage

r / r(from the last year) * 100 - 100

OR

r(from the last eyxr) / r * 100

But as i can't get 50 per cent i added both calcculations, select the one you want

WITH CTE AS (SELECT
name, zip, Year, r, geom,
   LEAD(r) OVER (PARTITION BY name, zip ORDER BY YEAR DESC) / r * 100 as lasttwoyears
  , ROW_NUMBER() OVER (PARTITION BY name, zip ORDER BY YEAR DESC) rn
FROM rg)
SELECT name, zip,geom, lasttwoyears  FROM CTE WHERe rn = 1
name zip geom lasttwoyears
AB, NM 87105 000000000101000000c3802557b18841408a929048dbac5ac0 66.66666666666666 
WITH CTE AS (SELECT
name, zip, Year, r, geom,
   r / LEAD(r) OVER (PARTITION BY name, zip ORDER BY YEAR DESC)  * 100 as lasttwoyears
  , ROW_NUMBER() OVER (PARTITION BY name, zip ORDER BY YEAR DESC) rn
FROM rg)
SELECT name, zip,geom, lasttwoyears  FROM CTE WHERe rn = 1
name zip geom lasttwoyears
AB, NM 87105 000000000101000000c3802557b18841408a929048dbac5ac0 150 
WITH CTE AS (SELECT
name, zip, Year, r, geom,
   r / LEAD(r) OVER (PARTITION BY name, zip ORDER BY YEAR DESC)  * 100 - 100 as lasttwoyears
  , ROW_NUMBER() OVER (PARTITION BY name, zip ORDER BY YEAR DESC) rn
FROM rg)
SELECT name, zip,geom, lasttwoyears  FROM CTE WHERe rn = 1
name zip geom lasttwoyears
AB, NM 87105 000000000101000000c3802557b18841408a929048dbac5ac0 50

fiddle

nbk0
Related