如何在BigQuery中获取连续时间戳之间的差异

Question

我有一个如下所示的表：

ID    DateTime
1     5-1-16 12:25:13
1     5-1-16 12:28:46
2     5-1-16 12:25:18
2     5-1-16 12:29:34

我想找出每个ID的每个连续时间戳之间的差异（以秒为单位）。在BigQuery中有办法做到这一点吗？我有几千张唱片。我知道我需要先把时间和日期分开。

Answer 1

尝试低于

SELECT
  ID, 
  TIMESTAMP_TO_SEC(TIMESTAMP(DateTime))-TIMESTAMP_TO_SEC(TIMESTAMP(prev_DateTime)) AS diff,
FROM (
  SELECT 
    ID,
    DateTime,
    LAG(DateTime) OVER(PARTITION BY ID ORDER BY DateTime) AS prev_DateTime
  FROM 
    (SELECT 1 AS ID, '2016-05-01 12:25:13' AS DateTime),
    (SELECT 1 AS ID, '2016-05-01 12:28:46' AS DateTime),
    (SELECT 2 AS ID, '2016-05-01 12:25:18' AS DateTime),
    (SELECT 2 AS ID, '2016-05-01 12:29:34' AS DateTime)
)

添加

 SELECT
  ID, 
  TIMESTAMP_TO_SEC(TIMESTAMP(DateTime))-TIMESTAMP_TO_SEC(TIMESTAMP(prev_DateTime)) AS diff,
FROM (
  SELECT 
    ID,
    DateTime,
    LAG(DateTime) OVER(PARTITION BY ID ORDER BY DateTime) AS prev_DateTime
  FROM YourTable
)

我注意到-看起来你的DateTime字段有不寻常的格式-"5-1-16 12:29:34"
如果在实现上述查询时遇到问题，您可以尝试以下查询
请注意：对于此查询，您需要启用标准SQL

SELECT
  ID, 
  UNIX_SECONDS(DateTime) - UNIX_SECONDS(prev_DateTime) AS diff
FROM (
  SELECT 
    ID,
    DateTime,
    LAG(DateTime) OVER(PARTITION BY ID ORDER BY DateTime) AS prev_DateTime
  FROM (
    SELECT
      ID, 
      PARSE_TIMESTAMP("%m-%d-%y %H:%M:%S", DateTime) AS DateTime
    FROM YourTable  
  )
)