T-SQL脚本来计算多行之间的时间差 [英] T-SQL script to calculate time difference between multiple rows

问题描述

我有一个像这样的表：

EventDateTime           EventName   AppID
-----------------------------------------
2014-06-27 22:17:19     GotFocus    1000
2014-06-27 22:17:20     LostFocus   1000
2014-06-27 22:17:22     GotFocus    1005
2014-06-27 22:17:24     LostFocus   1005
2014-06-27 22:17:27     GotFocus    1000
2014-06-27 22:17:30     LostFocus   1000
2014-06-27 22:17:37     GotFocus    1005
2014-06-27 22:17:40     LostFocus   1005

我需要做的是创建一个T-SQL脚本，用于计算每个应用关注的总计持续时间（即每个应用的所有LostFocus-GotFocus的总和）。因此，对于上表，我应该得到：

What I need to do is to create a T-SQL script that computes the total duration that each app was in focus (i.e. sum of all LostFocus-GotFocus per app). So for the table above I should get:

Duration    AppID
------------------
4           1000
5           1005

如何我实现了吗？

推荐答案

正如xQbert所指出的，取决于您的数据的干净程度。假设每个GotFocus都有一个LostFocus，并且LostFocus EventDateTime大于或等于GotFocus EventDateTime，这应该可以工作（小提琴： http://sqlfiddle.com/#!3/f36a4/14 ）：

As xQbert pointed out, depends on how clean your data is. Assuming that there is one LostFocus for every GotFocus and that LostFocus EventDateTime is greater than or equal to GotFocus EventDateTime, this should work (fiddle: http://sqlfiddle.com/#!3/f36a4/14):

WITH cteGotFocus
AS
(
  SELECT  AppID,
          ROW_NUMBER() OVER(PARTITION BY AppID ORDER BY EventDateTime) AS RID,
          EventDateTime
  FROM    Table1
  WHERE   EventName = 'GotFocus'
),
cteLostFocus
AS
(
  SELECT  AppID,
          ROW_NUMBER() OVER(PARTITION BY AppID ORDER BY EventDateTime) AS RID,
          EventDateTime
  FROM    Table1
  WHERE   EventName = 'LostFocus'
)

SELECT   SUM(DATEDIFF(s, gf.EventDateTime, lf.EventDateTime)) AS Duration,
         gf.AppID
FROM     cteGotFocus gf INNER JOIN cteLostFocus lf
         ON gf.AppID = lf.AppID AND
            gf.RID = lf.RID
GROUP BY  gf.AppID
ORDER BY  gf.AppID;

编辑：只是压缩一些不必要的CTE。小提琴链接已更新。

Just condensing down some of the unnecessary CTEs. Fiddle link updated.

这篇关于T-SQL脚本来计算多行之间的时间差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！