SQL Lag函数返回值方向错误：求2018-2020年下载量增速最快国家

你的问题核心在于窗口函数未按国家分区。当前代码里的lag(...) over(order by year asc)是把所有国家的年份数据放在同一个窗口中排序计算，导致不同国家的下载量被混在一起对比，自然会出现值混乱、未知值的情况。单个国家时没有其他数据干扰，所以结果正常。

修正后的代码 #

select 
  country,
  year,
  sum(total_downloads) as totals,
  lag(sum(total_downloads), 1) over(partition by country order by year asc) as previous_year_downloads,
  round(
    (sum(total_downloads) - lag(sum(total_downloads), 1) over(partition by country order by year asc)) 
    / nullif(lag(sum(total_downloads), 1) over(partition by country order by year asc), 0), 
    2
  ) as percentage_change
from cte
group by country, year
order by country, year

关键修改说明 #

• 给窗口函数添加partition by country，让每个国家的年份数据单独形成一个计算窗口，Lag只会取同一个国家上一年的下载量。
• 用nullif(..., 0)避免除数为0的报错（如果某国家某一年无前置数据，分母会返回null，最终增长率也为null，符合逻辑）。
• 末尾添加order by country, year让结果展示更规整。

优化版（减少重复计算） #

可以先通过CTE计算好各国每年的下载总量，再复用该结果计算Lag和增长率，代码更简洁：

with country_year_totals as (
  select 
    country,
    year,
    sum(total_downloads) as totals
  from cte
  group by country, year
)
select 
  country,
  year,
  totals,
  lag(totals, 1) over(partition by country order by year asc) as previous_year_downloads,
  round(
    (totals - lag(totals, 1) over(partition by country order by year asc)) 
    / nullif(lag(totals, 1) over(partition by country order by year asc), 0), 
    2
  ) as percentage_change
from country_year_totals
order by country, year

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