BigQuery中如何根据value列索引给start_time字段添加对应小时数

核心实现逻辑 #

原表为宽表结构，每行包含固定的project、基础start_time，以及多列命名为value_1、value_2……value_n的数值字段，需要将宽表转为长表，同时按value后缀的序号累加小时数到start_time字段。

分步实现思路 #

• 宽表转长表：使用BigQuery的UNNEST函数搭配自定义STRUCT数组，将每一行的多个value字段拆分为独立的行，同时为每个value绑定其对应的后缀序号作为小时偏移量。
• 时间字段计算：调用DATETIME_ADD函数，将原基础start_time加上对应偏移量的小时数，得到调整后的start_time。
• 结果写入新表：筛选出要求的三个字段，直接创建新表即可。

参考可运行SQL代码 #

CREATE OR REPLACE TABLE `your_project.your_dataset.target_table`
AS
SELECT
  -- 保留字符串类型的project字段
  project,
  -- 累加对应小时数，输出为datetime类型的start_time
  DATETIME_ADD(base_start_time, INTERVAL value_offset HOUR) AS start_time,
  -- 保留numeric类型的value字段
  value
FROM
  `your_project.your_dataset.source_table`,
  -- 手动罗列所有value列和对应的偏移量，有多少列补充多少行即可
  UNNEST([
    STRUCT(1 AS value_offset, value_1 AS value),
    STRUCT(2 AS value_offset, value_2 AS value),
    STRUCT(3 AS value_offset, value_3 AS value),
    STRUCT(4 AS value_offset, value_4 AS value)
  ])
-- 可选：过滤掉value为空的无效行
-- WHERE value IS NOT NULL

如果value列数量较多，可查询INFORMATION_SCHEMA.COLUMNS表批量生成STRUCT数组的语句，无需手动逐个编写。

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