我有一个看起来像这样的表：
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中做到这一点？我有几千条记录。我知道我需要先将时间与日期分开。
尝试下面
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
)
)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句