CODEWITHSUNDEEP
google-bigqueryaverage
Misha Z
Misha Z

Reputation: 19

Aggregation function (AVG) running total per week / year BigQuery

I need an average duration per week per id

SELECT ID, EXTRACT (WEEK FROM starttime) as week1, AVG(duration) OVER (PARTITION BY ID ORDER BY FORMAT_TIMESTAMP('%Y-%m-%d',starttime) ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as avgperweek FROM DB WHERE DATE(StartTime) between "2020-01-01" And "2020-10-10"

expecting to see like that

enter image description here

but it is ungrouping by exact day time

Upvotes: 0

Views: 593

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173036

Below is for BigQuery Standard SQL

SELECT ID, 
  EXTRACT (WEEK FROM starttime) as week1, 
  AVG(duration) as avgperweek 
FROM DB 
WHERE DATE(StartTime) between "2020-01-01" And "2020-10-10" 
GROUP BY ID, week1

Upvotes: 1

Related Questions