Блог

Предположим, что существует следующая база данных BigQuery:

В предоставленных данных есть флаг — trip_status , указывающий, были ли захвачены заданные координаты во время trip режима.

• trip_status изменение значения от false до true указывает на начало поездки.
• trip_status изменение значения от true до false указывает на окончание поездки.
• Все последовательные строки с trip_status = true являются строками, принадлежащими одной и той же поездке

Вопрос:

Есть ли способ в BigQuery извлечь отдельные поездки из такого набора данных? Может быть, как-то сгруппировать данные там, где trip_status есть флаг true , и вернуть в виде отдельных наборов данных?

Например, из заданных данных мне нужно извлечь что-то вроде:

Trip 1:

Поездка 2:

Или, что еще лучше, что-то вроде:

Рассмотрим следующий подход

 select id, trip_number,   array_agg(struct(latitude as origin_latitude, longitude as origin_longitude) order by timestamp limit 1)[offset(0)].*,  array_agg(struct(latitude as destination_latitude, longitude as destination_longitude) order by timestamp desc limit 1)[offset(0)].*,  st_makeline(array_agg(st_geogpoint(longitude, latitude) order by timestamp)) as polyline,  min(timestamp) as start_timestamp,  max(timestamp) as end_timestamp,  from (  select * except(trip_status, prev_status, next_status),   countif(trip_start_end = 'trip_start') over win trip_number  from (  select *,   case  when trip_status and not prev_status then 'trip_start'  when trip_status and not next_status then 'trip_end'  else ''  end trip_start_end  from (  select *,   ifnull(lag(trip_status) over win, false) prev_status,  ifnull(lead(trip_status) over win, false) next_status  from your_table   window win as (partition by id order by timestamp)  )  )  where trip_status  window win as (partition by id order by timestamp) ) group by id, trip_number   

если применить к образцам данных в y, наш вопрос — вывод

и в гео визуализации это показывает

Важным моментом, который следует учитывать, является то, что вы должны упорядочивать свои данные явно на основе временных меток. Потому что, если вы просто выберете свои данные из таблицы в BQ, это даст вам случайные строки. Так что, в случае (например, вашего), всегда делайте order by timestamp это .

Вот подход к решению этой проблемы. Во-первых, определите предыдущую trip_status по каждой строке из вашего упорядоченного набора данных ( lag ). Затем используйте предыдущую поездку и текущий статус поездки (в соответствии с вашей логикой), чтобы определить начальную и конечную точки поездки. Затем используйте эти строки для группировки значений, находящихся между ними.

 with formatted as (  select  asset_id,  lat,  lon,  ts,  trip_status,  first_value(flag) over (partition BY grp order by ts) as trip_id,  st_geogpoint(lon, lat) as geo_point  from (  select  asset_id,  lat,  lon,  ts,  trip_status,  flag,  sum(case when flag is null then 0 else 1 end) over (order by ts) as grp  from (  select  asset_id,  lat,  lon,  trip_status,  case  when previous_trip_status = false and current_trip_status = true then concat('START', '-gt;', cast(ts AS string))  when previous_trip_status = true and current_trip_status = false then concat('END', '-gt;', cast(ts AS string))  end as flag,  ts  from (  select  asset_id,  lat,  lon,  trip_status,  lag(trip_status) over (order by 1=1) as previous_trip_status,  trip_status as current_trip_status,  timestamp AS ts  from `mydataset.mytable`  )   )   )  where trip_status = true )  select   asset_id,   trip_id,   array_agg(geo_point)[safe_offset(0)] as origin,   array_reverse(array_agg(geo_point))[safe_offset(0)] as destination,   ST_MAKELINE(array_agg(geo_point order by ts)) as polyline ,  min(ts) as start_timestamp,  max(ts) as end_timestamp from formatted group by 1,2  

с визуализацией, как показано ниже