CODEWITHSUNDEEP
google-bigquery
annethologybyme
annethologybyme

Reputation: 9

Getting Average for Weekdays and Weekends within 30 days in BQ

I'm trying to get the average repairs in the weekdays and weekends within the last 30 days. Each day is tagged whether it's a weekday or a weekend. Holidays are tagged as weekends.

If I use:

AVG(Completed_Repairs) OVER(PARTITION BY day_type ORDER BY UNIX_DATE(WORK_DT) RANGE BETWEEN 30 PRECEDING AND CURRENT ROW)

I only get either the average repairs for all weekdays or for all weekends in the last 30 days depending on what type of day the date is. But I also need the average for the opposite to compute a prorated monthly number. I basically would need another column with the value of the opposite day type.

enter image description here

Upvotes: 0

Views: 114

Answers (1)

Xavier Hamida
Xavier Hamida

Reputation: 194

If I understood correctly, not partitioning might be the way:

with
input as (
  select cast('2022-10-11' as date) as WORK_DT, "weekday" as day_type, 307 as completed_repairs union all
  select cast('2022-10-12' as date) as WORK_DT, "weekday" as day_type, 100 as completed_repairs union all
  select cast('2022-10-09' as date) as WORK_DT, "weekend" as day_type, 750 as completed_repairs union all
  select cast('2022-10-10' as date) as WORK_DT, "weekend" as day_type, 647 as completed_repairs 
)
select 
  *,
  avg(if(day_type = 'weekday', completed_repairs,0)) OVER(ORDER BY UNIX_DATE(WORK_DT) RANGE BETWEEN 30 PRECEDING AND CURRENT ROW) as avg_weekday,
  avg(if(day_type = 'weekend', completed_repairs,0)) OVER(ORDER BY UNIX_DATE(WORK_DT) RANGE BETWEEN 30 PRECEDING AND CURRENT ROW) as avg_weekend,
from input
order by work_dt

You can replace the 0 by null if you don't want the weekends to impact the average of the weekdays and vice-versa. If you'd rather have a column "matching" and a column "opposite" you can then use the result of this to write a condition depending on the day_type and the column name.

Upvotes: 0

Related Questions