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

Откуда:
Сообщений: 930
Здравствуйте, всем доброго дня.
Я написал следующий запрос с использованием курсора и рекурсивного CTE:
if (OBJECT_ID('tempdb..#SampleData') is not null)
DROP TABLE tempdb..#SampleData

create table #SampleData
(
	[Date] date not null,
	Id nvarchar(10) not null,
	ParentId nvarchar(10) not null
)

if (OBJECT_ID('tempdb..#ResultData') is not null)
DROP TABLE tempdb..#ResultData

create table #ResultData
(
	Id nvarchar(10) not null,
	ParentId nvarchar(10) not null,
	[DS] date not null,
	[DE] date not null,
	rn int not null
)

;WITH
	SampleData AS
	(
		select cast('20140501' as Date) as Date, '1' as id, NULL as ParentId
		UNION ALL
		select cast('20140501' as Date) as Date, '2' as id, NULL as ParentId
		UNION ALL
		select cast('20140501' as Date) as Date, '1.1' as id, '1' as ParentId
		UNION ALL
		select cast('20140501' as Date) as Date, '1.2' as id, '1' as ParentId
		UNION ALL
		select cast('20140501' as Date) as Date, '1.2.1' as id, '1.2' as ParentId
		UNION ALL
		select cast('20140501' as Date) as Date, '1.2.2' as id, '1.2' as ParentId
		UNION ALL
		select cast('20140501' as Date) as Date, '2.1' as id, '2' as ParentId
		UNION ALL
		select cast('20140501' as Date) as Date, '2.2' as id, '2' as ParentId
		---------------------------------------------------------------------
		UNION ALL
		select cast('20140515' as Date) as Date, '1' as id, NULL as ParentId
		UNION ALL
		select cast('20140515' as Date) as Date, '2' as id, NULL as ParentId
		UNION ALL
		select cast('20140515' as Date) as Date, '1.1' as id, '1' as ParentId
		UNION ALL
		select cast('20140515' as Date) as Date, '1.3' as id, '1' as ParentId
		UNION ALL
		select cast('20140515' as Date) as Date, '2.2' as id, '1.1' as ParentId
		UNION ALL
		select cast('20140515' as Date) as Date, '2.1' as id, '1' as ParentId
		UNION ALL
		select cast('20140515' as Date) as Date, '1.2' as id, '2' as ParentId
		UNION ALL
		select cast('20140515' as Date) as Date, '1.2.1' as id, '1.2' as ParentId
		UNION ALL
		select cast('20140515' as Date) as Date, '1.2.2' as id, '1.2' as ParentId
		---------------------------------------------------------------------
		--'2.1' вернулся назад под '2'
		UNION ALL
		select cast('20140520' as Date) as Date, '1' as id, NULL as ParentId
		UNION ALL
		select cast('20140520' as Date) as Date, '2' as id, NULL as ParentId
		UNION ALL
		select cast('20140520' as Date) as Date, '1.1' as id, '1' as ParentId
		UNION ALL
		select cast('20140520' as Date) as Date, '1.3' as id, '1' as ParentId
		UNION ALL
		select cast('20140520' as Date) as Date, '2.2' as id, '1.1' as ParentId
		UNION ALL
		select cast('20140520' as Date) as Date, '2.1' as id, '2' as ParentId
		UNION ALL
		select cast('20140520' as Date) as Date, '1.2' as id, '2' as ParentId
		UNION ALL
		select cast('20140520' as Date) as Date, '1.2.1' as id, '1.2' as ParentId
		UNION ALL
		select cast('20140520' as Date) as Date, '1.2.2' as id, '1.2' as ParentId
	)
insert into #SampleData
select
	Date,
	id,
	ISNULL(ParentId, '0')
from SampleData

insert into #ResultData
select
	data.Id,
	data.ParentId,
	data.Date,
	'99991231',
	data.rn
from
	(
		select 
			*,
			DENSE_RANK() OVER (ORDER BY Date) as rn
		from #SampleData
	) as data
where
	data.rn = 1

declare 
	@CurDate date,
	@CurId nvarchar(10),
	@CurParentId nvarchar(10),
	@CurRn int

DECLARE SampleData CURSOR LOCAL FORWARD_ONLY STATIC FOR 
select
	data.Date,
	data.Id,
	data.ParentId,
	data.rn
from
	(
		select 
			*,
			DENSE_RANK() OVER (ORDER BY Date) as rn
		from #SampleData
	) as data
where
	data.rn > 1

OPEN SampleData
WHILE 1=1 BEGIN
  FETCH NEXT FROM SampleData INTO @CurDate, @CurId, @CurParentId, @CurRn;
  IF @@FETCH_STATUS <> 0 BREAK

	if not exists(
					select
						1
					from
					(
						select 
							*,
							DENSE_RANK() OVER (ORDER BY Date) as rn
						from #SampleData
					) as data 
					where
						rn = @CurRn - 1 and
						Id = @CurId and
					    ParentId = @CurParentId
					)
	begin
		insert into #ResultData
		select 
			@CurId, @CurParentId, @CurDate, '99991231', @CurRn
		
		update
			#ResultData
		set
			DE = dateadd(day, -1, @CurDate)
		where
			Id = @CurId and
			rn = @CurRn - 1

		;with 
		PrevPeriods as
		(
			select
				*
			from
			(
				select 
					*,
					DENSE_RANK() OVER (ORDER BY Date) as rn
				from #SampleData
			) as data 
			where
				rn = @CurRn - 1
		),
		cte as
		(
			select 
				@CurId as Id, @CurParentId as ParentId, @CurDate as DS, @CurRn as rn
			union all
			select 
				PrevPeriods.Id, PrevPeriods.ParentId, PrevPeriods.Date, cast(PrevPeriods.rn as int) 
			from
						   PrevPeriods
				inner join cte
						on PrevPeriods.ParentId = cte.Id
		)
		update
			ResultData
		set
			ResultData.DE = dateadd(day, -1, @CurDate)
		output
			tmp.Id, tmp.ParentId, @CurDate, '99991231', tmp.rn
		into #ResultData
		from 
					   cte as tmp
			inner join #ResultData as ResultData
					on tmp.Id = ResultData.Id and
					   tmp.DS = ResultData.DS
		where
			tmp.rn = @CurRn - 1
	end
END;
CLOSE SampleData;
DEALLOCATE SampleData;

select * from #ResultData order by Id, DS,  DE

/*
Key	Id		StartDate	EndDate
1	'1'		2014-05-01	9999-12-31
2	'1.1'	2014-05-01	9999-12-31
4	'1.2'	2014-05-01	2014-05-14
11	'1.2'	2014-05-15	9999-12-31
5	'1.2.1'	2014-05-01	2014-05-14
6	'1.2.2'	2014-05-01	2014-05-14
12	'1.2.1'	2014-05-15	9999-12-31
13	'1.2.2'	2014-05-15	9999-12-31
14	'1.3'	2014-05-15	9999-12-31
8	'2'		2014-05-01	9999-12-31
9	'2.1'	2014-05-01	2014-05-14
7	'2.1'	2014-05-15	2014-05-19
9	'2.1'	2014-05-20	9999-12-31
10	'2.2'	2014-05-01	2014-05-14
3	'2.2'	2014-05-15	9999-12-31
*/
Таким образом имеется одна выгрузка за три дня, в которых данные могут могут добавляться, изменяться или не изменяться.
эту выгрузку необходимо преобразовать к другому виду, так же в дерево с историей изменения каждой его ветки.
Необходимо фиксировать изменения, если появилась новая запись.
Если у записи сменился родитель, то необходимо фиксировать изменения у этой записи и всех ее потомков.
Помогите пожалуйста написать запрос без использования курсора.
6 июн 14, 16:56    [16134085]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос: рекурсивный CTE  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
test as (
	  select StartDate = Date, endDAte =CAST('99991231' as date),id, ParentID , n=1
	  from SampleData
	  where ParentID IS NULL
	  union all
	  select StartDAta = t.StartDate,EndDAte = d.Date,d.id, d.ParentID , n=n+1
	  from SampleData d
	    inner join test t on d.ParentID = t.id
	)
	select * from test order by id

ну и дальше слопіайте диапазон как хотите.. ето если я правилдьно понял вашу задачу
6 июн 14, 17:17    [16134230]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос: рекурсивный CTE  [new]
rsolanov
Member

Откуда:
Сообщений: 930
Maxx
test as (
	  select StartDate = Date, endDAte =CAST('99991231' as date),id, ParentID , n=1
	  from SampleData
	  where ParentID IS NULL
	  union all
	  select StartDAta = t.StartDate,EndDAte = d.Date,d.id, d.ParentID , n=n+1
	  from SampleData d
	    inner join test t on d.ParentID = t.id
	)
	select * from test order by id

ну и дальше слопіайте диапазон как хотите.. ето если я правилдьно понял вашу задачу
Да нет это не то, тогда было бы слишком просто )
6 июн 14, 17:29    [16134310]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос: рекурсивный CTE  [new]
rsolanov
Member

Откуда:
Сообщений: 930
Может быть тут пахнет одним рекурсивным CTE, который будет вызывать другой рекурсивный CTE.
6 июн 14, 17:31    [16134329]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос: рекурсивный CTE  [new]
Maxx
Member [скрыт]

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

вам виднее конечно но в вашем выводе вы потярели миграции 2.1 от 1 к 2 . Из ваших пояснений вообще ничего не понятно если честно:( Посему сочинял как понял.. какой и итоге то должен быть результат ?

	select 
	 ParentID,id  , MIN(StartDate), MAX(EndDAte)
    from test 
	group by ParentID, id 
	order by id
6 июн 14, 17:35    [16134353]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос: рекурсивный CTE  [new]
rsolanov
Member

Откуда:
Сообщений: 930
Maxx, результат этого запроса такой, какой и должен быть, он верный. Запустите запрос и увидите что должно получиться на выходе.
6 июн 14, 17:41    [16134392]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить