TSQL OrderedTable

Question

因此，我正在查询一些数据，并尝试确定每个用户登录和注销之间的时间量。我接近得到这个，但我得到的结果是不工作。下面是我现在拥有的代码：

WITH OrderedTable AS
(
SELECT  EventType
        , ModuleAndEventText
        , Time
        , Node
        , UserSID
        , ROW_Number() OVER (Partition BY UserSID ORDER BY UserSID,EventID,Time) RN 
FROM    viewevent 
where EventType in ('BROKER_USERLOGGEDIN','BROKER_USERLOGGEDOUT') and usersid = 'S-1-5-21-999033763-294680432-740312968-10026'
)

SELECT  t1.EventType
    , t1.ModuleAndEventText
    , t1.Node
    , t1.UserSID
    , t1.Time as TimeIn
    , t2.Time as TimeOut
    , DATEDIFF(hour, t1.Time, t2.Time ) TimeElapsedInHours
FROM    OrderedTable t1
JOIN    OrderedTable t2 ON t1.UserSID = t2.UserSID AND t2.RN = t1.RN + 1
WHERE   t1.RN % 2 <> 0
ORDER BY t1.UserSID

以下是我得到的一些结果：

BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-04-14 08:00:36.137   2012-04-14 09:32:08.267     1
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-04-14 08:00:36.137   2012-04-14 09:32:08.267     1
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-11 14:50:40.420   2012-06-11 16:43:08.640     2
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-11 17:49:46.330   2012-06-11 18:42:50.047     1
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-11 18:59:40.550   2012-06-12 23:20:16.027     29
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-12 23:20:16.777   2012-06-12 23:20:16.823     0
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-12 23:20:17.120   2012-06-15 13:03:31.807     62

前两条记录是正确的。最后几个不是，它选择注销的记录作为时间输入，我不清楚如何解决这个问题。以下是数据：

EventType    ModuleAndEventText    Time    Node    UserSID
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-04-14 08:00:36.137 VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-04-14 09:32:08.267 VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-04-14 08:00:36.137 VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-04-14 09:32:08.267 VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-06-11 14:50:40.420 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-06-11 16:43:08.640 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-06-11 17:49:46.330 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-06-11 18:42:50.047 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-06-11 18:59:40.550 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-06-12 23:20:16.027 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-06-12 23:20:16.777 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-06-12 23:20:16.823 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-06-12 23:20:17.120 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-06-15 13:03:31.807 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026

提前谢谢。卡梅隆

Answer 1

问题是，日志文件没有整齐地交错登录和注销。

这是另一种方法。它选择每次登录后的最短注销时间：

select ins.UserSID, ins.time as login_time, min(outs.time) as logout_time
from (SELECT *
      FROM viewevent
      where EventType in ('BROKER_USERLOGGEDOUT')
     ) outs left outer join
     (SELECT *
      FROM viewevent
      where EventType in ('BROKER_USERLOGGEDIN')
     ) ins
     on outs.UserSID = ins.UserSID and
        outs.Time >= ins.Time
group by ins.UserSID

我省略了对用户id的限制(您应该将其添加到子查询或放入with子句中)，以及小时差值的具体计算(因为我使用的是浮点型而不是整型)。

Answer 2

您是否可以在您的t1 WHERE上添加限制

WITH OrderedTable  AS
(
SELECT  EventType
    , ModuleAndEventText
    , Time
    , Node
    , UserSID
    , ROW_Number() OVER (Partition BY UserSID ORDER BY UserSID,EventID,Time) RN 
FROM  viewevent 
where EventType in ('BROKER_USERLOGGEDIN','BROKER_USERLOGGEDOUT') and usersid = 'S-1-5-21-      999033763-294680432-740312968-10026'
 )

SELECT  t1.EventType
    , t1.ModuleAndEventText
    , t1.Node
    , t1.UserSID
    , t1.Time as TimeIn
    , t2.Time as TimeOut
    , DATEDIFF(hour, t1.Time, t2.Time ) TimeElapsedInHours
FROM    OrderedTable2 t1
JOIN    OrderedTable2 t2 ON t1.UserSID = t2.UserSID AND t2.RN = t1.RN + 1
WHERE   t1.RN % 2 <> 0 AND t1.EventType = 'BROKER_USERLOGGEDIN' --ONLY CHANGE IS HERE - THIS RETURNS WHAT MY UNDERSTANDING OF WHAT YOU EXPECT IS

EventType   ModuleAndEventText  Node    UserSID TimeIn  TimeOut TimeElapsedInHours
--------------------------------------------------------------------------------------
BROKER_USERLOGGEDIN User YRMC_MAIN\jerogers has logged in   VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-04-14 08:00:36.137 2012-04-14 09:32:08.267 1
BROKER_USERLOGGEDIN User YRMC_MAIN\jerogers has logged in   VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-04-14 08:00:36.137 2012-04-14 09:32:08.267 1
BROKER_USERLOGGEDIN  User YRMC_MAIN\jerogers has logged in  VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-11 14:50:40.420 2012-06-11 16:43:08.640 2
BROKER_USERLOGGEDIN User YRMC_MAIN\jerogers has logged in   VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-11 17:49:46.330 2012-06-11 18:42:50.047 1
ORDER BY t1.UserSID

Answer 3

假设在没有connect的情况下没有断开连接，我喜欢以下生成伪sessionId的方法

declare @tEvent table (
    UserId int,
    EventType varchar(10),
    EventDate datetime2
)

insert into @tEvent (UserId, EventType, EventDate) values
(1, 'CONNECT', '20120101'),
(2, 'CONNECT', '20120101'),
(1, 'DISCONNECT', '20120102'),
(1, 'CONNECT', '20120103'),
(2, 'DISCONNECT', '20120103'),
(1, 'DISCONNECT', '20120105'),
(1, 'CONNECT', '20120106')

select 
    conn.UserId, conn.SessionId, conn.EventDate as startDate, disco.EventDate as endDate, DATEDIFF(d, conn.EventDate, disco.EventDate) as duration
from 
    (select 
        ROW_NUMBER() over (partition by UserId order by EventDate) SessionId,
        UserId,
        EventDate
    from
        @tEvent 
    where EventType = 'CONNECT') conn
    left join (
    select 
        ROW_NUMBER() over (partition by UserId order by EventDate) SessionId,
        UserId,
        EventDate
    from
        @tEvent 
    where EventType = 'DISCONNECT' ) disco on conn.UserId = disco.UserId and conn.SessionId = disco.SessionId