python - Bigquery 根据时间/位置数据获取速度(当前行上方/下方的行)
问题描述
我在 Bigquery 中有一个表格,其中包含 Nascar 驱动程序的跟踪数据(我正在从事的项目的虚拟数据)。x 和 y 坐标每秒取 10 次。表示当前capture_frame
帧,并且每个顺序capture_frame
应该相隔 100 毫秒,因为数据每 100 毫秒获取一次。
我想计算每个车手每圈的速度。我知道如何在 pandas 中做到这一点,但我认为这在 bigquery 中是可能的。为了计算速度,我查看之前的capture_frame
2 行和之后的 2 行,然后除以 epoch 时间的差异,这应该是 400 毫秒。
以下是一位车手在第一圈进行的一场比赛的一些捕捉帧示例。每圈有几百个捕捉帧,然后混合了 20 位车手,但如果我们只看一位车手/比赛/单圈,就更容易理解。
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| Race | Capture | Lap | Driver | … | X | Y | Epoch_time | Delta_dist | Curr_speed |
| | _frame | | | | | | | | |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| I500 | 1 | 1 | Logano | …. | 2.1 | 1 | 1552089720 | NULL | Null |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| I500 | 2 | 1 | Logano | … | 2.2 | 1.1 | 1552089820 | NULL | Null |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| I500 | 3 | 1 | Logano | … | 2.22 | 1.2 | 1552089920 | 2.265921446 | 0.005664804 |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| I500 | 4 | 1 | Logano | .. | 3.22 | 1.5 | 1552090020 | 3.124163888 | 0.00781041 |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| I500 | 5 | 1 | Logano | .. | 4.22 | 1.8 | 1552090120 | NULL | null |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
| I500 | 6 | 1 | Logano | .. | 5.22 | 1.9 | 1552090220 | NULL | null |
+------+---------+-----+--------+----+------+-----+------------+-------------+-------------+
第delta_dist
3 帧的计算方法是sqrt((4.22-2.1)^2 + (1.8-1)^2)/1
该curr_speed
数字除以 400。比赛的第一个/最后两个距离和速度将为空,因为没有先前的 x 或 y 坐标,这是可以的,因为实际上没有任何距离启动或停止 0.1 秒时的速度。
在 pandas 中我会这样做(这不是很好的代码,因为我只是让每个车手自己参加比赛):
#laps_per_race dictionary with num laps per race
for driver in driver_list:
for race in race_list:
driver_race_query = “SELECT * from nascar_xyz where driver={driver} and Race={race}”.format(driver=driver, race=race)
df_entire_race = client.query(driver_race_query).to_dataframe()
num_laps = laps_per_race[race]
for lap in num_laps:
#get subset of dataframe just for this lap
df = df_entire_race.loc[df_entire_race['Lap'] == lap]
df.sort_values(‘Epoch_time’, inplace=True)
df[‘prev_x’] = df[‘X’].shift(2)
df[‘next_x’] = df[‘X’].shift(-2)
df[‘prev_y’] = df[‘Y’].shift(2)
df[‘next_y’] = df[‘Y’].shift(-2)
#this is just distance function sqrt((x2-x1)^2 + (y2-y1)^2)
df['delta_dist'] = np.sqrt((df[‘X’].shift(-2) - df[‘X’].shift(2))**2 + (df[‘Y’].shift(-2) - df[‘Y’].shift(2))**2))
#400.0 is the time actual difference
df['Curr_speed'] = df['delta_dist']/400.0
我认为在我的 sql 查询中,我要么必须进行分组,要么进行分区,因为我想查看每场比赛driver_id
,然后是 lap(如果该抽象级别有意义的话)。也许为了速度和向前看 capture_frames 我可以用窗口(https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts)或称为滞后的东西来做一些事情,这似乎是等价的在.shift()
熊猫中。
解决方案
你在正确的道路上。我将获取在史泰登岛周围行驶的公共汽车数据集 - 我将通过查看它们的纬度、经度来使用地理距离:
WITH data AS (
SELECT bus, ST_GeogPoint(longitude, latitude) point
, PARSE_TIMESTAMP('%Y%m%d %H%M%S',FORMAT('%i %06d', day, time)) ts
FROM `fh-bigquery.mta_nyc_si.201410_bustime`
WHERE day=20141014
AND bus IN (7043, 7086, 7076, 2421, 7052, 7071)
)
SELECT *
FROM (
SELECT bus, ts, distance/time speed
FROM (
SELECT bus, ts
, ST_DISTANCE(point, LAG(point, 3) OVER(PARTITION BY bus ORDER BY ts)) distance
, TIMESTAMP_DIFF(ts, LAG(ts, 3) OVER(PARTITION BY bus ORDER BY ts), SECOND) time
FROM data
)
WHERE time IS NOT null
)
WHERE speed < 500
推荐阅读
- r - 更改下拉图标
- algorithm - 用 O(log n) 时间优化两个变量函数
- javascript - 根据表值更改文本颜色 Laravel
- excel - 使用 For 循环 VBA 选择多列和特定行
- node.js - Cloud Run 或 Kubernetes 上的 Firebase 实时数据库触发器,而不是 Cloud Functions
- python - 如何将触发绑定的 TKinter 按钮/标签传递给绑定调用的函数?
- java - 为什么我不能对 ArrayList Integer 列求和?
- c++ - 在 Windows10 上使用 MATLAB R2016b、MinGW64 和 mysql 从 c++ 编译 mex 文件
- c++ - C++ Lambda 开销
- node.js - 无法在 Linux 终端中安装 Vue CLI