Как посчитать максимальное кол-во подряд идущих строк с определенным значением поля

Sillabub

Имеется табличкаWeek Rating1 02 13 14 05 06 17 18 19 0Нужно посчитать максимальное кол-во строк с рейтингом 1, идущих подряд (то есть в данном примере должно получиться 3)Уже всю голову сломала, не могу придумать как сделать(Сервер - 2000
5 ответов

Sillabub

Sillabub,
declare @t table (Week int, Rating int)
insert @t
select <b>1</b>, <b>0</b> union all
select <b>2</b>, <b>1</b> union all
select <b>3</b>, <b>1</b> union all
select <b>4</b>, <b>0</b> union all
select <b>5</b>, <b>0</b> union all
select <b>6</b>, <b>1</b> union all
select <b>7</b>, <b>1</b> union all
select <b>8</b>, <b>1</b> union all
select <b>9</b>, <b>1</b>

declare @MaxR int , @Cur int
select @MaxR = <b>0</b>, @Cur = <b>0</b>

select @Cur = case Rating when <b>1</b> then @Cur + <b>1</b> else <b>0</b> end,
	 @MaxR = case when @MaxR < @Cur then @Cur else @MaxR end
from @t
order by Week

select max(x) from (select @Cur union all select @MaxR) t(x)
но решение на недокументированных особенностях сервера построено, может работать не всегда.


Sillabub

Полностью документированное решение... хе-хе.
declare @t table([Week] int, Rating int)
insert @t
select <b>1</b>, <b>0</b> union all
select <b>2</b>, <b>1</b> union all
select <b>3</b>, <b>1</b> union all
select <b>4</b>, <b>0</b> union all
select <b>5</b>, <b>0</b> union all
select <b>6</b>, <b>1</b> union all
select <b>7</b>, <b>1</b> union all
select <b>8</b>, <b>1</b> union all
select <b>9</b>, <b>1</b>


declare @b table([Week] int, n int identity primary key clustered)
declare @e table([Week] int, n int identity primary key clustered)

insert @b
select T.[Week]
FROM (select * FROM @t where Rating=<b>1</b>) T left outer join @t P
ON T.[Week]-<b>1</b>=P.[Week] AND T.Rating=P.Rating
WHERE P.[Week] is null
ORDER BY T.[Week]

insert @e
select T.[Week]
FROM (select * FROM @t where Rating=<b>1</b>) T left outer join @t P
ON T.[Week]+<b>1</b>=P.[Week] AND T.Rating=P.Rating
WHERE P.[Week] is null
ORDER BY T.[Week]

select max(e.[Week]-b.[Week]) 
FROM @b b inner join @e e
on b.n=e.n


Sillabub

Свести задачу к "поиску диапазонов/дырок"-------------------------There’s no silver bullet!


Sillabub

iljy,Спасибо)Ваше недокументированное решение помогло)


Sillabub

Sillabub,можно еще так:
declare @t table (Week int primary key, Rating int)
insert @t
select <b>1</b>, <b>0</b> union all
select <b>2</b>, <b>1</b> union all
select <b>3</b>, <b>1</b> union all
select <b>4</b>, <b>0</b> union all
select <b>5</b>, <b>0</b> union all
select <b>6</b>, <b>1</b> union all
select <b>7</b>, <b>1</b> union all
select <b>8</b>, <b>1</b> union all
select <b>9</b>, <b>1</b>

select MAX(w1 - w2) from
(
	select t1.Week w1, t2.Week w2 from 
	@t t1 left join @t t2 
	on t2.Week = (select max(Week) from @t where Rating = <b>0</b> and Week < t1.Week)
	where t1.Rating = <b>1</b>
)t
Это полностью документированное. Но в производительности может проиграть. А может и нет