BigQuery同表内按location分组查找每行最近邻并计算距离的方法

针对3000万行量级的最近邻计算需求，不要使用全连接，采用BigQuery内置的空间索引优化+窗口函数的方案即可实现，核心逻辑如下：

• 先将经纬度转换为BigQuery支持的地理点类型，方便调用空间函数
• 自连接时仅关联同location分组的点，同时用ST_DWITHIN按距离阈值过滤掉不可能成为最近邻的点对，触发BigQuery空间索引优化，避免生成全量笛卡尔积
• 用QUALIFY窗口函数筛选每个点对应的最小距离结果

参考SQL #

WITH points AS (
  -- 预处理原表，生成地理点字段
  SELECT 
    location,
    address,
    latitude,
    longitude,
    ST_GEOGPOINT(longitude, latitude) AS geog
  FROM `你的项目名.你的数据集名.你的表名`
)
SELECT 
  p1.location,
  p1.address,
  p1.latitude,
  p1.longitude,
  -- ST_DISTANCE返回结果单位为米，保留两位小数可按需调整
  ROUND(ST_DISTANCE(p1.geog, p2.geog), 2) AS distance
  -- 如果需要返回最近邻的地址，可取消注释下一行
  -- , p2.address AS nearest_neighbor_address
FROM points p1
INNER JOIN points p2
  ON p1.location = p2.location
  -- 排除自己和自己匹配
  AND p1.address != p2.address
  -- 距离阈值可根据业务场景调整，单位为米，阈值越小计算性能越高
  AND ST_DWITHIN(p1.geog, p2.geog, 50000)
-- 取每个点距离最小的匹配结果
QUALIFY ROW_NUMBER() OVER (PARTITION BY p1.location, p1.address ORDER BY ST_DISTANCE(p1.geog, p2.geog) ASC) = 1

性能优化说明 #

• 如果你的location分组内的点数普遍小于1000，即使不加ST_DWITHIN也能正常运行，因为分组内的笛卡尔积数据量可控
• 若存在部分大分组（单分组超过10万点），可以针对大分组单独调整ST_DWITHIN阈值，或者拆分分组分批处理
• 经纬度字段建议提前存为GEOGRAPHY类型，不需要每次查询动态生成，可以进一步提升性能

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