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.

CodePudding user response：

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.

CodePudding user response：

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

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

db<>fiddle here