BigQuery多表左连接优化：获取右表大于左表值的最小匹配行

在BigQuery中，需要将Table.Users表与多个动作表（如Table.Action1）做LEFT JOIN，为Users表的每条记录匹配该记录日期之后最早发生的对应动作记录。之前用ROW_NUMBER加子查询实现，但关联5个动作表时查询代码过于冗长，不方便团队同事审核。另外因为要保留动作表的完整关联信息，无法使用聚合函数简化。

示例数据 #

Table.Users #

| User_id | Date |
| 1       | 1    |
| 1       | 2    |
| 1       | 3    |
| 1       | 4    |
| 1       | 5    |
| 1       | 6    |

Table.Action1 #

| User_id | Date | Action.id |
| 1       | 1    | 1         |
| 1       | 2    | 2         |
| 1       | 5    | 3         |
| 1       | 6    | 4         |

理想结果 #

| User_id | Date | Action1.Date | Action1.id |
| 1       | 1    | 2            | 2          |
| 1       | 2    | 5            | 3          |
| 1       | 3    | 5            | 3          |
| 1       | 4    | 5            | 3          |
| 1       | 5    | 6            | 4          |
| 1       | 6    |              |            |

简洁实现方案：采用LATERAL JOIN #

BigQuery支持的LATERAL JOIN可以针对主表每行数据，关联动作表并筛选出符合条件的最早记录，每个动作表的逻辑独立，代码结构清晰，大幅减少冗余，方便团队审核。

单动作表实现代码 #

SELECT
  u.User_id,
  u.Date,
  a1.Date AS Action1_Date,
  a1.Action.id AS Action1_id
FROM Table.Users u
LEFT JOIN LATERAL (
  -- 筛选当前用户、当前日期之后的最早动作
  SELECT Date, Action.id
  FROM Table.Action1
  WHERE User_id = u.User_id
    AND Date > u.Date
  ORDER BY Date ASC
  LIMIT 1
) a1 ON TRUE

多动作表扩展写法 #

关联多个动作表时，只需依次添加LEFT JOIN LATERAL块即可，逻辑一目了然：

SELECT
  u.User_id,
  u.Date,
  -- 动作1字段
  a1.Date AS Action1_Date,
  a1.Action.id AS Action1_id,
  -- 动作2字段
  a2.Date AS Action2_Date,
  a2.Action.id AS Action2_id,
  -- 按需添加其他动作表字段
FROM Table.Users u
-- 关联动作1表
LEFT JOIN LATERAL (
  SELECT Date, Action.id
  FROM Table.Action1
  WHERE User_id = u.User_id
    AND Date > u.Date
  ORDER BY Date ASC
  LIMIT 1
) a1 ON TRUE
-- 关联动作2表
LEFT JOIN LATERAL (
  SELECT Date, Action.id
  FROM Table.Action2
  WHERE User_id = u.User_id
    AND Date > u.Date
  ORDER BY Date ASC
  LIMIT 1
) a2 ON TRUE
-- 继续添加其他动作表的LATERAL JOIN

方案优势 #

• 模块化结构：每个动作表的关联逻辑独立成块，便于单独修改、排查问题，同事审核时快速定位对应部分。
• 无冗余嵌套：无需多层子查询或ROW_NUMBER分组筛选，代码简洁直接。
• 保留完整信息：LATERAL子查询可返回动作表任意需要的字段，满足保留关联详情的要求。

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