如何在MySQL中按国家计算人口增长率年度差值

要实现按国家计算相邻年份人口增长率的差值，用窗口函数LAG()是最直接的方案，它能精准获取同国家上一年的增长率数据，避免你之前用临时表时出现的全局匹配问题。

查询语句（仅计算展示） #

如果只是需要查询结果，不需要修改原表，用以下语句即可：

SELECT
    country,
    year,
    population_growth_rate,
    -- 计算当前行与同国家上一年增长率的差值，首行自动为NULL
    population_growth_rate - LAG(population_growth_rate) OVER (PARTITION BY country ORDER BY year) AS population_growth_difference
FROM test;

持久化到原表（新增列并填充数据） #

如果要把差值永久存入新增的population_growth_difference列，可以分两步操作：

1. 新增列：

ALTER TABLE test ADD COLUMN population_growth_difference DECIMAL(10,4);

2. 填充差值数据：

WITH growth_diff AS (
    SELECT
        country,
        year,
        population_growth_rate - LAG(population_growth_rate) OVER (PARTITION BY country ORDER BY year) AS diff
    FROM test
)
UPDATE test t
SET population_growth_difference = gd.diff
FROM growth_diff gd
WHERE t.country = gd.country AND t.year = gd.year;

为什么之前的临时表方案失效？ #

你之前用临时表得到全0的结果，大概率是因为没有按country分组、按year排序来匹配上一行数据，导致全局取到的上一行可能是其他国家的同年份数据，或者顺序混乱，最终出现同年份相减为0的错误。而LAG()配合PARTITION BY country能确保只在同一个国家的行内，按年份顺序获取上一行的增长率，完美解决这个问题。

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