Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

Откуда:
Сообщений: 89
Извините -_-

create table table1 (AlertKey nvarchar(50), ServerSerial int)
insert into table1 
		values ('AGG', 1),('AGG', 2),('AGG', 3),('AGG_2', 4)


create table table2 (StartDate datetime, EndDate datetime, ServerSerial int)
insert into table2 
		values ('2014-01-01 08:00:00.000', '2014-01-01 12:00:00.000', 1),('2014-01-01 09:00:00.000', '2014-01-01 13:00:00.000', 1),
		('2014-01-01 07:00:00.000', '2014-01-01 09:00:00.000', 2),('2014-01-01 06:00:00.000', '2014-01-01 09:00:00.000', 2),
		('2014-01-01 10:00:00.000', '2014-01-01 15:00:00.000', 3),('2014-01-01 03:00:00.000', '2014-01-01 04:00:00.000', 3),
		('2014-01-01 16:00:00.000', '2014-01-01 17:00:00.000', 3)
14 май 14, 11:19    [16014181]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

Откуда:
Сообщений: 89
У меня вопрос:
;with tbl1 as
(
select #t1.f1, #t1.f2, case when #t1.f1 < t2.f2 and t2.f1 < #t1.f2 then 1 else 0 end [intersect]
from #t1, #t1 t2
where #t1.f1<>t2.f1 and #t1.f2<>t2.f2
)


Это джойн таблицы t1 и t1 (только под псевдонимом t2) ?

Т.е. t1 идентична t2?
14 май 14, 12:01    [16014490]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

Откуда:
Сообщений: 89
Ну т.е. не джойн, а таблица t1 это тоже самое что таблица t2?
14 май 14, 12:32    [16014748]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8821
full join сама с собой.
14 май 14, 12:50    [16014848]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8821
Надо второй задачей уже лень думать
14 май 14, 12:52    [16014859]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

Откуда:
Сообщений: 89
where #t1.f1<>t2.f1 and #t1.f2<>t2.f2


Вот по этому условию берутся: Строка из t1 и следующая за ней строка из t2? Или...?
14 май 14, 12:59    [16014930]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8821
Здесь просто убираются идентичные строки в обеих половинах, т.к. строка сама с собой всегда пересекается и это сравнение не нужно учитывать.
14 май 14, 13:04    [16014986]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

Откуда:
Сообщений: 89
Спасибо!
Фуухх, в целом вроде разобрался.

Не могли бы Вы прокомментировать вот эту часть:
	select max(f2) - min(f1) diff
	from #t2
	cross apply (select sum(iif(f3=0,1,0))+#t2.f3 from #t2 t3 where t3.rn<=#t2.rn) fn(x)
	group by x


Выводится разница...не совсем понимаю как работает cross apply...
Что-то типа where? Или мы делаем max(f2) - min(f1) из того что вернет cross apply?
По примеру он возвращает две строки

f1 f2
6 17
3 4

да?
14 май 14, 14:56    [16015876]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
chiz,

CROSS APPLY - это что-то вроде INNER JOIN,
но внутри подзапроса доступны все поля "соседних" таблиц из FROMа
(в INNER JOINе пришлось бы связывать снаружи после всей выборки. Логически, по крайней мере).
14 май 14, 15:17    [16016082]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

Откуда:
Сообщений: 89
Вот t2.


f1 f2 f3 rn
3 4 0 1
6 9 1 2
7 9 1 3
8 12 1 4
9 13 1 5
10 15 1 6
16 17 0 7

Объясните пожалуйста по действиям как ведет себя запрос:

	select max(f2) - min(f1) diff
	from #t2
	cross apply (select sum(iif(f3=0,1,0))+#t2.f3 from #t2 t3 where t3.rn<=#t2.rn) fn(x)
	group by x
14 май 14, 15:40    [16016298]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

Откуда:
Сообщений: 89
а можно ли как-нибудь вот это дело:

select F1, f2, max([intersect]) f3, row_number() over (order by f1, f2) rn
into #t2
from tbl1 
group by f1, f2


Заинсертить в обычную таблицу? Т.е. не использовать into? Или использовать какой-то аналог, только без создания таблицы, а записи в существующую?
15 май 14, 10:11    [16019368]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

Откуда:
Сообщений: 89
простите, глупая привычка, сначала спрашиваю - потом думаю.

и все-таки, если кто-то может ответить, то ответьте пожалуйста:

Вот t2.


f1     f2    f3   rn
3 4 0 1
6 9 1 2
7 9 1 3
8 12 1 4
9 13 1 5
10 15 1 6
16 17 0 7

Объясните пожалуйста по действиям как ведет себя запрос:
	select max(f2) - min(f1) diff
	from #t2
	cross apply (select sum(iif(f3=0,1,0))+#t2.f3 from #t2 t3 where t3.rn<=#t2.rn) fn(x)
	group by x
15 май 14, 10:24    [16019440]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8821
cross apply вызывает аналог функции, которая находится в скобках, для каждой записи таблицы #t2 и возвращает результат X.
Т.к. при выборке следующей строки функция выполняет запрос по предыдущим строкам таблицы #t2, подсчитывает количество непересекающихся и прибавляет единицу, если текущая строка относится к группе пересекающихся.
Суть этого запроса в монотонной нумерации групп.

Например:
1 0 группа1 1
2 0 группа2 2
3 1 группа3 3
4 1 группа3 3
5 1 группа3 3
6 1 группа3 3
7 0 группа4 4
15 май 14, 12:01    [16020134]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8821
Есть пара ошибок, одну легко исправить, надо
select #t1.f1, #t1.f2, case when #t1.f1 <= t2.f2 and t2.f1 <= #t1.f2 then 1 else 0 end [intersect]
from #t1, #t1 t2
where not (#t1.f1=t2.f1 and #t1.f2=t2.f2)

в первом запросе.

Вторая же алгоритмическая, т.к. схема была составлено исходя их того, что пересечения чередуются с непересекающимися. :(
15 май 14, 12:20    [16020276]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

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

ага, первую я заметил. Насчет второй, пока не понял о чем Вы говорите.

я тут написал вот такую хрень, вы в нее не вчитывайтесь) там все работает , все гуд.
delete from tmp_self
;with tbl1 as
(
select 
		REP_AUDIT_SEVERITY.StartDate, 
		REP_AUDIT_SEVERITY.EndDate, 
		case when REP_AUDIT_SEVERITY.StartDate < temp.EndDate and temp.StartDate < REP_AUDIT_SEVERITY.EndDate then 1 else 0 end [intersect]
from 
		REP_AUDIT_SEVERITY, 
		REP_AUDIT_SEVERITY temp, 
		REPORTER_STATUS a
where 
		not (REP_AUDIT_SEVERITY.StartDate=temp.StartDate and REP_AUDIT_SEVERITY.EndDate=temp.EndDate) 
		and REP_AUDIT_SEVERITY.Severity='5' 
		and a.Agent='SelfMonitoring' 
		and a.ServerSerial=REP_AUDIT_SEVERITY.ServerSerial 
		and a.ServerSerial=temp.ServerSerial 
		and a.FirstOccurrence between '2014-05-01 00:00:00.000' and '2014-05-15 00:00:00.000'
)
insert into tmp_self (StartDate, EndDate, f3, rn)
select StartDate, EndDate, max([intersect]) f3, row_number() over (order by StartDate, EndDate) rn
from tbl1 
group by StartDate, EndDate

;with tbl1 as 
(
	select datediff(second,min(StartDate),max(EndDate)) diff
	from tmp_self
	cross apply (select sum(iif(f3=0,1,0))+tmp_self.f3 from tmp_self t3 where t3.rn<=tmp_self.rn) fn(x)
	group by x
)
select (100-sum(diff)*100/datediff(second,'2014-05-01 00:00:00.000','2014-05-15 00:00:00.000')) as "sum" from tbl1


У меня возникла потребность запихать все это в цикл пока в запросе:
SELECT distinct AlertGroup from REPORTER_STATUS where Agent='SelfMonitoring'


Можно сделать через WHILE или курсор? Пока пробую , но все никак...

Т.е.

ПОКА AlertGroup из запроса есть, для каждого AlertGroup делать тот огромный запрос.

+ Вывести:


AlertGroup1 sum1
AlertGroup2 sum2
....
AlertGroup(n) sum(n)

Это реально?)

Спасибо)
15 май 14, 12:39    [16020438]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8821
Хм, в общем случае вторую часть запросом у меня не получилось сделать, только курсором.
15 май 14, 14:37    [16021397]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

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

Как то так получилось...

DECLARE @AlertGroup varchar(512)
DECLARE SELF_C CURSOR
FOR SELECT distinct AlertGroup from REPORTER_STATUS where Agent='SelfMonitoring' ;
OPEN SELF_C
FETCH NEXT FROM SELF_C
INTO @AlertGroup
WHILE @@FETCH_STATUS=0
BEGIN

delete from tmp_self
;with tbl1 as
(
select 
		REP_AUDIT_SEVERITY.StartDate, 
		REP_AUDIT_SEVERITY.EndDate , 
		case when REP_AUDIT_SEVERITY.StartDate < temp.EndDate and temp.StartDate < REP_AUDIT_SEVERITY.EndDate then 1 else 0 end [intersect]
from 
		REP_AUDIT_SEVERITY
FULL OUTER JOIN 
		REP_AUDIT_SEVERITY temp
ON
		REP_AUDIT_SEVERITY.ServerSerial=temp.ServerSerial
INNER JOIN
		REPORTER_STATUS a
ON
		REP_AUDIT_SEVERITY.ServerSerial=a.ServerSerial
where 
		not (REP_AUDIT_SEVERITY.StartDate=temp.StartDate and REP_AUDIT_SEVERITY.EndDate=temp.EndDate) 
		and REP_AUDIT_SEVERITY.Severity='5' 
		and a.Agent='SelfMonitoring' 
		and a.FirstOccurrence between '2014-04-01 00:00:00.000' and '2014-05-15 00:00:00.000'
		and a.AlertGroup = @AlertGroup
)
insert into tmp_self (StartDate, EndDate, f3, rn)
select StartDate, EndDate, max([intersect]) f3, row_number() over (order by StartDate, EndDate) rn
from tbl1 
group by StartDate, EndDate

;with tbl1 as 
(
	select datediff(second,min(StartDate),max(EndDate)) diff
	from tmp_self
	cross apply (select sum(iif(f3=0,1,0))+tmp_self.f3 from tmp_self t3 where t3.rn<=tmp_self.rn) fn(x)
	group by x
)
select @AlertGroup as "Модуль",(100-sum(diff)*100/datediff(second,'2014-04-01 00:00:00.000','2014-05-15 00:00:00.000')) as "sum" from tbl1

FETCH NEXT FROM SELF_C
INTO @AlertGroup
END
CLOSE SELF_C;
DEALLOCATE SELF_C;
15 май 14, 15:08    [16021637]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8821
Запрос
	select max(f2) - min(f1) diff
	from #t2
	cross apply (select sum(iif(f3=0,1,0))+#t2.f3 from #t2 t3 where t3.rn<=#t2.rn) fn(x)
	group by x


Все же некорректно работает, поэтому я переписал для курсора так:

create table #t1 (F1 int, f2 int)
insert into #t1 
	values (9,12),(9,13),(6,7),(6,8),(10,14),(3,4),(17,18)

;with tbl1 as
(
select #t1.f1, #t1.f2, case when #t1.f1 <= t2.f2 and t2.f1 <= #t1.f2 then 1 else 0 end [intersect]
from #t1, #t1 t2
where not (#t1.f1=t2.f1 and #t1.f2=t2.f2)
)
select F1, f2, max([intersect]) f3, row_number() over (order by f1, f2) rn
into #t2
from tbl1 
group by f1, f2


declare @f1 int, @f2 int, @f3 int, @itog int = 0, @low int = 0, @high int = 0
declare cur1 cursor fast_forward local for select f1, f2, f3 from #t2 order by rn
open cur1
fetch next from cur1 into @f1, @f2, @f3
while @@fetch_status = 0
begin
	if @f3 = 0 
	begin
		if @low <> 0 
			set @itog = @itog + (@high - @low)
		select @itog = @itog + (@f2-@f1), @low = 0
	end
	else
	begin
		if @f1 > @high
			select @itog = @itog + (@high - @low), @low = 0
		if @low = 0 
			set @low = @f1
		set @high = @f2
	end
fetch next from cur1 into @f1, @f2, @f3
end 
select @itog
15 май 14, 15:36    [16021819]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

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

Не корректно работает для дат...

Если есть два промежутка, один начался 2014-04-25 04:45:47.000, а второй 2014-05-08 10:30:34.000 и заканчиваются в одно время 2014-05-15 00:00:00.000
То алгоритм считает что они не пересекаются
Подскажите как быть с датами?
16 май 14, 08:36    [16024917]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

Откуда:
Сообщений: 89
Вот..

StartDate	EndDate	f3	rn
2014-04-25 04:45:47.000 2014-05-15 00:00:00.000 0 1
2014-05-08 10:30:34.000 2014-05-15 00:00:00.000 0 2
16 май 14, 08:58    [16025011]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

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

У Вас ест ьмысли по этому поводу?
19 май 14, 11:53    [16035550]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8821
Легко убедиться, что алгоритм определения пересечений считает эти даты пересекающимися:
declare @t1 table (f1 datetime, f2 datetime)

insert into @t1 values ('20140425 04:45:47',	'20140515 00:00:00'),
						('20140508 10:30:34',	'20140515 00:00:00')

select distinct t1.*, case when t1.f1 <= t2.f2 and t2.f1 <= t1.f2 then 1 else 0 end [intersect]
from @t1 t1, @t1 t2
19 май 14, 13:19    [16036179]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

Откуда:
Сообщений: 89
Подскажите пожалуйста, не могу понять почему запрос отваливается с ошибкой если добавляю в него условие выделенное большим шрифтом. Без этого условия выполняется за 30 сек. Вместе с условием 8 минут и ошибка...

delete from tmp_self
;with tbl1 as
(
select
REP_AUDIT_SEVERITY.StartDate,
REP_AUDIT_SEVERITY.EndDate,
case when REP_AUDIT_SEVERITY.StartDate <= temp.EndDate and temp.StartDate <= REP_AUDIT_SEVERITY.EndDate then 1 else 0 end [intersect]

from
REP_AUDIT_SEVERITY
FULL OUTER JOIN
REP_AUDIT_SEVERITY temp
ON
REP_AUDIT_SEVERITY.ServerSerial=temp.ServerSerial
INNER JOIN
REPORTER_STATUS a
ON
REP_AUDIT_SEVERITY.ServerSerial=a.ServerSerial
where
not (REP_AUDIT_SEVERITY.StartDate=temp.StartDate and REP_AUDIT_SEVERITY.EndDate=temp.EndDate)
and REP_AUDIT_SEVERITY.Severity='5'
and a.Agent='SelfMonitoring'
and a.AlertGroup='gateway status'
and REP_AUDIT_SEVERITY.STARTDATE < '2014-05-20 00:00:00.000'
and REP_AUDIT_SEVERITY.ENDDATE > '2014-05-01 00:00:00.000'
and (datediff(minute, REP_AUDIT_SEVERITY.STARTDATE, REP_AUDIT_SEVERITY.ENDDATE)>=0 or datediff(minute, REP_AUDIT_SEVERITY.STARTDATE, REP_AUDIT_SEVERITY.ENDDATE) is null)
)
insert into tmp_self (StartDate, EndDate, f3, rn)
select
CASE
WHEN (Startdate<'2014-05-20 00:00:00.000' and StartDate>'2014-05-01 00:00:00.000') THEN Startdate
WHEN (StartDate<'2014-05-01 00:00:00.000') THEN '2014-05-01 00:00:00.000'
END,

iif(EndDate is null or ('2014-05-20 00:00:00.000'>StartDate and '2014-05-20 00:00:00.000'<EndDate),'2014-05-20 00:00:00.000',EndDate),
max([intersect]) f3,
row_number() over (order by StartDate, EndDate) rn
from tbl1
group by StartDate, EndDate

;with tbl1 as
(
select datediff(second,min(StartDate),max(EndDate)) diff
from tmp_self
cross apply (select sum(iif(f3=0,1,0))+tmp_self.f3 from tmp_self t3 where t3.rn<=tmp_self.rn) fn(x)
group by x
)
select (100-sum(diff)*100/datediff(second,'2014-05-01 00:00:00.000','2014-05-20 00:00:00.000')) as "sum" from tbl1

Transaction (Process ID 162) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
20 май 14, 13:11    [16042200]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
chiz
Member

Откуда:
Сообщений: 89
Подскажите пожалуйста, не могу понять почему запрос отваливается с ошибкой если добавляю в него условие выделенное большим шрифтом. Без этого условия выполняется за 30 сек. Вместе с условием 8 минут и ошибка...

delete from tmp_self
;with tbl1 as
(
select 
REP_AUDIT_SEVERITY.StartDate, 
REP_AUDIT_SEVERITY.EndDate, 
case when REP_AUDIT_SEVERITY.StartDate <= temp.EndDate and temp.StartDate <= REP_AUDIT_SEVERITY.EndDate then 1 else 0 end [intersect]

from 
REP_AUDIT_SEVERITY
FULL OUTER JOIN 
REP_AUDIT_SEVERITY temp
ON
REP_AUDIT_SEVERITY.ServerSerial=temp.ServerSerial
INNER JOIN
REPORTER_STATUS a
ON
REP_AUDIT_SEVERITY.ServerSerial=a.ServerSerial
where 
not (REP_AUDIT_SEVERITY.StartDate=temp.StartDate and REP_AUDIT_SEVERITY.EndDate=temp.EndDate) 
and REP_AUDIT_SEVERITY.Severity='5' 
and a.Agent='SelfMonitoring' 
and a.AlertGroup='gateway status'
and REP_AUDIT_SEVERITY.STARTDATE < '2014-05-20 00:00:00.000'
[SIZE=4]and REP_AUDIT_SEVERITY.ENDDATE > '2014-05-01 00:00:00.000'[/SIZE]
and (datediff(minute, REP_AUDIT_SEVERITY.STARTDATE, REP_AUDIT_SEVERITY.ENDDATE)>=0 or datediff(minute, REP_AUDIT_SEVERITY.STARTDATE, REP_AUDIT_SEVERITY.ENDDATE) is null)
)
insert into tmp_self (StartDate, EndDate, f3, rn)
select 
CASE 
WHEN (Startdate<'2014-05-20 00:00:00.000' and StartDate>'2014-05-01 00:00:00.000') THEN Startdate 
WHEN (StartDate<'2014-05-01 00:00:00.000') THEN '2014-05-01 00:00:00.000' 
END, 

iif(EndDate is null or ('2014-05-20 00:00:00.000'>StartDate and '2014-05-20 00:00:00.000'<EndDate),'2014-05-20 00:00:00.000',EndDate), 
max([intersect]) f3, 
row_number() over (order by StartDate, EndDate) rn
from tbl1 
group by StartDate, EndDate

;with tbl1 as 
(
select datediff(second,min(StartDate),max(EndDate)) diff
from tmp_self
cross apply (select sum(iif(f3=0,1,0))+tmp_self.f3 from tmp_self t3 where t3.rn<=tmp_self.rn) fn(x)
group by x
)
select (100-sum(diff)*100/datediff(second,'2014-05-01 00:00:00.000','2014-05-20 00:00:00.000')) as "sum" from tbl1



Ошибка:
Transaction (Process ID 162) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
20 май 14, 13:12    [16042209]     Ответить | Цитировать Сообщить модератору
 Re: Использование конструкции IF в DATEDIFF  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8821
Кто-то пишет в эту таблицу.
Попробуйте
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
в начале процедуры.
20 май 14, 13:28    [16042325]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить