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

Откуда: Israel
Сообщений: 5500
Есть две таблицы: поездки водителя и поездки пассажиров:

declare @DriverTrip table (
	did int
	dtm1 datetime
	dtm2 datetime)

declare @UserTrip table (
	uid int
	utm1 datetime
	utm2 datetime)

insert into @DriverTrip
select 1, '08:00', '10:00'

insert into @UserTrip
select 2, '08:15', '09:00'
union
select 3, '08:30', '08:45'
union
select 4, '08:45', '09:15'
union
select 4, '09:30', '10:00'
union
select 5, '09:45', '10:00'

Как отсюда выделить "холостые" пробеги водителя, когда он едет в машине один?
Т.е. должно получиться:

1 '08:00' '08:15'
1 '09:15' '09:30'

Можно ли это сделать одним запросом или придется делать временные таблицы?
В принципе, в таблицах много поездок и много поездок пассажиров. Есть, конечно, идентификатор поездки. Я упростил данные до одной поездки
29 июн 11, 17:08    [10894710]     Ответить | Цитировать Сообщить модератору
 Re: Найти временные промежутки, когда водитель едет без пассажиров  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Rivkin Dmitry, union обоих таблиц с признаком хто из ху, а потом поиск интервалов. Одним запросом - решаемо.
29 июн 11, 17:13    [10894763]     Ответить | Цитировать Сообщить модератору
 Re: Найти временные промежутки, когда водитель едет без пассажиров  [new]
Rivkin Dmitry
Member

Откуда: Israel
Сообщений: 5500
kDnZP
Rivkin Dmitry, union обоих таблиц с признаком хто из ху, а потом поиск интервалов. Одним запросом - решаемо.

Я пока не въехал. Можно показать решение на моием примере?
29 июн 11, 17:16    [10894795]     Ответить | Цитировать Сообщить модератору
 Re: Найти временные промежутки, когда водитель едет без пассажиров  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Rivkin Dmitry
kDnZP
Rivkin Dmitry, union обоих таблиц с признаком хто из ху, а потом поиск интервалов. Одним запросом - решаемо.

Я пока не въехал. Можно показать решение на моием примере?

Ниче страшного, я походу ерунду сказал))), проще можно. А пример щаз иль чуть попозжее сделаю.
29 июн 11, 17:56    [10895251]     Ответить | Цитировать Сообщить модератору
 Re: Найти временные промежутки, когда водитель едет без пассажиров  [new]
aleks2
Guest
declare @DriverTrip table (
	did int,
	dtm1 datetime,
	dtm2 datetime)

declare @UserTrip table (
	uid int,
	utm1 datetime,
	utm2 datetime)

insert into @DriverTrip
select 1, '08:00', '10:00'

insert into @UserTrip
select 2, '08:15', '09:00'
union
select 3, '08:30', '08:45'
union
select 4, '08:45', '09:15'
union
select 4, '09:30', '10:00'
union
select 5, '09:45', '10:00'

declare @BareTrip table (
	did int,
	utm1 datetime,
	utm2 datetime)
	
	--1. холостой пробег до загрузки первого пассажира
insert @BareTrip 
select did, dtm1, MIN(utm1) FROM @DriverTrip D INNER JOIN @UserTrip U ON utm1 between dtm1 and dtm2
group by did, dtm1
having dtm1 < MIN(utm1)

	--2. холостой пробег после выгрузки последнего пассажира
insert @BareTrip 
select did, MAX(utm2), dtm2 FROM @DriverTrip D INNER JOIN @UserTrip U ON utm1 between dtm1 and dtm2
group by did, dtm2
having MAX(utm2)< dtm2

	--3. Дырки между пассажирами
;with
b as (select U.*, ROW_NUMBER() OVER(ORDER BY U.utm2) N FROM ( select * FROM @UserTrip WHERE utm2<(select MAX(utm2) from @UserTrip)) U LEFT OUTER JOIN @UserTrip T ON U.uid<>T.uid and U.utm2 between T.utm1 and T.utm2 WHERE T.uid is null )
,
e as (select U.*, ROW_NUMBER() OVER(ORDER BY U.utm1) N FROM ( select * FROM @UserTrip WHERE utm1>(select MIN(utm1) from @UserTrip)) U LEFT OUTER JOIN @UserTrip T ON U.uid<>T.uid and U.utm1 between T.utm1 and T.utm2 WHERE T.uid is null)
,
holes as (select b.utm2 tm1, e.utm1 tm2 from e inner join b on e.N=b.N)
insert @BareTrip 
select did, tm1, tm2 FROM @DriverTrip D INNER JOIN holes U ON tm1 between dtm1 and dtm2

	
select * FROM @BareTrip
	
-- ЗЫ. Можно, канешно, все это сложить в один запрос... тока зачем?

-- ЗЗЫ. Явно не хватает сведений "хто на ком ехаль".
29 июн 11, 18:35    [10895516]     Ответить | Цитировать Сообщить модератору
 Re: Найти временные промежутки, когда водитель едет без пассажиров  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Ну значит не зря я спать лег))). aleks2 все по полочкам разложил. А то, че-то после отпуска, первый рабочий день мне тяжко думается.
29 июн 11, 21:18    [10895952]     Ответить | Цитировать Сообщить модератору
 Re: Найти временные промежутки, когда водитель едет без пассажиров  [new]
Rivkin Dmitry
Member

Откуда: Israel
Сообщений: 5500
Я тоже не зря пошел спать. Утром родил такой вариант:
declare @tmp table (n int, id int, dt datetime)

insert into @tmp
select row_number() over (order by dt) as n, id, dt
from (
	select did id, dtm1 dt from @DriverTrip
	union
	select did, dtm2 from @DriverTrip
	union 
	select uid, utm1 from @UserTrip
	union
	select uid, utm2 from @UserTrip) t


declare @piece table (id int, dt1 datetime, dt2 datetime)

insert into @piece
select t1.id, t1.dt dt1, t2.dt dt2
from @tmp t1, @tmp t2
where t1.n + 1 = t2.n


declare @Trip table (
	id int,
	tm1 datetime,
	tm2 datetime)

insert into @Trip
select t.id, dt1, dt2
from (
	select did id, dtm1 tm1, dtm2 tm2 from @DriverTrip
	union 
	select uid id, utm1 tm1, utm2 tm2 from @UserTrip) t, @piece p
where p.dt1>=t.tm1 and p.dt2<=t.tm2


select b.*
from @Trip b
inner join (select tm1, tm2 from @Trip group by tm1, tm2 having count(*) = 1) c
on b.tm1 = c.tm1 and b.tm2 = c.tm2
where b.id in (select did from @DriverTrip)
Остается только как-то сравнить эффективность обоих методов. Покритикуйте мой вариант

ЗЫ
Я, конечно, упростил задачу, хотя, в принципе не очень важно хто на хтом ехаль, важнее в какое время и сколько километров

В любом случае, спасибо за помощь и красивое решение
30 июн 11, 10:41    [10897518]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить