基于BigQuery中Type列唯一值自动生成列实现数据透视统计

正确实现代码 #

DECLARE pivot_columns STRING;

-- 生成符合条件的PIVOT列列表（替换Type中的空格为下划线，用双引号包裹确保合法）
SET pivot_columns = (
  SELECT STRING_AGG(DISTINCT CONCAT('"', REPLACE(Type, ' ', '_'), '"'))
  FROM `your-project.your-dataset.your-table`
  WHERE `Group` IN ('Group1', 'Group2')
);

-- 动态执行PIVOT查询
EXECUTE IMMEDIATE FORMAT("""
WITH pre_aggregated AS (
  SELECT 
    Location,
    REPLACE(Type, ' ', '_') AS cleaned_type,
    COUNT(DISTINCT Name) AS unique_name_count
  FROM `your-project.your-dataset.your-table`
  WHERE `Group` IN ('Group1', 'Group2')
  GROUP BY Location, cleaned_type
)
SELECT *
FROM pre_aggregated
PIVOT (
  MAX(unique_name_count) -- 预聚合后每个组合仅一行，MAX/ANY_VALUE均可
  FOR cleaned_type IN (%s)
)
""", pivot_columns);

关键修正说明 #

1. 预聚合优化
   先通过CTE预计算每个Location+Type的唯一Name数量，再进行PIVOT，避免重复计算，大幅提升查询效率。

2. 合法列名处理
   用REPLACE(Type, ' ', '_')将Type中的空格替换为下划线，并用双引号包裹列名（CONCAT('"', ..., '"')），确保生成的列名符合BigQuery语法规范。如果需要处理更多特殊字符，可改用REGEXP_REPLACE(Type, r'[^a-zA-Z0-9_]', '_')替换所有非法字符。

3. 精准获取目标Type
   在生成PIVOT列列表时，添加与主查询一致的WHERE条件，确保仅包含Group1/Group2范围内的Type值，避免生成无关列。

4. 修正PIVOT聚合逻辑
   原代码中引用了不存在的Result字段，这里改用预聚合后的unique_name_count，结合MAX函数（因预聚合后每个Location+cleaned_type仅一行数据，MAX/ANY_VALUE均可正确取值）。

5. 保留字处理
   Group是BigQuery保留字，引用时需用反引号`Group`包裹，避免语法错误。

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