sql - Google Bigquery - 创建活动记录数的时间序列

Question

我正在尝试在 google bigquery SQL 中创建时间序列。我的数据是涵盖该记录活动期间的一系列时间范围。这是一个例子：

Start                   End
2020-11-01 21:04:00 UTC 2020-11-02 07:15:00 UTC
2020-11-01 21:45:00 UTC 2020-11-02 04:00:00 UTC
2020-11-01 22:00:00 UTC 2020-11-02 09:48:00 UTC
2020-11-01 22:00:00 UTC 2020-11-02 06:00:00 UTC

我希望创建一个新表来汇总 15 分钟内的活动记录数。例如，“21:00:00”是 21:00 到 21:14.59。我对上述内容的期望输出是：

Period              Active_Records
2020-11-01 21:00:00 1
2020-11-01 21:15:00 1
2020-11-01 21:30:00 1
2020-11-01 21:45:00 2
2020-11-01 22:00:00 4
2020-11-01 22:15:00 4

等，直到最后一个活动范围结束。

我还希望能够通过查询日期范围并让它在该范围内每 15 分钟块返回一次以及该期间有多少活动记录来动态生成它。

任何帮助将不胜感激。

标签： sqldatabasegoogle-cloud-platformgoogle-bigqueryaggregate

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
select ts as period, count(1) as Active_Records
from unnest((
  select generate_timestamp_array(timestamp_trunc(min(start), hour), max(`end`), interval 15 minute)
  from `project.dataset.table` 
)) ts 
join `project.dataset.table`
on not (`end` < ts or start > timestamp_add(ts, interval 15 * 60 - 1 second))
group by ts

如果适用于您的问题的样本数据 - 输出是