BigQuery标准SQL：将字符串格式营业时间转换为结构化时段数据

AI 小北

2026-8-16

BigQuery标准SQL实现营业时间字符串转多行多列结构

原始数据格式

示例STRING类型营业时间字段值：
Monday: 10:00 - 20:00, Tuesday: 10:00 - 20:00, Wednesday: 10:00 - 20:00, Thursday: 10:00 - 20:00, Friday: 10:00 - 20:00, Saturday: 10:00 - 20:00, Sunday: 11:00 - 18:00

目标输出结构

转换为包含以下字段的多行数据集：

Weekday：星期对应数字（0=周一，1=周二，…，6=周日）
Opening：开门时间，格式为00:HH:MM
Closing：关门时间，格式为00:HH:MM

实现SQL代码

WITH sample_data AS (
  SELECT 'Monday: 10:00 - 20:00, Tuesday: 10:00 - 20:00, Wednesday: 10:00 - 20:00, Thursday: 10:00 - 20:00, Friday: 10:00 - 20:00, Saturday: 10:00 - 20:00, Sunday: 11:00 - 18:00' AS business_hours
)
SELECT
  CASE weekday_name
    WHEN 'Monday' THEN 0
    WHEN 'Tuesday' THEN 1
    WHEN 'Wednesday' THEN 2
    WHEN 'Thursday' THEN 3
    WHEN 'Friday' THEN 4
    WHEN 'Saturday' THEN 5
    WHEN 'Sunday' THEN 6
  END AS Weekday,
  FORMAT_TIMESTAMP('00:%H:%M', PARSE_TIMESTAMP('%H:%M', opening_time)) AS Opening,
  FORMAT_TIMESTAMP('00:%H:%M', PARSE_TIMESTAMP('%H:%M', closing_time)) AS Closing
FROM sample_data,
UNNEST(SPLIT(business_hours, ', ')) AS daily_hours,
UNNEST([STRUCT(
  REGEXP_EXTRACT(daily_hours, r'^(\w+):') AS weekday_name,
  REGEXP_EXTRACT(daily_hours, r'(\d{2}:\d{2}) - ') AS opening_time,
  REGEXP_EXTRACT(daily_hours, r' - (\d{2}:\d{2})$') AS closing_time
)])
ORDER BY Weekday;

代码说明

模拟数据：sample_dataCTE模拟包含营业时间字段的表，实际使用时替换为你的业务表和字段名。
拆分字符串：用SPLIT将原始字符串按, 拆分为单个工作日的营业时间条目。
提取字段：通过正则表达式REGEXP_EXTRACT从每个条目中提取星期名称、开门时间、关门时间。
星期转数字：用CASE语句将英文星期名称映射为0-6的数字（0对应周一，6对应周日）。
时间格式化：先用PARSE_TIMESTAMP解析HH:MM格式的时间，再用FORMAT_TIMESTAMP转换为00:HH:MM的目标格式。
排序结果：按星期数字排序，保证输出顺序符合工作日逻辑。