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

Откуда: Moscow
Сообщений: 1369
господа что то пятничная мысль в голову не идет, есть идеи как реализовать без цикла/курсора?

act = 6 вход в состояние
act = 5 откат состояния

на state как таковой завязываться нельзя это текстовое поле с кучей информации, я привел сокращение данных в нем для ясности.

declare @t table ([id] int identity, act int, [date] datetime, [state] varchar(255))

insert into @t values 
(6, '20180110 08:00:00', 'введен'),
(6, '20180110 09:00:00', 'исполнен'),
(5, '20180110 10:00:00', 'откат исполнен'),
(6, '20180110 11:00:00', 'исполнен'),
(5, '20180110 12:00:00', 'откат исполнен'),
(5, '20180110 13:00:00', 'откат введен'),
(6, '20180110 14:00:00', 'введен'),
(6, '20180110 15:00:00', 'исполнен'),
(5, '20180110 16:00:00', 'откат исполнен');


необходимо сопоставить каждому откату состояния запись вхождения в состояние:
то есть в итоге должна получится таблица вида

idactdatastateidactdatestate
16'20180110 08:00:00''введен'65'20180110 13:00:00''откат введен'
26'20180110 09:00:00''исполнен'35'20180110 10:00:00''откат исполнен'
46'20180110 11:00:00''исполнен'55'20180110 12:00:00''откат исполнен'
76'20180110 14:00:00''введен'nullnullnullnull
86'20180110 15:00:00''исполнен'95'20180110 16:00:00''откат исполнен'


думаю смысл понятен.

Как вариант я накатал scalar-clr которая возвращает нумерацию для групп с учетом изменения значения входного параметра, но он а довольно убого выглядит, особенно в связи с предположением что если ее использовать в нескольких запросах результаты будут корявыми.

а вот стандартные окошки что то у меня в голове не двигаются с учетом вариативности изменения значения.
11 май 18, 12:35    [21402737]     Ответить | Цитировать Сообщить модератору
 Re: плавающее окно  [new]
Владислав Колосов
Member

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

гемор какой-то, у вас нет пар идентификаторов введен - откат. Либо заводите справочник пар по колонке state.
11 май 18, 13:02    [21402861]     Ответить | Цитировать Сообщить модератору
 Re: плавающее окно  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Владислав Колосов
felix_ff,

гемор какой-то, у вас нет пар идентификаторов введен - откат. Либо заводите справочник пар по колонке state.

Таки да, более того, ему необходимо идентифицировать номера строк и номера тех строк, где лежит логичная смена значения, а не кривая.
А то будет "13:00" - "введен", "14:00" - "откат исполнен", "15:00" - откат исполнен, "16:00" - откат исполнен, "17:00" - введен, как начнется резание на диапазоны 13-16 вместо 13-14 как корректный и 14-17 как мусор, который далее не анализируется.

Или наоборот - диапазон 13-16 корректный, а запись 14:00 и 15:00 это мусор, который нужно выкинуть.

Сначала нужно определиться с вопросом, как быть с явно мусорными записями, которые следуют рядом друг за другом в совершенно нелогичном порядке, типа 2-3 отката один за другим, что из этого правда, что ложь.
11 май 18, 13:07    [21402884]     Ответить | Цитировать Сообщить модератору
 Re: плавающее окно  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1369
Владислав Колосов,

я согласен что геммор.

Andy_OLAP,
Сначала нужно определиться с вопросом, как быть с явно мусорными записями, которые следуют рядом друг за другом в совершенно нелогичном порядке, типа 2-3 отката один за другим, что из этого правда, что ложь.


Всмысле в нелогичном порядке?

Грубо говоря это протокол прохождения документа по состояниям.

состояния документа всегда линейны. к примеру "введен" => "контроль" => "исполнен"
откат может быть в обратную сторону: "исполнен" => "контроль" = > "введен"
но никто не мешает пользователям гонять документ туда-сюда

введен => контроль => исполнен => откатили исполнен => исполнен => откатили исполнен => откатили контроль => откатили до введен => опять прокатили на контроль => снова исполнили.

в таблице в таком случае записи будут чередоваться 6=>6=>6=>5=>6=>5=>5=>5=>6=>6

зависимости id как таковых нет.
принцип который необходимо реализовать это рекурсивно для каждого отката сопоставить запись наката протокола

в курсорной обработки это выглядело бы вот так убого:
declare @t table ([id] int identity, act int, [date] datetime, [state] varchar(255))
declare @t2 table ([id] int, act int, [date] datetime, [state] varchar(255))

insert into @t values 
(6, '20180110 08:00:00', 'введен'),
(6, '20180110 09:00:00', 'исполнен'),
(5, '20180110 10:00:00', 'откат исполнен'),
(6, '20180110 11:00:00', 'исполнен'),
(5, '20180110 12:00:00', 'откат исполнен'),
(5, '20180110 13:00:00', 'откат введен'),
(6, '20180110 14:00:00', 'введен'),
(6, '20180110 15:00:00', 'исполнен'),
(5, '20180110 16:00:00', 'откат исполнен');

insert into @t2 select * from @t;
declare @result table (id int, id2 int);

declare @cur cursor, @i int, @id int, @act int, @date datetime, @state varchar(255);
 
select * from @t
set @cur = cursor local dynamic for select * from @t;
open @cur;
set @i = 0;
while 1 = 1 begin
    fetch next from @cur into @id, @act, @date, @state;

	if @@fetch_status <> 0 break;
    if @act = 5 begin
	   delete from t
	     output deleted.id, @id into @result
	     from @t t
		 where t.id = (select top (1) id from @t where id < @id order by [date] desc);
	   delete from @t where id = @id
	end
	else set @i += 1;
end;
close @cur;
deallocate @cur;

select * from @t2 t
 left join @result r on r.id = t.id
     left join @t2 t2 on t2.id = r.id2
where t.act = 6
11 май 18, 14:03    [21403063]     Ответить | Цитировать Сообщить модератору
 Re: плавающее окно  [new]
Посетитель
Member

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

я бы посмотрел в сторону явного указания при откате, что же все-таки откатывается.

ну а извратиться при желании можно, например, так
with a as
(
select *,sum(case act when 6 then 1 else -1 end)over(order by date,id) s from @t
)

select a.id,a.act,a.date,a.state,a2.id,a2.act,a2.date,a2.state
  from a
  outer apply (select top 1 a2.id,a2.act,a2.date,a2.state
                  from a a2
                 where a2.act = 5
                   and a2.s = a.s-1
                   and a2.date>a.date
                 order by a2.date) a2
 where a.Act = 6
11 май 18, 15:27    [21403393]     Ответить | Цитировать Сообщить модератору
 Re: плавающее окно  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1369
Посетитель,

благодарю, этого результата вроде и хотел добиться.
не догадался суммировать по +-
12 май 18, 02:08    [21404493]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить