如何在Google BigQuery中基于trip_status标识提取坐标数据集独立行程

完全可以在BigQuery中实现你要求的独立行程提取能力，以下是具体实现逻辑和可直接复用的代码：

核心实现思路 #

• 先对每个资产（asset_id）的时序数据按时间戳升序排序
• 用窗口函数判断当前行的trip_status和上一行是否发生变化，标记行程分界点
• 对连续trip_status=true的行分配统一的行程ID，用于后续关联和聚合
• 基于行程ID可以分别输出明细数据和聚合统计结果

WITH trip_boundary AS (
  SELECT
    *,
    -- 标记是否是新行程的起点：上一行trip_status为false/无初始值，当前行为true
    IF(
      trip_status = true 
      AND LAG(trip_status, 1, false) OVER (PARTITION BY asset_id ORDER BY timestamp) = false,
      1, 0
    ) AS is_new_trip
  FROM `你的项目ID.你的数据集名.你的坐标时序表名`
),
trip_with_id AS (
  SELECT
    *,
    -- 对同一个资产的新行程标记累加，生成全局唯一行程ID
    CONCAT(asset_id, '-', SUM(is_new_trip) OVER (PARTITION BY asset_id ORDER BY timestamp)) AS trip_id
  FROM trip_boundary
  -- 此处过滤掉非行程时段数据，有全量数据分析需求可删除该行
  WHERE trip_status = true
)

1. 行程明细数据集获取 #

直接查询上面生成的trip_with_id公共表表达式即可，每条坐标数据都带有归属的行程ID，可按需求筛选特定资产、特定时段的行程明细：

SELECT * FROM trip_with_id ORDER BY asset_id, trip_id, timestamp

2. 行程聚合结果获取 #

基于行程ID分组聚合即可得到你需要的所有聚合字段：

SELECT
  asset_id,
  trip_id,
  -- 起点经纬度
  FIRST_VALUE(latitude) OVER w AS start_latitude,
  FIRST_VALUE(longitude) OVER w AS start_longitude,
  -- 终点经纬度
  LAST_VALUE(latitude) OVER w AS end_latitude,
  LAST_VALUE(longitude) OVER w AS end_longitude,
  -- 生成行程路径折线
  ST_MAKELINE(ARRAY_AGG(ST_GEOGPOINT(longitude, latitude) ORDER BY timestamp)) AS trip_path,
  -- 行程起止时间
  MIN(timestamp) AS trip_start_time,
  MAX(timestamp) AS trip_end_time,
  -- 可选扩展字段：行程点数量、行程时长
  COUNT(*) AS trip_point_count,
  TIMESTAMP_DIFF(MAX(timestamp), MIN(timestamp), SECOND) AS trip_duration_second
FROM trip_with_id
GROUP BY asset_id, trip_id
WINDOW w AS (PARTITION BY asset_id, trip_id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY asset_id, trip_start_time

注意：如果同一资产存在时序数据断档超过你预期的行程间隔的情况，可以在行程ID生成逻辑中额外增加时间差判断，避免把间隔太久的连续true数据合并为同一段行程。

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