SQL 计算仅包含 2 个特定值的两行之间的时间差

Question

我有一个表格，其中包含有关移动设备上用户的状态更新；每次他们更改状态或更新他们的位置时，都会创建一个新行:

view_id  time_stamp           user           issue_event       original_value   new_value
-----------------------------------------------------------------------------------------
6040462  2017-03-20 02:00:43  DerekRoberts   Position updated  NULL             NULL
6040461  2017-03-20 02:04:01  JamesMorrison  state changed     Active           Paused
6040461  2017-03-20 02:08:33  JamesMorrison  Position updated  NULL             NULL
6040462  2017-03-20 02:20:42  DerekRoberts   Position updated  NULL             NULL
6040462  2017-03-20 02:32:29  DerekRoberts   state changed     Active           Paused
6040461  2017-03-20 02:34:11  JamesMorrison  state changed     Paused           Active
6040461  2017-03-20 02:36:22  JamesMorrison  Position updated  NULL             NULL
6040462  2017-03-20 02:52:47  DerekRoberts   Position updated  NULL             NULL
6040462  2017-03-20 03:01:03  DerekRoberts   state changed     Paused           Active

我想要做的是找出每个用户保持“暂停”状态的时间长度……所以这就是 [new_value] 列中“暂停”值和“事件”值之间的时间差.对于每个用户。

我以为我可以使用 LEAD() 为每个用户查找下一行，但是标记他们暂停时间结束的“事件”很少是下一行......它可以是表格下方的任意行数。

到目前为止，我的查询如下所示:

;WITH UserPauseActivity AS
(SELECT [new_value],
 ShiftDate = CAST([time_stamp] as DATE),
 PauseStartUser = [user],
 PauseEndUser = LEAD([user], 1) OVER(ORDER BY [user], [time_stamp]),
 PauseStart = [time_stamp],
 PauseEnd = LEAD([time_stamp], 1) OVER(ORDER BY [user], [time_stamp]),
 PauseStartDate = CAST([time_stamp] AS DATE),
 PauseEndDate = CAST(LEAD([time_stamp], 1) OVER(ORDER BY [user], [time_stamp]) AS DATE)
FROM [SAFE].[dbo].[cc_shift_log_view])

SELECT PauseStartUser [user],
ShiftDate,
PauseStart,
PauseEnd,
DATEDIFF(minute, PauseStart, PauseEnd) IdleTime
FROM UserPauseActivity
WHERE [new_value] = 'Paused'
AND PauseEnd IS NOT NULL
AND PauseStartUser = PauseEndUser
AND PauseStartDate = PauseEndDate
AND PauseStartDate >= '2017-03-20 00:00:00' and PauseStartDate <= '2017-03-21 23:59:59'
ORDER BY ShiftDate, [user]

返回这个:

user             ShiftDate    PauseStart           PauseEnd              IdleTime
---------------------------------------------------------------------------------
JamesMorrison    2017-03-20   2017-03-20 02:04:01  2017-03-20 02:08:33   4
DerekRoberts     2017-03-20   2017-03-20 02:32:29  2017-03-20 02:52:47   20

PauseStart 值是正确的，但 PauseEnd 是不正确的，作为该用户的表中的下一行，而不是包含相应的“事件”值的下一行，该值将标记其暂停持续时间的实际结束，因此任何帮助弄清楚这一点将不胜感激!

我正在使用 MS SQL Server 2012。

Answer 1

假设唯一可能的状态更改是从事件 -> 暂停 -> 事件...，您可以使用 lead以获得所需的结果。

select usr,shiftDate,pause_start,pause_end,datediff(second,pause_start,pause_end)/60.0 as idleTime
from (select usr,cast(time_stamp as date) as shiftDate,time_stamp as pause_start
      ,lead(time_stamp) over(partition by usr order by time_stamp) as pause_end
      ,original_value,new_value
      from t
      where issue_event='state changed'
     ) t 
where original_value='Active' and new_value='Paused'