怎样统计logout时间

Question

hi,有个表,结构如下:CREATE TABLE [dbo].[bw_LoggingEvent](
[Id] [int] IDENTITY(1,1) NOT NULL,
[OwnerId] [int] NOT NULL,
[TypeId] [int] NOT NULL,
[ApplicationId] [int] NOT NULL,
[Created] [datetime] NOT NULL)ownerid是userid,typeid=2是login,typid=3是logout,但是有的只有login没有logout,也许可能有的只有logout没有login(dirty data),applicationid是各个module的id,现在要把user在各个application中花费的时间,login的次数都统计出来,怎样统计?
    我的想法是,同一个user,同一个applicationid,与loginTime最近的typeid=3的就是它对应的logout时间,写法如下:  select min(ownerid) as userid,min(typeid) as typeid,min(ApplicationId) as applicationid,created as loginTime,min(int3) as userid,min(date1) as logoutTime from
    ( SELECT ble.OwnerId, ble.TypeId ,ble.ApplicationId ,ble.Created  FROM [bw_LoggingEvent] ble
 where ble.typeid=2 and created>'2011-01-01' and created<getdate()) aa,(
  SELECT ble.TypeId as int1,ble.ApplicationId as int2,ble.Created as date1,ble.OwnerId as int3
  FROM [bw_LoggingEvent] ble where ble.typeid=3 and created>'2011-01-01' and created<getdate() ) bb 
  where aa.OwnerId=bb.int3 and aa.applicationid=bb.int2 and bb.date1>aa.created 
  and  datediff(hour,aa.created,bb.date1)<12  
  group by created我是认为两个相差12小时以内的都可能为login,logout关系,超过12小时的就不计算在内,结果如下:
userid typeid applicationid loginTime userid logoutTime
5 2 1 2011-05-06 12:48:47.000 5 2011-05-06 12:51:54.000
5 2 1 2011-05-06 12:52:08.000 5 2011-05-06 13:04:52.000
5 2 1 2011-05-06 13:10:28.000 5 2011-05-06 13:11:25.000
5 2 1 2011-05-06 13:11:25.000 5 2011-05-06 13:13:38.000
5 2 1 2011-05-06 13:13:37.000 5 2011-05-06 13:13:38.000
137 2 1 2011-06-08 15:52:28.000 137 2011-06-08 16:28:14.000
137 2 1 2011-06-08 16:32:11.000 137 2011-06-08 16:36:04.000
57 2 1 2011-06-08 16:53:56.000 57 2011-06-08 16:55:19.000
47 2 1 2011-06-08 16:56:33.000 47 2011-06-08 16:57:41.000
27 2 1 2011-06-08 17:09:55.000 27 2011-06-08 17:12:53.000
47 2 1 2011-06-08 17:12:06.000 47 2011-06-08 17:14:21.000
56 2 1 2011-06-08 17:13:30.000 56 2011-06-08 17:15:06.000
54 2 1 2011-06-08 17:14:16.000 54 2011-06-08 17:17:21.000
57 2 1 2011-06-08 17:15:23.000 57 2011-06-08 17:18:21.000
51 2 1 2011-06-08 17:17:49.000 51 2011-06-08 17:23:49.000
57 2 1 2011-06-08 17:19:15.000 57 2011-06-08 17:22:05.000
51 2 1 2011-06-08 17:24:01.000 51 2011-06-08 17:28:05.000
57 2 1 2011-06-08 17:25:25.000 57 2011-06-08 17:32:35.000
现在的问题是:有些没有logout,导致统计出来的数据不正确,
5 2 1 2011-05-06 13:11:25.000 5 2011-05-06 13:13:38.000
5 2 1 2011-05-06 13:13:37.000 5 2011-05-06 13:13:38.000大家有没有什么好的方法改进一下?参考数据:OwnerId TypeId ApplicationId Created
5 2 1 2011-05-06 12:48:47.000
5 1 1 2011-05-06 12:50:19.000
5 1 1 2011-05-06 12:51:52.000
5 3 1 2011-05-06 12:51:54.000
5 2 1 2011-05-06 12:52:08.000
5 1 1 2011-05-06 13:04:44.000
5 3 1 2011-05-06 13:04:52.000
5 2 1 2011-05-06 13:10:28.000
5 3 1 2011-05-06 13:10:28.000
5 2 1 2011-05-06 13:11:25.000
5 3 1 2011-05-06 13:11:25.000
5 2 1 2011-05-06 13:13:37.000
5 3 1 2011-05-06 13:13:38.000
77 3 1 2011-06-06 14:12:04.000
87 3 1 2011-06-06 14:26:35.000
87 3 1 2011-06-06 14:27:25.000
87 3 1 2011-06-06 14:28:18.000
87 3 1 2011-06-06 14:32:45.000
87 3 1 2011-06-06 14:34:48.000
87 3 1 2011-06-06 14:43:26.000
87 3 1 2011-06-06 14:44:26.000
77 3 1 2011-06-06 15:20:15.000
78 3 1 2011-06-06 15:20:21.000
78 3 1 2011-06-06 15:20:21.000
87 3 1 2011-06-06 14:45:26.000
47 3 1 2011-06-06 14:48:13.000
87 3 1 2011-06-06 14:33:53.000
48 3 1 2011-06-06 14:48:28.000
47 3 1 2011-06-06 14:49:29.000
47 3 1 2011-06-06 14:50:29.000

Answer 1

login,logout二者缺一的不统计,行不?

Answer 2

--这个按照你的要求查出的结果,至于后面对于时间的统计,你要统计分,小时,还是...?
select m.OwnerId , 
       m.ApplicationId , 
       m.Created loginTime,
       logoutTime = (select top 1 Created from bw_LoggingEvent n where n.OwnerId = m.OwnerId and n.ApplicationId = m.ApplicationId and n.Created > m.Created order by n.Created and n.typeid = 3)
from bw_LoggingEvent m where typeid = 2

Answer 3

--我更改了部分数据,以实现测试效果.
create table bw_LoggingEvent(OwnerId int,TypeId int,ApplicationId int,Created datetime)
insert into bw_LoggingEvent values(5  ,2 ,1 ,'2011-05-06 12:48:47.000')
insert into bw_LoggingEvent values(5  ,1 ,1 ,'2011-05-06 12:50:19.000')
insert into bw_LoggingEvent values(5  ,1 ,1 ,'2011-05-06 12:51:52.000')
insert into bw_LoggingEvent values(5  ,3 ,1 ,'2011-05-06 12:51:54.000')
insert into bw_LoggingEvent values(5  ,2 ,1 ,'2011-05-06 12:52:08.000')
insert into bw_LoggingEvent values(5  ,1 ,1 ,'2011-05-06 13:04:44.000')
insert into bw_LoggingEvent values(5  ,3 ,1 ,'2011-05-06 13:04:52.000')
insert into bw_LoggingEvent values(5  ,2 ,1 ,'2011-05-06 13:10:28.000')
insert into bw_LoggingEvent values(5  ,3 ,1 ,'2011-05-06 13:10:28.000')
insert into bw_LoggingEvent values(5  ,2 ,1 ,'2011-05-06 13:11:25.000')
insert into bw_LoggingEvent values(5  ,3 ,1 ,'2011-05-06 13:11:25.000')
insert into bw_LoggingEvent values(5  ,2 ,1 ,'2011-05-06 13:13:37.000')
insert into bw_LoggingEvent values(5  ,3 ,1 ,'2011-05-06 13:13:38.000')
insert into bw_LoggingEvent values(77 ,3 ,1 ,'2011-06-06 14:12:04.000')
insert into bw_LoggingEvent values(87 ,2 ,1 ,'2011-06-06 14:26:35.000')
insert into bw_LoggingEvent values(87 ,3 ,1 ,'2011-06-06 14:27:25.000')
insert into bw_LoggingEvent values(87 ,2 ,1 ,'2011-06-06 14:28:18.000')
insert into bw_LoggingEvent values(87 ,3 ,1 ,'2011-06-06 14:32:45.000')
insert into bw_LoggingEvent values(87 ,2 ,1 ,'2011-06-06 14:34:48.000')
insert into bw_LoggingEvent values(87 ,3 ,1 ,'2011-06-06 14:43:26.000')
insert into bw_LoggingEvent values(87 ,2 ,1 ,'2011-06-06 14:44:26.000')
insert into bw_LoggingEvent values(77 ,2 ,1 ,'2011-06-06 15:20:15.000')
insert into bw_LoggingEvent values(78 ,3 ,1 ,'2011-06-06 15:20:21.000')
insert into bw_LoggingEvent values(78 ,3 ,1 ,'2011-06-06 15:20:21.000')
insert into bw_LoggingEvent values(87 ,3 ,1 ,'2011-06-06 14:45:26.000')
insert into bw_LoggingEvent values(47 ,2 ,1 ,'2011-06-06 14:48:13.000')
insert into bw_LoggingEvent values(87 ,3 ,1 ,'2011-06-06 14:33:53.000')
insert into bw_LoggingEvent values(48 ,3 ,1 ,'2011-06-06 14:48:28.000')
insert into bw_LoggingEvent values(47 ,3 ,1 ,'2011-06-06 14:49:29.000')
insert into bw_LoggingEvent values(47 ,3 ,1 ,'2011-06-06 14:50:29.000')
go--这是查询出来的数据。
select m.OwnerId , 
       m.ApplicationId , 
       m.Created loginTime,
       logoutTime = (select top 1 Created from bw_LoggingEvent n where n.OwnerId = m.OwnerId and n.ApplicationId = m.ApplicationId and n.Created > m.Created and n.typeid = 3 order by n.Created)
from bw_LoggingEvent m where typeid = 2
order by m.OwnerId , m.ApplicationId , loginTime
/*
OwnerId     ApplicationId loginTime                                              logoutTime                                             
----------- ------------- ------------------------------------------------------ ------------------------------------------------------ 
5           1             2011-05-06 12:48:47.000                                2011-05-06 12:51:54.000
5           1             2011-05-06 12:52:08.000                                2011-05-06 13:04:52.000
5           1             2011-05-06 13:10:28.000                                2011-05-06 13:11:25.000
5           1             2011-05-06 13:11:25.000                                2011-05-06 13:13:38.000
5           1             2011-05-06 13:13:37.000                                2011-05-06 13:13:38.000
47          1             2011-06-06 14:48:13.000                                2011-06-06 14:49:29.000
77          1             2011-06-06 15:20:15.000                                NULL
87          1             2011-06-06 14:26:35.000                                2011-06-06 14:27:25.000
87          1             2011-06-06 14:28:18.000                                2011-06-06 14:32:45.000
87          1             2011-06-06 14:34:48.000                                2011-06-06 14:43:26.000
87          1             2011-06-06 14:44:26.000                                2011-06-06 14:45:26.000（所影响的行数为 11 行）*/--这是按照秒进行的统计，如果不是按照秒进行统计，自己更换参数即可。datediff(mi,loginTime,logoutTime) -- > 分钟
select OwnerId , ApplicationId , sum(case when loginTime is null or logoutTime is null then 0 else datediff(ss,loginTime,logoutTime) end) 秒
from
(
select m.OwnerId , 
       m.ApplicationId , 
       m.Created loginTime,
       logoutTime = (select top 1 Created from bw_LoggingEvent n where n.OwnerId = m.OwnerId and n.ApplicationId = m.ApplicationId and n.Created > m.Created and n.typeid = 3 order by n.Created)
from bw_LoggingEvent m where typeid = 2
) k
group by OwnerId , ApplicationId
/*
OwnerId     ApplicationId 秒           
----------- ------------- ----------- 
5           1             1142
47          1             76
77          1             0
87          1             895（所影响的行数为 4 行）
*/
drop table bw_LoggingEvent
 
 

Answer 4

order by n.Created and n.typeid = 3,
and放错地方了,我试了一下:
  select m.OwnerId , 
       m.ApplicationId , 
       m.Created loginTime,
       logoutTime = (
       select top 1 Created from bw_LoggingEvent n where n.OwnerId = m.OwnerId and n.ApplicationId = m.ApplicationId 
       and n.Created > m.Created   and n.typeid = 3  order by n.Created )
from bw_LoggingEvent m where typeid = 2结果还是不能去掉重复的logout记录,比如:OwnerId ApplicationId loginTime logoutTime
5 1 2011-05-06 13:11:25.000 2011-05-06 13:13:38.000
5 1 2011-05-06 13:13:37.000 2011-05-06 13:13:38.000
这两个的logout都一样,有没有什么办法避免这种情况?

Answer 5

OwnerId ApplicationId loginTime logoutTime
5 1 2011-05-06 13:11:25.000 2011-05-06 13:13:38.000
5 1 2011-05-06 13:13:37.000 2011-05-06 13:13:38.000
这种情况要么只应该为
OwnerId ApplicationId loginTime logoutTime
5 1 2011-05-06 13:11:25.000 2011-05-06 13:13:37.000
5 1 2011-05-06 13:13:37.000 2011-05-06 13:13:38.000
或者只有一条,
OwnerId ApplicationId loginTime logoutTime
5 1 2011-05-06 13:11:25.000 2011-05-06 13:13:38.000现在的问题是怎样把重复的logout去掉,也就是已经匹配的logout时间就不要再去匹配新的login了.

Answer 6

OwnerId ApplicationId loginTime logoutTime
5 1 2011-05-06 13:11:25.000 2011-05-06 13:13:38.000
5 1 2011-05-06 13:13:37.000 2011-05-06 13:13:38.000
这两个的logout都一样,有没有什么办法避免这种情况?--
你如果缺少logout的话,就有可能出现重复,无法避免?
或者你的意思上面两个任选一个?

Answer 7

能变成下面的任意一种情况就行:
1.OwnerId ApplicationId loginTime logoutTime
5 1 2011-05-06 13:11:25.000 2011-05-06 13:13:37.000
5 1 2011-05-06 13:13:37.000 2011-05-06 13:13:38.0002.OwnerId ApplicationId loginTime logoutTime
5 1 2011-05-06 13:11:25.000 2011-05-06 13:13:38.000有什么方法变吗?我就是被卡在这地方了.难道要group by logout时间?

Answer 8

--再用一层子查询,分组去最小(我目前用,你自己更换)或最大,然后再group by 
create table bw_LoggingEvent(OwnerId int,TypeId int,ApplicationId int,Created datetime)
insert into bw_LoggingEvent values(5  ,2 ,1 ,'2011-05-06 12:48:47.000')
insert into bw_LoggingEvent values(5  ,1 ,1 ,'2011-05-06 12:50:19.000')
insert into bw_LoggingEvent values(5  ,1 ,1 ,'2011-05-06 12:51:52.000')
insert into bw_LoggingEvent values(5  ,3 ,1 ,'2011-05-06 12:51:54.000')
insert into bw_LoggingEvent values(5  ,2 ,1 ,'2011-05-06 12:52:08.000')
insert into bw_LoggingEvent values(5  ,1 ,1 ,'2011-05-06 13:04:44.000')
insert into bw_LoggingEvent values(5  ,3 ,1 ,'2011-05-06 13:04:52.000')
insert into bw_LoggingEvent values(5  ,2 ,1 ,'2011-05-06 13:10:28.000')
insert into bw_LoggingEvent values(5  ,3 ,1 ,'2011-05-06 13:10:28.000')
insert into bw_LoggingEvent values(5  ,2 ,1 ,'2011-05-06 13:11:25.000')
insert into bw_LoggingEvent values(5  ,3 ,1 ,'2011-05-06 13:11:25.000')
insert into bw_LoggingEvent values(5  ,2 ,1 ,'2011-05-06 13:13:37.000')
insert into bw_LoggingEvent values(5  ,3 ,1 ,'2011-05-06 13:13:38.000')
insert into bw_LoggingEvent values(77 ,3 ,1 ,'2011-06-06 14:12:04.000')
insert into bw_LoggingEvent values(87 ,2 ,1 ,'2011-06-06 14:26:35.000')
insert into bw_LoggingEvent values(87 ,3 ,1 ,'2011-06-06 14:27:25.000')
insert into bw_LoggingEvent values(87 ,2 ,1 ,'2011-06-06 14:28:18.000')
insert into bw_LoggingEvent values(87 ,3 ,1 ,'2011-06-06 14:32:45.000')
insert into bw_LoggingEvent values(87 ,2 ,1 ,'2011-06-06 14:34:48.000')
insert into bw_LoggingEvent values(87 ,3 ,1 ,'2011-06-06 14:43:26.000')
insert into bw_LoggingEvent values(87 ,2 ,1 ,'2011-06-06 14:44:26.000')
insert into bw_LoggingEvent values(77 ,2 ,1 ,'2011-06-06 15:20:15.000')
insert into bw_LoggingEvent values(78 ,3 ,1 ,'2011-06-06 15:20:21.000')
insert into bw_LoggingEvent values(78 ,3 ,1 ,'2011-06-06 15:20:21.000')
insert into bw_LoggingEvent values(87 ,3 ,1 ,'2011-06-06 14:45:26.000')
insert into bw_LoggingEvent values(47 ,2 ,1 ,'2011-06-06 14:48:13.000')
insert into bw_LoggingEvent values(87 ,3 ,1 ,'2011-06-06 14:33:53.000')
insert into bw_LoggingEvent values(48 ,3 ,1 ,'2011-06-06 14:48:28.000')
insert into bw_LoggingEvent values(47 ,3 ,1 ,'2011-06-06 14:49:29.000')
insert into bw_LoggingEvent values(47 ,3 ,1 ,'2011-06-06 14:50:29.000')
go--这是查询出来的数据。
select OwnerId , ApplicationId , min(loginTime) loginTime , logoutTime from
(
select m.OwnerId , 
       m.ApplicationId , 
       m.Created loginTime,
       logoutTime = (select top 1 Created from bw_LoggingEvent n where n.OwnerId = m.OwnerId and n.ApplicationId = m.ApplicationId and n.Created > m.Created and n.typeid = 3 order by n.Created)
from bw_LoggingEvent m 
) t where logoutTime is not null
group by OwnerId , ApplicationId ,logoutTime
/*
OwnerId     ApplicationId loginTime                                              logoutTime                                             
----------- ------------- ------------------------------------------------------ ------------------------------------------------------ 
5           1             2011-05-06 12:48:47.000                                2011-05-06 12:51:54.000
5           1             2011-05-06 12:51:54.000                                2011-05-06 13:04:52.000
5           1             2011-05-06 13:04:52.000                                2011-05-06 13:10:28.000
5           1             2011-05-06 13:10:28.000                                2011-05-06 13:11:25.000
5           1             2011-05-06 13:11:25.000                                2011-05-06 13:13:38.000
47          1             2011-06-06 14:48:13.000                                2011-06-06 14:49:29.000
47          1             2011-06-06 14:49:29.000                                2011-06-06 14:50:29.000
87          1             2011-06-06 14:26:35.000                                2011-06-06 14:27:25.000
87          1             2011-06-06 14:27:25.000                                2011-06-06 14:32:45.000
87          1             2011-06-06 14:32:45.000                                2011-06-06 14:33:53.000
87          1             2011-06-06 14:33:53.000                                2011-06-06 14:43:26.000
87          1             2011-06-06 14:43:26.000                                2011-06-06 14:45:26.000
*/--这是按照秒进行的统计，如果不是按照秒进行统计，自己更换参数即可。datediff(mi,loginTime,logoutTime) -- > 分钟
select OwnerId , ApplicationId , sum(datediff(ss,loginTime,logoutTime)) 秒
from
(
select OwnerId , ApplicationId , min(loginTime) loginTime , logoutTime from
(
select m.OwnerId , 
       m.ApplicationId , 
       m.Created loginTime,
       logoutTime = (select top 1 Created from bw_LoggingEvent n where n.OwnerId = m.OwnerId and n.ApplicationId = m.ApplicationId and n.Created > m.Created and n.typeid = 3 order by n.Created)
from bw_LoggingEvent m 
) t where logoutTime is not null
group by OwnerId , ApplicationId ,logoutTime
) k
group by OwnerId , ApplicationId
/*
OwnerId     ApplicationId 秒           
----------- ------------- ----------- 
5           1             1491
47          1             136
87          1             1131（所影响的行数为 3 行）
*/
drop table bw_LoggingEvent

Answer 9

thanks,
根我的想法差不多,这是我的SQL
  select min(ownerid)as userid,min(applicationid) as applicationid,min(loginTime) as loginTime,logoutTime from(
  
  select m.OwnerId , 
       m.ApplicationId , 
       m.Created loginTime,
       logoutTime = (
       select top 1 Created from bw_LoggingEvent n where n.OwnerId = m.OwnerId and n.ApplicationId = m.ApplicationId 
       and n.Created > m.Created   and n.typeid = 3  order by n.Created )
from bw_LoggingEvent m where typeid = 2
) aa
group by logoutTime不过,我觉得这种方法也不算是最好的方法,如果数据量比较大的话,统计起来估计会比较慢.不过,非常感谢你的帮忙,你提供的select top 1 比我开始的方法效率高多了,
如果有更好的方法就告诉我,没有的话就只能这样了.分全给你

Answer 10

又看了一下执行效率:我的表中共有35205条记录,你这个查询group by OwnerId , ApplicationId ,logoutTime(有3个条件,)大约要花费3秒时间,
我下面的那个只有一个group by ,大给要花1秒时间.
thanks again.希望大家多多交流.

Answer 11

不知道如果以后有很多数据,上千万条时,会怎样,这个表是个杂表,里面有20个typeid,现在只能先这样了.以后再考虑效率的问题了.