如何创建基于前序行计算的新列 按城市分组计算月度CPI通胀

使用lag/lead出现跨城市取数错误，核心是窗口函数未指定分区规则：默认配置下函数会对全表所有行按排序顺序全局偏移，自然会把排序后上一个城市的最后一行数据，算到下一个城市第一行的偏移结果里，就会出现Malmo第一行偏移取到Stockholm最后一期CPI的问题。

计算时给窗口函数加上按城市分区的规则，分区内部再按日期升序排序，就能保证偏移取值始终限定在同城市的时间序列内，不会跨城市串值。
通用SQL实现代码如下：

SELECT
    CPI,
    Date,
    City,
    -- 取同城市上一个月的CPI值
    LAG(CPI, 1) OVER (PARTITION BY City ORDER BY Date ASC) AS last_month_cpi,
    -- 计算当月通胀率，单位为百分比，结果保留2位小数
    ROUND(
        (CPI - LAG(CPI, 1) OVER (PARTITION BY City ORDER BY Date ASC)) 
        / LAG(CPI, 1) OVER (PARTITION BY City ORDER BY Date ASC) * 100,
        2
    ) AS monthly_inflation_rate
FROM cpi_stats_table;

• PARTITION BY City是解决跨城市串数的核心配置：它会将全表数据按城市拆分为互相独立的计算窗口，偏移、排序等窗口逻辑只会在单个城市的数据集内部执行，不同城市的数据完全隔离
• 分区内必须添加ORDER BY Date ASC规则：保证单个城市的所有记录按月份从早到晚排列，偏移取到的上一行才是时间维度的上一期数据，不会出现月份顺序错乱
• 每个城市最早月份的记录没有对应的上一期CPI，计算出的通胀率会返回NULL，属于正常现象，如果需要填充默认值，可以在外层套COALESCE函数自定义空值的展示结果

用提供的样例数据验证：Malmo城市2005-01-01的记录是该城市分区的第一行，上一期CPI为空；2005-02-01的记录偏移取到的上一期CPI是106，不会再错误取到Stockholm最后一期的115.8，完全符合计算要求。

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