sql - 如何用sql计算累计时间差

Question

我想在sql中显示累积时间差。这是时间戳列。时间差应该是累积形式，而不使用临时表，但对于id，我们可以使用ROW_NUMBER():

Timestamp
2015-05-20 11:23:34.853
2015-05-20 11:21:40.790
2015-05-20 11:20:12.447
2015-05-20 11:19:31.723
2015-05-20 11:19:10.993

结果应该是这样的:

TimeDifference
00:00:00
00:01:54
00:01:28
00:00:41
00:00:21

Answer 1

您可以使用 DATEDIFF 与 CONVERT 到 Time 以及基于时间戳顺序的自连接来实现此目的。类似这样的东西(对于 SQL Server 2008)。

;WITH cte as 
(
select '2015-05-20 11:23:34.853' ts
union all select '2015-05-20 11:21:40.790'
union all select '2015-05-20 11:20:12.447'
union all select '2015-05-20 11:19:31.723'
union all select '2015-05-20 11:19:10.993'
), cte2 as 
(
SELECT *,ROW_NUMBER()OVER(ORDER BY ts DESC) rn
FROM cte
)
select t1.ts,t2.ts,CONVERT(TIME,DATEADD(s,ISNULL(DATEDIFF(s,t1.ts,t2.ts),0),0)) diff
from cte2 t1 LEFT JOIN cte2 t2 on t1.rn = t2.rn + 1

输出

ts  ts  diff
2015-05-20 11:23:34.853 NULL    00:00:00.0000000
2015-05-20 11:21:40.790 2015-05-20 11:23:34.853 00:01:54.0000000
2015-05-20 11:20:12.447 2015-05-20 11:21:40.790 00:01:28.0000000
2015-05-20 11:19:31.723 2015-05-20 11:20:12.447 00:00:41.0000000
2015-05-20 11:19:10.993 2015-05-20 11:19:31.723 00:00:21.0000000

如果您使用的是 SQL 2012，则可以像这样使用 LAG/LEAD

SELECT CONVERT(TIME,DATEADD(s,ISNULL(DATEDIFF(s,ts,LAG(ts)over(order by ts DESC)),0),0))
FROM cte
ORDER BY ts DESC