Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
 Re: Выборка интервалов простоя/движения  [new]
aleks2
Guest
Дело, канешно, хозяйское, но фигней маетесь. Такие данные надо обрабатывать "по мере поступления", триггером или джобом вычисляя для каждой новой записи "предыдущую" и запоминая ссылку на "предыдущую" в эту же таблицу.
Тады все становится тривиальным.
29 июл 09, 14:41    [7474190]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
aleks2
Дело, канешно, хозяйское, но фигней маетесь. Такие данные надо обрабатывать "по мере поступления", триггером или джобом вычисляя для каждой новой записи "предыдущую" и запоминая ссылку на "предыдущую" в эту же таблицу.
Тады все становится тривиальным.


И как быстро это будет все обрабатываться? Каждые 5 сек поступает около 1000 данных. При чем, данные могут идти не последовательно, т.е. навигация с более ранним времением может придти позднее.
Зачем мне вычислять на ходу эти интервалы, если они мне нужны только для отчетов?
29 июл 09, 15:59    [7474872]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
aleks2
Guest
LexMinsk

И как быстро это будет все обрабатываться? Каждые 5 сек поступает около 1000 данных. При чем, данные могут идти не последовательно, т.е. навигация с более ранним времением может придти позднее.


Если напишешь правильно - успеет и 10000 "данных" за 5 сек.

LexMinsk

Зачем мне вычислять на ходу эти интервалы, если они мне нужны только для отчетов?

Ну... тебе ведь эти данные окромя как для отчетов и НЕ нужны. Зато отчеты будут быстрыми...
29 июл 09, 16:08    [7474946]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2LexMinsk
а вы чтоже, на каждый запрос пользователя формируете отчет с нуля? может подготовить данные для пользователя, а потом выдавать результат конечный..?

для спящего время бодрствования равносильно сну
29 июл 09, 16:08    [7474954]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Алексей2003
2LexMinsk
а вы чтоже, на каждый запрос пользователя формируете отчет с нуля? может подготовить данные для пользователя, а потом выдавать результат конечный..?

для спящего время бодрствования равносильно сну

Конечно же нет. Буду подготавливать отчеты.
29 июл 09, 17:01    [7475282]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2LexMinsk
а что с моим запросом? он еле ворочается?

для спящего время бодрствования равносильно сну
29 июл 09, 17:12    [7475342]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
+ offtop
Алексей2003
1. Скорость < 2 = стоянка
Прикольно. На "задней" передаче (-7) это стоянка. :)
iap
Между указанными моментами времени что происходит?
Мда, одной гармоники не достаточно, хотя б ещё километраж указывался. Это если надо ещё какие-то задачи решить, а дискретка по одному показателю сильный разброс может дать. Писать по жёсткому временному интервалу не выгодно, если "действия" происходят быстрее, то их не увидишь, а если медленнее то накладно по занимаемому месту. C акселерометром ещё лучше, что-то типа: Δt = 1 / ( k2*a2 + k1*|v| + k0 ).
iap
Как было бы здорово, если бы на каждой строке был бы статус объекта, время начала действия статуса, время окончания.
Уверен, что в фиксирующей аппаратуре всё это есть. Хотя не пойму, зачем это нуно.

Вот мой вариант с другого боку.

DECLARE	@Navigation	TABLE (
	 ID	BigInt	PRIMARY KEY IDENTITY	-- В (|)
	,Car	Int
	,[Time]	DateTime
	,Speed	Decimal(4,1)
	,UNIQUE (
		 Car
		,[Time]
	)
);
SELECT	 Car
	,Grp
	,Type
	,Min([Time])	AS [From]
	,Max([Time])	AS [To]
FROM (	SELECT	 N.Car
		,Row_Number()OVER(PARTITION BY N.Car         ORDER BY N.[Time])
		-Row_Number()OVER(PARTITION BY N.Car, S.Type ORDER BY N.[Time]) AS Grp
		,S.Type
		,N.[Time]
	FROM	@Navigation	N
		CROSS APPLY (
			SELECT CASE WHEN N.Speed < 2. THEN 0 ELSE 1 END AS Type
			)	S
	) T
GROUP BY Car
	,Grp
	,Type
Не знаю, как индексы подхватит.
29 июл 09, 22:59    [7476256]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Алексей2003
2LexMinsk
а что с моим запросом? он еле ворочается?

для спящего время бодрствования равносильно сну


2,5 млн записей обработал за 2 минуты. (Это без построения оптимальных индексов). Но обработал не правильно. Некоторые данные вообще пропустил, а по некоторым построил неправильные интервалы. :(
30 июл 09, 09:50    [7476945]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Mnior
+
+ offtop
Алексей2003
1. Скорость < 2 = стоянка
Прикольно. На "задней" передаче (-7) это стоянка. :)
iap
Между указанными моментами времени что происходит?
Мда, одной гармоники не достаточно, хотя б ещё километраж указывался. Это если надо ещё какие-то задачи решить, а дискретка по одному показателю сильный разброс может дать. Писать по жёсткому временному интервалу не выгодно, если "действия" происходят быстрее, то их не увидишь, а если медленнее то накладно по занимаемому месту. C акселерометром ещё лучше, что-то типа: Δt = 1 / ( k2*a2 + k1*|v| + k0 ).
iap
Как было бы здорово, если бы на каждой строке был бы статус объекта, время начала действия статуса, время окончания.
Уверен, что в фиксирующей аппаратуре всё это есть. Хотя не пойму, зачем это нуно.

Вот мой вариант с другого боку.

DECLARE	@Navigation	TABLE (
	 ID	BigInt	PRIMARY KEY IDENTITY	-- В (|)
	,Car	Int
	,[Time]	DateTime
	,Speed	Decimal(4,1)
	,UNIQUE (
		 Car
		,[Time]
	)
);
SELECT	 Car
	,Grp
	,Type
	,Min([Time])	AS [From]
	,Max([Time])	AS [To]
FROM (	SELECT	 N.Car
		,Row_Number()OVER(PARTITION BY N.Car         ORDER BY N.[Time])
		-Row_Number()OVER(PARTITION BY N.Car, S.Type ORDER BY N.[Time]) AS Grp
		,S.Type
		,N.[Time]
	FROM	@Navigation	N
		CROSS APPLY (
			SELECT CASE WHEN N.Speed < 2. THEN 0 ELSE 1 END AS Type
			)	S
	) T
GROUP BY Car
	,Grp
	,Type
Не знаю, как индексы подхватит.


Скорость всегда положительная величина.
С прибора поступает много информации в том числе:
- координаты
- курс
- скорость
- высота над уровнем моря
- время фиксации
- напряжение в сети электропитания.
Я просто их не указывал выше, т.к. они в этой задаче не помогут.
30 июл 09, 09:57    [7477009]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2LexMinsk
блин, у меня на работе нет 2005 сервера.. а какие данные не корректны?

для спящего время бодрствования равносильно сну
30 июл 09, 11:05    [7477532]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Алексей2003
2LexMinsk
блин, у меня на работе нет 2005 сервера.. а какие данные не корректны?

для спящего время бодрствования равносильно сну

Запрос не вывел около 60% данных, а которые вывел, то интервалы составил не правильно.
Запрос от Mnior работает верно, вот только временные разрывы есть между интервалами.
30 июл 09, 11:18    [7477672]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
LexMinsk
Запрос от Mnior работает верно, вот только временные разрывы есть между интервалами.
Ну и ступил. Неверно работает, фтопку.
30 июл 09, 11:48    [7477959]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Добавим костыль:
DECLARE	@Navigation	TABLE (
	 ID	BigInt	PRIMARY KEY IDENTITY	-- В (|)
	,Car	Int
	,[Time]	DateTime
	,Speed	Decimal(4,1)
	,UNIQUE (Car
		,[Time]
	)
);
SELECT	 T.Car
	,T.Grp
	,T.[Type]
	,T.[From]
	,N.[Time]	AS [To]
FROM  (	SELECT	 Car
		,Grp
		,[Type]
		,Min([Time])	AS [From]
		,Max([Time])	AS [To]
	FROM (	SELECT	 N.Car
			,Row_Number()OVER(PARTITION BY N.Car           ORDER BY N.[Time])
			-Row_Number()OVER(PARTITION BY N.Car, S.[Type] ORDER BY N.[Time]) AS Grp
			,S.[Type]
			,N.[Time]
		FROM	@Navigation	N
			CROSS APPLY (
				SELECT CASE WHEN N.Speed < 2. THEN 0 ELSE 1 END AS [Type]
				)	S
		) T
	GROUP BY Car
		,Grp
		,[Type]
	) T OUTER APPLY (
		SELECT	Top(1) *
		FROM	@Navigation	N
		WHERE	    N.Car	= T.Car
			AND N.[Time]	> T.[To]
		ORDER BY N.[Time]
	) N
LexMinsk
Запрос от Mnior работает верно, вот только ...
тормозит жутко, да?
30 июл 09, 12:04    [7478127]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Mnior
Добавим костыль:
DECLARE	@Navigation	TABLE (
	 ID	BigInt	PRIMARY KEY IDENTITY	-- В (|)
	,Car	Int
	,[Time]	DateTime
	,Speed	Decimal(4,1)
	,UNIQUE (Car
		,[Time]
	)
);
SELECT	 T.Car
	,T.Grp
	,T.[Type]
	,T.[From]
	,N.[Time]	AS [To]
FROM  (	SELECT	 Car
		,Grp
		,[Type]
		,Min([Time])	AS [From]
		,Max([Time])	AS [To]
	FROM (	SELECT	 N.Car
			,Row_Number()OVER(PARTITION BY N.Car           ORDER BY N.[Time])
			-Row_Number()OVER(PARTITION BY N.Car, S.[Type] ORDER BY N.[Time]) AS Grp
			,S.[Type]
			,N.[Time]
		FROM	@Navigation	N
			CROSS APPLY (
				SELECT CASE WHEN N.Speed < 2. THEN 0 ELSE 1 END AS [Type]
				)	S
		) T
	GROUP BY Car
		,Grp
		,[Type]
	) T OUTER APPLY (
		SELECT	Top(1) *
		FROM	@Navigation	N
		WHERE	    N.Car	= T.Car
			AND N.[Time]	> T.[To]
		ORDER BY N.[Time]
	) N
LexMinsk
Запрос от Mnior работает верно, вот только ...
тормозит жутко, да?


Предыдущий твой запрос 1 млн записей за 17 секудн :) Я только сейчас разобрался как он работает .
Сейчас 2-й протестю.
Сейчас этот
30 июл 09, 14:18    [7479236]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
У меня вот такой вопрос: можно провести ранживания по нескольким полям, а не по всем полям записи?
Я бы допустим задал только ID_CAR и TYPE и он бы мне проранжировал всю выборку, где ID_CAR и TYPE разные. А потом бы группировку сделал с мин и макс функциями. А так, он все поля сравнивает и если они отличаються, а поля ID_CAR и TYPE нет, то ранжирует не одинаково:(
30 июл 09, 14:31    [7479357]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
БЛИН, 20 МИН УЖЕ СЧЕТАЕТ
30 июл 09, 14:59    [7479623]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
LexMinsk
У меня вот такой вопрос: можно провести ранживания по нескольким полям, а не по всем полям записи?
Я бы допустим задал только ID_CAR и TYPE и он бы мне проранжировал всю выборку, где ID_CAR и TYPE разные. А потом бы группировку сделал с мин и макс функциями. А так, он все поля сравнивает и если они отличаються, а поля ID_CAR и TYPE нет, то ранжирует не одинаково:(
Много раз перечитывал, но не никак не втыкаю. :( Как это "все поля", нет там такого.
LexMinsk
БЛИН, 20 МИН УЖЕ СЧЕТАЕТ
А на план моно посмотреть?
30 июл 09, 15:05    [7479664]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
26 минут считало
30 июл 09, 15:17    [7479752]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
borin
Member

Откуда: angarsk/msk
Сообщений: 677
LexMinsk

Интервал 1 для машины 287 2009-03-18 06:46:30.000-2009-03-18 06:46:34.000 остановка, длина 14 сек.
Интервал 2 для машины 287 2009-03-18 06:46:34.000-2009-03-18 06:46:40.000 движение, длина 26 сек.


а не 4 и 6 сек вместо 14 и 26? :-/
30 июл 09, 15:47    [7479962]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
вот:

К сообщению приложен файл (1.csv - 22Kb) cкачать
30 июл 09, 15:57    [7480046]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
borin
LexMinsk

Интервал 1 для машины 287 2009-03-18 06:46:30.000-2009-03-18 06:46:34.000 остановка, длина 14 сек.
Интервал 2 для машины 287 2009-03-18 06:46:34.000-2009-03-18 06:46:40.000 движение, длина 26 сек.


а не 4 и 6 сек вместо 14 и 26? :-/

да, согласен, ошибся.
30 июл 09, 15:58    [7480063]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
LexMinsk
вот:
SELECT	T.ID_CAR	
	,T.GRP
	,T.TYPESTATUS
	,T.STARTDATE
	,N.[RECV_DATE]	AS [To]
FROM  (	SELECT 	 ID_CAR
		,GRP
		,TYPESTATUS
		,Min(RECV_DATE)					AS STARTDATE
		,Max(RECV_DATE)					AS ENDDATE
	--	,DateDiff(Second,Min(RECV_DATE),Max(RECV_DATE))	AS INTERVAL_TIME
FROM  (	SELECT	 N.ID_CAR
		,Row_Number()OVER(PARTITION BY N.ID_CAR               ORDER BY N.RECV_DATE)
		-Row_Number()OVER(PARTITION BY N.ID_CAR, S.TYPESTATUS ORDER BY N.RECV_DATE)	AS GRP
		,S.TYPESTATUS
		,N.RECV_DATE
	FROM	NAVIGATIONS	AS N
		CROSS APPLY (
			SELECT CASE WHEN N.SPEED < 2 THEN 0 ELSE 1 END AS TYPESTATUS) AS S
			)	AS T
	GROUP BY ID_CAR
		,GRP
		,TYPESTATUS
	) T OUTER APPLY (
		SELECT	Top(1) *
		FROM	NAVIGATIONS	N
		WHERE	    N.ID_CAR		= T.ID_CAR
			AND N.[RECV_DATE]	> T.ENDDATE
		ORDER BY N.[RECV_DATE]
	) N
ORDER BY ID_CAR
	,STARTDATE
+
  |--Parallelism(Gather Streams, ORDER BY:([N].[ID_CAR] ASC, [Expr1006] ASC))
|--Sort(ORDER BY:([N].[ID_CAR] ASC, [Expr1006] ASC))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([N].[ID_CAR], [Expr1007]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([N].[ID_CAR], [Expr1007]))
| |--Hash Match(Aggregate, HASH:([N].[ID_CAR], [Expr1005], [Expr1002]), RESIDUAL:([Navigation].[dbo].[NAVIGATIONS].[ID_CAR] as [N].[ID_CAR] = [Navigation].[dbo].[NAVIGATIONS].[ID_CAR] as [N].[ID_CAR] AND [Expr1005] = [Expr1005] AND [Expr1002] = [Expr1002]) DEFINE:([Expr1006]=MIN([partialagg1013]), [Expr1007]=MAX([partialagg1014])))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([N].[ID_CAR], [Expr1005], [Expr1002]))
| |--Hash Match(Partial Aggregate, HASH:([N].[ID_CAR], [Expr1005], [Expr1002]), RESIDUAL:([Navigation].[dbo].[NAVIGATIONS].[ID_CAR] as [N].[ID_CAR] = [Navigation].[dbo].[NAVIGATIONS].[ID_CAR] as [N].[ID_CAR] AND [Expr1005] = [Expr1005] AND [Expr1002] = [Expr1002]) DEFINE:([partialagg1013]=MIN([Navigation].[dbo].[NAVIGATIONS].[RECV_DATE] as [N].[RECV_DATE]), [partialagg1014]=MAX([Navigation].[dbo].[NAVIGATIONS].[RECV_DATE] as [N].[RECV_DATE])))
| |--Compute Scalar(DEFINE:([Expr1005]=[Expr1003]-[Expr1004]))
| |--Parallelism(Distribute Streams, RoundRobin Partitioning)
| |--Sequence Project(DEFINE:([Expr1004]=row_number))
| |--Compute Scalar(DEFINE:([Expr1018]=(1)))
| |--Segment
| |--Parallelism(Gather Streams, ORDER BY:([N].[ID_CAR] ASC, [Expr1002] ASC, [N].[RECV_DATE] ASC))
| |--Sort(ORDER BY:([N].[ID_CAR] ASC, [Expr1002] ASC, [N].[RECV_DATE] ASC))
| |--Parallelism(Distribute Streams, RoundRobin Partitioning)
| |--Sequence Project(DEFINE:([Expr1003]=row_number))
| |--Compute Scalar(DEFINE:([Expr1016]=(1)))
| |--Segment
| |--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [Navigation].[dbo].[NAVIGATIONS].[SPEED] as [N].[SPEED]<(2) THEN (0) ELSE (1) END))
| |--Parallelism(Gather Streams, ORDER BY:([N].[ID_CAR] ASC, [N].[RECV_DATE] ASC))
| |--Sort(ORDER BY:([N].[ID_CAR] ASC, [N].[RECV_DATE] ASC))
| |--Clustered Index Scan(OBJECT:([Navigation].[dbo].[NAVIGATIONS].[PK_NAVIGATIONS] AS [N]))
|--Index Spool(SEEK:([N].[ID_CAR]=[Navigation].[dbo].[NAVIGATIONS].[ID_CAR] as [N].[ID_CAR] AND [Expr1007]=[Expr1007]))
|--Sort(TOP 1, ORDER BY:([N].[RECV_DATE] ASC))
|--Index Spool(SEEK:([N].[ID_CAR]=[Navigation].[dbo].[NAVIGATIONS].[ID_CAR] as [N].[ID_CAR] AND [N].[RECV_DATE] > [Expr1007]))
|--Compute Scalar(DEFINE:([N].[RECV_DATE]=[Navigation].[dbo].[NAVIGATIONS].[RECV_DATE] as [N].[RECV_DATE]))
|--Clustered Index Scan(OBJECT:([Navigation].[dbo].[NAVIGATIONS].[PK_NAVIGATIONS] AS [N]))
Сразу видно, что делается лишняя сортировка в самом начале, так как нет подходящего индекса (Car,Time), да и в самом конце (последний ORDER BY). Но хорошо что параллелизм врубился. И видно что мой костыль притормаживает, из-за отсутствия индекса (Car,Time -> ID_CAR, RECV_DATE).
30 июл 09, 17:39    [7480873]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
24 sec работает с индексом. Все круто )). Спасибо всем кто помогал .
31 июл 09, 10:26    [7482947]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
Все форумы / Microsoft SQL Server Ответить