BigQuery计算各ID起止日期对应坐标点距离的SQL查询问题

问题原因 #

原写法中两个子查询仅通过ID关联，同ID下所有起始时间坐标会和所有结束时间坐标产生笛卡尔积，导致多组起止日期交叉匹配。

正确查询语句 #

SELECT
  t1.ID,
  t1.startdate,
  t2_start.Latitude AS start_lat,
  t2_start.Longitude AS start_lon,
  t1.enddate,
  t2_end.Latitude AS end_lat,
  t2_end.Longitude AS end_lon,
  ST_DISTANCE(
    ST_GEOGPOINT(t2_start.Longitude, t2_start.Latitude),
    ST_GEOGPOINT(t2_end.Longitude, t2_end.Latitude)
  ) AS distance
FROM `t1` AS t1
-- 关联起始日期的坐标
LEFT JOIN `t2` AS t2_start
  ON t1.ID = t2_start.ID
  AND t1.startdate = t2_start.Date
-- 关联结束日期的坐标
LEFT JOIN `t2` AS t2_end
  ON t1.ID = t2_end.ID
  AND t1.enddate = t2_end.Date
-- 过滤掉坐标缺失的行（可选，按需调整）
WHERE t2_start.Latitude IS NOT NULL 
  AND t2_end.Latitude IS NOT NULL

说明 #

• 直接以t1的每组起止日期为基础，分别关联两次t2获取对应起始、结束时间的坐标，从根源上避免交叉匹配问题
• ST_DISTANCE返回结果单位为米，若需要其他单位可自行转换

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