在BigQuery中为所有日期（含缺失日期）计算numbers_滚动平均值

现有如下间隔日期数据：

date_  numbers_
2021-10-05         1
2021-10-08         3
2021-10-11         5
2021-10-14         7
2021-10-17         9
2021-10-20        11
2021-10-23        13
2021-10-26        15
2021-10-29        17
2021-11-01        19

数据由以下BigQuery SQL生成：

select 
   date_, 
   numbers_
from (select generate_date_array('2021-10-05', '2021-11-01', interval 3 day) as date, generate_array(1, 20, 2) as numbers), 
unnest(date) as date_ with offset as pos1, unnest(numbers) as numbers_ with offset as pos2
where pos1 = pos2

需要生成包含2021-10-05至2021-11-01所有连续日期的结果，每个日期对应的avg是截至该日期所有numbers_的平均值（缺失日期沿用最近的累计平均），预期结果示例如下：

date_       avg
2021-10-05         1
2021-10-06         1
2021-10-07         1
2021-10-08         2
2021-10-09         2
2021-10-10         2
2021-10-11         3
...               ...

使用以下BigQuery SQL实现需求：

WITH original_data AS (
  -- 原始数据生成逻辑
  select 
     date_, 
     numbers_
  from (select generate_date_array('2021-10-05', '2021-11-01', interval 3 day) as date, generate_array(1, 20, 2) as numbers), 
  unnest(date) as date_ with offset as pos1, unnest(numbers) as numbers_ with offset as pos2
  where pos1 = pos2
),
all_dates AS (
  -- 生成所有连续日期
  SELECT date_
  FROM UNNEST(generate_date_array('2021-10-05', '2021-11-01', interval 1 day)) AS date_
),
cumulative_avg AS (
  -- 计算原始数据的累计平均值
  SELECT 
    date_,
    AVG(numbers_) OVER (ORDER BY date_ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg
  FROM original_data
)
-- 关联连续日期，填充缺失日期的平均值
SELECT 
  ad.date_,
  LAST_VALUE(ca.running_avg IGNORE NULLS) OVER (ORDER BY ad.date_) AS avg
FROM all_dates ad
LEFT JOIN cumulative_avg ca ON ad.date_ = ca.date_
ORDER BY ad.date_

1. original_data：复用原始代码生成带间隔的日期与对应numbers_数据。
2. all_dates：生成目标时间范围内的所有连续日期，间隔为1天。
3. cumulative_avg：通过窗口函数计算原始数据中每个日期的累计平均值，即截至当前日期所有numbers_的平均。
4. 最后关联连续日期和累计平均值，用LAST_VALUE(IGNORE NULLS)窗口函数填充缺失日期的平均值，确保非数据日期自动沿用最近的累计平均结果。

内容的提问来源于stack exchange，提问作者James Harrington