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

Откуда: большая деревня
Сообщений: 266
Приветствую. С SQL работаю недавно и не могу понять, в какую сторону копать.

Есть огромная таблица в базе (в день добавляется по 20 - 30 тысяч записей), содержащая полные логи работы пользователей в некой системе. Заносятся события: вход/выход из системы, текущая задача, её этапы. Все они имеют временные отметки, т.е. время начала операции и время окончания операции. Всего 5 видов операций.
Мне нужно вычислить длительность каждой операции за интервал, задаваемый пользователем. Например, user1 имеет показатели:
- время работы в системе 8 часов.
- время в перерыве 30 мин.
- время в разговоре 4 часа
- работа в интерфейсе 30 мин.
- ожидание входящей задачи 3 часа

Уточнение №2. Например, я хочу посчитать время пользователя в системе. Таблица (очень упрощенно) содержит записи:
time type
'20120308 08:00:00 'logon'
'20120308 09:00:00 'logoff'
'20120308 10:00:00 'logon'
'20120308 17:00:00 'logoff'
'20120308 17:01:00 'logoff'


Обратите внимание на последние две записи. При подсчете один из них надо отбросить (больший).

Второй пример той же таблицы, указано действие и время начала и окончания текущей задачи camp:
start type end
'20120308 08:00:00' 'camp1' '20120308 08:15:00'
'20120308 08:00:00' 'camp2' '20120308 08:20:00'
'20120308 08:005:00' 'camp3' '20120308 08:10:00'
'20120308 08:10:00' 'camp4' '20120308 08:50:00'
'20120308 08:00:00' 'camp5' '20120308 09:00:00'


В данном случае правильное значение длительности работы будет 01:00:00 (более ранние задачи "нивелируются" задачей camp5).


Первый пример с временем в системе я сделал, получил 150 строк кода и порядка 10 union. Решение не очевидное, разобраться в коде и при необходимости модифицировать его постороннему будет сложно.

Вопрос у меня в следующем: как в SQL выполнить определенное действие на основе информации о предыдущих шагах. То есть возможно ли сделать так, что операция с текущей строкой зависела от результата и данных в предыдущих строках. Этакий if, но оперирующий предыдущими значениями (на 1, 2, 3 и более шагов назад).
Идея в следующем: беру первую строку, смотрю что это и записываю данные о длительности операции в некоторую переменную. Потом смотрю вторую строку, если операция та же - плюсую к моей переменной, если другая - пишу в новую переменную. и так далее построчно.
8 мар 12, 21:25    [12215959]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
для работы построчно придётся использовать курсор, но это не есть хорошо

первая задача решается обычным TOP 1
вторая задача - "пересекающиеся периоды"
поищите на форуме
8 мар 12, 23:16    [12216324]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Первое и самое важное - перестать думать шагами / действиями / последовательностями.

Нет ничего сложного в понятии предыдущее:
Притом записать это можно разными способами.
SELECT	*
FROM	dbo.Operations	O OUTER APPLY (
		SELECT	Top(1) *
		FROM	dbo.Operations	P
		WHERE	P.[User] = O.[User]
		ORDER BY P.[DateTime] )	P
А иногда можно и короче записать.

Для курсоров предназначена только одна задача - запутаться в трёх соснах. В остальном можно написать практически в один простой и понятный запрос.
9 мар 12, 02:42    [12216995]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
HandKot, спасибо за наводку, попробую разобраться.

Mnior, мне кажется вы пропустили условие (p.type <> o.type), либо сравнение дат (p.start > o.start).
По этому же примеру: как максимально быстро посчитать время в системе, т.е. надо отнять время logon из времени logoff и сложить все периоды. Я придумал такой вариант: ввожу вспомогательную переменную date_base, которая меньше всех остальных дат, и считаю разность между ней и датами в таблице:
sum(datediff(ss, date_base, date_logoff)) - sum(datediff(ss, date_base, date_logon)).

Но этот рассчет неполный, т.к. если первым для пользователя идет logoff, то надо вычесть начальную дату, если последний logon - прибавить конечную дату. Эта задача также решается через TOP 1 (и наверное union), но потребуется повторное просматривание всей таблицы. Нет ли способа попроще/побыстрее?
10 мар 12, 09:57    [12220027]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
virtuOS
потребуется повторное просматривание всей таблицы. Нет ли способа попроще/побыстрее?
Мне кажется вы пытаетесь оперировать вещами о которых подозреваете, и относительно идеального коня в вакууме.

Хочу вас уверить, что логика вычислений не ложиться на физику вычислений прямо непосредственно.
Курсоры в MS SQL работают не так как вам кажется, да и запросы тоже.

Команда сама по себе занимает уйму процессорного времени независимо от того что она делает.
Одна команда SELECT будет в 100500 раз эффективнее 100500 команд проделанных в циклке (курсор).

Вторая ошибка заключается в том что вы пренебрегаете текущей архитектурой системы в целом. Чтение из памяти нелинейно и зависит от многих факторов. Кэширование разных уровней.

Поэтому так называемое "повторное чтение" перекрывается всем вышесказанными явлениями.

Третье, решить задачу можно многими способами, и не только в "лоб". И вы это отлично показали через date_base. Хотя её можно взять любую с потолка (не обязательно меньше чем). Возьмите нуль к примеру.

Четвёртое. Задача определяет структуру и архитектуру системы. Вы же понимаете что бессмысленно создавать систему в которой 99% запросов будут делать одни и те же вычисления. Нужно чтоб всё основное было сделано заранее. И целостность и валидность данных это одна из них.
Уберите в системе эти ошибки с записями, делов то.

Для Top(1) не будет просматривания всей таблицы. Seek же.
--------------------------
То о чём вы говорите есть оконные функции. Но эффективность оных не должно быть слепым и "очевидным".
В 2012 можно получить значение предыдущей строки (к примеру). Для 2005 можно через небольшое извращение.
11 мар 12, 01:54    [12223263]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Mnior
автор
Мне кажется вы пытаетесь оперировать вещами о которых подозреваете, и относительно идеального коня в вакууме.

Возможно, я же с SQL совсем недавно начал работать. Есть пробелы в знаниях, потому и делать пытаюсь так, на сколько хватает знаний.

автор
первая задача решается обычным TOP 1


Не получается. Вот часть таблицы:
CAMPAIGNCUSTOMER_IDDATE_CALLSTARTOPERATORDATE_CALLEND
ADMWK LOGON 26.01.2012 9:0110226.01.2012 9:01
ADMWK LOGOFF 26.01.2012 11:3310226.01.2012 11:33
ADMWK LOGOFF 26.01.2012 11:3410226.01.2012 11:34
ADMWK LOGON 26.01.2012 11:3410226.01.2012 11:34
ADMWK LOGOFF 26.01.2012 13:4410226.01.2012 13:44
ADMWK LOGON 26.01.2012 13:4610226.01.2012 13:46
ADMWK LOGOFF 26.01.2012 13:5110226.01.2012 13:51
ADMWK LOGOFF 26.01.2012 13:5310226.01.2012 13:53
ADMWK LOGON 26.01.2012 13:5310226.01.2012 13:53
ADMWK LOGOFF 26.01.2012 14:0110226.01.2012 14:01
ADMWK LOGOFF 26.01.2012 7:0033126.01.2012 7:00
ADMWK LOGON 26.01.2012 20:0033126.01.2012 20:00


Как из неё сделать такую:

CAMPAIGNCUSTOMER_IDDATE_CALLSTARTOPERATORDATE_CALLEND
ADMWK LOGON 26.01.2012 9:0110226.01.2012 9:01
ADMWK LOGOFF 26.01.2012 11:3310226.01.2012 11:33
ADMWK LOGON 26.01.2012 11:3410226.01.2012 11:34
ADMWK LOGOFF 26.01.2012 13:4410226.01.2012 13:44
ADMWK LOGON 26.01.2012 13:4610226.01.2012 13:46
ADMWK LOGOFF 26.01.2012 13:5110226.01.2012 13:51
ADMWK LOGON 26.01.2012 13:5310226.01.2012 13:53
ADMWK LOGOFF 26.01.2012 14:0110226.01.2012 14:01
ADMWK LOGOFF 26.01.2012 7:0033126.01.2012 7:00
ADMWK LOGON 26.01.2012 20:0033126.01.2012 20:00


У меня получается очень длинно, и через поиск максимального значения, а не выборку первого.
11 мар 12, 12:43    [12224774]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
virtuOS, вам надо выкинуть все LogOff-ы которые не следуют за LogOn-нами?
В чём проблема?
SELECT	*
FROM	dbo.Operations	O OUTER APPLY (
		SELECT	Top(1) *
		FROM	dbo.Operations	P
		WHERE	    P.[User] = O.[User]
			AND P.[DateTime] < O.[DateTime]
		ORDER BY P.[DateTime] DESC)	P
WHERE	O.[Type] != P.[Type] OR P.[Type] IS NULL
Можно и через OVER окошки.
11 мар 12, 20:57    [12228052]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Mnior, проблема наверное в мышлении, сложно перестроиться на табличные вычисления.
Ваш вариант красивый и работает, но в моем случае к сожалению не применим.
Проблема в быстродействии. Как я уже говорил, у меня огромная таблица, в ней более 10 миллионов записей. Насколько я понимаю, ваш запрос для каждой записи этой таблицы просматривает копию этой же таблицы, в результате по грубой оценке надо просмотреть (10^7)^2. Это очень долго, и когда запрос через минуту так и не завершился, я его завершил и проверил на частичной копии таблицы (250 тыс. записей). Запрос выполнялся 2 сек, при том что все возможные индексы присутствуют.

Ниже мой вариант, работает существенно быстрее (менее 1 сек. на рабочей базе), хотя и не такой элегантный. Кроме того, у меня есть проблема "потерянных" записей. В результирующую таблицу не попадают записи по пользователям, у которых последней записью идет LOGON. Эту проблему я решил через дополнительные full outer join, но результат стал ещё более путанный.

Буду благодарен, если поможете упростить запрос или добавить в него недостающие записи.


DECLARE @Date_Call_Start datetime;
DECLARE @Date_Call_End datetime;
SET @Date_Call_Start = '20120126';
SET @Date_Call_End = '20120127';


SELECT	LOGON_LOGOFF.Operator as Operator
	,LOGON_LOGOFF.LOGON as LOGON
	,min(LOGON_LOGOFF.LOGOFF) as LOGOFF

FROM	(SELECT	LOGOFF.Oper as Operator
			,LOGOFF.CALLSTART as LOGOFF
			,max(LOGON.CALLSTART) as LOGON

		FROM	(SELECT	Operator as Oper
					,DATE_CALLSTART	as CALLSTART						
			FROM	CRM.dbo.CALL_HISTORY
			WHERE	DATE_CALLSTART >= @Date_Call_Start and DATE_CALLSTART < @Date_Call_End
				and CUSTOMER_ID = 'LOGOFF'	
				and Operator is not NULL	
			) as LOGOFF

		full outer join (SELECT	Operator as Oper
					,DATE_CALLSTART	as CALLSTART						
				FROM	CRM.dbo.CALL_HISTORY
				WHERE	DATE_CALLSTART >= @Date_Call_Start and DATE_CALLSTART < @Date_Call_End
					and CUSTOMER_ID = 'LOGON'	
					and Operator is not NULL
			) as LOGON
								
		on LOGOFF.Oper = LOGON.Oper
				
		and LOGOFF.CALLSTART > LOGON.CALLSTART

		GROUP BY LOGOFF.Oper, LOGOFF.CALLSTART

	) as LOGON_LOGOFF

GROUP BY LOGON_LOGOFF.LOGON, LOGON_LOGOFF.Operator

ORDER BY LOGON_LOGOFF.Operator, LOGON_LOGOFF.LOGON


Итог:
OperatorLOGONLOGOFF
102012-01-26 09:59:56.0002012-01-26 22:00:48.000
1022012-01-26 09:01:13.0002012-01-26 11:33:13.000
1022012-01-26 11:34:48.0002012-01-26 13:44:14.000
1022012-01-26 13:46:03.0002012-01-26 13:51:47.000
1022012-01-26 13:53:17.0002012-01-26 14:01:23.000
1052012-01-26 09:01:59.0002012-01-26 13:59:54.000
1062012-01-26 09:15:00.0002012-01-26 17:19:08.000
1062012-01-26 17:19:47.0002012-01-26 17:45:04.000
1072012-01-26 09:00:28.0002012-01-26 14:00:45.000
108NULL2012-01-26 07:59:14.000
1092012-01-26 07:59:01.0002012-01-26 16:26:00.000
1092012-01-26 16:26:31.0002012-01-26 20:00:54.000
1112012-01-26 08:57:40.0002012-01-26 17:33:35.000
1122012-01-26 12:30:03.0002012-01-26 15:21:24.000
1122012-01-26 15:22:02.0002012-01-26 19:36:57.000
1122012-01-26 19:37:36.0002012-01-26 21:00:50.000
12 мар 12, 11:10    [12229969]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
iljy
Member

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

есть много способов. 10млн - таблица, прямо скажем, не очень большая, но вот 10млн NESTED LOOPS - это уже существенно. В данном случае можно попробовать использовать такой способ обращения к предыдущей:
create table #t(usr int, dt datetime, tp varchar(10))
insert #t values
(1, '20120308 08:00:00',	 'logon'),
(1, '20120308 09:00:00',	 'logoff'),
(1, '20120308 10:00:00',	 'logon'),
(1, '20120308 17:00:00',	 'logoff'),
(1, '20120308 17:01:00',	 'logoff'),
(2, '20120308 08:00:00',	 'logon'),
(2, '20120308 17:00:00',	 'logoff'),
(2, '20120308 17:01:00',	 'logoff'),
(2, '20120308 17:01:00',	 'logon')

;with cte as(
	select *, ROW_NUMBER() over(partition by usr order by dt) RN from #t
)
select t1.usr, SUM(datediff(s,t1.dt, isnull(t2.dt, GETDATE())))
from cte t1 left join cte t2 on t1.usr = t2.usr and t1.RN+1 = t2.RN
where t1.tp = 'logon' and isnull(t2.tp,'logoff') = 'logoff'
group by t1.usr


drop table #t
12 мар 12, 11:52    [12230293]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Огромное спасибо.
Запрос не полный, часть записей не попадает, но это я уже сам дополнил.
12 мар 12, 17:53    [12234000]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
iljy, вы с IsNull усложнили ради Merge. Это типа хинта получается.
А вот так более понятнее:
DECLARE	@Moving TABLE (
	 [User]	Int
	,[Time]	DateTime
	,[Type]	Bit
	,PRIMARY KEY (
		 [User]
		,[Time]
		,[Type]
	)
)INSERT @Moving VALUES
 (1,'20120308 08:00:00',0)
,(1,'20120308 08:30:00',0)
,(1,'20120308 09:00:00',1)
,(1,'20120308 10:00:00',0)
,(1,'20120308 17:00:00',1)
,(1,'20120308 17:01:00',1)
,(2,'20120308 08:00:00',0)
,(2,'20120308 17:00:00',1)
,(2,'20120308 17:01:00',1)
,(2,'20120308 17:02:00',0)
;WITH CTE AS (
	SELECT	*,Row_Number()OVER(PARTITION BY [User] ORDER BY [Time]) AS ID
	FROM	@Moving
)
SELECT	 L.[User]
	,L.[Time]
	,R.[Time]
FROM	          CTE	L
	LEFT JOIN CTE	R ON R.[User]	= L.[User]
			 AND R.ID	= L.ID + 1
			 AND R.[Type]	= 1
WHERE	L.[Type] = 0
-- OPTION (MERGE JOIN)
Только вот двойной LogOn не обрабатывает.

Вот тоже самое но в "один проход":
;WITH [Sequence] AS (
	SELECT	*
		,Row_Number()OVER(PARTITION BY [User] ORDER BY [Time]) - 1 AS ID
	FROM	@Moving
),[Next] AS (
	SELECT	*
		,CASE	ID % 2
			WHEN 0 THEN Max(CASE WHEN [Type] = 1 THEN [Time] END)OVER(PARTITION BY [User],(ID + 0)/2)
			WHEN 1 THEN Max(CASE WHEN [Type] = 1 THEN [Time] END)OVER(PARTITION BY [User],(ID + 1)/2)
			END	AS [Next]
	FROM	[Sequence]
)
SELECT	 [User]
	,[Time]
	,[Next]
FROM	[Next]
WHERE	[Type] = 0
Хотя читается за "одни раз", но не думаю что догонит Merge даже на больших данных.
Все эти OVER пока коряво реализованы.
А их допиливать ...
C точки зрения маркетинга — бесперспективно. © invm #спасибомикрософтзаэто
12 мар 12, 18:09    [12234142]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Сколько вариантов то! :)

У всех есть один недочет: если первой записью для пользователя идет logoff, он не попадает в результат. Но это мелочи.
А двойных логонов ещё не было замечено.
13 мар 12, 10:03    [12236521]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Подскажите ещё момент.
DECLARE @Date_Call_Start datetime;
DECLARE @Date_Call_End datetime;
SET @Date_Call_Start = '20120308';
SET @Date_Call_End = '20120309';

create table #t1(usr int, dt datetime, CUSTOMER_ID varchar(10))
insert #t1 values
(1, '20120307 23:00:00',	 'logon'),
(2, '20120307 22:00:00',	 'logoff'),
(1, '20120308 07:00:00',	 'logoff'),
(1, '20120308 08:00:00',	 'logon'),
(1, '20120308 09:00:00',	 'logoff'),
(1, '20120308 10:00:00',	 'logon'),
(1, '20120308 17:00:00',	 'logoff'),
(1, '20120308 17:01:00',	 'logoff'),
(2, '20120308 07:30:00',	 'logoff'),
(2, '20120308 08:00:00',	 'logon'),
(2, '20120308 17:00:00',	 'logoff'),
(2, '20120308 17:01:00',	 'logoff'),
(2, '20120308 17:01:00',	 'logon'),
(3, '20120308 09:00:00',	 'logon'),
(3, '20120308 10:00:00',	 'logon')
;

with cte as(
	select	*, 
			ROW_NUMBER() over( partition by usr order by dt) RN 
	from #t1
	where dt >=@Date_Call_Start and dt < @Date_Call_End
)

SELECT	db_MIN_LOGOFF.usr
			,db_MIN_LOGOFF.DATE_CALLSTART
			,datediff(second, db_MIN_LOGOFF.DATE_CALLSTART, db_MIN_LOGOFF.min_logoff) as interval
	FROM (
	
		SELECT	t50.usr
				,min(t50.dt) as min_logoff
				,dateadd(day,DATEDIFF(day,0,t50.dt),0) as DATE_CALLSTART
		FROM	cte t50
		WHERE	CUSTOMER_ID = 'LOGOFF'	
		GROUP BY t50.usr, dateadd(day,DATEDIFF(day,0,t50.dt),0)
	) as db_MIN_LOGOFF
	left join(
		SELECT *
		FROM(	
					SELECT	t60.usr
							,min(t60.dt) as min_logon
							,dateadd(day,DATEDIFF(day,0,t60.dt),0) as DATE_CALLSTART
					FROM	cte t60
					WHERE	CUSTOMER_ID = 'LOGON'		
					GROUP BY t60.usr, dateadd(day,DATEDIFF(day,0,t60.dt),0)
				)as MIN_LOGON 
				
		) as db_MIN_LOGON		
	on	db_MIN_LOGOFF.usr = db_MIN_LOGON.usr
		and db_MIN_LOGON.DATE_CALLSTART = db_MIN_LOGOFF.DATE_CALLSTART
	where  db_MIN_LOGON.min_logon is null
			or db_MIN_LOGOFF.min_logoff < db_MIN_LOGON.min_logon		
order by db_MIN_LOGOFF.usr

drop table #t1


Этот код группирует записи по датам, ищет записи, в которых первым идет logoff, и считает интервал от начала дня до этого logoff (т.е. время в системе).
Мне необходимо поставить проверку, что ранее даты @Date_Call_Start последняя запись для пользователя идет logon. Иначе пропускать запись для данного оператора.
Результат должен быть такой:
usrDATE_CALLSTARTInterval
12012-03-08 00:00:00.00025200
20 мар 12, 10:52    [12279365]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
подниму :)
25 мар 12, 09:47    [12309195]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
А чё тут подымать, если вы не поняли даже того что было написано ранее.
Пока вы будете игнорировать то, что вы не понимаете когда вам что-то говорят, тогда так и будут игнорировать вас.

Может вам обратиться в форум работа?
25 мар 12, 14:27    [12309876]     Ответить | Цитировать Сообщить модератору
 Re: управление потоком выполнения?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Ах да, индексы по перекалькулированному полю практически не работают.
25 мар 12, 14:33    [12309905]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить