, :
with cte_ranked as (
select *, row_number() over (partition by UserId order by Time) as rn
from table)
select l.*, datediff(minute, r.Time, l.Time) as gap_length
from cte_ranked l join cte_ranked r on l.UserId = r.UserId and l.rn = r.rn-1
, ..
Mac . , , 1M. :
create table access (id int identity(1,1)
, UserId int not null
, Time datetime not null);
create clustered index cdx_access on access(UserID, Time);
go
, :
with cte_gap as (
select Id, UserId, a.Time, (a.Time - prev.Time) as gap
from access a
cross apply (
select top(1) Time
from access b
where a.UserId = b.UserId
and a.Time > b.Time
order by Time desc) as prev)
, cte_max_gap as (
select UserId, max(gap) as max_gap
from cte_gap
group by UserId)
select g.*
from cte_gap g
join cte_max_gap m on m.UserId = g.UserId and m.max_gap = g.gap
where g.UserId = 42;
1M , ~ 47 . , 1 ( ), 48 .
UserId = 42 , , ( ), 6379139 , 14 .
, UserId max ( , ):
select UserId, max(a.Time-prev.Time) as gap
from access a
cross apply (
select top(1) Time
from access b
where a.UserId = b.UserId
and a.Time > b.Time
order by Time desc
) as prev
group by UserId
3193448 , 6 1M-. - , , , , , max. , , on (UserId, Time), .
CTE "" ( ): ANSI SQL-99 . SQL Server datediff, . , "" " SQL, ". , cross apply . , . , CTE . , CTE:
select g.*
from (
select Id, UserId, a.Time, (a.Time - (
select top(1) Time
from access b
where a.UserId = b.UserId
and a.Time > b.Time
order by Time desc
)) as gap
from access a) as g
join (
select UserId, max(gap) as max_gap
from (
select Id, UserId, a.Time, (a.Time - (
select top(1) Time
from access b
where a.UserId = b.UserId
and a.Time > b.Time
order by Time desc
)) as gap
from access a) as cte_gap
group by UserId) as m on m.UserId = g.UserId and m.max_gap = g.gap
where g.UserId = 42
, , LOL. , CTE. , 5-6 CTE , .
, , ( , ):
select UserId, max(gap)
from (
select UserId, a.Time-(
select top(1) Time
from access b
where a.UserId = b.UserId
and a.Time > b.Time
order by Time desc) as gap
from access a) as gaps
group by UserId