Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Замена в запросе рекурсивного CTE на нерекурсивную конструкцию  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6201
Задача: имеется выборка вида (datetime, smallint), где поле типа smallint принимает только значения 0 и 1 (это данные, поступающие с датчиков). Выборка упорядочена по возрастанию поля, содержащего datetime, при этом порядок чередования 0 и 1 во втором поле произвольный. Требуется исключить из этой выборки все записи с идущими подряд значениями 0 или 1, оставив от таких повторений только самую первую запись (при упорядочивании по времени по возрастанию). Я сделал вот таким образом:
declare
  @t table (dt datetime,action smallint);
insert into @t
  select convert(datetime, dateadd(hour,number, getdate())), convert(smallint,rand(checksum(newid()))*2)
    from master..spt_values where type='P' and number between 0 and 50;

with cte0 as (
  select dt, action, row_number() over(order by dt) ord from @t
),
cte1 as (
  select dt,action, ord, 1 flag from cte0 where ord=1
  union all
  select t.dt, t.action, t.ord, abs(t.action-e.action) flag
  from cte0 t
  inner join cte1 e on (t.ord=e.ord+1)
)
select dt, action from cte1 where flag=1

Все бы ничего, но при количестве записей в выборке, большем 100, вылетает исключение из-за дефолтного значения maxrecursion=100. Разумеется, его можно задать в явном виде, и задать побольше (я пока задал 10 000 - вроде бы хватает). Но на данный момент трудно предсказать изменение размеров выборки в будущем - не исключено, что не хватит и выставления maxrecursion по максимуму. Может быть, у кого-то есть идеи, как вышеописанное можно сделать иначе?
19 янв 12, 18:07    [11934987]     Ответить | Цитировать Сообщить модератору
 Re: Замена в запросе рекурсивного CTE на нерекурсивную конструкцию  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
maxrecursion=0
19 янв 12, 18:08    [11935003]     Ответить | Цитировать Сообщить модератору
 Re: Замена в запросе рекурсивного CTE на нерекурсивную конструкцию  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Сон Веры Павловны
Все бы ничего, но при количестве записей в выборке, большем 100, вылетает исключение из-за дефолтного значения maxrecursion=100. Разумеется, его можно задать в явном виде, и задать побольше (я пока задал 10 000 - вроде бы хватает). Но на данный момент трудно предсказать изменение размеров выборки в будущем - не исключено, что не хватит и выставления maxrecursion по максимуму. Может быть, у кого-то есть идеи, как вышеописанное можно сделать иначе?


option (maxrecursion 0)

и никаких ограничений на вложенность
19 янв 12, 18:09    [11935005]     Ответить | Цитировать Сообщить модератору
 Re: Замена в запросе рекурсивного CTE на нерекурсивную конструкцию  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6201
Knyazev Alexey
option (maxrecursion 0)

и никаких ограничений на вложенность

Такой вариант мне приходил в голову, но не будет ли это чревато в плане производительности? Если, скажем, на вход поступит не 10 000, а два миллиона записей? Может быть, я изобрел велосипед, и есть что-то более оптимальное?
19 янв 12, 18:13    [11935047]     Ответить | Цитировать Сообщить модератору
 Re: Замена в запросе рекурсивного CTE на нерекурсивную конструкцию  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Сон Веры Павловны,

как-то так?

with cte as (
	select dt, action, ROW_NUMBER() OVER (ORDER BY dt) rn
	from @t
) 
select t1.dt, t1.action
from cte t1
left join cte t0 on t0.rn = t1.rn - 1
where t1.action <> t0.action or t0.action is null
19 янв 12, 18:14    [11935056]     Ответить | Цитировать Сообщить модератору
 Re: Замена в запросе рекурсивного CTE на нерекурсивную конструкцию  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6201
Shakill,

Да, видимо как-то так. И план выглядит лучше, чем в моем случае.
19 янв 12, 18:26    [11935155]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить