BigQuery SQL中用PIVOT/UNPIVOT转置行列的报错解决及多指标适配

1. 修复PIVOT报错 #

报错原因是BigQuery要求PIVOT中用于生成列的日期/时间类型值必须指定别名，原始日期格式无法直接作为合法列名。修改后的语句如下：

SELECT * FROM (
  SELECT * FROM original_table 
  UNPIVOT(value for metric IN (metric1))
)
PIVOT(
  MIN(value) 
  FOR month IN (
    DATETIME '2022-12-01T00:00:00' AS `2022-12-01`,
    DATETIME '2023-01-01T00:00:00' AS `2023-01-01`,
    DATETIME '2023-02-01T00:00:00' AS `2023-02-01`
  )
)

注意事项：

• 保持month字段类型一致（原表为DATETIME，IN子句需统一使用DATETIME类型，避免混合DATE）
• 用反引号`包裹别名，确保日期格式的列名合法

2. 多指标适配写法 #

如果原表包含metric1、metric2等多个指标列，只需在UNPIVOT的IN子句中添加所有指标即可，PIVOT部分无需额外修改：

SELECT * FROM (
  SELECT * FROM original_table 
  UNPIVOT(value for metric IN (metric1, metric2, metric3)) -- 此处添加所有指标列
)
PIVOT(
  MIN(value) 
  FOR month IN (
    DATETIME '2022-12-01T00:00:00' AS `2022-12-01`,
    DATETIME '2023-01-01T00:00:00' AS `2023-01-01`,
    DATETIME '2023-02-01T00:00:00' AS `2023-02-01`
  )
)

执行后会得到每个指标对应各月份值的结果：

3. 更高效的写法（无需中间表） #

将UNPIVOT和PIVOT合并到单个查询中，避免创建中间表，提升查询效率——上述示例已采用该写法，直接执行即可。

若日期范围不固定，不想硬编码日期值，可通过动态SQL自动生成PIVOT列：

DECLARE pivot_cols STRING;

-- 自动获取所有需转置的日期并生成别名
SET pivot_cols = (
  SELECT STRING_AGG(
    FORMAT("DATETIME '%s' AS `%s`", FORMAT_TIMESTAMP('%Y-%m-%d', month), FORMAT_TIMESTAMP('%Y-%m-%d', month))
  )
  FROM original_table
);

-- 执行动态PIVOT
EXECUTE IMMEDIATE FORMAT("""
  SELECT * FROM (
    SELECT * FROM original_table 
    UNPIVOT(value for metric IN (metric1, metric2))
  )
  PIVOT(
    MIN(value) 
    FOR month IN (%s)
  )
""", pivot_cols);

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