MySQL按城市和邮编分组计算最近两年数据的百分比变化

表结构与数据 #

假设表名为city_data，结构和数据如下：

City    zip    year   value 

AB, NM  87102  2012   150
AB, NM  87102  2013   175
AB, NM  87102  2014   200
DL, TX  75212  2018   100
DL, TX  75212  2019   150
DL, TX  75212  2020   175 
AT, TX  83621  2020   150

需求 #

按City和zip字段分组，计算每组可用的最近两年数据的百分比变化。注意：

• 最近的两年可能不连续
• 仅保留有至少两年数据的分组

预期输出 #

City     zip     pct_change

AB, NM   87102   14.3
DL, TX   75212   16.6

当前尝试的查询语句 #

select City, zip, max(year), calculate diff between value
from table
group by City, zip
where ....

解决方案 #

简化版查询（MySQL 8.0+支持） #

利用窗口函数ROW_NUMBER()和LAG()可以高效实现需求：

SELECT
    City,
    zip,
    ROUND(
        ((value - LAG(value) OVER (PARTITION BY City, zip ORDER BY year)) / LAG(value) OVER (PARTITION BY City, zip ORDER BY year)) * 100,
        1
    ) AS pct_change
FROM (
    SELECT
        City,
        zip,
        year,
        value,
        ROW_NUMBER() OVER (PARTITION BY City, zip ORDER BY year DESC) AS rn
    FROM city_data
) AS ranked
WHERE rn = 1 AND LAG(value) OVER (PARTITION BY City, zip ORDER BY year) IS NOT NULL;

分步说明 #

1. 分组内排序：用ROW_NUMBER()给每个City+zip分组的记录按年份倒序编号，最近年份标记为rn=1
2. 获取前一年数据：LAG()函数直接提取同分组内上一条（按年份升序）的value，即次近年份的数据
3. 计算百分比变化：通过公式((当前值-前一年值)/前一年值)*100计算变化率，用ROUND()保留1位小数
4. 过滤无效分组：仅保留存在至少两年数据的分组（即LAG(value)不为空的记录）

兼容低版本MySQL的写法 #

如果使用MySQL 5.x不支持窗口函数，可以用关联子查询实现：

SELECT
    t1.City,
    t1.zip,
    ROUND(
        ((t1.value - t2.value) / t2.value) * 100,
        1
    ) AS pct_change
FROM city_data t1
JOIN city_data t2 
    ON t1.City = t2.City 
    AND t1.zip = t2.zip
    AND t2.year = (
        SELECT MAX(year) 
        FROM city_data 
        WHERE City = t1.City AND zip = t1.zip AND year < t1.year
    )
WHERE t1.year = (
    SELECT MAX(year) 
    FROM city_data 
    WHERE City = t1.City AND zip = t1.zip
);

内容的提问来源于stack exchange，提问作者kms