如何用SQL高效获取BigQuery中行程数据的首尾点位信息？

你可以通过窗口函数或条件聚合的方式，仅用单次或两次表扫描完成需求，完全替代低效的循环遍历方案。以下是两种实用写法：

方法1：窗口函数标记首尾行（清晰直观） #

先分别筛选每个行程的最早和最晚记录，再通过行程ID关联结果：

WITH journey_start AS (
  SELECT
    journey_id,
    timestamp AS start_timestamp,
    latitude AS start_latitude,
    longitude AS start_longitude
  FROM (
    SELECT
      *,
      -- 按行程分组，时间升序排名，取第1条作为起点
      ROW_NUMBER() OVER (PARTITION BY journey_id ORDER BY timestamp ASC) AS rn
    FROM `你的项目ID.你的数据集ID.原表名`
  )
  WHERE rn = 1
),
journey_end AS (
  SELECT
    journey_id,
    timestamp AS end_timestamp,
    latitude AS end_latitude,
    longitude AS end_longitude
  FROM (
    SELECT
      *,
      -- 按行程分组，时间降序排名，取第1条作为终点
      ROW_NUMBER() OVER (PARTITION BY journey_id ORDER BY timestamp DESC) AS rn
    FROM `你的项目ID.你的数据集ID.原表名`
  )
  WHERE rn = 1
)
SELECT
  s.journey_id,
  s.start_timestamp,
  e.end_timestamp,
  s.start_latitude,
  s.start_longitude,
  e.end_latitude,
  e.end_longitude
FROM journey_start s
JOIN journey_end e ON s.journey_id = e.journey_id

方法2：条件聚合（单次表扫描，性能最优） #

通过聚合函数结合条件判断，在一次扫描中直接计算所有字段，效率最高：

SELECT
  journey_id,
  MIN(timestamp) AS start_timestamp,
  MAX(timestamp) AS end_timestamp,
  -- 取最早时间对应的纬度
  MAX(CASE WHEN timestamp = MIN(timestamp) THEN latitude END) AS start_latitude,
  -- 取最早时间对应的经度
  MAX(CASE WHEN timestamp = MIN(timestamp) THEN longitude END) AS start_longitude,
  -- 取最晚时间对应的纬度
  MAX(CASE WHEN timestamp = MAX(timestamp) THEN latitude END) AS end_latitude,
  -- 取最晚时间对应的经度
  MAX(CASE WHEN timestamp = MAX(timestamp) THEN longitude END) AS end_longitude
FROM `你的项目ID.你的数据集ID.原表名`
GROUP BY journey_id

注意事项 #

• 若同一个行程存在多条时间戳完全相同的首尾记录，MAX()会随机取其中一条；如果需要保留所有这类记录，可改用ARRAY_AGG()收集后处理。
• 替换SQL中的你的项目ID.你的数据集ID.原表名为实际的BigQuery表路径。

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