如何在BigQuery中优雅转置国家人口宽表为窄表？

针对你需要将bigquery-public-data.world_bank_global_population.population_by_country宽表转成窄表的需求，推荐使用BigQuery原生的UNPIVOT语法，这是最简洁高效的方案，完全不需要依赖UDF或编写大量CASE WHEN语句。

方案1：静态列名UNPIVOT（直观易读） #

直接通过UNPIVOT将所有年份列转换为行，再提取年份字段：

SELECT
  country,
  country_code,
  -- 从列名中提取四位年份数字并转为整数类型
  CAST(REGEXP_EXTRACT(year_column, r'year_(\d{4})') AS INT64) AS year,
  population
FROM
  `bigquery-public-data.world_bank_global_population.population_by_country`
UNPIVOT (
  population FOR year_column IN (
    year_1960, year_1961, year_1962, year_1963, year_1964, year_1965,
    year_1966, year_1967, year_1968, year_1969, year_1970, year_1971,
    year_1972, year_1973, year_1974, year_1975, year_1976, year_1977,
    year_1978, year_1979, year_1980, year_1981, year_1982, year_1983,
    year_1984, year_1985, year_1986, year_1987, year_1988, year_1989,
    year_1990, year_1991, year_1992, year_1993, year_1994, year_1995,
    year_1996, year_1997, year_1998, year_1999, year_2000, year_2001,
    year_2002, year_2003, year_2004, year_2005, year_2006, year_2007,
    year_2008, year_2009, year_2010, year_2011, year_2012, year_2013,
    year_2014, year_2015, year_2016, year_2017, year_2018
  )
)

说明： #

• UNPIVOT会将每个年份列（如year_1960）拆分为两行数据：year_column存储列名（如year_1960），population存储对应年份的人口数值
• 用REGEXP_EXTRACT从year_column中提取年份数字，转换为整数后得到year字段

方案2：动态生成列名（灵活适配列变化） #

如果后续数据集可能新增年份列，可通过INFORMATION_SCHEMA自动获取所有年份列名，避免手动维护列列表：

DECLARE year_columns STRING;

-- 从元数据中获取所有以year_开头的列名，拼接为逗号分隔的字符串
SET year_columns = (
  SELECT STRING_AGG(COLUMN_NAME, ', ')
  FROM `bigquery-public-data.world_bank_global_population.INFORMATION_SCHEMA.COLUMNS`
  WHERE TABLE_NAME = 'population_by_country'
    AND COLUMN_NAME LIKE 'year_%'
);

-- 执行动态生成的UNPIVOT查询
EXECUTE IMMEDIATE FORMAT("""
  SELECT
    country,
    country_code,
    CAST(REGEXP_EXTRACT(year_column, r'year_(\\d{4})') AS INT64) AS year,
    population
  FROM
    `bigquery-public-data.world_bank_global_population.population_by_country`
  UNPIVOT (
    population FOR year_column IN (%s)
  )
""", year_columns);

说明： #

• 利用INFORMATION_SCHEMA.COLUMNS查询表的元数据，筛选出所有年份列
• 通过STRING_AGG拼接列名，再用EXECUTE IMMEDIATE执行动态生成的SQL，自动适配所有年份列

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