BigQuery中手机屏显进出记录的精准配对SQL实现

我有两张记录用户手机屏显进出的表：phone_enter记录进入时间，phone_leave记录离开时间及停留时长Extent。需要按UserID、Type和时间配对进出记录，匹配规则为离开时间与进入时间的差值和Extent的误差在±1秒内。核心难点是：当存在多条符合条件的进出记录时，需选取最早的可用进入记录、最晚的可用离开记录，且每条记录仅能使用一次。目前我的BigQuery SQL无法实现该逻辑，现提供数据、现有查询、当前结果及期望结果，寻求高效的优化方案，避免低效的循环实现。

数据示例 #

表：phone_enter #

表：phone_leave #

现有BigQuery查询 #

SELECT * 
FROM (
SELECT enter.UserID, enter.Type, enter.RecordedTime as enter_time,
       leave.RecordedTime as leave_time,
       leave.Extent, DATE_DIFF(leave.RecordedTime,enter.RecordedTime,SECOND) AS DvnExtent,
       DENSE_RANK() OVER (PARTITION BY enter.UserID, enter.Type
                          ORDER BY enter.RecordedTime) rnk_enter,
       DENSE_RANK() OVER (PARTITION BY leave.UserID, leave.Type,
                                      ((DATE_DIFF(leave.RecordedTime,enter.RecordedTime,SECOND) = leave.Extent-1) OR
                                       (DATE_DIFF(leave.RecordedTime,enter.RecordedTime,SECOND) = leave.Extent+1) OR
                                       (DATE_DIFF(leave.RecordedTime,enter.RecordedTime,SECOND) = leave.Extent))
                          ORDER BY leave.RecordedTime) rnk_leave
FROM   test.phone_enter enter
JOIN   test.phone_leave leave
ON     enter.UserID = leave.UserID
AND    enter.Type = leave.Type
AND   ((DATE_DIFF(leave.RecordedTime,enter.RecordedTime,SECOND) = leave.Extent-1) OR
       (DATE_DIFF(leave.RecordedTime,enter.RecordedTime,SECOND) = leave.Extent+1) OR
       (DATE_DIFF(leave.RecordedTime,enter.RecordedTime,SECOND) = leave.Extent))
)
WHERE rnk_enter = rnk_leave
ORDER BY UserID, Type, enter_time, leave_time

当前返回结果 #

期望结果 #

优化方案 #

针对该场景，推荐两种高效实现方式，均避免循环逻辑，适配BigQuery大数据量处理特性：

方案一：使用MATCH_RECOGNIZE实现序列匹配 #

利用BigQuery的MATCH_RECOGNIZE时间序列模式匹配能力，直接在合并的事件流中定位符合规则的进出对，确保每条记录仅匹配一次：

WITH combined_data AS (
  SELECT 
    UserID,
    Type,
    RecordedTime,
    'ENTER' AS event_type,
    NULL AS Extent
  FROM test.phone_enter
  UNION ALL
  SELECT 
    UserID,
    Type,
    RecordedTime,
    'LEAVE' AS event_type,
    Extent
  FROM test.phone_leave
),
sorted_events AS (
  SELECT *
  FROM combined_data
  ORDER BY UserID, Type, RecordedTime
)
SELECT 
  UserID,
  Type,
  enter_time,
  leave_time,
  Extent,
  DATE_DIFF(leave_time, enter_time, SECOND) AS DvnExtent
FROM sorted_events
MATCH_RECOGNIZE (
  PARTITION BY UserID, Type
  ORDER BY RecordedTime
  MEASURES
    A.RecordedTime AS enter_time,
    B.RecordedTime AS leave_time,
    B.Extent AS Extent
  PATTERN (A B)
  DEFINE
    A AS event_type = 'ENTER',
    B AS event_type = 'LEAVE' 
      AND ABS(DATE_DIFF(B.RecordedTime, A.RecordedTime, SECOND) - B.Extent) <= 1
      -- 确保当前离开记录未被更早的进入记录匹配
      AND NOT EXISTS (
        SELECT 1 FROM sorted_events prev
        WHERE prev.UserID = B.UserID
          AND prev.Type = B.Type
          AND prev.event_type = 'ENTER'
          AND prev.RecordedTime < A.RecordedTime
          AND ABS(DATE_DIFF(B.RecordedTime, prev.RecordedTime, SECOND) - B.Extent) <= 1
      )
      -- 确保当前进入记录未被更晚的离开记录匹配
      AND NOT EXISTS (
        SELECT 1 FROM sorted_events next
        WHERE next.UserID = A.UserID
          AND next.Type = A.Type
          AND next.event_type = 'LEAVE'
          AND next.RecordedTime > B.RecordedTime
          AND ABS(DATE_DIFF(next.RecordedTime, A.RecordedTime, SECOND) - next.Extent) <= 1
      )
)
ORDER BY UserID, Type, enter_time;

方案二：窗口函数+贪心匹配 #

通过窗口函数给进出记录排序，筛选每个进入记录的最晚有效离开、每个离开记录的最早有效进入，实现一对一无重复匹配：

WITH enter_ranked AS (
  SELECT 
    UserID,
    Type,
    RecordedTime AS enter_time,
    ROW_NUMBER() OVER (PARTITION BY UserID, Type ORDER BY RecordedTime) AS enter_seq
  FROM test.phone_enter
),
leave_ranked AS (
  SELECT 
    UserID,
    Type,
    RecordedTime AS leave_time,
    Extent,
    ROW_NUMBER() OVER (PARTITION BY UserID, Type ORDER BY RecordedTime) AS leave_seq
  FROM test.phone_leave
),
valid_pairs AS (
  SELECT 
    e.UserID,
    e.Type,
    e.enter_time,
    l.leave_time,
    l.Extent,
    DATE_DIFF(l.leave_time, e.enter_time, SECOND) AS DvnExtent,
    -- 标记当前离开记录对应的最早有效进入
    ROW_NUMBER() OVER (PARTITION BY l.UserID, l.Type, l.leave_seq ORDER BY e.enter_time) AS enter_match_rank,
    -- 标记当前进入记录对应的最晚有效离开
    ROW_NUMBER() OVER (PARTITION BY e.UserID, e.Type, e.enter_seq ORDER BY l.leave_time DESC) AS leave_match_rank
  FROM enter_ranked e
  JOIN leave_ranked l
    ON e.UserID = l.UserID
    AND e.Type = l.Type
    AND ABS(DATE_DIFF(l.leave_time, e.enter_time, SECOND) - l.Extent) <= 1
    AND e.enter_time <= l.leave_time
),
final_pairs AS (
  SELECT *
  FROM valid_pairs
  WHERE enter_match_rank = 1 AND leave_match_rank = 1
)
SELECT 
  UserID,
  Type,
  enter_time,
  leave_time,
  Extent,
  DvnExtent
FROM final_pairs
ORDER BY UserID, Type, enter_time;

方案说明 #

• 方案一逻辑直观，利用MATCH_RECOGNIZE原生的序列匹配能力，性能更优，适合超大规模数据集。
• 方案二基于基础窗口函数实现，兼容性强，无需依赖特定SQL特性，便于理解和调试。

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