tsql - Finding groups of clustered data points -

i have table records events:

create table #events (  introwid int identity(1,1),    intitemid int,    intuserid int,    datevent datetime) 

it's big table many millions of rows, recording events against several thousand items , tens of thousands of users.

there's select group of ten itemids want for, when occur in pattern: i'm trying find rows ten of these items have events registered against them same userid , close in time, 5 minutes.

i have absolutely no idea how go this. 1 assume partitioning involved somewhere, help, somewhere started, appreciated.

cheers, matt

lets want statistics items id: 1, 2, ... , 10. first create table eventbyitems:

create table eventbyitems (      introwid int identity(1,1),       intuserid int,    datevent datetime,    intitem1 int,    intitem2 int,    intitem3 int,    ...    intitem10 int ) 

then use query populate table:

select intuserid, datevent,     sum(pvt.[1]), sum(pvt.[2]), sum(pvt.[3]), ... , sum(pvt.[10]) #events pivot (     count(intitemid)     intitemid in ([1], [2], [3], ... , [10]) ) pvt group intuserid, datevent 

now can work table. example can update fill gaps according logic. or can queries that:

select   introwid,   intuserid,   datevent   eventbyitems e   ((intitem1 > 0) or exists(select *                              eventbyitems                              intuserid = e.intuserid                              , intitem1 > 0                              , datediff(minute, datevent, e.datevent) <= 5                              , introwid != e.introwid ))   ,    ...    ,   ((intitem10 > 0) or exists(select *                              eventbyitems                              intuserid = e.intuserid                              , intitem10 > 0                              , datediff(minute, datevent, e.datevent) <= 5                              , introwid != e.introwid ))