BigQuery中分组聚合实现长表转宽表的高效SQL方法

不用手动写上百个CTE做left join，直接用BigQuery原生动态PIVOT能力即可实现自动适配所有source，零维护成本。

实现逻辑 #

• 分两层计算最低价：一层按id聚合算全来源全局最低价minPrice，一层按id+source聚合算单来源最低价
• 用动态SQL自动拉取源表中所有去重的source值，生成PIVOT需要的列定义，不需要手动枚举source
• PIVOT过程自动把每个source的最低价转成独立列，列名按minPrice+source名称的规则自动生成，和预期输出结构完全一致

可直接运行的代码 #

把代码里的你的源表路径替换成实际的表名即可：

EXECUTE IMMEDIATE FORMAT("""
WITH calc_source_min AS (
  SELECT
    id,
    source,
    MIN(price) AS min_val
  FROM `你的源表路径`
  -- 如需按日期范围过滤，在这里加WHERE条件即可，两个CTE保持相同过滤规则
  GROUP BY id, source
),
calc_global_min AS (
  SELECT
    id,
    MIN(price) AS minPrice
  FROM `你的源表路径`
  -- 如需按日期范围过滤，在这里加和上面一致的WHERE条件
  GROUP BY id
)
SELECT
  g.id,
  g.minPrice,
  p.* EXCEPT(id)
FROM calc_global_min g
LEFT JOIN (
  SELECT * FROM calc_source_min
  PIVOT(
    MIN(min_val) FOR source IN (%s)
  )
) p
USING(id)
""",
(
  -- 自动生成所有source对应的pivot列定义，自动处理非法列名字符
  SELECT STRING_AGG(
    DISTINCT CONCAT("'", source, "' AS minPrice", REGEXP_REPLACE(source, r'[^a-zA-Z0-9_]', '_'))
    ORDER BY source
  )
  FROM `你的源表路径`
  -- 如果需要排除部分source不生成列，可以在这里加WHERE过滤
));

使用说明 #

• 代码里用正则把source值里的非字母数字下划线字符替换成下划线，避免生成不符合BigQuery规范的非法列名，有特殊命名需求可以自行调整这部分逻辑
• 不管source后续新增多少个，都不需要修改SQL，运行时会自动识别所有存在的source生成对应列
• 整个查询只会扫描两次源表，比多CTE+多轮LEFT JOIN的写法性能高很多，不会因为join数量过多导致查询报错或者跑数过慢
• 如果需要限定参与计算的日期范围、source范围，直接在对应CTE和动态列生成的子查询里加WHERE条件即可，逻辑统一不容易出错
• BigQuery单表最多支持10000个列，只要source总数不超过这个上限都可以正常运行，超过的话在动态列生成部分加过滤条件保留需要的source即可

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