如何在BigQuery中筛选同一会话内完成两个指定事件的用户？

原SQL返回空结果的核心原因是逻辑错误：你要求同一条hit记录的eventLabel同时等于label1和label2，但单个事件的标签只能是一个值，这种矛盾的条件自然查不到数据。要筛选同一会话内完成两个指定事件的用户，得先识别出包含这两个事件的会话，再提取对应数据。

方案一：分组筛选会话后关联数据 #

先筛选出符合事件类别、动作的记录，按会话分组后筛选出同时包含两个标签的会话，最后关联回原始数据获取详细信息：

WITH qualified_sessions AS (
  SELECT 
    sessionid,
    clientid
  FROM `table_name`, UNNEST(hits) AS hits
  WHERE _TABLE_SUFFIX BETWEEN '20230220' AND '20230220'
    AND hits.eventInfo.eventCategory = 'category'
    AND hits.eventInfo.eventAction = 'click'
    AND hits.eventInfo.eventLabel IN ('label1', 'label2')
  GROUP BY sessionid, clientid
  HAVING COUNT(DISTINCT hits.eventInfo.eventLabel) = 2
)

SELECT
  t.date,
  hits.eventInfo.eventCategory,
  hits.eventInfo.eventAction,
  hits.eventInfo.eventLabel,
  t.sessionid AS session,
  t.clientid,
  hits.hitId AS hits
FROM `table_name` t, UNNEST(t.hits) AS hits
JOIN qualified_sessions qs 
  ON t.sessionid = qs.sessionid AND t.clientid = qs.clientid
WHERE _TABLE_SUFFIX BETWEEN '20230220' AND '20230220'
  AND hits.eventInfo.eventCategory = 'category'
  AND hits.eventInfo.eventAction = 'click'
  AND hits.eventInfo.eventLabel IN ('label1', 'label2')

方案二：用窗口函数标记符合条件的会话 #

通过窗口函数统计每个会话内符合条件的标签数量，直接筛选出目标记录：

SELECT
  date,
  eventCategory,
  eventAction,
  eventLabel,
  sessionid AS session,
  clientid,
  hitId AS hits
FROM (
  SELECT
    t.date,
    hits.eventInfo.eventCategory,
    hits.eventInfo.eventAction,
    hits.eventInfo.eventLabel,
    t.sessionid,
    t.clientid,
    hits.hitId,
    COUNT(DISTINCT hits.eventInfo.eventLabel) OVER (PARTITION BY t.sessionid, t.clientid) AS label_count
  FROM `table_name` t, UNNEST(t.hits) AS hits
  WHERE _TABLE_SUFFIX BETWEEN '20230220' AND '20230220'
    AND hits.eventInfo.eventCategory = 'category'
    AND hits.eventInfo.eventAction = 'click'
    AND hits.eventInfo.eventLabel IN ('label1', 'label2')
)
WHERE label_count = 2

额外注意 #

原SQL存在拼写错误：categoty需改为category，lable需改为label，且最后一个引号未闭合，这些细节也会影响查询结果。

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