将嵌套重复记录转为扁平字段：Google Analytics数据处理问题

针对你需要将customDimensions（会话级）和hits.customDimensions（命中级）从索引-值对转为扁平子字段的需求，以下是BigQuery中的可行方案，能避免UNNEST导致的重复记录，同时为每个hit条目添加cd_0、cd_1等字段：

方案1：简洁版（仅处理命中级自定义维度） #

如果只需要扁平化hit级的自定义维度，无需处理会话级，可直接在重构hits数组时提取对应索引的值：

SELECT
  -- 保留所有会话级原有字段
  fullVisitorId,
  visitId,
  visitNumber,
  date,
  totals.*,
  trafficSource.*,
  device.*,
  -- 重构hits数组，为每个hit添加扁平的cd_N字段
  ARRAY(
    SELECT AS STRUCT
      hit.* EXCEPT(customDimensions), -- 保留hit原有字段，排除原customDimensions
      -- 提取对应索引的自定义维度值，不存在则返回NULL
      (SELECT value FROM UNNEST(hit.customDimensions) WHERE index = 0) AS cd_0,
      (SELECT value FROM UNNEST(hit.customDimensions) WHERE index = 1) AS cd_1,
      (SELECT value FROM UNNEST(hit.customDimensions) WHERE index = 2) AS cd_2,
      -- 按需添加更多索引对应的字段（如cd_3、cd_4等）
    FROM UNNEST(hits) AS hit
  ) AS hits
FROM
  `your-project.your-dataset.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131' -- 替换为你的日期范围

为什么这个方案有效： #

• 直接在ARRAY()构造函数内部逐个处理每个hit，通过子查询从hit.customDimensions中提取指定索引的值，不会展开整个hits数组，因此不会产生重复的会话记录。
• 每个hit的customDimensions中索引是唯一的，子查询只会返回对应值或NULL，完美匹配需求。

方案2：完整版（同时处理会话级和命中级） #

如果需要同时扁平化会话级的customDimensions，可以用CTE先处理hit级数据，再聚合会话级：

WITH processed_hits AS (
  SELECT
    fullVisitorId,
    visitId,
    visitNumber,
    hit.hitNumber,
    -- 保留hit的所有原有字段，排除原customDimensions
    hit.* EXCEPT(customDimensions),
    -- 扁平化当前hit的自定义维度
    MAX(IF(hit_cd.index = 0, hit_cd.value, NULL)) AS cd_0,
    MAX(IF(hit_cd.index = 1, hit_cd.value, NULL)) AS cd_1,
    MAX(IF(hit_cd.index = 2, hit_cd.value, NULL)) AS cd_2
    -- 按需添加更多hit级索引字段
  FROM
    `your-project.your-dataset.ga_sessions_*`,
    UNNEST(hits) AS hit
    LEFT JOIN UNNEST(hit.customDimensions) AS hit_cd ON TRUE
  WHERE
    _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  GROUP BY
    fullVisitorId,
    visitId,
    visitNumber,
    hit.hitNumber,
    hit.* EXCEPT(customDimensions)
)
SELECT
  fullVisitorId,
  visitId,
  visitNumber,
  date,
  totals.*,
  trafficSource.*,
  device.*,
  -- 扁平化会话级自定义维度
  MAX(IF(session_cd.index = 0, session_cd.value, NULL)) AS cd_0,
  MAX(IF(session_cd.index = 1, session_cd.value, NULL)) AS cd_1,
  MAX(IF(session_cd.index = 2, session_cd.value, NULL)) AS cd_2,
  -- 按需添加更多会话级索引字段
  -- 将处理后的hit重新组合为数组
  ARRAY_AGG(STRUCT(
    hitNumber,
    page,
    time,
    type,
    -- 列出需要保留的hit原有字段，或用* EXCEPT(fullVisitorId, visitId, visitNumber)
    cd_0,
    cd_1,
    cd_2
  )) AS hits
FROM
  `your-project.your-dataset.ga_sessions_*`
LEFT JOIN UNNEST(customDimensions) AS session_cd ON TRUE
JOIN processed_hits USING(fullVisitorId, visitId, visitNumber)
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY
  fullVisitorId,
  visitId,
  visitNumber,
  date,
  totals.*,
  trafficSource.*,
  device.*

关键说明： #

1. processed_hits CTE：展开每个hit和其customDimensions，通过MAX(IF(...))和GROUP BY hit.hitNumber确保每个hit只生成一条记录，避免重复。
2. 主查询：处理会话级自定义维度时，同样用MAX(IF(...))和会话级分组来避免重复，最后通过ARRAY_AGG将处理后的hit重新组合成数组，确保每个会话仅一条记录。

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