1

I am looking to pull and union similar aggregations from a core table, but differing on the time period truncation. Eg this is how I would normally do so:

with base as (
  select 
    event_datetime
    , event_location
    , event_uuid
  from events_table
)

select
  date_trunc(event_datetime, day) as day
  , "day" as period_type
  , event_location
  , count(distinct event_uuid) as number_events
from base
group by day, event_location

union all

select
  date_trunc(event_datetime, week) as week
  , "week" as period_type
  , event_location
  , count(distinct event_uuid) as number_events
from base
group by week, event_location

union all

select
  date_trunc(event_datetime, week) as month
  , "month" as period_type
  , event_location
  , count(distinct event_uuid) as number_events
from base
group by month, event_location

Does anyone know if there is a way to avoid having to maintain three separate subqueries and have a single subquery that re-runs based on the different date_trunc and unions the results (producing the same output as code above)? I know someone at my previous company accomplished this, but I can't figure out how.

Thanks!

emorgsssss
  • 17
  • 4

1 Answers1

0

Consider below approach

select 
  day, 
  period_type, 
  event_location, 
  count(distinct event_uuid) as number_events
from base t, 
unnest([
  struct('day' as period_type, date_trunc(event_datetime, day) as day),
  struct('week', date_trunc(event_datetime, week)),
  struct('month', date_trunc(event_datetime, month))
]) 
group by day, period_type, event_location
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • Hi Mikhail - thank you again for your answer above! I added a follow-up / alteration to the edit above. Would really appreciate if you could take another look and help me once again :) – emorgsssss Jul 26 '21 at 23:01
  • This is not how it works here on SO. If you have new or follow up question - please post it as a new question and I/we will be happy to help. Meantime, please rollback your question to its original state – Mikhail Berlyant Jul 26 '21 at 23:03
  • My apologies! That makes sense. I removed the edit above and made a new question here: https://stackoverflow.com/questions/68538191/bigquery-run-a-query-multiple-times-based-on-different-when-statement-time-date Thanks! – emorgsssss Jul 27 '21 at 02:20
  • Sure. Will check shortly – Mikhail Berlyant Jul 27 '21 at 02:21
  • glad it worked for you - consider also voting up the answer! :o) – Mikhail Berlyant Jul 28 '21 at 14:35