时间戳

首页 > 解决方案 > Bigquery：根据不同的 date_trunc 多次运行查询并将结果合并，而不是多个 UNION ALL

google-bigquery - Bigquery：根据不同的 date_trunc 多次运行查询并将结果合并，而不是多个 UNION ALL

问题描述

我希望从核心表中提取和合并类似的聚合，但在时间段截断方面有所不同。例如，这就是我通常会这样做的方式：

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

有谁知道是否有办法避免必须维护三个单独的子查询并拥有一个根据不同的 date_trunc 重新运行的子查询并将结果合并（产生与上面的代码相同的输出）？我知道我以前的公司有人做到了这一点，但我不知道是怎么做到的。

谢谢！

标签： google-bigqueryrecursive-queryunion-all

解决方案

考虑以下方法

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

推荐阅读