如何基于不同行的聚合信息实现两个数据表的Join关联查询

原查询问题分析 #

你当前的查询存在两个核心问题：

• 仅行级匹配content字段，同一个Tote事件下的多条content会分别关联产生大量重复记录
• 缺少Date、时间戳维度的匹配过滤，会出现跨事件的错误匹配，且无法处理同content多事件的顺序匹配问题

解决方案思路 #

我们需要先将两个表中属于同一个Tote事件的content聚合为唯一标识，再结合时间顺序规则关联，即可得到预期结果，该方案在百万级数据下也有稳定性能：

1. 对两个表分别做分组聚合，以Date+Tote+对应时间戳作为分组键，将同组的content排序后拼接为统一字符串（或聚合为有序数组），同时取同组唯一的location值
2. 两个聚合后的子表通过Date+Tote+聚合后的content唯一标识关联，增加「结束时间晚于到达时间」的业务逻辑过滤，再通过窗口函数取每个到达事件对应的最早结束事件，避免多匹配问题

可直接运行的SQL示例（兼容绝大多数SQL引擎） #

WITH first_agg AS (
  SELECT 
    Date,
    Tote,
    TotearrivalTimestamp,
    -- 同组content排序后拼接为唯一键，避免顺序不同导致匹配失败
    GROUP_CONCAT(content ORDER BY content SEPARATOR '|') AS content_key,
    MAX(location) AS location_first_table
  FROM `first_table`
  GROUP BY Date, Tote, TotearrivalTimestamp
),
second_agg AS (
  SELECT 
    Date,
    Tote,
    ToteendingTimestamp,
    GROUP_CONCAT(content ORDER BY content SEPARATOR '|') AS content_key,
    MAX(location) AS location_second_table
  FROM `second_table`
  GROUP BY Date, Tote, ToteendingTimestamp
),
matched AS (
  SELECT 
    f.*,
    s.ToteendingTimestamp,
    s.location_second_table,
    -- 每个到达事件只取最早对应的结束事件
    ROW_NUMBER() OVER(PARTITION BY f.Date, f.Tote, f.TotearrivalTimestamp, f.content_key ORDER BY s.ToteendingTimestamp ASC) AS rn
  FROM first_agg f
  INNER JOIN second_agg s
  ON f.Date = s.Date 
  AND f.Tote = s.Tote 
  AND f.content_key = s.content_key
  AND s.ToteendingTimestamp > f.TotearrivalTimestamp
)
SELECT 
  Date,
  Tote,
  TotearrivalTimestamp,
  ToteendingTimestamp,
  location_first_table,
  location_second_table
FROM matched
WHERE rn = 1

如果使用支持数组类型的数据库（如BigQuery、PostgreSQL），可以用ARRAY_AGG(content ORDER BY content)代替字符串拼接，性能更高。

百万级数据性能优化建议 #

• 给两个表增加Date字段的分区，关联时只会匹配同日期的数据，可减少90%以上的扫描量
• 业务查询时提前限定需要的日期范围，进一步减少聚合计算的数据量
• 可给两个表预先建立Date+Tote的联合索引，提升分组和关联的效率

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