如何使用BigQuery Standard SQL实现行转列构建数据透视表

适用场景 #

现有数据表包含字段：catalog_number、manufacturer、region、region_price、region_catalog_number，需要按catalog_number+manufacturer组合去重，将不同区域的字段转为独立列，列名格式为region_{区域名}、region_{区域名}_price、region_{区域名}_catalog_number。

实现方案 #

方案1：静态透视（已知区域枚举值） #

如果区域取值固定（比如仅包含CN、US、EU三个区域），直接用CASE WHEN结合聚合函数实现，性能最优：

SELECT
  catalog_number,
  manufacturer,
  -- 中国区字段
  MAX(CASE WHEN region = 'CN' THEN region END) AS region_CN,
  MAX(CASE WHEN region = 'CN' THEN region_price END) AS region_CN_price,
  MAX(CASE WHEN region = 'CN' THEN region_catalog_number END) AS region_CN_catalog_number,
  -- 美国区字段
  MAX(CASE WHEN region = 'US' THEN region END) AS region_US,
  MAX(CASE WHEN region = 'US' THEN region_price END) AS region_US_price,
  MAX(CASE WHEN region = 'US' THEN region_catalog_number END) AS region_US_catalog_number,
  -- 欧盟区字段
  MAX(CASE WHEN region = 'EU' THEN region END) AS region_EU,
  MAX(CASE WHEN region = 'EU' THEN region_price END) AS region_EU_price,
  MAX(CASE WHEN region = 'EU' THEN region_catalog_number END) AS region_EU_catalog_number
FROM
  `你的项目ID.你的数据集名.你的表名`
GROUP BY
  catalog_number,
  manufacturer

说明：同catalog_number+manufacturer+region组合仅存在一条数据时，MAX函数会直接取对应的值，不存在该区域数据的列会返回NULL。如果存在重复数据，可根据业务需要将MAX替换为MIN、AVG等聚合函数。

方案2：动态透视（区域值不固定） #

如果区域取值会动态新增，不需要每次修改SQL，可通过BigQuery的EXECUTE IMMEDIATE动态生成并执行透视语句：

DECLARE pivot_sql STRING;

-- 自动读取所有区域值，生成透视SQL
SET pivot_sql = (
  SELECT CONCAT(
    "SELECT catalog_number, manufacturer, ",
    STRING_AGG(
      CONCAT(
        "MAX(CASE WHEN region = '", region, "' THEN region END) AS region_", region, ", ",
        "MAX(CASE WHEN region = '", region, "' THEN region_price END) AS region_", region, "_price, ",
        "MAX(CASE WHEN region = '", region, "' THEN region_catalog_number END) AS region_", region, "_catalog_number"
      ), ", " ORDER BY region
    ),
    " FROM `你的项目ID.你的数据集名.你的表名` GROUP BY catalog_number, manufacturer"
  )
  FROM (SELECT DISTINCT region FROM `你的项目ID.你的数据集名.你的表名`)
);

-- 执行生成的SQL
EXECUTE IMMEDIATE pivot_sql;

说明：动态透视的SQL长度受BigQuery语句长度限制，区域枚举值过多的场景不推荐使用。

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