如何从这个时间差列中删除 “0-0 0″？

huangapple
go评论107阅读模式
英文:

How do I remove the "0-0 0" from this time difference column?

问题

以下是翻译好的部分，代码部分不翻译：

"sample output" 翻译成中文：
样本输出：

"01:56:26 267 ENTER null" 翻译成中文：
01:56:26 267 输入 空

"04:20:24 267 DELETE 0-0 0 2:23:58" 翻译成中文：
04:20:24 267 删除 0-0 0 2:23:58

"01:57:00 268 ENTER null" 翻译成中文：
01:57:00 268 输入 空

"03:31:57 268 DELETE 0-0 0 1:34:57" 翻译成中文：
03:31:57 268 删除 0-0 0 1:34:57

英文:

I have a data set that pulls the difference of time between each entered and deleted order. However with this code I get an output that reads 0-0 0 and the time difference. I would like to remove the 0-0 0 if possible.

  1. SELECT CAST(Left(time, 8) AS time) AS time, 
  2.        coalesce(buyer_order_id, SELLER_ORDER_ID) AS order_id,
  3.        CAST(left(time, 8) AS time) - lag(CAST(Left(time, 8) AS time), 1) 
  4.         OVER (partition by coalesce(buyer_order_id, SELLER_ORDER_ID) order by time) AS time_difference
  5. FROM orders.sheet1 
  6. WHERE message_type = "ENTER" OR message_Type = "DELETE" 
  7. order by order_id

sample output:

  1. time     order_id  message_type time_difference
  2. 01:56:26 267       ENTER        null
  3. 04:20:24 267       DELETE       0-0 0 2:23:58	
  4. 01:57:00 268       ENTER        null
  5. 03:31:57 268       DELETE       0-0 0 1:34:57

tried many variations of replace and right functions but have been unsuccessful since the output is a time one and not a string.

答案1

得分: 0

在BigQuery中执行以下操作：

  1. TIME(TIMESTAMP_SUB(CAST(TIME(TIMESTAMP(left(time, 8))) AS TIMESTAMP), TIMESTAMP_SUB(CAST(TIME(TIMESTAMP(left(time, 8))) AS TIMESTAMP), 1, 'SECOND')))

如果是SQL Server，可以使用CONVERT函数（选择14或114）：

  1. CONVERT(TIME, DATEADD(SECOND, DATEDIFF(SECOND, '00:00:00', CAST(TIME FROM (CAST(left(time, 8) AS time))) - LAG(CAST(TIME FROM (CAST(LEFT(time, 8) AS time))), 1, '00:00:00'), 114), '00:00:00'), 114)

更多详细信息请参阅此处：

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

英文:

In bigquery do this:

  1. TIME(CAST(left(time, 8) AS time) - lag(CAST(Left(time, 8))

You can use convert if this is SQL server (you want 14 or 114)

  1. CONVERT(time, CAST(left(time, 8) AS time) - lag(CAST(Left(time, 8), 114)

More details documented here:

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

答案2

得分: 0

  1. 你愿意尝试以下内容吗？
  3. ```sql
  4. SELECT *,
  5.        TIME (
  6.          EXTRACT(HOUR FROM time_difference),
  7.          EXTRACT(MINUTE FROM time_difference),
  8.          EXTRACT(SECOND FROM time_difference)
  9.        ) AS time_diff
  10.   FROM (
  11.     SELECT CAST(LEFT(time, 8) AS TIME) AS time, 
  12.           COALESCE(buyer_order_id, SELLER_ORDER_ID) AS order_id,
  13.           CAST(LEFT(time, 8) AS TIME) - LAG(CAST(LEFT(time, 8) AS TIME), 1) 
  14.             OVER (PARTITION BY COALESCE(buyer_order_id, SELLER_ORDER_ID) ORDER BY time) AS time_difference
  15.     FROM orders.sheet1 
  16.   WHERE message_type = "ENTER" OR message_type = "DELETE" 
  17. ) ORDER BY order_id;
  2. <details>
  3. <summary>英文:</summary>
  5. Would you try below ?
  7. ```sql
  8. SELECT *,
  9.        TIME (
  10.          EXTRACT(HOUR FROM time_difference),
  11.          EXTRACT(MINUTE FROM time_difference),
  12.          EXTRACT(SECOND FROM time_difference)
  13.        ) AS time_diff
  14.   FROM (
  15.     SELECT CAST(Left(time, 8) AS time) AS time, 
  16.           coalesce(buyer_order_id, SELLER_ORDER_ID) AS order_id,
  17.           CAST(left(time, 8) AS time) - lag(CAST(Left(time, 8) AS time), 1) 
  18.             OVER (partition by coalesce(buyer_order_id, SELLER_ORDER_ID) order by time) AS time_difference
  19.     FROM orders.sheet1 
  20.   WHERE message_type = &quot;ENTER&quot; OR message_Type = &quot;DELETE&quot; 
  21. ) order by order_id;

答案3

得分: 0

你可能想重新考虑time_difference列的类型。BigQuery有TIME_DIFF()函数，返回INT64。以下是一个示例：

  1. WITH
  2.   -- 样本数据
  3.   tmp AS (
  4.   SELECT
  5.     t,
  6.     oid,
  7.     msg_type
  8.   FROM
  9.     UNNEST( ["01:56:26", "04:20:24", "01:57:00", "03:31:57"]) t
  10.   WITH
  11.   OFFSET
  12.     o1
  13.   LEFT JOIN
  14.     UNNEST([267, 267, 268, 268]) oid
  15.   WITH
  16.   OFFSET
  17.     o2
  18.   ON
  19.     o1 = o2
  20.   LEFT JOIN
  21.     UNNEST(["ENTER", "DELETE", "ENTER", "DELETE"]) msg_type
  22.   WITH
  23.   OFFSET
  24.     o3
  25.   ON
  26.     o2 = o3 ),
  28.   -- 计算滞后
  29.   lags AS (
  30.   SELECT
  31.     CAST(LEFT(t, 8) AS time) AS time,
  32.     oid,
  33.     msg_type,
  34.     LAG(CAST(LEFT(t, 8) AS time), 1) OVER (PARTITION BY oid ORDER BY t) AS time_lag
  35.   FROM
  36.     tmp
  37.   WHERE
  38.     msg_type = "ENTER"
  39.     OR msg_type = "DELETE"
  40.   ORDER BY
  41.     oid)
  43. -- 时间差异（以秒为单位）
  44. SELECT
  45.   time,
  46.   oid AS order_id,
  47.   msg_type AS message_type,
  48.   TIME_DIFF(time, time_lag, SECOND) AS time_difference_seconds
  49. FROM lags;
  50. [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#time_diff
英文:

You might want to reconsider the type of the time_difference column. BigQuery has the TIME_DIFF() function which returns an INT64.
Here's an example:

  1. WITH
  2.   -- sample data
  3.   tmp AS (
  4.   SELECT
  5.     t,
  6.     oid,
  7.     msg_type
  8.   FROM
  9.     UNNEST( [&quot;01:56:26&quot;, &quot;04:20:24&quot;, &quot;01:57:00&quot;, &quot;03:31:57&quot;]) t
  10.   WITH
  11.   OFFSET
  12.     o1
  13.   LEFT JOIN
  14.     UNNEST([267, 267, 268, 268]) oid
  15.   WITH
  16.   OFFSET
  17.     o2
  18.   ON
  19.     o1 = o2
  20.   LEFT JOIN
  21.     UNNEST([&quot;ENTER&quot;, &quot;DELETE&quot;, &quot;ENTER&quot;, &quot;DELETE&quot;]) msg_type
  22.   WITH
  23.   OFFSET
  24.     o3
  25.   ON
  26.     o2 = o3 ),
  28.   -- calculate the lag
  29.   lags AS (
  30.   SELECT
  31.     CAST(LEFT(t, 8) AS time) AS time,
  32.     oid,
  33.     msg_type,
  34.     LAG(CAST(LEFT(t, 8) AS time), 1) OVER (PARTITION BY oid ORDER BY t) AS time_lag
  35.   FROM
  36.     tmp
  37.   WHERE
  38.     msg_type = &quot;ENTER&quot;
  39.     OR msg_type = &quot;DELETE&quot;
  40.   ORDER BY
  41.     oid)
  43. -- time difference in seconds
  44. SELECT
  45.   time,
  46.   oid AS order_id,
  47.   msg_type AS message_type,
  48.   TIME_DIFF(time, time_lag, SECOND) AS time_difference_seconds
  49. FROM lags;

huangapple
  • 本文由 发表于 2023年2月16日 05:07:36
  • 转载请务必保留本文链接：https://go.coder-hub.com/75465442.html
  • casting
  • google-bigquery
  • partitioning
  • sql
  • window-functions
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定