Hokay, so I think I have an algorithm to do this with a quirky update. There may be some bugs in it because there are so many combinations of how the elements in the window are arranged, but I think more or less, this does the trick.
Early on I had the idea to store off the second highest value, and then if the first highest value rolled off, the second highest value would become the first. Originally I was storing the second highest value in the window whether it was before or after the first highest value. But after I jotted it down on paper, it became clear that you only need to store the second highest value if its IN FRONT of the first highest. No value smaller than the first highest value, behind it in the sequence, would ever supplant the first highest.
Consider the following sequence and assume a rolling window of 3. F = first highest, S = second highest
6,2,5,3,7,1,1,3
•[6],2,5,3,7,1,1,3
oF = 6
oS = null
•[6,2],5,3,7,1,1,3
oF = 6
oS = 2
•[6,2,5],3,7,1,1,3
oF = 6
oS = 5
•6,[2,5,3],7,1,1,3
o6 falls off
oF = 5
oS = 3
•6,2,[5,3,7],1,1,3
oF = 7
oS = null (again, we’re only interested in second highest value IN FRONT of the first highest)
•6,2,5,[3,7,1],1,3
oF = 7
oS = 1
•6,2,5,3,[7,1,1],3
oF = 7
oS = 1
•6,2,5,3,7,[1,1,3]
o7 falls off
oF = 3
oS = null
All these rules can be done on successive rows, making a quirky update possible. This works for the randomized data I originally asked about, but to show it in SQL, I’ve hard coded this exact sequence into this script.
Note the use of @temp<name> variables. The operations need to take place on the variables as they were when they entered the update. If you were to use the actual variables, as soon as the first clause modified @first, it would throw off the clauses for subsequent variable assignments.
The windowed function in 2012+ I think still works better than mine, but this is considerably better than any of the RBAR solutions in terms of performance.
/**********************
** BUILD SAMPLE DATA **
**********************/
if object_id('tempdb.dbo.#dat') is not null drop table #dat
create table #dat
(
RID int identity(1,1) primary key clustered,
Value float,
MaxRollingValue float,
Debug varchar(max),
FirstRID int
)
go
insert into #dat(Value)
values (6),(2),(5),(3),(7),(1),(1),(3)
--select top 10000 (abs(checksum(newid())) % 1000) * rand()
--from sys.objects a, sys.objects b
declare
@tempFirst float,
@first float = -1,
@tempFirstRID int,
@firstRID int = 1,
@tempSecond float,
@second float = -1,
@tempSecondRID int,
@secondRID int = null,
@interval int = 3,
@Anchor int
update #dat with (tablockx)
set @Anchor = RID,
@tempfirst = case when Value > @first then value
when (RID - @Interval) = @firstRID and Value < @second then @second
when (RID - @Interval) = @firstRID and Value > @second then Value
else @first
end,
@tempfirstRID = case when Value > @first then RID
when (RID - @Interval) = @firstRID and Value < @second then @secondRID
when (RID - @Interval) = @firstRID and Value > @second then RID
else @firstRID
end,
@tempSecond = case when Value > @first then -1
when Value < @first and value > @second then value
when (RID - @Interval) = @firstRID then -1
else @second
end,
@tempSecondRID = case when (RID - @Interval) = @firstRID then null
when Value > @first then null
when Value < @first and Value > @second then RID
else @SecondRID
end,
@first = @tempFirst,
@firstRID = @tempFirstRID,
@second = @tempSecond,
@secondRID = @tempSecondRID,
MaxRollingValue = @first,
FirstRID = @firstRID,
Debug = concat('@first:', @first, ' @firstRID:', @firstRID, ' @second:', @second, ' @secondRID:', @secondRID)
option (maxdop 1)
select *
from #dat
order by RID