在BigQuery中对GA4数据按Session ID分组的技术实现问题

要在BigQuery中按session_id合并GA4事件数据，你不能直接用GROUP BY session_id搭配SELECT *——因为未分组的非聚合字段会触发报错。正确的做法是用聚合函数将同一会话下的所有事件信息打包成数组，实现单session一行的结果。

核心查询方案 #

以下查询会把每个session_id对应的所有事件详情（事件名、页面地址、时间戳等）聚合为数组，最终每个会话仅占一行：

SELECT
  -- 统一session_id类型为字符串，避免数值精度问题
  CAST((SELECT COALESCE(value.int_value, value.float_value, value.double_value) FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING) AS session_id,
  -- 聚合同会话的所有事件为结构体数组
  ARRAY_AGG(STRUCT(
    event_name,
    event_timestamp,
    (SELECT COALESCE(value.string_value) FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    event_params -- 保留原始事件参数数组，按需取舍
  ) ORDER BY event_timestamp) AS session_events
FROM
  `digital-marketing-xxxxxx.analytics_xxxxxxx.events_intraday*`
GROUP BY
  session_id

关键细节解释 #

• 类型统一：将ga_session_id转为字符串，避免不同数值类型（int/float）导致的分组异常
• STRUCT+ARRAY_AGG：用STRUCT封装单条事件的核心字段，再通过ARRAY_AGG将同会话的所有事件打包成有序数组（按时间戳排序）
• **避免SELECT ***：只提取需要的字段并做聚合处理，这是解决"无法聚合非分组字段"报错的核心

输出示例 #

执行后会得到类似结构的结果（仅展示核心列）：

扩展：提取特定事件信息 #

如果需要单独提取会话的关键信息（比如首页面、所有点击事件地址），可以基于聚合后的数组做二次处理：

SELECT
  session_id,
  -- 获取会话的第一个访问页面
  (SELECT page_location FROM UNNEST(session_events) ORDER BY event_timestamp LIMIT 1) AS first_page,
  -- 获取会话内所有点击事件的页面地址
  ARRAY(SELECT page_location FROM UNNEST(session_events) WHERE event_name = 'click_url') AS click_pages,
  session_events
FROM (
  -- 嵌套前面的聚合查询
  SELECT
    CAST((SELECT COALESCE(value.int_value, value.float_value, value.double_value) FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING) AS session_id,
    ARRAY_AGG(STRUCT(
      event_name,
      event_timestamp,
      (SELECT COALESCE(value.string_value) FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
    ) ORDER BY event_timestamp) AS session_events
  FROM
    `digital-marketing-xxxxxx.analytics_xxxxxxx.events_intraday*`
  GROUP BY
    session_id
)

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