bigquery创建间隔15分钟的时间戳桶

Question

我要做到这一点：

输出

12:00:00 - 12:15:00
12:15:00 - 12:30:00
12:30:00 - 12:45:00
12:45:00 - 1:00:00 .......,
count(orders)
from table

我有一个时间戳在数据表可用(2022-07-05 19:45:45)，我想实现#订单每15分钟间隔一天。

Answer 1

使用RANGE_BUCKET函数，您可以每15分钟创建一个时间戳桶。考虑下面的示例查询：

• https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#range_bucket

CREATE TEMP TABLE sample_table AS 
SELECT * FROM UNNEST(GENERATE_TIMESTAMP_ARRAY('2022-07-05 00:00:00', '2022-07-05 10:00:00', INTERVAL 3 MINUTE)) `order`
;

SELECT TIMESTAMP_SECONDS(intervals[SAFE_OFFSET(RANGE_BUCKET(UNIX_SECONDS(`order`), intervals) - 1)]) ts,
       COUNT(`order`) AS orders,
  FROM `sample_table`,
UNNEST ([STRUCT(GENERATE_ARRAY(UNIX_SECONDS('2022-07-05'), UNIX_SECONDS('2022-07-06'), 60 * 15) AS intervals)])
 GROUP BY 1
 ORDER BY 1

​

​