BigQuery中多重UNNEST hits自定义维度提取国家与拆分标签的实现问题

你可以通过两次UNNEST搭配条件聚合的方式完成需求，具体逻辑和代码如下：

核心思路 #

• 先展开hits层级的customDimensions数组，分别提取index=1的国家值、index=4的标签字符串，聚合到同一行的对应字段
• 再对标签字符串按|分割后做二次展开，最终得到每行对应单个标签的结果

示例SQL #

WITH extract_dim AS (
  -- 第一步：提取同一条记录下的国家和标签字符串
  SELECT
    date,
    -- 提取index=1对应的国家值
    MAX(IF(cd.index = 1, cd.value, NULL)) AS Country,
    -- 提取index=4对应的|分隔标签字符串
    MAX(IF(cd.index = 4, cd.value, NULL)) AS label_str
  FROM
    `你的项目名.你的数据集名.你的表名`,
    UNNEST(hits) AS h,
    UNNEST(h.customDimensions) AS cd
  GROUP BY
    date
  -- 如果有会话/命中级别的唯一标识（比如fullVisitorId、visitId、hitId）建议也加入GROUP BY，避免数据合并错误
)
-- 第二步：拆分标签字符串并展开得到单行单标签结果
SELECT
  date,
  Country,
  label
FROM
  extract_dim,
  UNNEST(SPLIT(label_str, '|')) AS label
WHERE
  -- 过滤掉没有国家或者没有标签的无效记录
  Country IS NOT NULL
  AND label_str IS NOT NULL
  AND label != ''

注意事项 #

• 如果你的表中同一条date对应多条独立的命中记录，需要把对应的唯一标识字段（如fullVisitorId、visitId、hitId等）加入第一步的GROUP BY逻辑，避免多条记录的自定义维度被错误合并
• 标签拆分后如果存在空值可以根据业务需求调整WHERE条件的过滤规则

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