MySQL-计算最近2个可用年份之间的百分比变化

Question

我在MySQL 8.0中有一个具有以下结构的表。

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

如何编写查询以返回最近2个可用年份之间r的百分比差异？Igroup by、name、zip和geom列。

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

预期输出：

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

即2016和2017的r值20、30之间的差为50。

Answer 1

使用两个CTE，一个用于获取上一个最大年份，另一个用于最大年份。JOIN使用name和zip将它们组合在一起，并运行差异计算(上面提到的是您的预期产出(，以获得最近两个可用年份之间的百分比变化。

计算：

((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

结果：

差异

名称	zip	geom
AB，NM	87105	点(35.067912-106.7000884(	50%

你的数学有点偏离

vut查询看起来像，但50我无法获得

窗口函数LEAD为您提供Line中的下一个值。你可以用它做的数学运算

Row_number还为您提供窗口函数中给定的部分和顺序的行号。

由于您只对过去两年感兴趣，我们按年份对行进行排序，并需要名称和zip 的分区

百分比的计算是

r / r(from the last year) * 100

要获得50%的增长，您需要从实际百分比中减去100

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

或

r(from the last eyxr) / r * 100

但由于我不能获得50%，我添加了两个计算，请选择您想要的

---

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

最近两年AB，NM