如何在BigQuery中针对列而非行使用窗口函数计算年度最大人口增长率？

你的核心问题是把宽表转成窄表，将按列存储的年份数据转为行结构，之后就能用常规窗口函数计算同比增长了，完全不需要Python，纯SQL就能高效解决。

具体步骤： #

1. 宽表转窄表
   用BigQuery的UNPIVOT语法，把year_1960到year_2019这些列拆成year（年份）和population（人口）两行数据，每个国家对应50行（1960-2019）。

2. 计算同比增长率
   对每个国家按年份排序，用LAG()函数获取上一年的人口，然后计算当年的同比增长率：(当年人口 - 上年人口) / 上年人口。

3. 定位增长率最高的年份
   用窗口函数RANK()或ROW_NUMBER()对每个国家的增长率排序，取排名第一的年份即可。

完整SQL示例 #

WITH unpivoted_data AS (
  -- 第一步：宽表转窄表
  SELECT
    country,
    country_code,
    -- 提取年份数字，将year_1960转为1960
    CAST(REPLACE(year_col, 'year_', '') AS INT64) AS year,
    population
  FROM
    `bigquery-public-data.world_bank_global_population.population_by_country`
  UNPIVOT (
    population FOR year_col IN (year_1960, year_1961, year_1962, 
                                -- 省略中间年份，需补全1960到2019的所有列名
                                year_2018, year_2019)
  )
),
growth_rates AS (
  -- 第二步：计算每个国家每年的同比增长率
  SELECT
    country,
    country_code,
    year,
    population,
    -- 用SAFE_DIVIDE避免上年人口为0的报错
    SAFE_DIVIDE(population - LAG(population) OVER (PARTITION BY country ORDER BY year), 
                LAG(population) OVER (PARTITION BY country ORDER BY year)) AS year_over_year_growth
  FROM
    unpivoted_data
)
-- 第三步：找出每个国家增长率最高的年份
SELECT
  country,
  country_code,
  year AS highest_growth_year,
  population AS population_in_high_growth_year,
  year_over_year_growth AS highest_growth_rate
FROM (
  SELECT
    *,
    RANK() OVER (PARTITION BY country ORDER BY year_over_year_growth DESC) AS growth_rank
  FROM
    growth_rates
  -- 排除无上年数据的1960年
  WHERE year_over_year_growth IS NOT NULL
)
WHERE growth_rank = 1
-- 若需处理并列年份，可替换RANK()为ROW_NUMBER()取最早/最晚年份

补充说明 #

• 如果你已经筛选出人口增长幅度最大的目标国家，可以在unpivoted_data的FROM子句后加WHERE country IN ('国家1', '国家2', ...)缩小查询范围，提升效率。
• 若确认所有国家人口均大于0，可去掉SAFE_DIVIDE直接使用普通除法。

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