如何在BigQuery中筛选含eventCategory=x且eventAction=Click事件的会话？

要筛选出**存在单个事件同时满足eventCategory = 'x'且eventAction = 'Click'**的会话，核心是先精准定位这类会话，再基于它们统计指标。以下是两种可行的修改方案：

方案一：使用EXISTS子查询（推荐，性能更优） #

通过EXISTS子查询直接检查当前会话是否包含符合条件的事件，避免不必要的行展开重复计算：

SELECT
  device.deviceCategory AS device_category,
  device.operatingSystem AS operating_system,
  COUNT(DISTINCT CONCAT(fullvisitorid, CAST(visitstarttime AS string))) AS Sessions,
  COUNT(DISTINCT hits.`transaction`.transactionid) AS Transactions,
  COUNT(DISTINCT hits.`transaction`.transactionid) / COUNT(DISTINCT CONCAT(fullvisitorid, CAST(visitstarttime AS string))) AS Ecommerce_Conversion_Rate,
  (SUM(hits.TRANSACTION.transactionrevenue)/1000000)/NULLIF(COUNT(DISTINCT hits.TRANSACTION.transactionid), 0) AS avg_order_value
FROM
  `my_data_source` AS sessions,
  UNNEST(hits) AS hits
WHERE
  totals.visits = 1
  AND PARSE_DATE('%y%m%d', _TABLE_SUFFIX) BETWEEN "2022-12-02" AND "2022-12-04"
  AND device.deviceCategory != "tablet"
  -- 关键：筛选存在符合条件事件的会话
  AND EXISTS (
    SELECT 1
    FROM UNNEST(sessions.hits) AS inner_hits
    WHERE inner_hits.eventCategory = 'x' 
      AND inner_hits.eventAction = 'Click'
  )
GROUP BY
  device.deviceCategory, device.operatingSystem -- 修正GROUP BY，匹配SELECT中的非聚合字段
ORDER BY
  device.deviceCategory, device.operatingSystem

关键点说明： #

• EXISTS子查询针对每个会话，检查其包含的hits中是否有同时满足两个条件的事件，确保是同一事件符合要求，而非会话内不同事件分别满足条件。
• 给avg_order_value添加NULLIF，避免当Transactions为0时出现除以0的错误。
• 修正了GROUP BY子句，原SQL中SELECT包含device.operatingSystem但未在GROUP BY中声明，会导致语法错误。

方案二：先获取符合条件的会话，再关联统计 #

如果觉得子查询不够直观，可以先提取所有符合条件的会话ID，再关联原表计算指标：

WITH qualifying_sessions AS (
  SELECT
    CONCAT(fullvisitorid, CAST(visitstarttime AS string)) AS session_id,
    device.deviceCategory,
    device.operatingSystem
  FROM `my_data_source` AS sessions,
    UNNEST(hits) AS hits
  WHERE
    totals.visits = 1
    AND PARSE_DATE('%y%m%d', _TABLE_SUFFIX) BETWEEN "2022-12-02" AND "2022-12-04"
    AND device.deviceCategory != "tablet"
    AND hits.eventCategory = 'x' 
    AND hits.eventAction = 'Click'
  GROUP BY session_id, device.deviceCategory, device.operatingSystem
)

SELECT
  qs.deviceCategory AS device_category,
  qs.operatingSystem AS operating_system,
  COUNT(DISTINCT qs.session_id) AS Sessions,
  COUNT(DISTINCT hits.`transaction`.transactionid) AS Transactions,
  COUNT(DISTINCT hits.`transaction`.transactionid) / COUNT(DISTINCT qs.session_id) AS Ecommerce_Conversion_Rate,
  (SUM(hits.TRANSACTION.transactionrevenue)/1000000)/NULLIF(COUNT(DISTINCT hits.TRANSACTION.transactionid), 0) AS avg_order_value
FROM qualifying_sessions AS qs
JOIN `my_data_source` AS sessions
  ON qs.session_id = CONCAT(sessions.fullvisitorid, CAST(sessions.visitstarttime AS string))
JOIN UNNEST(sessions.hits) AS hits
GROUP BY
  qs.deviceCategory, qs.operatingSystem
ORDER BY
  qs.deviceCategory, qs.operatingSystem

关键点说明： #

• 先通过CTEqualifying_sessions筛选出所有包含目标事件的会话，再与原表关联统计交易相关指标。
• 这种方式逻辑清晰，适合需要多次复用符合条件会话的场景，但性能略逊于EXISTS方案（取决于数据量）。

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