如何在BigQuery中拆分工作时段并添加对应时段列？
2026-8-21
需求实现方案
要在BigQuery中基于start_time和finish_time字段生成逐时段拆分的行记录，可借助数组生成和展开的方式实现，以下是具体步骤和示例：
原表与结果表示例
原表（假设表名为your_table）
| id | start_time | finish_time | other_column |
|---|---|---|---|
| 1 | 2023-10-01 08:00:00 | 2023-10-01 10:00:00 | 示例数据A |
| 2 | 2023-10-02 14:00:00 | 2023-10-02 16:00:00 | 示例数据B |
期望结果表
| id | start_time | finish_time | other_column | 时段 |
|---|---|---|---|---|
| 1 | 2023-10-01 08:00:00 | 2023-10-01 10:00:00 | 示例数据A | 08:00-09:00 |
| 1 | 2023-10-01 08:00:00 | 2023-10-01 10:00:00 | 示例数据A | 09:00-10:00 |
| 1 | 2023-10-01 08:00:00 | 2023-10-01 10:00:00 | 示例数据A | 10:00-11:00 |
| 2 | 2023-10-02 14:00:00 | 2023-10-02 16:00:00 | 示例数据B | 14:00-15:00 |
| 2 | 2023-10-02 14:00:00 | 2023-10-02 16:00:00 | 示例数据B | 15:00-16:00 |
| 2 | 2023-10-02 14:00:00 | 2023-10-02 16:00:00 | 示例数据B | 16:00-17:00 |
实现SQL
SELECT t.id, t.start_time, t.finish_time, t.other_column, -- 按需格式化时段显示，这里是"起始时间-结束时间"格式 FORMAT_TIMESTAMP("%H:%M-%H:%M", interval_start, interval_start + INTERVAL 1 HOUR) AS 时段 FROM `你的项目ID.你的数据集ID.your_table` t, -- 生成从start_time到finish_time的时间戳数组，每1小时递增 UNNEST(GENERATE_TIMESTAMP_ARRAY( t.start_time, t.finish_time, INTERVAL 1 HOUR )) AS interval_start
关键说明
GENERATE_TIMESTAMP_ARRAY：生成包含从start_time到finish_time的时间戳数组，递增间隔可自定义（比如1分钟替换为INTERVAL 1 MINUTE）。若finish_time刚好是间隔的整数倍，会包含该时间点。UNNEST：将数组拆分为多行，每行对应一个时段的起始时间。FORMAT_TIMESTAMP：根据需求调整时段格式，若只需提取小时数，可替换为EXTRACT(HOUR FROM interval_start) AS 时段。
如果时间字段是DATE类型，只需将GENERATE_TIMESTAMP_ARRAY换成GENERATE_DATE_ARRAY，FORMAT_TIMESTAMP换成FORMAT_DATE即可适配。
内容的提问来源于stack exchange，提问作者JG K
相关产品推荐
相关产品推荐
