在Google BigQuery上使用起始/结束日期优化活动帐户查询

Question

我希望优化一个查询，以获得每个时间间隔(日、周等)的活动帐户，其中active在帐户的开始日期和结束日期上定义。布局如下：

accounts: account_id Int subscription_start Timestamp subscription_end Timestamp -- null if still active

我尝试了几种方法，其中最快的一种是使用generate_array创建活动天的数组，然后取消嵌套(大约2分钟)。

with nested_dates as (
  select GENERATE_DATE_ARRAY(DATE(subscription_start), IFNULL(date(subscription_end), current_date()), INTERVAL 1 DAY) as dates
  from `accounts`
),
all_dates as (
  select date_item from nested_dates, UNNEST(dates) as date_item
)
select date_item, count(1) from all_dates group by date_item

我还使用了每天的子选择，它在6-9个月前的类似情况下表现得相当好，当时BigQuery仍然拥有性能层次，并且给出了更多的使用CPU的lee方式。但随着他们取消了更高的计算层定价，现在的定价似乎更加严格/效率更低。这个行刑大约需要12分钟。

select
  day,
  (select 
     count(1) 
   from `accounts` where
     subscription_start <= day and 
     (subscription_end is null or subscription_end >= day)
  )
from unnest(
    generate_date_array(date('2015-06-01'), current_date(), interval 1 day)
) AS day

我还对生成日期和条件和条件的帐户的交叉连接进行了性能测试(由于CPU限制超过了3300秒，在3300秒后失败)。

当然，非常快的是，当我为每个帐户生成一个所有日期的物化表时，如果我实现分区的速度可能更快，那么查询将在6-10秒内完成(例如，使用with from第一次查询"all_dates“)。

select date(day_active), count(1) from `account_all_dates`

现在的问题是:是否有一种方法可以获得物化交叉连接的性能，而不实际首先炸掉数据并将其物化，即在没有开销的情况下实时完成。

我试着用一些分析函数，但找不到能做到这一点的东西。

Answer 1

我认为您的第一个查询非常完美，而且它已经是最通用的，并且适用于任何时间间隔(日、周等)以及所有非物化选项中最快的选项。

唯一的潜在改进(也是更紧凑的)如下(在我对虚拟数据的快速测试中，它不断地显示出稍微快一些的结果--但我不能肯定地说，在真实数据上看到结果会很有趣)。

#standardSQL
SELECT date_item, COUNT(1) active_accounts 
FROM `accounts`, 
UNNEST(GENERATE_DATE_ARRAY(DATE(subscription_start), 
  IFNULL(DATE(subscription_end), CURRENT_DATE()), INTERVAL 1 DAY)) date_item
GROUP BY date_item