基于BigQuery计算NASCAR车手单圈速度：时间/位置数据实现

Got it, let's translate your Pandas logic into clean, efficient BigQuery SQL. The core idea is using window functions (specifically LAG and LEAD) to replicate the shift behavior from Pandas, paired with partitioning to group data exactly how you did with your nested loops.

Quick Logic Recap #

In your Pandas code, you're:

1. Isolating data for each driver, race, and lap
2. Sorting frames by timestamp
3. Shifting X/Y values 2 rows back and 2 rows forward
4. Calculating the distance between those shifted positions, then dividing by 400ms to get speed

BigQuery can do all this in a single query (no loops required!)—here's how:

Full BigQuery SQL Query #

WITH sorted_lap_data AS (
  -- First, prepare and sort data within each driver/race/lap group
  SELECT
    赛事,
    捕获帧,
    圈数,
    车手,
    X,
    Y,
    时间戳,
    -- Grab X/Y from 2 frames earlier (matches df['X'].shift(2))
    LAG(X, 2) OVER (PARTITION BY 赛事, 车手, 圈数 ORDER BY 时间戳) AS prev_x,
    LAG(Y, 2) OVER (PARTITION BY 赛事, 车手, 圈数 ORDER BY 时间戳) AS prev_y,
    -- Grab X/Y from 2 frames later (matches df['X'].shift(-2))
    LEAD(X, 2) OVER (PARTITION BY 赛事, 车手, 圈数 ORDER BY 时间戳) AS next_x,
    LEAD(Y, 2) OVER (PARTITION BY 赛事, 车手, 圈数 ORDER BY 时间戳) AS next_y
  FROM
    `your-project.your-dataset.nascar_xyz` -- Replace with your actual table path
)
SELECT
  赛事,
  捕获帧,
  圈数,
  车手,
  X,
  Y,
  时间戳,
  -- Calculate distance difference (matches df['delta_dist'])
  CASE
    WHEN prev_x IS NOT NULL AND next_x IS NOT NULL THEN
      SQRT(POW(next_x - prev_x, 2) + POW(next_y - prev_y, 2))
    ELSE NULL
  END AS 距离差,
  -- Calculate current speed (matches df['Curr_speed'])
  CASE
    WHEN prev_x IS NOT NULL AND next_x IS NOT NULL THEN
      SQRT(POW(next_x - prev_x, 2) + POW(next_y - prev_y, 2)) / 400.0
    ELSE NULL
  END AS 当前速度
FROM
  sorted_lap_data
ORDER BY
  赛事, 车手, 圈数, 时间戳;

Key Details Explained #

• Partitioning: PARTITION BY 赛事, 车手, 圈数 groups data exactly like your nested loops—each driver's race/lap data is treated as an independent set.
• Ordering: ORDER BY 时间戳 ensures frames are processed in chronological order, just like df.sort_values('Epoch_time').
• LAG/LEAD Functions:
  • LAG(X, 2) pulls the X value from 2 rows earlier in the partition (equivalent to Pandas' shift(2)).
  • LEAD(X, 2) pulls the X value from 2 rows later in the partition (equivalent to Pandas' shift(-2)).
• NULL Handling: The CASE statements only compute distance/speed when both the 2-previous and 2-next frames exist—this automatically leaves the first 2 and last 2 frames of each lap as NULL, matching your expected output.

Why This Beats Looped Pandas #

• Efficiency: BigQuery processes all groups in parallel, no need to iterate through drivers/races/laps one by one.
• Scalability: Works seamlessly with massive datasets (BigQuery's sweet spot), whereas Pandas might choke on large race data volumes.
• Maintainability: A single query is far easier to tweak and debug than nested loops.

内容的提问来源于stack exchange，提问作者H. Rose