如何在BigQuery中使用Pivot实现产品层级表行转列

针对单物料多行存储的产品层级表转单行宽表的需求，BigQuery标准SQL下有两种成熟可落地的实现方案，均能满足字段转换要求：

方案1：条件聚合（生产环境优先推荐） #

核心逻辑是按salesorg、distr_chan、material三个维度分组，通过条件判断提取不同层级对应的编码和文本值，是OLAP场景下行转列性能最优、兼容性最好的写法。

SELECT
  salesorg,
  distr_chan,
  material,
  MAX(IF(hier_lvl = 'prodh1', prod_hier, NULL)) AS prodh1,
  MAX(IF(hier_lvl = 'prodh2', prod_hier, NULL)) AS prodh2,
  MAX(IF(hier_lvl = 'prodh3', prod_hier, NULL)) AS prodh3,
  MAX(IF(hier_lvl = 'prodh4', prod_hier, NULL)) AS prodh4,
  MAX(IF(hier_lvl = 'prodh5', prod_hier, NULL)) AS prodh5,
  MAX(IF(hier_lvl = 'prodh6', prod_hier, NULL)) AS prodh6,
  MAX(IF(hier_lvl = 'prodh7', prod_hier, NULL)) AS prodh7,
  MAX(IF(hier_lvl = 'prodh1', txt, NULL)) AS prodh1txt,
  MAX(IF(hier_lvl = 'prodh2', txt, NULL)) AS prodh2txt,
  MAX(IF(hier_lvl = 'prodh3', txt, NULL)) AS prodh3txt,
  MAX(IF(hier_lvl = 'prodh4', txt, NULL)) AS prodh4txt,
  MAX(IF(hier_lvl = 'prodh5', txt, NULL)) AS prodh5txt,
  MAX(IF(hier_lvl = 'prodh6', txt, NULL)) AS prodh6txt,
  MAX(IF(hier_lvl = 'prodh7', txt, NULL)) AS prodh7txt
FROM `替换为你的原表完整路径`
GROUP BY 1,2,3

方案说明 #

• 由于业务上每个(salesorg, distr_chan, material, hier_lvl)组合唯一，使用MAX/MIN聚合不会出现数据错配，仅需扫描一次原表即可完成计算，大表场景下性能优势明显
• 若存在某物料部分层级缺失的情况，对应字段会自动返回NULL，符合宽表存储的常规逻辑
• 数据校验可在SELECT子句中增加COUNT(DISTINCT hier_lvl) AS valid_hier_cnt字段，快速排查是否存在层级缺数、重复数据问题
• 若原表存在同层级多条重复记录，可提前在子查询中按业务时间戳等规则去重，避免聚合结果不符合预期

方案2：原生PIVOT语法（临时查询推荐） #

BigQuery原生支持PIVOT行转列语法，代码可读性更高，适合快速写临时查询取数，由于PIVOT一次仅能转换一个指标字段，需要分别转换编码、文本后关联得到最终结果。

WITH
hier_code_pivot AS (
  SELECT * FROM (
    SELECT salesorg, distr_chan, material, hier_lvl, prod_hier
    FROM `替换为你的原表完整路径`
  )
  PIVOT(
    MAX(prod_hier) FOR hier_lvl IN ('prodh1','prodh2','prodh3','prodh4','prodh5','prodh6','prodh7')
  )
),
hier_txt_pivot AS (
  SELECT * FROM (
    SELECT salesorg, distr_chan, material, hier_lvl, txt
    FROM `替换为你的原表完整路径`
  )
  PIVOT(
    MAX(txt) FOR hier_lvl IN ('prodh1','prodh2','prodh3','prodh4','prodh5','prodh6','prodh7')
  )
)
SELECT
  c.salesorg,
  c.distr_chan,
  c.material,
  c.prodh1, c.prodh2, c.prodh3, c.prodh4, c.prodh5, c.prodh6, c.prodh7,
  t.prodh1 AS prodh1txt,
  t.prodh2 AS prodh2txt,
  t.prodh3 AS prodh3txt,
  t.prodh4 AS prodh4txt,
  t.prodh5 AS prodh5txt,
  t.prodh6 AS prodh6txt,
  t.prodh7 AS prodh7txt
FROM hier_code_pivot c
LEFT JOIN hier_txt_pivot t
USING(salesorg, distr_chan, material)

方案说明 #

• PIVOT子句中IN列表的取值必须和hier_lvl字段的实际值完全一致，大小写敏感
• 该写法需要两次扫描原表再做JOIN，TB级大表场景下性能略低于条件聚合方案
• 后续如果需要调整层级数量，仅需修改IN列表的取值即可，不用重复编写条件判断语句

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