sql - 在 HH :MM:SS format to seconds in Google BigQuery (Standard SQL) 中转换字符串

Question

我的 Google Analytics(分析)表格中有一个名为“视频时长”的自定义维度。数据是字符串值，但有些采用以下 3 种格式:

1) HH:MM:SS(例如，1:54:55)

2) MM:SS(例如，2:26)

3) 秒数(例如，对于长度为 2:26 的视频，长度为 146)

• 我已尝试将 case 语句归零，以解决值是 HH:MM:SS 格式或 MM:SS 格式以及字符长度的情况。
• 我还为遵循 MM:SS 格式的值添加了额外的零。
• 我必须CAST才能INT64，这样我就可以将值相加以获得字符串。

查询有效，但是当我运行查询时，出现“无法解析输入字符串”的错误

select old_video_length,
 case 
  when videoLength like "%:%" and length(videoLength) > 7 then cast(cast(parse_time("%E*S",videoLength) as string) as int64)+cast(cast(parse_time("%M",videoLength) as string) as int64)*60+cast(cast(parse_time("%H",videoLength) as string) as int64)*3600
  when videoLength like "%:%" and length(videoLength) between 6 and 7 then cast(cast(parse_time("%E*S",concat("0",videoLength)) as string) as int64)+cast(cast(parse_time("%M",concat("0",videoLength)) as string) as int64)*60+cast(cast(parse_time("%H",concat("0",videoLength)) as string) as int64)*3600
  when videoLength like "%:%" and length(videoLength) <= 5 then cast(cast(parse_time("%E*S",concat("00:",videoLength)) as string) as int64)+cast(cast(parse_time("%M",concat("00:",videoLength)) as string) as int64)*60+cast(cast(parse_time("%H",concat("00:",videoLength)) as string) as int64)*3600
  else cast(videoLength as int64) end as video_length_converted
  from vid_length_table


Ideally, I'd like the table to look like this: 

old_video_length | video_length_converted
1:54:55            6895
2:26               146
146                146

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT videoLength AS old_video_length,
  CASE
    WHEN REGEXP_CONTAINS(videoLength, r':\d\d:\d\d$') THEN TIME_DIFF(SAFE.PARSE_TIME('%T', videoLength), TIME '00:00:00', SECOND)
    WHEN REGEXP_CONTAINS(videoLength, r':\d\d$') THEN TIME_DIFF(SAFE.PARSE_TIME('%M:%S', videoLength), TIME '00:00:00', SECOND)
    ELSE SAFE_CAST(videoLength AS INT64)
  END AS video_length_converted
FROM `project.dataset.vid_length_table`  

您可以使用您问题中的样本数据进行测试，如下例所示

#standardSQL
WITH `project.dataset.vid_length_table` AS (
  SELECT '1:54:55' videoLength UNION ALL
  SELECT '2:26' UNION ALL
  SELECT '146'
)
SELECT videoLength AS old_video_length,
  CASE
    WHEN REGEXP_CONTAINS(videoLength, r':\d\d:\d\d$') THEN TIME_DIFF(SAFE.PARSE_TIME('%T', videoLength), TIME '00:00:00', SECOND)
    WHEN REGEXP_CONTAINS(videoLength, r':\d\d$') THEN TIME_DIFF(SAFE.PARSE_TIME('%M:%S', videoLength), TIME '00:00:00', SECOND)
    ELSE SAFE_CAST(videoLength AS INT64)
  END AS video_length_converted
FROM `project.dataset.vid_length_table`   

结果

Row old_video_length    video_length_converted   
1   1:54:55             6895     
2   2:26                146  
3   146                 146