BigQuery技术问询：如何为每个值展开时间戳间隔生成30分钟时段

直接使用CROSS JOIN UNNEST()就能将data表的字段与生成的时间数组关联起来，以下是完整的可行SQL语句：

SELECT 
  d.value,
  TIME(interval_start) AS start_time,
  TIME(TIMESTAMP_SUB(TIMESTAMP_ADD(interval_start, INTERVAL 30 MINUTE), INTERVAL 1 SECOND)) AS end_time
FROM 
  `data` d
CROSS JOIN UNNEST(
  GENERATE_TIMESTAMP_ARRAY(
    -- 起始时间：前一天的00:对应minute:00
    TIMESTAMP(CONCAT(CURRENT_DATE() - 1, " 00:", d.minute, ":00 UTC")),
    -- 结束时间：当天的00:对应pre_minute:59
    TIMESTAMP(CONCAT(CURRENT_DATE(), " 00:", d.pre_minute, ":59 UTC")),
    INTERVAL 30 MINUTE
  )
) AS interval_start

关键说明 #

• 关联字段的核心：CROSS JOIN UNNEST
  原来的问题是单独使用UNNEST()无法关联value，通过CROSS JOIN将data表的每一行与生成的时间数组做笛卡尔积，就能让每个时间间隔都带上对应的value、minute等字段。
• 动态适配时间范围
  利用表中已有的minute和pre_minute字段动态生成数组范围，不需要额外写CASE WHEN判断：
  • 当minute = "00"时，pre_minute = "59"，时间范围自动变成「前一天00:00:00」到「当天00:59:59」，刚好生成48个30分钟间隔。
  • 其他场景比如minute = "25"，时间范围是「前一天00:25:00」到「当天00:24:59」，完全匹配示例需求。

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