我怎样才能得到最近的不同事件？

Question

下面是mytable的样子：

+-----------------+-----------+-------------+------------------+
|      Time       | EventType | MachineName | UserDisplayName  |
+-----------------+-----------+-------------+------------------+
| 7/22/2014 6:52  | CONNECTED | MACH-10     | alice.brown      |
| 7/22/2014 6:52  | PENDING   | MACH-10     | alice.brown      |
| 7/22/2014 9:09  | PENDING   | MACH-2      | mike.hensworth   |
| 7/22/2014 6:58  | CONNECTED | MACH-2      | mike.hensworth   |
| 7/22/2014 6:57  | PENDING   | MACH-2      | mike.hensworth   |
| 7/22/2014 7:00  | CONNECTED | MACH-3      | will.garden      |
| 7/22/2014 6:59  | PENDING   | MACH-3      | will.garden      |
| 7/22/2014 5:30  | PENDING   | MACH-3      | will.garden      |
| 7/22/2014 11:20 | CONNECTED | MACH-4      | kent.clark.admin |
| 7/22/2014 11:20 | PENDING   | MACH-4      | kent.clark.admin |
| 7/22/2014 9:28  | CONNECTED | MACH-5      | inigo.montoya    |
| 7/22/2014 9:28  | PENDING   | MACH-5      | inigo.montoya    |
| 7/22/2014 9:15  | PENDING   | MACH-123    | inigo.montoya    |
| 7/22/2014 12:37 | CONNECTED | P-002       | joseph.smith     |
| 7/22/2014 12:36 | PENDING   | P-002       | joseph.smith     |
| 7/22/2014 9:43  | CONNECTED | P-005       | penny.pennyworth |
| 7/22/2014 9:41  | PENDING   | P-005       | penny.pennyworth |
| 7/22/2014 5:41  | CONNECTED | P-006       | thor.zeus        |
| 7/22/2014 5:40  | PENDING   | P-006       | thor.zeus        |
+-----------------+-----------+-------------+------------------+

情况是人们(UserDisplayName)正在登录机器(MachineNames)。事件类型挂起是登录的开始，Eventtype连接是成功的连接。

本质上，我需要用户登录到机器所需的平均时间。我没有一种很好的方法将PENDING状态连接到CONNECTED状态，因此我试图计算CONNECTED状态与给定MachineName的最新PENDING EventType之间的时间差。

我不知道该怎么做，这就是这个问题的意义所在。

但我也有一些问题，我想克服和识别。

1. (例如，inigo.montoya)有时用户在一台机器上启动一个PENDING，然后在需要长时间时放弃，然后在另一台机器上启动第二个PENDING。没有列出CONNECTED来关闭第一个PENDING事件。我想确认这些事件。
2. (例如，MACH-3)有时PENDING永远不会成为机器上的CONNECTED，而且用户可以以某种方式启动第二个PENDING，该PENDING按预期工作。我想确认这些事件。

Answer 1

我觉得这能做你想做的事：

with successes as
 (select x.userdisplayname,
         x.machinename,
         x.time as connection_time,
         y.time as pending_time,
         datediff(mi, y.time, x.time) as mins_between
    from mytable x
    join mytable y
      on x.userdisplayname = y.userdisplayname
     and x.machinename = y.machinename
   where x.eventtype = 'CONNECTED'
     and y.eventtype = 'PENDING'
     and y.time = (select max(z.time)
                     from mytable z
                    where z.userdisplayname = y.userdisplayname
                      and z.machinename = y.machinename
                      and z.time < y.time))
select y.userdisplayname, y.machinename, null, y.time, null
  from mytable y
  left join successes s
    on y.userdisplayname = s.userdisplayname
   and s.machinename = y.machinename
   and y.time = s.pending_time
 where s.connection_time is null
   and y.eventtype = 'PENDING'
union all
select * from successes

请注意，具有空值的行表示连接尝试失败。