Timeseries Weighted Average in BigQuery

weighted_average_bigquery.sql

CREATE TEMP FUNCTION WeightedElement(element ANY TYPE, powered ANY TYPE, alpha ANY TYPE) AS (
    # weighted average formula
    element * alpha * POWER(1-alpha, powered)
);
CREATE TEMP FUNCTION ElementAlphaPower(elements ANY TYPE, powers ANY TYPE, alpha ANY TYPE) AS (
    # array of history
  ARRAY(SELECT WeightedElement(x, powers[OFFSET(off)], alpha)  FROM UNNEST(elements) AS x WITH OFFSET off ORDER BY off)
);

CREATE TEMP FUNCTION ArraySum(elements ANY TYPE) AS (
    # array of history
  (select SUM(x) FROM UNNEST(elements) AS x WITH OFFSET off)
);

CREATE TEMP FUNCTION WeightedAverage(history ANY TYPE, alpha ANY TYPE) AS (
    # array of history, alpha to finetune, l0 is defined as the first value observed (couldd be finetuned), 
  ArraySum(ElementAlphaPower(history, GENERATE_ARRAY(ARRAY_LENGTH(history)-1, 0, -1), alpha)) + POWER(1-alpha, ARRAY_LENGTH(history))*history[OFFSET(0)]
);

with preceding_values as (
SELECT 
datetime,
signal_with_noise,
lag(signal_with_noise) over (order by datetime asc ) as naive_previous_value,
AVG(signal_with_noise) over (order by datetime asc rows between 720 PRECEDING and 1 PRECEDING) as naive_weighted_average,
ARRAY_AGG(signal_with_noise) over (order by datetime asc rows between 720 PRECEDING and 1 PRECEDING) as history  #put a max history length here, not unbounded
FROM `development-175109.00_thirdeye_demo.examples` 
WHERE DATE(_PARTITIONTIME) >= "2021-01-14" 
order by datetime
)

select 
datetime, 
signal_with_noise, 
naive_previous_value, 
naive_weighted_average,
WeightedAverage(history, 0.3) as weighted_average_03,
WeightedAverage(history, 0.5) as weighted_average_05,
WeightedAverage(history, 0.7) as weighted_average_07
from preceding_values
order by datetime asc