# #sql #google-bigquery
#sql #google-bigquery
Вопрос:
Предположим, что существует следующая база данных BigQuery:
идентификатор актива | широта | долгота | trip_status | отметка времени |
---|---|---|---|---|
2 | 52.1052016 | 10.141829999999999 | ложный | 1638274080 |
2 | 52.10512 | 10.1420266 | ложный | 1638274081 |
2 | 52.104774899999995 | 10.1427066 | истинный | 1638274085 |
2 | 52.1044833 | 10.1431966 | истинный | 1638274088 |
2 | 52.104156599999996 | 10.143821599999999 | истинный | 1638274092 |
2 | 52.10398 | 10.1441433 | истинный | 1638274094 |
2 | 52.1038016 | 10.1444783 | истинный | 1638274096 |
2 | 52.1036183 | 10.144823299999999 | истинный | 1638274098 |
2 | 52.1034333 | 10.1451783 | истинный | 1638274100 |
2 | 52.1032483 | 10.1455383 | ложный | 1638274102 |
2 | 52.1030533 | 10.145886599999999 | истинный | 1638274104 |
2 | 52.1028666 | 10.146175 | истинный | 1638274106 |
2 | 52.10279 | 10.1463266 | истинный | 1638274108 |
2 | 52.1026616 | 10.1466566 | истинный | 1638274110 |
2 | 52.102464999999995 | 10.147016599999999 | истинный | 1638274112 |
2 | 52.102215 | 10.1474083 | истинный | 1638274114 |
2 | 52.101968299999996 | 10.147795 | истинный | 1638274116 |
2 | 52.101756599999995 | 10.148195 | ложный | 1638274117 |
2 | 52.101538299999994 | 10.14864 | ложный | 1638274119 |
2 | 52.1013583 | 10.149076599999999 | ложный | 1638274121 |
В предоставленных данных есть флаг — trip_status
, указывающий, были ли захвачены заданные координаты во время trip
режима.
trip_status
изменение значения отfalse
доtrue
указывает на начало поездки.trip_status
изменение значения отtrue
доfalse
указывает на окончание поездки.- Все последовательные строки с
trip_status = true
являются строками, принадлежащими одной и той же поездке
Вопрос:
Есть ли способ в BigQuery извлечь отдельные поездки из такого набора данных? Может быть, как-то сгруппировать данные там, где trip_status
есть флаг true
, и вернуть в виде отдельных наборов данных?
Например, из заданных данных мне нужно извлечь что-то вроде:
Trip 1:
asset_id | latitude | longitude | trip_status | timestamp |
---|---|---|---|---|
2 | 52.104774899999995 | 10.1427066 | true | 1638274085 |
2 | 52.1044833 | 10.1431966 | истинный | 1638274088 |
2 | 52.104156599999996 | 10.143821599999999 | истинный | 1638274092 |
2 | 52.10398 | 10.1441433 | истинный | 1638274094 |
2 | 52.1038016 | 10.1444783 | истинный | 1638274096 |
2 | 52.1036183 | 10.144823299999999 | истинный | 1638274098 |
2 | 52.1034333 | 10.1451783 | истинный | 1638274100 |
Поездка 2:
идентификатор актива | широта | долгота | trip_status | отметка времени |
---|---|---|---|---|
2 | 52.1030533 | 10.145886599999999 | истинный | 1638274104 |
2 | 52.1028666 | 10.146175 | истинный | 1638274106 |
2 | 52.10279 | 10.1463266 | истинный | 1638274108 |
2 | 52.1026616 | 10.1466566 | истинный | 1638274110 |
2 | 52.102464999999995 | 10.147016599999999 | истинный | 1638274112 |
2 | 52.102215 | 10.1474083 | истинный | 1638274114 |
2 | 52.101968299999996 | 10.147795 | истинный | 1638274116 |
Или, что еще лучше, что-то вроде:
ряд | идентификатор актива | происхождение.широта | происхождение.долгота | место назначения.широта | место назначения.долгота | полилиния | start_timestamp | end_timestamp |
---|---|---|---|---|---|---|---|---|
0 | 2 | 52.104774899999995 | 10.1427066 | 52.1034333 | 10.1451783 | ST_MAKELINE со всех координат поездки | 1638274085 | 1638274100 |
1 | 2 | 52.1030533 | 10.145886599999999 | 52.101968299999996 | 10.147795 | ST_MAKELINE со всех координат поездки | 1638274104 | 1638274116 |
Комментарии:
1. Похоже, у вас нет ничего, на чем вы могли бы совершать групповые поездки. Является ли это изменением статуса trip_status с ложного на истинное, которое определяет поездки?
2. как вы можете отличить поездки друг от друга ? какова была ваша логика, чтобы разделить эти две поездки?
3.
trip_status
изменение сfalse
наtrue
указывает на начало поездки. И изменение значения отtrue
доfalse
указывает на конец поездки. Я выделил строки сtrip_status = false
исходным набором данных для лучшей видимости. Позже в вопросе я разделил эти поездки в соответствии соtrip_status
стоимостью.
Ответ №1:
Рассмотрим следующий подход
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, наш вопрос — вывод
и в гео визуализации это показывает
Ответ №2:
Важным моментом, который следует учитывать, является то, что вы должны упорядочивать свои данные явно на основе временных меток. Потому что, если вы просто выберете свои данные из таблицы в 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
с визуализацией, как показано ниже
Комментарии:
1. Извините, пришлось исправить
st_geogpoint(lat, lon)
st_geogpoint(lon, lat)
, иначе поездка была где-то в Аравийском море :o) Также добавлена визуализация, чтобы проиллюстрировать дополнительное преимущество наличия места отправления и назначения в качестве геопунктов …2. Спасибо @MikhailBerlyant
3. следует ли использовать наиболее вложенное выражение
order by ts
, а неorder by 1=1
слишком?4. ДА. Самое сокровенное можно использовать
order by timestamp
.