问题

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

"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

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

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

答案1

得分: 0

在BigQuery中执行以下操作：

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）：

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

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

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

答案2

得分: 0

你愿意尝试以下内容吗？

```sql
SELECT *,
       TIME (
         EXTRACT(HOUR FROM time_difference),
         EXTRACT(MINUTE FROM time_difference),
         EXTRACT(SECOND FROM time_difference)
       ) AS time_diff
  FROM (
    SELECT CAST(LEFT(time, 8) AS TIME) AS time, 
          COALESCE(buyer_order_id, SELLER_ORDER_ID) AS order_id,
          CAST(LEFT(time, 8) AS TIME) - LAG(CAST(LEFT(time, 8) AS TIME), 1) 
            OVER (PARTITION BY COALESCE(buyer_order_id, SELLER_ORDER_ID) ORDER BY time) AS time_difference
    FROM orders.sheet1 
  WHERE message_type = "ENTER" OR message_type = "DELETE" 
) ORDER BY order_id;

<details>
<summary>英文:</summary>

Would you try below ?

```sql
SELECT *,
       TIME (
         EXTRACT(HOUR FROM time_difference),
         EXTRACT(MINUTE FROM time_difference),
         EXTRACT(SECOND FROM time_difference)
       ) AS time_diff
  FROM (
    SELECT CAST(Left(time, 8) AS time) AS time, 
          coalesce(buyer_order_id, SELLER_ORDER_ID) AS order_id,
          CAST(left(time, 8) AS time) - lag(CAST(Left(time, 8) AS time), 1) 
            OVER (partition by coalesce(buyer_order_id, SELLER_ORDER_ID) order by time) AS time_difference
    FROM orders.sheet1 
  WHERE message_type = &quot;ENTER&quot; OR message_Type = &quot;DELETE&quot; 
) order by order_id;

答案3

得分: 0

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

WITH
  -- 样本数据
  tmp AS (
  SELECT
    t,
    oid,
    msg_type
  FROM
    UNNEST( ["01:56:26", "04:20:24", "01:57:00", "03:31:57"]) t
  WITH
  OFFSET
    o1
  LEFT JOIN
    UNNEST([267, 267, 268, 268]) oid
  WITH
  OFFSET
    o2
  ON
    o1 = o2
  LEFT JOIN
    UNNEST(["ENTER", "DELETE", "ENTER", "DELETE"]) msg_type
  WITH
  OFFSET
    o3
  ON
    o2 = o3 ),

  -- 计算滞后
  lags AS (
  SELECT
    CAST(LEFT(t, 8) AS time) AS time,
    oid,
    msg_type,
    LAG(CAST(LEFT(t, 8) AS time), 1) OVER (PARTITION BY oid ORDER BY t) AS time_lag
  FROM
    tmp
  WHERE
    msg_type = "ENTER"
    OR msg_type = "DELETE"
  ORDER BY
    oid)

-- 时间差异（以秒为单位）
SELECT
  time,
  oid AS order_id,
  msg_type AS message_type,
  TIME_DIFF(time, time_lag, SECOND) AS time_difference_seconds
FROM lags;
[1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#time_diff

WITH
  -- sample data
  tmp AS (
  SELECT
    t,
    oid,
    msg_type
  FROM
    UNNEST( ["01:56:26", "04:20:24", "01:57:00", "03:31:57"]) t
  WITH
  OFFSET
    o1
  LEFT JOIN
    UNNEST([267, 267, 268, 268]) oid
  WITH
  OFFSET
    o2
  ON
    o1 = o2
  LEFT JOIN
    UNNEST(["ENTER", "DELETE", "ENTER", "DELETE"]) msg_type
  WITH
  OFFSET
    o3
  ON
    o2 = o3 ),

  -- calculate the lag
  lags AS (
  SELECT
    CAST(LEFT(t, 8) AS time) AS time,
    oid,
    msg_type,
    LAG(CAST(LEFT(t, 8) AS time), 1) OVER (PARTITION BY oid ORDER BY t) AS time_lag
  FROM
    tmp
  WHERE
    msg_type = "ENTER"
    OR msg_type = "DELETE"
  ORDER BY
    oid)

-- time difference in seconds
SELECT
  time,
  oid AS order_id,
  msg_type AS message_type,
  TIME_DIFF(time, time_lag, SECOND) AS time_difference_seconds
FROM lags;