将持续时间格式P0DT0H0M0S (ISO 8601持续时间格式)转换为小时

Question

我正在寻找一种从“ISO 8601持续时间格式”（P0DT0H0M0S）转换为小时的便捷方法。

到目前为止，我想到了这个:

# Example in BigQuery
SELECT
    24 * CAST(SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('P1DT2H3M44S', 'P', ''), 'DT', '-'), 'H', '-'), 'M', '-'), 'S', ''), '-')[OFFSET(0)] AS INT64)
    + CAST(SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('P1DT2H3M44S', 'P', ''), 'DT', '-'), 'H', '-'), 'M', '-'), 'S', ''), '-')[OFFSET(1)] AS INT64)
    + (1/60) * CAST(SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('P1DT2H3M44S', 'P', ''), 'DT', '-'), 'H', '-'), 'M', '-'), 'S', ''), '-')[OFFSET(2)] AS INT64)
    + (1/3600) * CAST(SPLIT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('P1DT2H3M44S', 'P', ''), 'DT', '-'), 'H', '-'), 'M', '-'), 'S', ''), '-')[OFFSET(3)] AS INT64) AS HOURS

如您所见，我的方法是将数字拆分并乘以24、1、1/60、1/3600以得到小时。我可以减少代码量吗？

样本数据和所需结果
输入： 'P1DT2H3M44S' （1 天 2 小时 3 分 44 秒）
期望输出： 26.062222222222222 （这是小时）

Answer 1

这个选项比原始代码更简洁，但仍然易于理解和修改。

SELECT
    'P0DT5H47M0S' AS iso_8601_duration,
    24 * CAST(REGEXP_EXTRACT('P0DT5H47M0S', '[0-9]+', 1, 1) AS INT64)
    + 1 * CAST(REGEXP_EXTRACT('P0DT5H47M0S', '[0-9]+', 1, 2) AS INT64)
    + (1/60) * CAST(REGEXP_EXTRACT('P0DT5H47M0S', '[0-9]+', 1, 3) AS INT64)
    + (1/3600) * CAST(REGEXP_EXTRACT('P0DT5H47M0S', '[0-9]+', 1, 4) AS INT64) AS Hours

Answer 2

尝试下面的选项

select *,    
  (select sum(cast(val as int64) * weight)
   from unnest(regexp_extract_all(col, r'\d+')) val with offset
   join unnest([24, 1, 1/60, 1/3600]) weight with offset 
   using(offset)
  ) as hours
from `project.dataset.table`     

如果应用到您的问题中的样本数据，输出是

上面假设所有部分（天，小时，分钟，秒）都存在，即使它们为零

如果不是这种情况-我怀疑是这样，但不确定-则需要对上述解决方案进行轻微调整。让我知道