如何在BigQuery中筛选存在IN事件无OUT事件的Visitor-Host对

要准确找出当前在楼宇内的访客-受访公司配对，核心是以Visitor+Host作为唯一判断单元，而不是单独以Visitor为维度。以下是两种高效的实现方案：

方案一：取每个配对的最新事件判断 #

通过窗口函数定位每个访客-受访公司组合的最新事件记录，只要最新事件是IN，就说明该配对当前处于在楼状态：

WITH latest_visitor_host_events AS (
  SELECT
    Visitor,
    Host,
    Event,
    -- 按访客-受访公司分组，给每条记录按时间倒序排名
    ROW_NUMBER() OVER (PARTITION BY Visitor, Host ORDER BY Time DESC) AS event_rank
  FROM
    `your-project.your-dataset.visitor_records` -- 替换成你的表路径
)
SELECT
  Visitor,
  Host
FROM
  latest_visitor_host_events
WHERE
  event_rank = 1 -- 只保留每个配对的最新事件
  AND Event = 'IN'; -- 筛选最新事件为进入的配对

方案二：统计配对的进出次数差 #

如果同一个访客可能多次访问同一受访公司（多次IN/OUT），可以通过统计每个配对的IN和OUT次数差来判断：

SELECT
  Visitor,
  Host
FROM
  `your-project.your-dataset.visitor_records`
GROUP BY
  Visitor,
  Host
HAVING
  -- 统计IN的次数减去OUT的次数大于0，说明存在未匹配的进入记录
  COUNT(CASE WHEN Event = 'IN' THEN 1 END) > COUNT(CASE WHEN Event = 'OUT' THEN 1 END);

为什么之前的方法会出错？ #

之前仅按Visitor维度筛选时，会把访客的所有进出记录混在一起统计。比如Carla曾访问HostA并完成IN+OUT，但同时访问HostB只做了IN操作，按单个Visitor统计会认为她的总进出次数相等，从而错误排除她和HostB的有效在楼配对。而按Visitor+Host配对统计，就能精准区分不同受访公司的访问状态。

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