如何在Google BigQuery中用UNNEST函数正确统计会话数？

问题背景 #

需要统计带有指定参数的会话数量，但现有SQL执行后结果过多，推测是UNNEST函数的使用方式导致问题。

现有SQL语句 #

SELECT 
    event_date,
    event_name,
    device.category AS device,
    user_pseudo_id AS total_users,
    (SELECT value FROM UNNEST(event_params) WHERE key='source').string_value AS source,
    (SELECT value FROM UNNEST(event_params) WHERE key='medium').string_value AS medium,
    (SELECT value FROM UNNEST(event_params) WHERE key='campaign').string_value AS campaign,
    (SELECT value FROM UNNEST(event_params) WHERE key='page_title').string_value AS page_title,
    (SELECT value FROM UNNEST(event_params) WHERE key='page_location').string_value AS page_location,
    (SELECT value FROM UNNEST(event_params) WHERE key='ga_session_id').int_value AS ga_session_id,
    COUNT(distinct CONCAT(user_pseudo_id,(SELECT value.int_value FROM unnest(event_params) WHERE key = 'ga_session_id'))) as sessions,
    COUNT(*) AS event_count
FROM 
   `analytics_375018211.events_*`
GROUP BY
    event_date,
    event_name,
    device,
    page_title,
    page_location,
    source,
    medium,
    campaign,
    total_users,
    ga_session_id

event_params列示例 #

{
"event_params": [{
"key": "ga_session_id",
"value": {
"string_value": null,
"int_value": "1714136265",
"float_value": null,
"double_value": null
}
}, {
"key": "campaign",
"value": {
"string_value": "campaign",
"int_value": null,
"float_value": null,
"double_value": null
}
}, {
"key": "ga_session_number",
"value": {
"string_value": null,
"int_value": "1",
"float_value": null,
"double_value": null
}
}, {
"key": "page_title",
"value": {
"string_value": "title",
"int_value": null,
"float_value": null,
"double_value": null
}
}, {
"key": "page_referrer",
"value": {
"string_value": "/",
"int_value": null,
"float_value": null,
"double_value": null
}
}, {
"key": "source",
"value": {
"string_value": "source",
"int_value": null,
"float_value": null,
"double_value": null
}
}, {
"key": "medium",
"value": {
"string_value": "medium",
"int_value": null,
"float_value": null,
"double_value": null
}
}, {
"key": "page_location",
"value": {
"string_value": "/",
"int_value": null,
"float_value": null,
"double_value": null
}
}]
}

问题分析 #

现有SQL的核心问题是GROUP BY包含了事件级字段（如event_name、page_title）和会话ID（ga_session_id），导致每个会话下的不同事件被单独分组，结果行数被事件数量放大。同时，会话数的计算逻辑重复且依赖分组后的字段，进一步导致统计不准确。

修正后的SQL #

WITH event_data AS (
  SELECT 
    event_date,
    event_name,
    device.category AS device,
    user_pseudo_id,
    -- 统一提取所需参数，避免重复UNNEST
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='source') AS source,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='medium') AS medium,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='campaign') AS campaign,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='page_title') AS page_title,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='page_location') AS page_location,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id') AS ga_session_id
  FROM 
    `analytics_375018211.events_*`
  -- 过滤掉无会话ID的记录，确保统计的是有效会话
  WHERE (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id') IS NOT NULL
)
SELECT 
  event_date,
  -- 若不需要按事件名称分组，可移除event_name
  event_name,
  device,
  source,
  medium,
  campaign,
  page_title,
  page_location,
  -- 按用户+会话ID去重统计会话数
  COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id)) AS sessions,
  -- 统计该维度下的总事件数
  COUNT(*) AS event_count,
  -- 统计该维度下的独立用户数
  COUNT(DISTINCT user_pseudo_id) AS total_users
FROM event_data
GROUP BY 
  event_date,
  event_name,
  device,
  source,
  medium,
  campaign,
  page_title,
  page_location;

关键修正点 #

1. 用CTE统一提取参数：避免重复执行UNNEST操作，提升查询效率，同时清晰分离数据提取和聚合逻辑。
2. 调整分组维度：移除GROUP BY中的user_pseudo_id和ga_session_id，改为通过聚合函数计算去重的会话数，避免事件级字段导致结果膨胀。
3. 过滤无效会话：添加WHERE条件排除无ga_session_id的记录，确保统计的是有效会话。
4. 优化统计逻辑：明确区分会话数、事件数和独立用户数，统计结果更精准。

内容的提问来源于stack exchange，提问作者Юлия Радионова