如何在BigQuery中对数据执行Pivot/转置操作？

针对你的需求，用BigQuery的PIVOT语法就能直接实现行转列，以下分两种场景给出最优写法：

场景1：Metrics值固定（已知是metric a、b、c） #

如果你的metrics取值是固定的，直接用静态PIVOT即可，代码清晰高效：

SELECT 
  uuid,
  `metric a`,
  `metric b`,
  `metric c`
FROM (
  -- 先筛选出需要用到的核心字段，排除无关的project_id、project_name
  SELECT uuid, metrics, values 
  FROM your_table_name -- 替换成你的实际表名
)
PIVOT (
  -- 因为每个uuid+metrics组合唯一，用MAX/MIN/SUM都能得到正确值，这里选MAX
  MAX(values)
  -- 指定要转成列的metrics取值
  FOR metrics IN ('metric a', 'metric b', 'metric c')
)
ORDER BY uuid;

场景2：Metrics值动态（未来可能新增） #

如果后续metrics会新增取值，不想每次修改SQL，可以用动态SQL自动生成PIVOT语句：

DECLARE metrics_list STRING;

-- 自动获取所有不重复的metrics值，拼接成IN子句需要的格式
SET metrics_list = (
  SELECT STRING_AGG(DISTINCT CONCAT("'", metrics, "'"), ', ')
  FROM your_table_name
);

-- 动态生成并执行PIVOT SQL
EXECUTE IMMEDIATE FORMAT("""
  SELECT uuid, %s
  FROM (
    SELECT uuid, metrics, values 
    FROM your_table_name
  )
  PIVOT (
    MAX(values)
    FOR metrics IN (%s)
  )
  ORDER BY uuid
""", 
-- 把metrics值转成带反引号的列名，处理空格问题
(SELECT STRING_AGG(DISTINCT CONCAT("`", metrics, "`"), ', ') FROM your_table_name),
metrics_list
);

关键说明 #

• PIVOT必须配合聚合函数使用，因为SQL需要处理分组后的聚合逻辑，这里每个uuid+metrics只有一个值，所以任意聚合函数（MAX/MIN/SUM）结果都一致
• 列名包含空格时，必须用反引号`包裹，否则会报错
• 动态SQL适合metrics不固定的场景，能自动适配新增的metrics值

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