当前位置：文江博客话题详情

列字符串到时间

发布于 2025-02-01 23:00:42 字数 109 浏览 4 评论 0原文

SQL的新事物！我使用BigQuery。如何将整个字符串列转换为时间？列名是ride_length，其格式为00:00（分钟：秒）。架构是字符串，但我需要时间。那我该如何计算平均水平？先感谢您。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只怪假的太真实 2025-02-08 23:00:42

我认为有多种方法可以做到这一点。但是，这完全取决于您如何需要最终结果（甚至处理中间值）。一种方法是，您可以使用Split，Cast＆amp;可能计算总数添加，进行平均＆amp;将其放回时间数据类型（或以分钟/秒的时间维护）

WITH
  s AS (
  SELECT
    '45:00' AS time
  UNION ALL
  SELECT
    '30:00' AS time
  UNION ALL
  SELECT
    '20:00' AS time
  UNION ALL
  SELECT
    '25:00' AS time
  UNION ALL
  SELECT
    '04:00' AS time
  UNION ALL
  SELECT
    '25:30' AS time )
SELECT
  avg_s,
  time(TIMESTAMP_SECONDS(avg_s))
FROM (
  SELECT
    CAST(AVG(secs) AS INT64) AS avg_s
  FROM (
    SELECT
      time,
      SAFE_CAST(SPLIT(time, ':') [ORDINAL(1)] AS INT64)*60+ SAFE_CAST(SPLIT(time, ':') [ORDINAL(2)] AS INT64) AS secs
    FROM
      s ) )

I think there are multiple ways to do it. But, it totally depends on how you need the end result (or even process the intermediate values). One way is you could possibly calculate the total seconds using split, cast & add, do the average & cast it back to time datatype (or maintain in minutes/seconds)

WITH
  s AS (
  SELECT
    '45:00' AS time
  UNION ALL
  SELECT
    '30:00' AS time
  UNION ALL
  SELECT
    '20:00' AS time
  UNION ALL
  SELECT
    '25:00' AS time
  UNION ALL
  SELECT
    '04:00' AS time
  UNION ALL
  SELECT
    '25:30' AS time )
SELECT
  avg_s,
  time(TIMESTAMP_SECONDS(avg_s))
FROM (
  SELECT
    CAST(AVG(secs) AS INT64) AS avg_s
  FROM (
    SELECT
      time,
      SAFE_CAST(SPLIT(time, ':') [ORDINAL(1)] AS INT64)*60+ SAFE_CAST(SPLIT(time, ':') [ORDINAL(2)] AS INT64) AS secs
    FROM
      s ) )