BigQuery如何为时间区间覆盖的每一分钟生成对应数据行

你可以使用 BigQuery 内置的时间序列生成函数GENERATE_TIMESTAMP_ARRAY配合UNNEST拆解数组实现需求，完整可运行的 SQL 代码如下：

WITH
  example_table AS (
  SELECT
    1 AS id,
    TIMESTAMP('2021-02-05 21:45:57') AS start_timestamp,
    TIMESTAMP('2021-02-05 21:48:17') AS end_timestamp
  UNION ALL
  SELECT
    2 AS id,
    TIMESTAMP('2021-02-05 23:12:01') AS start_timestamp,
    TIMESTAMP('2021-02-05 23:13:42') AS end_timestamp)
SELECT
  id,
  minute_start AS start_timestamp,
  TIMESTAMP_ADD(minute_start, INTERVAL 1 MINUTE) AS end_timestamp
FROM example_table,
UNNEST(GENERATE_TIMESTAMP_ARRAY(
  TIMESTAMP_TRUNC(start_timestamp, MINUTE),
  TIMESTAMP_TRUNC(end_timestamp, MINUTE),
  INTERVAL 1 MINUTE
)) AS minute_start

核心逻辑说明 #

• 用TIMESTAMP_TRUNC(时间, MINUTE)将原始起止时间向下取整到整分钟，作为时间序列的起止边界
• 用GENERATE_TIMESTAMP_ARRAY生成间隔为1分钟的时间戳数组，覆盖原始时间区间涉及的所有整分钟点
• 用UNNEST将数组拆分为多行，每行的时间区间为当前整分钟点到加1分钟后的时间点，刚好符合整分钟跨度要求

内容的提问来源于stack exchange，提问作者rafael_c