如何在SQL中从时间范围集合移除纽约周五17点至上海周一9点时段？

问题背景 #

我有一组会话时段数据（SQL示例如下），需要移除所有处于美国纽约时间周五17:00至中国上海时间周一09:00之间的时段，最终拆分出可统计的工作时段（标记为Open）和需排除的非工作时段（标记为Closed），用于工单系统的等待/解决时长计算。当前我能想到的方案是生成包含所有周五/周一目标时段的表，再用标准Gaps and Islands解法处理，想知道这是否为最优方案。

补充说明：因服务由多国团队支持，工作时段规则较复杂，仅统计工作时段时长。

原始会话数据SQL #

WITH sessions AS (
  (
  SELECT 
    TIMESTAMP('2022-09-24 13:49:32+0100') AS start,
    TIMESTAMP('2022-09-26 02:06:17+0100') AS end
  
  UNION ALL
  
  SELECT
    TIMESTAMP('2022-09-26 08:42:19+0100'), 
    TIMESTAMP('2022-09-26 09:41:40+0100'),  

  UNION ALL
 
  SELECT
    TIMESTAMP('2022-09-26 09:53:06+0100'),
    TIMESTAMP('2022-09-26 11:20:34+0100'))
)

SELECT * FROM sessions WHERE.... 

预期结果 #

Start                     End                       Status
2022-09-24 13:49:32+0100  2022-09-26 02:00:00+0100  Closed
2022-09-26 02:00:00+0100  2022-09-26 02:06:17+0100  Open
2022-09-26 08:42:19+0100  2022-09-26 09:41:40+0100  Open
2022-09-26 09:53:06+0100  2022-09-26 11:20:34+0100  Open

注：2022-09-26对应时段属于上海工作时间，因此标记为Open

方案分析与实现 #

1. 时间规则转换核心逻辑 #

首先明确两个时区的时间边界映射：

• 纽约时区（America/New_York）周五17:00，对应上海时区（Asia/Shanghai）周六05:00（夏令时）/06:00（冬令时）
• 上海时区周一09:00，对应纽约时区周日20:00（夏令时）/19:00（冬令时）

需要判断任意时间点是否处于「纽约周五17:00 → 上海周一09:00」的窗口内，本质是覆盖纽约周末、上海周六05:00至周一09:00的时段。

2. 最优方案选择 #

你提到的Gaps and Islands方案是可行的，但分场景选择更优：

• 中小数据量：直接对每个会话时段做时间窗口判断+拆分更简洁，无需预生成周末窗口表
• 大数据量（千万级以上）：预生成所有需要排除的时段表（比如未来1年的周末窗口），再关联会话数据做拆分，性能更优——因为提前计算好排除窗口，避免逐行时区转换计算

3. 具体实现代码（BigQuery） #

以下是直接拆分法的代码，无需预生成窗口表，适合中小数据场景：

WITH sessions AS (
  SELECT TIMESTAMP('2022-09-24 13:49:32+0100') AS start, TIMESTAMP('2022-09-26 02:06:17+0100') AS end
  UNION ALL
  SELECT TIMESTAMP('2022-09-26 08:42:19+0100'), TIMESTAMP('2022-09-26 09:41:40+0100')
  UNION ALL
  SELECT TIMESTAMP('2022-09-26 09:53:06+0100'), TIMESTAMP('2022-09-26 11:20:34+0100')
),
-- 收集所有需要拆分的时间点：会话起止点、落在会话内的排除窗口边界
split_points AS (
  SELECT start AS point FROM sessions
  UNION ALL
  SELECT end AS point FROM sessions
  UNION ALL
  -- 生成会话期间的纽约周五17:00（UTC）
  SELECT TIMESTAMP_ADD(TIMESTAMP_TRUNC(start, WEEK(MONDAY)), INTERVAL 5*24+17 HOUR) 
  FROM sessions 
  WHERE TIMESTAMP_ADD(TIMESTAMP_TRUNC(start, WEEK(MONDAY)), INTERVAL 5*24+17 HOUR) BETWEEN start AND end
  UNION ALL
  -- 生成会话期间的上海周一09:00（UTC）
  SELECT TIMESTAMP_ADD(TIMESTAMP_TRUNC(start, WEEK(MONDAY)), INTERVAL 7*24+1 HOUR) 
  FROM sessions 
  WHERE TIMESTAMP_ADD(TIMESTAMP_TRUNC(start, WEEK(MONDAY)), INTERVAL 7*24+1 HOUR) BETWEEN start AND end
  UNION ALL
  -- 处理跨周的情况
  SELECT TIMESTAMP_ADD(TIMESTAMP_TRUNC(start, WEEK(MONDAY)), INTERVAL 12*24+17 HOUR) 
  FROM sessions 
  WHERE TIMESTAMP_ADD(TIMESTAMP_TRUNC(start, WEEK(MONDAY)), INTERVAL 12*24+17 HOUR) BETWEEN start AND end
  UNION ALL
  SELECT TIMESTAMP_ADD(TIMESTAMP_TRUNC(start, WEEK(MONDAY)), INTERVAL 14*24+1 HOUR) 
  FROM sessions 
  WHERE TIMESTAMP_ADD(TIMESTAMP_TRUNC(start, WEEK(MONDAY)), INTERVAL 14*24+1 HOUR) BETWEEN start AND end
),
-- 对每个会话的拆分点排序，生成连续时段
session_splits AS (
  SELECT
    s.start AS original_start,
    s.end AS original_end,
    sp.point AS split_start,
    LEAD(sp.point) OVER (PARTITION BY s.start, s.end ORDER BY sp.point) AS split_end
  FROM sessions s
  JOIN split_points sp ON sp.point BETWEEN s.start AND s.end
)
-- 过滤有效时段并标记状态
SELECT
  split_start AS Start,
  split_end AS End,
  CASE
    -- 判断当前时段是否属于排除窗口
    WHEN EXTRACT(DAYOFWEEK FROM TIMESTAMP(split_start, 'America/New_York')) IN (1,7) -- 纽约周六/周日
      OR (EXTRACT(DAYOFWEEK FROM TIMESTAMP(split_start, 'America/New_York'))=6 AND EXTRACT(HOUR FROM TIMESTAMP(split_start, 'America/New_York'))>=17) -- 纽约周五17点后
      OR (EXTRACT(DAYOFWEEK FROM TIMESTAMP(split_start, 'Asia/Shanghai'))=2 AND EXTRACT(HOUR FROM TIMESTAMP(split_start, 'Asia/Shanghai'))<9) -- 上海周一9点前
    THEN 'Closed'
    ELSE 'Open'
  END AS Status
FROM session_splits
WHERE split_end IS NOT NULL
ORDER BY Start;

4. 方案对比总结 #

两种方案均可行，根据你的数据规模选择即可。

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