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

Откуда: Republic of Belarus
Сообщений: 451
Здравствуйте.
Есть таблица
NAVIGATIONS
-------------
ID_NAVIGATION INT
ID_CAR INT
SPEED INT
RECV_DATE DATETIME

Данные в следующем виде:
id_navigationid_carspeed recv_date
41909036202009-03-18 06:46:30.000
419091 28702009-03-18 06:46:34.000
419092 329302009-03-18 06:46:40.000
41909336252009-03-18 06:46:40.000
41909428712009-03-18 06:46:44.000
419095329322009-03-18 06:46:50.000

т.е. объект джижеться, останавливаеться и т.д.
Мне необходимо определить интервалы стоянки/движения каждого объекта за определенный промежуток времени и их продолжительность.

Правила у меня такие
1. Скорость < 2 = стоянка
2. Приорететно всегда движение, т.е. время стоянки я считаю как разницу двух близких однотипных записей.

Как сделать эту зажачку на курсорах я знаю. Однако, тут все время говорят, что лучше не использовать курсоры.
Помогите плиз или укажите где рыть надо

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
28 июл 09, 11:50    [7467970]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
на выходе то что надо получить?

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

Откуда: Москва
Сообщений: 46975
Алексей2003
на выходе то что надо получить?

для спящего время бодрствования равносильно сну
Да и на входе не всё ясно. Например, ID_CAR=287 и в 06:46:34, и в 06:46:44 стоит, и больше ничего неизвестно.
А ID_CAR=362 в 06:46:30 стоит, а в 06:46:40 движется. Как это всё интерпретировать?
Между указанными моментами времени что происходит?
28 июл 09, 12:56    [7468387]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
На выходе надо получить интервалы остановок и движения с продолжительностью.
Данные поступают через 5-10 сек. Все что между данными я считаю движением, если нет ярко выраженных границ интервалов, типа:
id_navigationid_carspeedrecv_date
41909028702009-03-18 06:46:30.000
41909128702009-03-18 06:46:34.000
419092287302009-03-18 06:46:40.000
41909328752009-03-18 06:46:50.000

Тут я считаю так:
Интервал 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 сек.
и т.д.
Я хз, как объединять радом стоящие записи по прищнаку скорости. Курсор у меня обсчитывает 2 млн. записей за 7 минут. Хотелось бы сделать все одним запросом и побыстрее.

На выходе надо получить выборку по итнервалам движения/остановик типа:
ID_CAR START_DATEEND_DATELENGHTTYPE
2872009-03-18 06:46:30.0002009-03-18 06:46:34.00014 остановка
и т.д.
28 июл 09, 14:02    [7468736]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
;WITH CTE AS
(
 SELECT ROW_NUMBER()OVER(PARTITION BY ID_CAR ORDER BY RECV_DATE, ID_NAVIGATION) N,*
 FROM NAVIGATIONS
)
SELECT t1.ID_CAR, t1.n, t1.RECV_DATE datestart, t2.RECV_DATE dateend, datediff(ss, t2.RECV_DATE, t1.RECV_DATE) diff
FROM CTE t1 join CTE t2 on t1.ID_CAR = t2.ID_CAR and t1.n = t2.n-1;

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

Откуда: Republic of Belarus
Сообщений: 451
Алексей2003
;WITH CTE AS
(
 SELECT ROW_NUMBER()OVER(PARTITION BY ID_CAR ORDER BY RECV_DATE, ID_NAVIGATION) N,*
 FROM NAVIGATIONS
)
SELECT t1.ID_CAR, t1.n, t1.RECV_DATE datestart, t2.RECV_DATE dateend, datediff(ss, t2.RECV_DATE, t1.RECV_DATE) diff
FROM CTE t1 join CTE t2 on t1.ID_CAR = t2.ID_CAR and t1.n = t2.n-1;

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


В этом случае от объединяет только две соседние записи. А если записей об отсутствии движения либо движении несколько?

например:

id_navigationid_car speedrecv_date
445 1 0 2008-05-12 16:59:03.000
447 1 0 2008-05-12 16:59:13.000
449 1 8 2008-05-12 16:59:23.000
451 1 0 2008-05-12 16:59:33.000
453 1 0 2008-05-12 16:59:43.000
455 1 0 2008-05-12 16:59:53.000
457 1 0 2008-05-12 17:00:03.000
459 1 15 2008-05-12 17:00:13.000
461 1 13 2008-05-12 17:00:23.000
463 1 31 2008-05-12 17:00:33.000
465 1 26 2008-05-12 17:00:43.000
467 1 18 2008-05-12 17:00:53.000
469 1 12 2008-05-12 17:01:03.000
471 1 13 2008-05-12 17:01:13.000
473 1 9 2008-05-12 17:01:23.000
475 1 0 2008-05-12 17:01:33.000
477 1 0 2008-05-12 17:01:43.000
479 1 7 2008-05-12 17:01:53.000
481 1 0 2008-05-12 17:02:03.000
483 1 0 2008-05-12 17:02:13.000
485 1 9 2008-05-12 17:02:23.000
487 1 9 2008-05-12 17:02:33.000
489 1 11 2008-05-12 17:02:43.000
491 1 7 2008-05-12 17:02:53.000
493 1 1 2008-05-12 17:03:03.000
495 1 0 2008-05-12 17:03:13.000
761 1 0 2008-05-13 07:19:21.000
764 1 0 2008-05-13 07:19:31.000
767 1 0 2008-05-13 07:19:41.000
770 1 0 2008-05-13 07:19:51.000
28 июл 09, 14:41    [7469064]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
ну а на выходе получить то что надо?

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

Откуда: Republic of Belarus
Сообщений: 451
Мне необходимо из всего этого получить:

id_car start_date end_date length
1 2008-05-12 16:59:03.000 2008-05-12 16:59:13.000 10 остановка
1 2008-05-12 16:59:13.000 2008-05-12 16:59:33.000 20 движение
1 2008-05-12 16:59:33.000 2008-05-12 17:00:03.000 30 остановка
1 2008-05-12 17:00:13.000 2008-05-12 17:01:23.000 70 движение
и т.д.
28 июл 09, 14:47    [7469122]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
почему 2ая строка по 33
а 4ая строка по 23 всего?

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

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

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

4-я строка будет по 2008-05-12 17:01:33.000, просто не дописал, сорри :) Т.е. на движение я включаю интервалы по время остановки.
28 июл 09, 14:57    [7469201]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
а если между движениями 1 строка с остановкой, тогда что отображать?

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

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

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

Игнорируем.
28 июл 09, 15:56    [7469699]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Мне кажется, структуру и сами данные надо проектировать так,
чтобы удобно было доставать их из таблицы и вычислять то, что требуется.
А тут получается, что сама задача громоздит максимальное количество препятствий для написания запроса.
Перепроектировать никак нельзя?
Например, как было бы здорово, если бы на каждой строке был бы статус объекта,
время начала действия статуса, время окончания. Тогда задача решалась бы с полпинка.
28 июл 09, 16:00    [7469745]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Алексей2003
Member

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

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

Откуда: Санкт-Петербург
Сообщений: 629
Select 1 as ID, '2009-01-01' as DB, 0 as Type into #tmp
union All
Select 1 as ID, '2009-01-02' as DB, 0 as Type
union All
Select 1 as ID, '2009-01-03' as DB, 0 as Type
union All
Select 1 as ID, '2009-01-04' as DB, 1 as Type
union All
Select 1 as ID, '2009-01-05' as DB, 1 as Type
union All
Select 1 as ID, '2009-01-06' as DB, 0 as Type
union All
Select 1 as ID, '2009-01-07' as DB, 0 as Type
union All
Select 1 as ID, '2009-01-08' as DB, 1 as Type
union All
Select 1 as ID, '2009-01-09' as DB, 0 as Type

Select
T.ID, T.DB, TMin.DB, TMin.Type as MinID
from #tmp T
left join #tmp TMin on TMin.ID = T.ID and TMin.Type = T.Type and TMin.DB < T.DB
left join #tmp TMinMax on TMinMax.ID = T.ID and TMinMax.Type = T.Type
and T.MinMax.DB < T.DB and TMinMax.DB > TMin.DB
join #tmp TS on TS.ID = T.ID and TS.DB > TMin.DB and TS.DB < T.DB
where TMinMax.ID is null
group by T.ID, T.DB, TMin.DB, TMin.Type
Order by 2



Топкистартеру - примерно это надо? (в этом алгоритме надо "докрутить" границы). А так вроде нечто похожее (только в итоговой выборке инверть значение Type)
28 июл 09, 16:05    [7469809]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
iap
Мне кажется, структуру и сами данные надо проектировать так,
чтобы удобно было доставать их из таблицы и вычислять то, что требуется.
А тут получается, что сама задача громоздит максимальное количество препятствий для написания запроса.
Перепроектировать никак нельзя?
Например, как было бы здорово, если бы на каждой строке был бы статус объекта,
время начала действия статуса, время окончания. Тогда задача решалась бы с полпинка.

Ну, навигационные данные приходят вот в таком вот виде (как показано выше). Статус объекта можно определить по "скорости". Данные тут только дискретные.
Пробовал строить интервалы на лету. В этом случае идет большая загрузка сервера. Решили делать это по событию (как пользователю понадобиться).
28 июл 09, 16:11    [7469858]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

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

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

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

Откуда: Republic of Belarus
Сообщений: 451
Mikle83
Select 1 as ID, '2009-01-01' as DB, 0 as Type into #tmp
union All
Select 1 as ID, '2009-01-02' as DB, 0 as Type
union All
Select 1 as ID, '2009-01-03' as DB, 0 as Type
union All
Select 1 as ID, '2009-01-04' as DB, 1 as Type
union All
Select 1 as ID, '2009-01-05' as DB, 1 as Type
union All
Select 1 as ID, '2009-01-06' as DB, 0 as Type
union All
Select 1 as ID, '2009-01-07' as DB, 0 as Type
union All
Select 1 as ID, '2009-01-08' as DB, 1 as Type
union All
Select 1 as ID, '2009-01-09' as DB, 0 as Type

Select
T.ID, T.DB, TMin.DB, TMin.Type as MinID
from #tmp T
left join #tmp TMin on TMin.ID = T.ID and TMin.Type = T.Type and TMin.DB < T.DB
left join #tmp TMinMax on TMinMax.ID = T.ID and TMinMax.Type = T.Type
and T.MinMax.DB < T.DB and TMinMax.DB > TMin.DB
join #tmp TS on TS.ID = T.ID and TS.DB > TMin.DB and TS.DB < T.DB
where TMinMax.ID is null
group by T.ID, T.DB, TMin.DB, TMin.Type
Order by 2



Топкистартеру - примерно это надо? (в этом алгоритме надо "докрутить" границы). А так вроде нечто похожее (только в итоговой выборке инверть значение Type)

Да, что то типа этого, только я ничего не понял как это работает :)) и результат не правильный дает.
P.S. Я только месяц с MS SQL Server работаю :)
28 июл 09, 16:21    [7469950]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Mikle83
Member

Откуда: Санкт-Петербург
Сообщений: 629
У меня по тем данным выдает:
2009-01-06 2009-01-03 0
2009-01-08 2009-01-05 1
2009-01-09 2009-01-07 0


т.е. один отрезок с 03-01 по 06-01
второй с 05-01 по 08-01 и т.д.

А что вы хотели увидеть?
28 июл 09, 17:04    [7470297]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Mikle83
Member

Откуда: Санкт-Петербург
Сообщений: 629
Вообще крайне не оптимальный запрос но смысл следующий:
Select
T.ID, T.DB, TMin.DB, TMin.Type as MinID
from #tmp T
left join #tmp TMin on TMin.ID = T.ID and TMin.Type = T.Type and TMin.DB < T.DB
left join #tmp TMinMax on TMinMax.ID = T.ID and TMinMax.Type = T.Type
and T.MinMax.DB < T.DB and TMinMax.DB > TMin.DB
join #tmp TS on TS.ID = T.ID and TS.DB > TMin.DB and TS.DB < T.DB
where TMinMax.ID is null
group by T.ID, T.DB, TMin.DB, TMin.Type
Order by 2

Т - основная таблица.
Для каждой записи в которой ищем предыдудщие по времени (поле DB) записи того же типа (движение/стоянка - поле Type) для одного авто (поле ID) — таблица TMin

Далее из TMin выберем только ту запись, которая была предыдущей для нашей из Т. (чтоб поймать интервал между двумя соседними записями одного типа) - делаем это через отрицание (смотри условие Where) наличия записи такого же типа между основной записью и каждой из таблицы TMin.

А теперь осталась последняя ботва - надо поймать тот промежуток, в котором были записи другого типа - таблица TS (тогда отсечем соседние записи одного типа идущие подряд)

Вроде как-то так... :)
Вообщем первое что в голову пришло то и написал - вроде работает
28 июл 09, 17:27    [7470479]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Вот, что:
123
2009-01-01 2009-01-03 0
2009-01-03 2009-01-06 1
2009-01-06 2009-01-07 0
2009-01-07 2009-01-09 1
28 июл 09, 17:30    [7470500]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
;WITH CTE AS
(
 SELECT ROW_NUMBER()OVER(PARTITION BY ID_CAR ORDER BY RECV_DATE, ID_NAVIGATION) N,*
 FROM NAVIGATIONS
), cte2 as (
SELECT t1.ID_CAR, t1.RECV_DATE datestart, row_number() over(PARTITION BY ID_CAR ORDER BY RECV_DATE, ID_NAVIGATION) N
 FROM CTE t1
  left join CTE t2 on t1.ID_CAR = t2.ID_CAR and t1.n = t2.n-1 --следующая запись
  left join CTE t3 on t1.ID_CAR = t3.ID_CAR and t1.n = t3.n+1 --предыдущая запись
 where t1.SPEED = 0 and ((t2.SPEED = 0 and t3.SPEED <> 0) or (t2.SPEED <> 0 and t3.speed = 0))
)
select t1.ID_CAR, t1.RECV_DATE datestart, t2.RECV_DATE dateend, datediff(ss, t2.RECV_DATE, t1.RECV_DATE) diff
 from cte2 t1
  join cte2 t2 on t1.ID_CAR = t3.ID_CAR and t1.n = t3.n-1;

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

Откуда: Republic of Belarus
Сообщений: 451
Алексей2003
;WITH CTE AS
(
 SELECT ROW_NUMBER()OVER(PARTITION BY ID_CAR ORDER BY RECV_DATE, ID_NAVIGATION) N,*
 FROM NAVIGATIONS
), cte2 as (
SELECT t1.ID_CAR, t1.RECV_DATE datestart, row_number() over(PARTITION BY ID_CAR ORDER BY RECV_DATE, ID_NAVIGATION) N
 FROM CTE t1
  left join CTE t2 on t1.ID_CAR = t2.ID_CAR and t1.n = t2.n-1 --следующая запись
  left join CTE t3 on t1.ID_CAR = t3.ID_CAR and t1.n = t3.n+1 --предыдущая запись
 where t1.SPEED = 0 and ((t2.SPEED = 0 and t3.SPEED <> 0) or (t2.SPEED <> 0 and t3.speed = 0))
)
select t1.ID_CAR, t1.RECV_DATE datestart, t2.RECV_DATE dateend, datediff(ss, t2.RECV_DATE, t1.RECV_DATE) diff
 from cte2 t1
  join cte2 t2 on t1.ID_CAR = t3.ID_CAR and t1.n = t3.n-1;

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


Выдает ошибку:
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'ID_CAR'.
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'RECV_DATE'.
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'ID_NAVIGATION'.
Что это такое?
29 июл 09, 09:36    [7471964]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
LexMinsk
Алексей2003
;WITH CTE AS
(
 SELECT ROW_NUMBER()OVER(PARTITION BY ID_CAR ORDER BY RECV_DATE, ID_NAVIGATION) N,*
 FROM NAVIGATIONS
), cte2 as (
SELECT t1.ID_CAR, t1.RECV_DATE datestart, row_number() over(PARTITION BY ID_CAR ORDER BY RECV_DATE, ID_NAVIGATION) N
 FROM CTE t1
  left join CTE t2 on t1.ID_CAR = t2.ID_CAR and t1.n = t2.n-1 --следующая запись
  left join CTE t3 on t1.ID_CAR = t3.ID_CAR and t1.n = t3.n+1 --предыдущая запись
 where t1.SPEED = 0 and ((t2.SPEED = 0 and t3.SPEED <> 0) or (t2.SPEED <> 0 and t3.speed = 0))
)
select t1.ID_CAR, t1.RECV_DATE datestart, t2.RECV_DATE dateend, datediff(ss, t2.RECV_DATE, t1.RECV_DATE) diff
 from cte2 t1
  join cte2 t2 on t1.ID_CAR = t3.ID_CAR and t1.n = t3.n-1;

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


Выдает ошибку:
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'ID_CAR'.
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'RECV_DATE'.
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'ID_NAVIGATION'.
Что это такое?
алиасы таблиц у полей расставьте.
29 июл 09, 09:50    [7472035]     Ответить | Цитировать Сообщить модератору
 Re: Выборка интервалов простоя/движения  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
везде t1.

для спящего время бодрствования равносильно сну
29 июл 09, 12:47    [7473293]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить