BigQuery使用UNPIVOT实现多关联列同步逆透视匹配

你当前的查询仅对player1/player2/player3单组字段执行了逆透视，未对position系列字段做处理，因此三个position列会完整保留在结果中，无法实现同序号字段匹配。以下两种写法均可直接实现需求：

方案1：UNNEST+结构体数组（推荐） #

这是BigQuery中处理多列同步逆透视最简洁的写法，不需要多次逆透视后关联，逻辑直观且维护成本低：

SELECT
  match,
  item.player,
  item.position
FROM `match`,
UNNEST([
  STRUCT(player1 AS player, position1 AS position),
  STRUCT(player2 AS player, position2 AS position),
  STRUCT(player3 AS player, position3 AS position)
]) AS item

执行逻辑：为原表每一行构造包含3个结构体的数组，每个结构体直接绑定同序号的player和position值，再通过UNNEST将数组拆分为独立行，直接输出匹配后的结果。

方案2：双UNPIVOT+序号关联 #

如果需要严格使用UNPIVOT算子，可以分别对两组字段做逆透视，提取字段名末尾的序号做关联：

WITH unpivot_player AS (
  SELECT
    match,
    player_val AS player,
    REGEXP_EXTRACT(player_col, r'(\d+)$') AS seq
  FROM `match`
  UNPIVOT(player_val FOR player_col IN (player1, player2, player3))
),
unpivot_position AS (
  SELECT
    match,
    position_val AS position,
    REGEXP_EXTRACT(position_col, r'(\d+)$') AS seq
  FROM `match`
  UNPIVOT(position_val FOR position_col IN (position1, position2, position3))
)
SELECT
  p.match,
  p.player,
  pos.position
FROM unpivot_player p
INNER JOIN unpivot_position pos
  ON p.match = pos.match
  AND p.seq = pos.seq

注意：方案2依赖字段名末尾的数字序号做匹配，如果字段命名规则不统一，容易出现匹配错误，优先使用方案1。

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