根据when语句中的不同日期参数多次运行查询，并对结果进行联合，而不是多个union all

Question

我希望从核心表中提取并合并类似的聚合，但在WHEN语句中定义的日期周期上有所不同。我通常是这样做的:

with base as (
select 
event_datetime
, event_location
, event_uuid
from events_table
)
select
"last_30_days" as period
, event_location
, count(distinct event_uuid) as number_events
from base
where event_datetime >= current_date - 30
group by event_location
union all
select
"last_60_days" as period
, event_location
, count(distinct event_uuid) as number_events
from base
where event_datetime >= current_date - 60
group by event_location
union all
select
"all_time" as period
, event_location
, count(distinct event_uuid) as number_events
from base
where event_datetime >= current_date - 10000
group by event_location

有没有人知道，如果有一种方法来避免必须维护三个独立的子查询，并有一个子查询，重新运行基于不同的周期和联合的结果(产生相同的输出与上面的代码)?

Answer 1

考虑下面的方法

select 
period, 
event_location, 
count(distinct if(event_datetime >= current_date - days, event_uuid, null)) as number_events
from base, 
unnest([
struct('last_30_days' as period, 30 as days),
struct('last_60_days', 60),
struct('all_time', 10000)
]) 
group by period, event_location