BigQuery窗口函数中基于当前行值计算赛事标准化时间的均值与标准差

需要计算赛事标准化时间的均值（AVG）和标准差（STDDEV），步骤如下：

• 先通过完赛时间（finish_time）除以距离（distance）计算每位参赛者的配速（pace）
• 基于当前行的距离对均值和标准差进行标准化，例如计算第5行（不含自身）的标准化均值：

  AVG(Pace * current_distance) = (Pace₁distance₅ + Pace₂distance₅ + Pace₃distance₅ + Pace₄distance₅)/4
  其中 Pace₁ = finish_time₁ / distance₁

尝试过两种方法但存在问题：

1. AVG(pace * distance) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)：用的是每行自身的distance而非当前行的distance，不符合需求
2. (AVG(finishing_time / distance) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING))*distance：均值计算正确，但不确定如何应用到标准差（STDDEV）计算

均值计算 #

你的第二种方法是完全正确的，从数学角度推导：
AVG(Paceᵢ * current_distance) = current_distance * AVG(Paceᵢ)
因为current_distance是当前行的固定值，可以从平均值运算中提取出来，所以直接用配速的均值乘以当前行距离即可得到标准化均值。

标准差计算 #

标准差同样可以利用类似的数学性质：对于正数常数a，STDDEV(a*X) = a * STDDEV(X)，由于赛事距离必然为正数，因此可以直接用配速的标准差乘以当前行距离，得到标准化后的标准差：

STDDEV(finishing_time / distance) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) * distance AS stddev_normalized_finishing_time

完整SQL示例 #

SELECT
    date,
    finish_time,
    distance,
    finish_time / distance AS pace,
    -- 标准化均值
    (AVG(finish_time / distance) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)) * distance AS avg_normalized_finishing_time,
    -- 标准化标准差
    STDDEV(finish_time / distance) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) * distance AS stddev_normalized_finishing_time
FROM
    race_results
ORDER BY
    date;

内容的提问来源于stack exchange，提问作者J-t