Google BigQuery转换Excel导入时长字段类型报错求助

问题原因 #

你的时长字符串格式为0.00:22:28，开头的0.是Excel导出时的格式残留，BigQuery的INTERVAL类型不识别这种前缀，导致转换报错。

解决方案 #

步骤1：清洗字符串，移除无效前缀 #

用REGEXP_REPLACE函数去掉开头的0.：

REGEXP_REPLACE(ride_length, '^0\\.', '')

该表达式仅匹配并移除字符串开头的0.，对正常格式的时长（如01:30:00）无影响。

步骤2：转换为INTERVAL类型（可选） #

清洗后的字符串可正常转为INTERVAL：

CAST(REGEXP_REPLACE(ride_length, '^0\\.', '') AS INTERVAL)

步骤3：转换为数值类型（用于统计计算） #

要计算最大值、最小值和平均值，建议转为总秒数（数值类型），有两种实现方式：

方式一：基于INTERVAL提取计算

SELECT
  ride_length,
  EXTRACT(HOUR FROM interval_ride) * 3600 +
  EXTRACT(MINUTE FROM interval_ride) * 60 +
  EXTRACT(SECOND FROM interval_ride) AS total_seconds
FROM (
  SELECT
    ride_length,
    CAST(REGEXP_REPLACE(ride_length, '^0\\.', '') AS INTERVAL) AS interval_ride
  FROM your_table_name
)

方式二：直接拆分字符串计算

无需转换为INTERVAL，直接拆分小时、分钟、秒并计算总秒数：

SELECT
  ride_length,
  SPLIT_PART(cleaned_ride_length, ':', 1)::INT * 3600 +
  SPLIT_PART(cleaned_ride_length, ':', 2)::INT * 60 +
  SPLIT_PART(cleaned_ride_length, ':', 3)::FLOAT AS total_seconds
FROM (
  SELECT
    ride_length,
    REGEXP_REPLACE(ride_length, '^0\\.', '') AS cleaned_ride_length
  FROM your_table_name
)

直接统计时长的最大值、最小值和平均值 #

将上述逻辑整合到统计查询中：

SELECT
  MAX(total_seconds) AS max_ride_seconds,
  MIN(total_seconds) AS min_ride_seconds,
  AVG(total_seconds) AS avg_ride_seconds
FROM (
  SELECT
    EXTRACT(HOUR FROM CAST(REGEXP_REPLACE(ride_length, '^0\\.', '') AS INTERVAL)) * 3600 +
    EXTRACT(MINUTE FROM CAST(REGEXP_REPLACE(ride_length, '^0\\.', '') AS INTERVAL)) * 60 +
    EXTRACT(SECOND FROM CAST(REGEXP_REPLACE(ride_length, '^0\\.', '') AS INTERVAL)) AS total_seconds
  FROM your_table_name
)

内容的提问来源于stack exchange，提问作者Darragh O'Dowd