BigQuery - Moving Averages using Windowing Function

user_819132 0
Viewed 74

I am trying to create a measure to calculate moving averages using a windowing function, instead of creating several common table expressions CTEs. Why is the windowing function returning incorrect results when i partition the data similar to how i group data in the CTEs?

WITH data as (
  SELECT
  1 as id, "2020-06-20" as day,10 as quantity
  UNION ALL SELECT 2,"2020-06-20", 15
  UNION ALL SELECT 2, "2020-06-20", 20
  UNION ALL SELECT 2, "2020-06-20", 21
  UNION ALL SELECT 2, "2020-06-20", 19

  UNION ALL SELECT 1,"2020-06-21",5
  UNION ALL SELECT 2,"2020-06-21",10
  UNION ALL SELECT 2, "2020-06-21",5

  UNION ALL SELECT 1,"2020-06-22",9
  UNION ALL SELECT 2,"2020-06-22",4

  UNION ALL SELECT 2,"2020-06-23",10
  UNION ALL SELECT 2,"2020-06-23",10
  UNION ALL SELECT 2,"2020-06-23",20
)
, CTEDAILY AS (
  SELECT 
    AVG(quantity) AS sq,
    day
  FROM data 
  GROUP BY day 
)
, CTEMA AS (
  SELECT 
    day,
    AVG(sq) OVER (ORDER BY day ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ma3_version2
  FROM CTEDAILY 
)

, CTECOMP AS (
SELECT  
  day,
  quantity,
  SUM(quantity) OVER w1 AS sum_quantity,
  AVG(quantity) OVER w1 AS avg_quantity,
  PERCENTILE_CONT(quantity, 0.5) OVER w1 AS median,
  STDDEV(quantity) OVER w1 AS stdd,

  AVG(quantity) OVER w2_ma3 AS ma3_version1, /* not daily, currently returning MA by row? want to first sum then MA3??? */
FROM data  
WINDOW w1 AS (PARTITION BY day),
w2_ma3 AS (PARTITION BY day ORDER BY day ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY day ASC
)
SELECT 
  CTECOMP.day, 
  ANY_VALUE(CTECOMP.sum_quantity) sum_q, 
  ANY_VALUE(avg_quantity) AS avg_q, 
  ANY_VALUE(median) AS median, 
  ANY_VALUE(stdd) AS stdd,
  ANY_VALUE(CTECOMP.ma3_version1) AS ma3_incorrect,
  ANY_VALUE(CTEMA.ma3_version2) AS ma3_correct
FROM CTECOMP
INNER JOIN CTEMA 
  ON CTECOMP.day = CTEMA.day 
GROUP BY CTECOMP.day
ORDER BY day ASC;

enter image description here

google-bigquery
1 Answers
  1. Your first CTE takes intra day average.
  2. Your second CTE takes moving average of your intra day averages over X days
  3. Your third CTE and its window function takes moving (3 periods) INTRA DAY average

Following your designated "correct" answer. Here is one in your style with a subquery

WITH data as (
  SELECT
  1 as id, "2020-06-20" as day,10 as quantity
  UNION ALL SELECT 2,"2020-06-20", 15
  UNION ALL SELECT 2, "2020-06-20", 20
  UNION ALL SELECT 2, "2020-06-20", 21
  UNION ALL SELECT 2, "2020-06-20", 19

  UNION ALL SELECT 1,"2020-06-21",5
  UNION ALL SELECT 2,"2020-06-21",10
  UNION ALL SELECT 2, "2020-06-21",5

  UNION ALL SELECT 1,"2020-06-22",9
  UNION ALL SELECT 2,"2020-06-22",4

  UNION ALL SELECT 2,"2020-06-23",10
  UNION ALL SELECT 2,"2020-06-23",10
  UNION ALL SELECT 2,"2020-06-23",20
)

select
*,
avg(avg_intra_day_quantity) over(order by day asc rows between 2 preceding and current row) ma3
from
(
  select
  distinct  
  day,
  sum(quantity) over w1 as sum_daily_quantity,
  avg(quantity) over w1 as avg_intra_day_quantity,
  percentile_cont(quantity, 0.5) over w1 as median,
  stddev(quantity) over w1 as stdd,


  from data
  window w1 as (partition by day)
)

enter image description here

Here is one with a subquery that has a group by and a window function (no CTEs)

WITH data as (
  SELECT
  1 as id, "2020-06-20" as day,10 as quantity
  UNION ALL SELECT 2,"2020-06-20", 15
  UNION ALL SELECT 2, "2020-06-20", 20
  UNION ALL SELECT 2, "2020-06-20", 21
  UNION ALL SELECT 2, "2020-06-20", 19

  UNION ALL SELECT 1,"2020-06-21",5
  UNION ALL SELECT 2,"2020-06-21",10
  UNION ALL SELECT 2, "2020-06-21",5

  UNION ALL SELECT 1,"2020-06-22",9
  UNION ALL SELECT 2,"2020-06-22",4

  UNION ALL SELECT 2,"2020-06-23",10
  UNION ALL SELECT 2,"2020-06-23",10
  UNION ALL SELECT 2,"2020-06-23",20
)

select
day,
avg(sq) over(order by day asc rows between 2 preceding and current row) ma3

from 
(
select 
avg(quantity) as sq,
day

from data 
group by day
)

enter image description here

Here is one with a subquery that's using window functions only

WITH data as (
  SELECT
  1 as id, "2020-06-20" as day,10 as quantity
  UNION ALL SELECT 2,"2020-06-20", 15
  UNION ALL SELECT 2, "2020-06-20", 20
  UNION ALL SELECT 2, "2020-06-20", 21
  UNION ALL SELECT 2, "2020-06-20", 19

  UNION ALL SELECT 1,"2020-06-21",5
  UNION ALL SELECT 2,"2020-06-21",10
  UNION ALL SELECT 2, "2020-06-21",5

  UNION ALL SELECT 1,"2020-06-22",9
  UNION ALL SELECT 2,"2020-06-22",4

  UNION ALL SELECT 2,"2020-06-23",10
  UNION ALL SELECT 2,"2020-06-23",10
  UNION ALL SELECT 2,"2020-06-23",20
)


select
day,
avg(intra_day_avg) over(order by day asc rows between 2 preceding and current row) ma3

from 
(
select
distinct
day,
avg(quantity) over(partition by day) intra_day_avg

from
data
)

PS / fyi
If you are trying to calculate daily simple moving average you should sum your quantities by day first and then do moving average calculation on those sums over N time periods and that gets you to daily moving average. Right now you take averages of intra day averages.

user_44716720
Related