The output of this algorithm performed on the above data set is:

ObjectId   EventId   EventDate    EventsComplete
1          342       2017-10-27   0
1          342       2018-01-06   0
1          343       2018-04-18   0
1          401       2018-10-15   1
1          342       2018-11-12   0
1          342       2018-11-29   0
1          401       2018-12-10   0
1          342       2019-02-21   0
1          343       2019-04-23   1
1          401       2019-11-04   0
1          343       2020-02-15   0
2          342       2018-06-08   0
2          343       2018-09-18   0
2          342       2018-10-02   0

Here's a query that will create the data set in the example.

select 1 as ObjectId, 342 as EventId, cast('2017-10-27' as date) as EventDate
union select 1 as ObjectId, 342 as EventId, cast('2018-01-06' as date) as EventDate
union select 1 as ObjectId, 343 as EventId, cast('2018-04-18' as date) as EventDate
union select 1 as ObjectId, 401 as EventId, cast('2018-10-15' as date) as EventDate
union select 1 as ObjectId, 342 as EventId, cast('2018-11-12' as date) as EventDate
union select 1 as ObjectId, 342 as EventId, cast('2018-11-29' as date) as EventDate
union select 1 as ObjectId, 401 as EventId, cast('2018-12-10' as date) as EventDate
union select 1 as ObjectId, 342 as EventId, cast('2019-02-21' as date) as EventDate
union select 1 as ObjectId, 343 as EventId, cast('2019-04-23' as date) as EventDate
union select 1 as ObjectId, 401 as EventId, cast('2019-11-04' as date) as EventDate
union select 1 as ObjectId, 343 as EventId, cast('2020-02-15' as date) as EventDate
union select 2 as ObjectId, 342 as EventId, cast('2018-06-08' as date) as EventDate
union select 2 as ObjectId, 343 as EventId, cast('2018-09-18' as date) as EventDate
union select 2 as ObjectId, 342 as EventId, cast('2018-10-02' as date) as EventDate

Set based solution below.

No optimisation passes have been attempted other than using a bitfield. It works, that's enough for me. I can see a few points of possible simplification

I should add that, really, this problem is currently undefined, because if two different events can occur on the same date, there is no definition for the order in which we should treat them to have occured. So the row number allocated in the first CTE is arbitrary in those cases. No such cases occur in the sample data.

Using string concatenated paths - 150 ms.

Switching to bits instead of strings, still slower (~30 ms) than the cursor (~15 ms)

select 1 as ObjectId, 342 as EventId, cast('2017-10-27' as date) as EventDate
into t
union all select 1, 342, cast('2018-01-06' as date)
union all select 1, 343, cast('2018-04-18' as date)
union all select 1, 401, cast('2018-10-15' as date)
union all select 1, 342, cast('2018-11-12' as date)
union all select 1, 342, cast('2018-11-29' as date)
union all select 1, 401, cast('2018-12-10' as date)
union all select 1, 342, cast('2019-02-21' as date)
union all select 1, 343, cast('2019-04-23' as date)
union all select 1, 401, cast('2019-11-04' as date)
union all select 1, 343, cast('2020-02-15' as date)
union all select 2, 342, cast('2018-06-08' as date)
union all select 2, 343, cast('2018-09-18' as date)
union all select 2, 342, cast('2018-10-02' as date);
go

with numbered as  
-- just adding a row number to make it easier to follow
(
   select   objectid, 
            eventid, 
            eventdate, 
            rn = row_number() over (partition by objectid order by eventdate asc),
            bits = cast(power(2, case eventid when 342 then 0 when 343 then 1 else 2 end) as tinyint)
   from     t
),
paths as  
-- the concatenated paths of distinct eventid for each row, as a bitfield
(
   select      n.objectid, 
               n.eventid, 
               n.eventdate, 
               root = n.rn, 
               n.rn, 
               bits
   from        numbered n
   union all   
   select      n.objectid, 
               n.eventid, 
               n.eventdate, 
               p.root, 
               n.rn, 
               p.bits | n.bits
   from        paths       p
   join        numbered    n  on n.objectid = p.objectid
                                 and n.rn > p.rn
                                 and p.bits & n.bits = 0 
),
candidates as 
-- a row that has a path containing all 3 values (bits = 7)
(
   select   *
   from     (
               select   root, 
                        rn,
                        candidate = iif
                        (
                           rn = min(rn) over (partition by root), 
                           1, 0
                        )
               from     paths
               where    bits = 7
            ) c            
   where    c.candidate = 1
)
-- get the candidate rows where no earlier candidiate in row number order
-- has a root-to-end path which overlaps the path for this candidate
select      distinct 
            n.objectid,
            n.eventid,
            n.eventdate,
            isnull(c.candidate, 0)
from        numbered   n
left join   candidates c on c.rn = n.rn
                            and not exists 
                            (
                               select * 
                               from candidates prev
                               where prev.rn < c.rn
                                     and prev.rn > c.root
                                     and prev.root < c.rn
                            )
order by    n.objectid, 
            n.eventdate, 
            n.eventid

Pure cursor for the lulz.

declare @triplets table(objectid int, eventid int, eventdate date);
declare c cursor fast_forward for 
select objectid, eventid, eventdate from t order by objectid, eventdate asc;
declare 
   @ob int, @prevob int, @event int, @dt date, 
   @bits tinyint = 0;
open c;
fetch next from c into @ob, @event, @dt;
while @@fetch_status = 0
begin
    if (@ob = @prevob)
    begin           
        if @event = 342 set @bits |= 1;
        else if @event = 343 set @bits |= 2;
        else if @event = 401 set @bits |= 4;

        if (@bits = 7) 
        begin
            insert @triplets values (@ob, @event, @dt);
            set @bits = 0
        end
    end
    else select @bits = 0, @prevob = @ob;
    fetch next from c into @ob, @event, @dt;
end
close c;
deallocate c;
select      t.*, iif(tt.objectid is null, 0, 1)
from        t
left join   @triplets tt    on  t.objectid = tt.objectid 
                                and t.eventid = tt.eventid
                                and t.eventdate = tt.eventdate;

这篇关于如何识别表中哪些行满足特定条件，但条件是基于前一行的数据?提供的示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！