BigQuery如何将start_at与end_at间所有30分钟区间拆分为独立行

解决思路 #

核心通过BigQuery内置的GENERATE_TIMESTAMP_ARRAY函数生成两个时间戳之间的所有30分钟区间，再配合UNNEST将数组拆分为独立行，步骤如下：

1. 简化30分钟取整逻辑：你当前的取整语句可以直接替换为TIMESTAMP_TRUNC(时间戳字段, MINUTE, 30)，BigQuery原生支持指定N分钟粒度的截断，不需要手动计算分钟偏移。
2. 生成并拆分时间区间：在你现有的f CTE之后新增一个CTE，用GENERATE_TIMESTAMP_ARRAY生成start_at到end_at之间的所有30分钟区间，再通过UNNEST展开为单行。
3. 调整关联逻辑：用拆分后的单区间字段关联lost表的lost_order_30_interval字段，替换原来同时匹配起止区间的错误逻辑。

修改后完整查询片段 #

-------LOST ORDERS--------
a AS (SELECT created_date, closure, zone_id, city_id, l.interval_start, 
l.net as net_lost_orders, l.starts_at, CAST(DATETIME(l.starts_at, timezone)AS TIMESTAMP) as start_local_time
FROM `XXX`, UNNEST(lost_orders) as l),

b AS (SELECT city_id, city_name, zone_id, zone_name FROM `YYY`),

lost AS (SELECT DISTINCT created_date, closure, zone_name, city_name, start_local_time, 
-- 简化30分钟取整逻辑
TIMESTAMP_TRUNC(start_local_time, MINUTE, 30) AS lost_order_30_interval,
net_lost_orders
FROM a LEFT JOIN b ON a.city_id=b.city_id AND a.zone_id=b.zone_id
WHERE zone_name='Atlanta' AND created_date='2021-09-09'),

------PREPARATION CLOSURE START AND END INTERVALS------
f AS (SELECT
    country_code,
    report_date,
    Day,
    CASE
    WHEN Day="Monday" THEN 1
    WHEN Day="Tuesday" THEN 2
    WHEN Day="Wednesday" THEN 3
    WHEN Day="Thursday" THEN 4
    WHEN Day="Friday" THEN 5
    WHEN Day="Saturday" THEN 6
    WHEN Day="Sunday" THEN 7
  END AS Weekday_order,
    report_week,
    city_name,
    events_mod.zone_name,
    closure,
    start_at,
    end_at,
    activation_threshold,
    deactivation_threshold,
    shrinkage_drive_time,
    ROUND(duration/60,2) AS duration,
  FROM events_mod
  WHERE report_date="2021-09-09"
    AND events_mod.zone_name="Atlanta"
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
    ORDER BY report_date, start_at ASC),

-- 新增：拆分所有30分钟区间为独立行
f_expand AS (
  SELECT
    *,
    TIMESTAMP_TRUNC(start_at, MINUTE, 30) AS start_closure_30_interval,
    TIMESTAMP_TRUNC(end_at, MINUTE, 30) AS end_closure_30_interval,
    closure_interval_30
  FROM f,
  UNNEST(GENERATE_TIMESTAMP_ARRAY(
    TIMESTAMP_TRUNC(start_at, MINUTE, 30),
    TIMESTAMP_TRUNC(end_at, MINUTE, 30),
    INTERVAL 30 MINUTE
  )) AS closure_interval_30
)

------FINAL TABLE------
SELECT DISTINCT 
start_closure_30_interval,end_closure_30_interval, report_date, Day, Weekday_order, report_week, f_expand.city_name, f_expand.zone_name, closure, 
start_at, end_at, activation_threshold, deactivation_threshold, duration, net_lost_orders
FROM f_expand
LEFT JOIN lost ON f_expand.city_name=lost.city_name
  AND f_expand.zone_name=lost.zone_name
  AND f_expand.report_date=lost.created_date
  -- 用拆分后的单个区间匹配即可
  AND f_expand.closure_interval_30=lost.lost_order_30_interval
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

注意事项 #

• GENERATE_TIMESTAMP_ARRAY默认包含首尾区间，刚好满足你需要覆盖起止时间所有30分钟块的需求
• 如果涉及跨时区场景，需要先将start_at/end_at转换为对应时区的时间戳再做截断和数组生成，避免出现时区偏移错误

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