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

Откуда: Санкт-Петербург
Сообщений: 19
есть вот такой запрос он работает нормально за исключением того что мне нужно единичные записи например:
id type dt block_id address
4 1 2009-10-08 08:54:17.000 NAV00006884571058 Просвещения пр.
5 1 2009-10-08 08:55:33.000 NAV00006884571058 Ивана Фомина <-----
6 1 2009-10-08 08:55:46.000 NAV00006884571058 Просвещения пр.

а нужно получить:
1 2009-10-08 08:54:17.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:55:33.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:55:46.000 NAV00006884571058 Просвещения пр.


в итоге получается табличка вида а она должна быть без Ивана Фомина и без вот этой записи 0 2009-10-08 09:58:46.000 2009-10-08 10:22:57.000 NAV00006884571058 адрес не определён

type StartDate StopDate block_id address
0 2009-10-08 00:01:33.000 2009-10-08 08:48:31.000 NAV00006884571058 Асафьева
1 2009-10-08 08:48:31.000 2009-10-08 08:50:14.000 NAV00006884571058 Асафьева
1 2009-10-08 08:50:14.000 2009-10-08 08:54:17.000 NAV00006884571058 Энгельса пр.
1 2009-10-08 08:54:17.000 2009-10-08 08:55:33.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:55:33.000 2009-10-08 08:55:46.000 NAV00006884571058 Ивана Фомина
1 2009-10-08 08:55:46.000 2009-10-08 09:03:33.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 09:03:33.000 2009-10-08 09:05:14.000 NAV00006884571058 Культуры пр.
1 2009-10-08 09:05:14.000 2009-10-08 09:11:50.000 NAV00006884571058 КАД
1 2009-10-08 09:11:50.000 2009-10-08 09:18:53.000 NAV00006884571058 Шафировский пр.
1 2009-10-08 09:18:53.000 2009-10-08 09:19:03.000 NAV00006884571058 Зотовский пр.
1 2009-10-08 09:19:03.000 2009-10-08 09:27:22.000 NAV00006884571058 Индустриальный пр.
1 2009-10-08 09:27:22.000 2009-10-08 09:28:52.000 NAV00006884571058 Ириновский пр.
1 2009-10-08 09:28:52.000 2009-10-08 09:29:06.000 NAV00006884571058 Наставников пр.
1 2009-10-08 09:29:06.000 2009-10-08 09:32:23.000 NAV00006884571058 Ириновский пр.
1 2009-10-08 09:32:23.000 2009-10-08 09:40:16.000 NAV00006884571058 Рябовское шоссе
1 2009-10-08 09:40:16.000 2009-10-08 09:58:46.000 NAV00006884571058 адрес не определён
0 2009-10-08 09:58:46.000 2009-10-08 10:22:57.000 NAV00006884571058 адрес не определён
0 2009-10-08 10:22:57.000 2009-10-08 12:58:59.000 NAV00006884571058 Светлановский пр.


declare @t table(id int identity primary key clustered, type int, dt datetime,block_id varchar(50),address varchar(200))

insert @t(type, dt,block_id,address)
select(NS.sensor & 8)/8
, NS.send_date
, NS.block_id
, NS.street + ' ' + NS.house_num as address
FROM dbo.navSlices as NS
where NS.block_id in ('NAV00006884571058')
and NS.send_date >= '2009-10-08 00:00' and NS.send_date <= '2009-10-08 13:00'
ORDER BY NS.send_date ASC



declare @b table(id int identity primary key clustered, type bit, dt datetime,block_id varchar(50),address varchar(200))

insert @b(type,dt,block_id,address)
select TOP 1 type,dt,block_id,address
from @t
ORDER BY dt ASC


insert @b(type,dt,block_id,address)
select t1.type,t1.dt,t1.block_id,t1.address
from @t t2 inner join @t t1
ON t1.id-1 = t2.id
WHERE ((t1.type <> t2.type) or ((t1.address <> t2.address) ))
ORDER BY dt,t1.address ASC

select * from @b

insert @b(type,dt,block_id,address)
select TOP 1 type,dt,block_id,address
from @t
ORDER BY dt DESC



select t1.type,t1.dt as StartDate, t2.dt as StopDate,t2.block_id as block_id, t1.address
FROM @b t1 inner join @b t2
on t1.id+1=t2.id
order by StartDate

исходные данные которые выбирает первый селект вот такие
send_date block_id address
0 2009-10-08 08:41:40.000 NAV00006884571058 Асафьева
0 2009-10-08 08:43:40.000 NAV00006884571058 Асафьева
0 2009-10-08 08:45:41.000 NAV00006884571058 Асафьева
0 2009-10-08 08:46:31.000 NAV00006884571058 Асафьева
1 2009-10-08 08:48:31.000 NAV00006884571058 Асафьева
1 2009-10-08 08:49:22.000 NAV00006884571058 Асафьева
1 2009-10-08 08:50:14.000 NAV00006884571058 Энгельса пр.
1 2009-10-08 08:50:30.000 NAV00006884571058 Энгельса пр.
1 2009-10-08 08:51:21.000 NAV00006884571058 Энгельса пр.
1 2009-10-08 08:53:21.000 NAV00006884571058 Энгельса пр.
1 2009-10-08 08:54:17.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:54:46.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:54:58.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:55:10.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:55:33.000 NAV00006884571058 Ивана Фомина
1 2009-10-08 08:55:46.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:56:17.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:56:30.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:57:13.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:57:24.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:57:36.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:59:36.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 09:01:22.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 09:03:17.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 09:03:33.000 NAV00006884571058 Культуры пр.
1 2009-10-08 09:03:50.000 NAV00006884571058 Культуры пр.
1 2009-10-08 09:04:06.000 NAV00006884571058 Культуры пр.
1 2009-10-08 09:04:24.000 NAV00006884571058 Культуры пр.
1 2009-10-08 09:04:41.000 NAV00006884571058 Культуры пр.
1 2009-10-08 09:04:56.000 NAV00006884571058 Культуры пр.
1 2009-10-08 09:05:09.000 NAV00006884571058 Культуры пр.
1 2009-10-08 09:05:14.000 NAV00006884571058 КАД
1 2009-10-08 09:05:19.000 NAV00006884571058 КАД
9 окт 09, 10:34    [7762765]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Solonsky
мне нужно единичные записи например:
id type dt block_id address
4 1 2009-10-08 08:54:17.000 NAV00006884571058 Просвещения пр.
5 1 2009-10-08 08:55:33.000 NAV00006884571058 Ивана Фомина <-----
6 1 2009-10-08 08:55:46.000 NAV00006884571058 Просвещения пр.
Solonsky
а нужно получить:
1 2009-10-08 08:54:17.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:55:33.000 NAV00006884571058 Просвещения пр.
1 2009-10-08 08:55:46.000 NAV00006884571058 Просвещения пр.
Так что же все-таки нужно, первое или второе?
9 окт 09, 10:37    [7762787]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
aleks2
Guest
Беда тредстартера в том, что он сам не знает шо хочет...
9 окт 09, 10:59    [7762944]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Solonsky
Member

Откуда: Санкт-Петербург
Сообщений: 19
нужно второе для того чтоб в итоге получилась максимально чистая табличка
9 окт 09, 11:01    [7762956]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
А покакому это принципу вместо "Ивана Фомина" должно стать "Просвещения пр." а не "Асафьева" ?
И что такое чистота таблицы, в каких единицах она измерается?
9 окт 09, 11:19    [7763126]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Solonsky
Member

Откуда: Санкт-Петербург
Сообщений: 19
должна подставляться предыдущая улица в данном случае просвещения
для того чтоб получить движение по одной улице
9 окт 09, 11:34    [7763265]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
DENIS_CHEL
Member

Откуда:
Сообщений: 23097
Что значит “предыдущая улица”… Как определить улица это или нет и какое условие сортировки, что бы определить “предыдушность”?

---------------------------------------------------------
- Ну вы тут и нафлудили! - сказал Геракл, зайдя на Авгиев форум.
9 окт 09, 11:39    [7763321]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Solonsky
должна подставляться предыдущая улица в данном случае просвещения
для того чтоб получить движение по одной улице
Тогда давайте уж с начала начинать

Самая ранняя по StartDate запись - Асафьева
Значит, она является предыдущей для всех остальных записей, правда?
Следовательно, везде нужно проставить Асафьева
Тогда и будет "движение по одной улице"

Вам ведь это нужно?
9 окт 09, 11:50    [7763406]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Solonsky
Member

Откуда: Санкт-Петербург
Сообщений: 19
смысл в том что допустим едет машина по улице пересекает перекресток с другой улицей там будет всего 1 точка с названием другой улицы
в итоге алгоритм должен быть следующим если движение это type 1 и попалась одна точка а перед ней и после нее осталась та же улица значит заменить на улицу по которой идет движение или если идет движение и вдруг type стал равен 0 и время остановки меньше 10сек значит тоже изменить type на 1 так как скорее всего это глюк вот примерно так
9 окт 09, 11:51    [7763419]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
DENIS_CHEL
Member

Откуда:
Сообщений: 23097
Т.е. группировка идет по номеру маршрута, а сортировка по времени маршрута…

Осталось уточнить критерий “значит заменить на улицу по которой идет движение”…

---------------------------------------------------------
- Ну вы тут и нафлудили! - сказал Геракл, зайдя на Авгиев форум.
9 окт 09, 11:57    [7763478]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Недавно в центре Москвы блондинка снесла на перекрёстке будку гаишника, когда тупо ехала по навигатору.
Теперь я начинаю понимать, почему так получилось!

Можно, всё-таки, объяснить задачу по-человечески, на простом русском языке?
9 окт 09, 11:58    [7763487]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Solonsky
Member

Откуда: Санкт-Петербург
Сообщений: 19
нужно подставлять название улицы которая была перед ней
9 окт 09, 12:06    [7763568]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
я считаю, что
Solonsky
для того чтоб в итоге получилась максимально чистая табличка
нужно из исходных данных удалить "единичные точки" перекрестков
до применения запросов автора
вот решение задачи как я ее понял
declare @t table (type int, send_date datetime, block_id char(17), address varchar(20))
insert into @t(type, send_date, block_id, address)
select 0, '20091008 08:41:40', 'NAV00006884571058', 'Асафьева' union all
select 0, '20091008 08:43:40', 'NAV00006884571058', 'Асафьева' union all
select 0, '20091008 08:45:41', 'NAV00006884571058', 'Асафьева' union all
select 0, '20091008 08:46:31', 'NAV00006884571058', 'Асафьева' union all
select 1, '20091008 08:48:31', 'NAV00006884571058', 'Асафьева' union all
select 1, '20091008 08:49:22', 'NAV00006884571058', 'Асафьева' union all
select 1, '20091008 08:50:14', 'NAV00006884571058', 'Энгельса пр.' union all
select 1, '20091008 08:50:30', 'NAV00006884571058', 'Энгельса пр.' union all
select 1, '20091008 08:51:21', 'NAV00006884571058', 'Энгельса пр.' union all
select 1, '20091008 08:53:21', 'NAV00006884571058', 'Энгельса пр.' union all
select 1, '20091008 08:54:17', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 08:54:46', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 08:54:58', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 08:55:10', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 08:55:33', 'NAV00006884571058', 'Ивана Фомина' union all
select 1, '20091008 08:55:46', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 08:56:17', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 08:56:30', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 08:57:13', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 08:57:24', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 08:57:36', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 08:59:36', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 09:01:22', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 09:03:17', 'NAV00006884571058', 'Просвещения пр.' union all
select 1, '20091008 09:03:33', 'NAV00006884571058', 'Культуры пр.' union all
select 1, '20091008 09:03:50', 'NAV00006884571058', 'Культуры пр.' union all
select 1, '20091008 09:04:06', 'NAV00006884571058', 'Культуры пр.' union all
select 1, '20091008 09:04:24', 'NAV00006884571058', 'Культуры пр.' union all
select 1, '20091008 09:04:41', 'NAV00006884571058', 'Культуры пр.' union all
select 1, '20091008 09:04:56', 'NAV00006884571058', 'Культуры пр.' union all
select 1, '20091008 09:05:09', 'NAV00006884571058', 'Культуры пр.' union all
select 1, '20091008 09:05:14', 'NAV00006884571058', 'КАД' union all
select 1, '20091008 09:05:19', 'NAV00006884571058', 'КАД'

select type, send_date, block_id, address
  from (select t1.type, t1.send_date, t1.block_id, t1.address
              ,(select top 1 t2.address from @t as t2 
                 where t2.send_date > t1.send_date 
                   and t2.type = t1.type
                 order by t2.send_date) as next_addr
              ,(select top 1 t3.address 
                  from @t as t3 
                 where t3.send_date < t1.send_date 
                   and t3.type = t1.type
                 order by t3.send_date desc) as prev_addr
           from @t as t1) as t
 where not (type = 1 and next_addr = prev_addr and next_addr != address and prev_addr != address)
9 окт 09, 12:19    [7763698]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Solonsky
Member

Откуда: Санкт-Петербург
Сообщений: 19
почти работает :) Зотовский пр. тоже должен становиться Шафировский пр.
send_date block_id address
1 2009-10-08 09:17:34.000 NAV00006884571058 Шафировский пр.
1 2009-10-08 09:17:49.000 NAV00006884571058 Шафировский пр.
1 2009-10-08 09:17:59.000 NAV00006884571058 Шафировский пр.
1 2009-10-08 09:18:04.000 NAV00006884571058 Шафировский пр.
1 2009-10-08 09:18:08.000 NAV00006884571058 Шафировский пр.
1 2009-10-08 09:18:12.000 NAV00006884571058 Шафировский пр.
1 2009-10-08 09:18:30.000 NAV00006884571058 Шафировский пр.
1 2009-10-08 09:18:53.000 NAV00006884571058 Зотовский пр. <-----
1 2009-10-08 09:19:03.000 NAV00006884571058 Индустриальный пр.
1 2009-10-08 09:19:05.000 NAV00006884571058 Индустриальный пр.
1 2009-10-08 09:19:14.000 NAV00006884571058 Индустриальный пр.
1 2009-10-08 09:19:24.000 NAV00006884571058 Индустриальный пр.
1 2009-10-08 09:19:33.000 NAV00006884571058 Индустриальный пр.
1 2009-10-08 09:19:42.000 NAV00006884571058 Индустриальный пр.
9 окт 09, 12:47    [7764021]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Solonsky
Зотовский пр. тоже должен становиться Шафировский пр.
Нет, не должен

Solonsky
перед ней и после нее осталась та же улица значит заменить на улицу по которой идет движение
9 окт 09, 12:50    [7764052]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Solonsky
Member

Откуда: Санкт-Петербург
Сообщений: 19
просто это глюк GPS это часто встречается и там реально ехали по шафировскому и если встречаются так три улицы то нужно подставлять предыдущую тоже :(
9 окт 09, 12:55    [7764108]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
DENIS_CHEL
Member

Откуда:
Сообщений: 23097
Solonsky
и попалась одна точка а перед ней и после нее осталась та же улица значит заменить на улицу по которой идет движение


Так что Паганель прав, вы сами сформулировали такое условие…

---------------------------------------------------------
- Ну вы тут и нафлудили! - сказал Геракл, зайдя на Авгиев форум.
9 окт 09, 12:57    [7764126]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Ничего не понял, как глюк от неглюка отличить...

Быстрее, наверное, будет, если Вы сами поменяете это
Паганель
 where not (type = 1 and next_addr = prev_addr and next_addr != address and prev_addr != address)
на то, что Вам нужно?
(Например, можно убрать выделенное...)
9 окт 09, 13:00    [7764167]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Solonsky
Member

Откуда: Санкт-Петербург
Сообщений: 19
Ура почти то что нужно осталась только проблема почему-то когда доходит до адреса падает с ошибкой
1 2009-10-07 10:18:26.000 NAV00006884571058 Сампсониевский Большой пр.
1 2009-10-07 10:19:21.000 NAV00006884571058 Сампсониевский Большой пр.
1 2009-10-07 10:20:00.000 NAV00006884571058 Сампсониевский Большой пр.

Ошибка
Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.
9 окт 09, 13:52    [7764707]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Solonsky
Member

Откуда: Санкт-Петербург
Сообщений: 19
Solonsky
Ура почти то что нужно осталась только проблема почему-то когда доходит до адреса падает с ошибкой
1 2009-10-07 10:18:26.000 NAV00006884571058 Сампсониевский Большой пр.
1 2009-10-07 10:19:21.000 NAV00006884571058 Сампсониевский Большой пр.
1 2009-10-07 10:20:00.000 NAV00006884571058 Сампсониевский Большой пр.

Ошибка
Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.



Извиняюсь проблема решена.
9 окт 09, 13:53    [7764719]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Solonsky
Member

Откуда: Санкт-Петербург
Сообщений: 19
почти все убралось с адресами где type = 1 остались только
88 1 2009-10-07 10:15:32.000 NAV00006884571058 Литовская
89 1 2009-10-07 10:15:34.000 NAV00006884571058 Литовская
90 0 2009-10-07 10:15:35.000 NAV00006884571058 Литовская
91 1 2009-10-07 10:16:58.000 NAV00006884571058 Литовская

поменять тип если с 0 на 1 если разрыв во времени < 1 минуты
9 окт 09, 14:04    [7764841]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с хитрым запросом.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
declare @t table (type int, send_date datetime, block_id char(17), address varchar(20))
insert into @t(type, send_date, block_id, address)
select 1, '20091007 10:15:32', 'NAV00006884571058', 'Литовская' union all
select 1, '20091007 10:15:34', 'NAV00006884571058', 'Литовская' union all
select 0, '20091007 10:15:35', 'NAV00006884571058', 'Литовская' union all
select 1, '20091007 10:16:58', 'NAV00006884571058', 'Литовская'

select case when type = 0 and datediff(minute, prev_dt, send_date) < 1
            then 1
            else type
        end as new_type
      ,send_date
      ,block_id
      ,address
  from (select t1.type, t1.send_date, t1.block_id, t1.address
              ,(select top 1 t3.send_date 
                  from @t as t3 
                 where t3.send_date < t1.send_date 
                 order by t3.send_date desc) as prev_dt
           from @t as t1) as t

new_type    send_date               block_id          address
----------- ----------------------- ----------------- --------------------
1           2009-10-07 10:15:32.000 NAV00006884571058 Литовская
1           2009-10-07 10:15:34.000 NAV00006884571058 Литовская
1           2009-10-07 10:15:35.000 NAV00006884571058 Литовская
1           2009-10-07 10:16:58.000 NAV00006884571058 Литовская

(4 row(s) affected)
9 окт 09, 15:51    [7765869]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить