BigQuery如何根据id列值动态匹配对应element列数据？

方法1：用UNPIVOT转换表结构 #

针对列名有规律（element+数字）的场景，先把宽表转成长表，再匹配id和列名中的数字即可拿到对应值：

WITH unpivoted AS (
  SELECT 
    id,
    -- 提取列名里的数字，和id做匹配
    CAST(REGEXP_EXTRACT(element_key, r'element(\d+)') AS INT64) AS element_id,
    element_value
  FROM mytable
  -- 将所有element列转为key-value形式
  UNPIVOT (
    element_value FOR element_key IN (element1, element2, element3, ..., elementN)
  )
)
SELECT 
  original.id,
  unpivoted.element_value AS right_element
FROM mytable original
JOIN unpivoted 
  ON original.id = unpivoted.element_id
  AND original.id = unpivoted.id

如果列数太多不想手动罗列所有element列，用下面的动态SQL方案更高效。

方法2：动态生成UNPIVOT语句（适配列数极多/不确定的场景） #

借助BigQuery的元数据表自动获取所有element开头的列，生成动态SQL执行：

DECLARE element_columns STRING;

-- 自动获取所有element开头的列，拼接成UNPIVOT所需格式
SET element_columns = (
  SELECT STRING_AGG(QUOTENAME(column_name), ', ')
  FROM `你的项目名.你的数据集名.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'mytable'
    AND column_name LIKE 'element%'
);

-- 执行动态生成的查询
EXECUTE IMMEDIATE FORMAT("""
WITH unpivoted AS (
  SELECT 
    id,
    CAST(REGEXP_EXTRACT(element_key, r'element(\\d+)') AS INT64) AS element_id,
    element_value
  FROM mytable
  UNPIVOT (
    element_value FOR element_key IN (%s)
  )
)
SELECT 
  original.id,
  unpivoted.element_value AS right_element
FROM mytable original
JOIN unpivoted 
  ON original.id = unpivoted.element_id
  AND original.id = unpivoted.id
""", element_columns);

关键说明 #

• UNPIVOT的核心是把横向的多列转为纵向的key-value对，让列名里的数字能和id做关联匹配；
• 动态SQL方案无需手动维护列名列表，只要列名符合element+数字的规则就能自动适配。

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