计算SQL Server中多行之间的时差

I have the following table:

我有下表：

 | ID   | UserId |   Event   |         Date        |
 |------|--------|-----------|---------------------|
 | 1    | 123    | Start     | 11/07/2017 14:00:00 |
 | 2    | 123    | PauseStart| 11/07/2017 14:52:52 |
 | 3    | 123    | PauseEnd  | 11/07/2017 14:56:03 |
 | 4    | 123    | PauseStart| 11/07/2017 15:39:45 |
 | 5    | 123    | PauseEnd  | 11/07/2017 15:45:10 |
 | 6    | 123    | Finish    | 11/07/2017 17:45:15 |
 | 7    | 124    | Start     | 11/07/2017 18:00:00 |
 | 8    | 124    | PauseStart| 11/07/2017 19:52:52 |
 | 9    | 124    | PauseEnd  | 11/07/2017 20:05:03 |
 | 10   | 124    | Finish    | 11/07/2017 20:45:15 |

I want to sum the time of all pauses for every user:

我想总结每个用户所有暂停的时间：

 | UserID | TotalPauses(seconds)|  
 |------  |---------------------|         
 | 123    | 720                 |
 | 124    | 840                 |

2 个解决方案

#1

One possible way with assumption that PauseStart is always followed directly by PauseEnd.

假设PauseStart总是直接跟随PauseEnd的一种可能方式。

SQL Fiddle Demo

SQL小提琴演示

;WITH cte AS (
SELECT *, 
  ROW_NUMBER() OVER (PARTITION BY userid ORDER BY DATE ASC) AS rn
FROM dbo.YourTable
)

SELECT 
    c1.userid
   , SUM(DATEDIFF(s, c1.date, c2.date)) AS [TotalPauses(seconds)]
FROM cte c1
JOIN cte c2
  ON c1.userid = c2.userid 
  AND c1.rn = c2.rn - 1
WHERE c1.event = 'PauseStart'
GROUP BY c1.userid

#2

Using Window functions on UserID partitions:

在UserID分区上使用Window函数：

;WITH temp AS (
    SELECT org_table.*, lag(Date) OVER (PARTITION BY UserID ORDER BY Date) as prev 
    FROM org_table 
    WHERE Event in ('PauseStart', 'PauseEnd') 
             ), temp2 AS (
                   SELECT UserID , DATEDIFF(s, prev, Date) as pause_time 
                   FROM temp 
                   WHERE Event = 'PauseEnd') 

SELECT UserID, sum(pause_time) as total_pause 
FROM temp2 
GROUP BY UserID

#1