BigQuery中按分组将行转换为列的实现方法

场景说明 #

假设现有原始表（单门店结构在多门店中重复）和目标转置格式如下：

原始表（store_metrics_raw）示例：

store_id	metric_type	metric_value
S001	日销售额	12000
S001	客单价	80
S001	到店人数	150
S002	日销售额	9500
S002	客单价	75
S002	到店人数	127

目标表格式：

store_id	日销售额	客单价	到店人数
S001	12000	80	150
S002	9500	75	127

方法1：使用原生PIVOT函数（固定指标场景推荐） #

如果指标类型是固定的，直接用BigQuery内置的PIVOT语法实现行转列：

CREATE OR REPLACE TABLE `your_project.your_dataset.store_metrics_pivoted` AS
SELECT *
FROM `your_project.your_dataset.store_metrics_raw`
PIVOT (
  MAX(metric_value) -- 若同一门店同一指标存在多条记录，根据需求用MAX/AVG/SUM等聚合函数
  FOR metric_type IN (
    '日销售额' AS 日销售额,
    '客单价' AS 客单价,
    '到店人数' AS 到店人数
  )
);

注：IN子括号里的内容要和原始表中metric_type的实际值完全匹配，列别名可以自定义。

方法2：动态生成转置SQL（指标类型不固定场景） #

如果门店的指标类型可能动态新增或变化，用EXECUTE IMMEDIATE自动生成PIVOT语句：

DECLARE pivot_columns STRING;

-- 自动提取所有唯一的指标类型，生成PIVOT列定义
SET pivot_columns = (
  SELECT STRING_AGG(DISTINCT CONCAT("'", metric_type, "' AS ", metric_type))
  FROM `your_project.your_dataset.store_metrics_raw`
);

-- 执行动态拼接的转置SQL
EXECUTE IMMEDIATE CONCAT("
  CREATE OR REPLACE TABLE `your_project.your_dataset.store_metrics_pivoted_dynamic` AS
  SELECT *
  FROM `your_project.your_dataset.store_metrics_raw`
  PIVOT (
    MAX(metric_value)
    FOR metric_type IN (", pivot_columns, ")
  )
");

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