如何计算多国家/城市维度下月度销售额的前后月差值

有一张按日统计各国家、城市销售数据的表，需要计算各月度销售总额与前后月的销售额差值。当前编写的SQL仅在单个国家场景下结果正确，多国家/城市时无法得到预期结果。

原始数据表 #

预期输出 #

当前使用的SQL #

select *, LAG(Sales, 1, Sales) OVER (ORDER BY date, Country, State) - Sales AS Diff
from (
  select min(date) as date,Country,State,sum(Sales) as Sales
  from SalesTable 
  group by YEAR(date), MONTH(date),Country,State
) as s
order by date desc

当前SQL存在两个核心问题：

1. 子查询中错误使用了State字段，原始表实际字段为City，导致分组逻辑错误
2. 窗口函数未按Country和City分区，全局排序会把不同国家/城市的月度数据混在一起计算差值，不符合业务逻辑

修正后的SQL如下：

select 
  *, 
  LAG(Sales, 1, Sales) OVER (PARTITION BY Country, City ORDER BY YEAR(date), MONTH(date)) - Sales AS Diff
from (
  select 
    min(date) as date,
    Country,
    City,  -- 修正字段名，匹配原始表结构
    sum(Sales) as Sales
  from SalesTable 
  group by YEAR(date), MONTH(date), Country, City  -- 同步修正分组字段
) as s
order by date desc

关键修改说明 #

• 字段修正：将子查询中的State替换为原始表的City，确保分组和输出字段与数据源匹配
• 窗口分区：新增PARTITION BY Country, City，让每个国家+城市的组合单独计算前后月的销售额差值，避免跨区域数据干扰
• 排序逻辑：窗口内按YEAR(date), MONTH(date)排序，确保时间顺序准确，LAG函数能正确取到前一个月的销售总额

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