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

Откуда: Israel
Сообщений: 5500
SQL Server 2005

Есть таблица поездок машин: водитель (id), авто (id), день поездки, начало и окончание поездки (время), показания спидометра в начале и в конце поездки.

declare @trip table (
id_trip int identity(1, 1) not null primary key,
driver nvarchar(10) not null,
vehicle nvarchar(10) not null,
drive_date datetime not null,
start_drive_time datetime not null,
end_drive_time datetime not null,
start_odom decimal(10,1) not null,
end_odom decimal(10,1) not null)



insert into @trip
select 'a', '1', '20150701', '08:10', '08:30', 1000.1, 1005.2
insert into @trip
select 'a', '1', '20150701', '08:35', '08:55', 1005.2, 1009.4
insert into @trip
select 'a', '1', '20150701', '09:00', '09:35', 1009.4, 1022.3
insert into @trip
select 'a', '2', '20150701', '10:05', '10:15', 22007.3, 22027.6
insert into @trip
select 'a', '2', '20150701', '11:00', '11:45', 22027.6, 22051.1
insert into @trip
select 'a', '1', '20150701', '13:05', '13:20', 1022.3, 1029.8
insert into @trip
select 'a', '1', '20150701', '13:55', '14:25', 1029.8, 1045.7
insert into @trip
select 'a', '2', '20150701', '16:10', '16:40', 22091.5, 22103.9

id_tripdrivervehicledrive_datestart_drive_timeend_drive_timestart_odomend_odom
1a12015-07-0108:1008:301000.11005.2
2a12015-07-0108:3508:551005.21009.4
3a12015-07-0109:0009:351009.41022.3
4a22015-07-0110:0510:1522007.322027.6
5a22015-07-0111:0011:4522027.622051.1
6a12015-07-0113:0513:201022.31029.8
7a12015-07-0113:5514:251029.81045.7
8a22015-07-0116:1016:4022091.522103.9


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

Е.е. получить такой результат:
drivervehicledrive_datestart_drive_timeend_drive_timestart_odomend_odom
a12015070108:1009:351000.11022.3
a22015070110:0511:4522007.322051.1
a12015070113:0514:251022.31045.7
a22015070116:1016:4022091.522103.9


Я решил эту задачу с помощью курсора. Давно уже... До сих пор не нахожу другого решения. А хотелось бы, наконец, избавиться от него (курсора)...
30 июл 15, 12:45    [17954514]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
invm
Member

Откуда: Москва
Сообщений: 9841
Объединение интервалов
30 июл 15, 12:52    [17954566]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31984
Rivkin Dmitry
Соседние поездки, по принципу - один и тот же водитель, та же машина, в тот же день, показания спидометра в конце предыдущей поездки и начале новой совпадают и (!) на оси времени у водителя не было других поездок на других машинах, должны быть объеденены в одну поездку.

А чего это вы объеденили 2 поездки
id_tripdrivervehicledrive_datestart_drive_timeend_drive_timestart_odomend_odom
6a12015-07-0113:0513:201022.31029.8
7a12015-07-0113:5514:251029.81045.7
в одну:
drivervehicledrive_datestart_drive_timeend_drive_timestart_odomend_odom
a12015070113:0514:251022.31045.7
?
Ведь время окончания первой поездки 13:20 не совпадает с временем начала второй поездки 13:55
30 июл 15, 12:54    [17954587]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
Rivkin Dmitry
Member

Откуда: Israel
Сообщений: 5500
alexeyvg,

А я про время ничего не писал - день поездки важен
30 июл 15, 13:23    [17954764]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Rivkin Dmitry
А я про время ничего не писал - день поездки важен
Как это?
Rivkin Dmitry
Так, что начало первой по времени и спидометру - это начало поездки, а время окончания последней поездки и ее спидометр - конец поездки.
Если не обращать внимания на время, то как понять, что
Rivkin Dmitry
на оси времени у водителя не было других поездок на других машинах
???
30 июл 15, 13:30    [17954819]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
Rivkin Dmitry
Member

Откуда: Israel
Сообщений: 5500
iap,
Водитель, например, сделал несколько поездок подряд на одной машине, затем, пересел на другую. В это время кто-то другой ездил на первой машине. Или не ездил никто. Но на оси времени данного водителя есть переход на другую машину. Он может вернуться на первую машину. Но в этом случае его поездки на этой (первой) машине уже не отъединяются
30 июл 15, 13:39    [17954893]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
Rivkin Dmitry
Member

Откуда: Israel
Сообщений: 5500
invm,

Спасибо, я поизучаю. Я не знал, что моя тема может так называться - объединение интервалов. Потому искал неверно.
30 июл 15, 13:41    [17954910]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31984
Rivkin Dmitry
alexeyvg,

А я про время ничего не писал - день поездки важен
А, показания спидометра совпадают... Перепутал, извините.
30 июл 15, 14:14    [17955129]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
Okmor
Member

Откуда:
Сообщений: 132
Где то так.
(Подлежит оптимизации!!!)

declare @trip table (
id_trip int identity(1, 1) not null primary key,
driver nvarchar(10) not null,
vehicle nvarchar(10) not null,
drive_date datetime not null,
start_drive_time datetime not null,
end_drive_time datetime not null,
start_odom decimal(10,1) not null,
end_odom decimal(10,1) not null)

insert into @trip select 'a', '1', '20150701', '08:10', '08:30', 1000.1, 1005.2
insert into @trip select 'a', '1', '20150701', '08:35', '08:55', 1005.2, 1009.4
insert into @trip select 'a', '1', '20150701', '09:00', '09:35', 1009.4, 1022.3
insert into @trip select 'a', '2', '20150701', '10:05', '10:15', 22007.3, 22027.6
insert into @trip select 'a', '2', '20150701', '11:00', '11:45', 22027.6, 22051.1
insert into @trip select 'a', '1', '20150701', '13:05', '13:20', 1022.3, 1029.8
insert into @trip select 'a', '1', '20150701', '13:55', '14:25', 1029.8, 1045.7
insert into @trip select 'a', '2', '20150701', '16:10', '16:40', 22091.5, 22103.9

insert into @trip select 'a', '1', '20150702', '08:10', '08:30', 1000.1, 1005.2
insert into @trip select 'a', '1', '20150702', '08:35', '08:55', 1005.2, 1009.4
insert into @trip select 'a', '1', '20150702', '09:00', '09:35', 1009.4, 1022.3
insert into @trip select 'a', '2', '20150702', '10:05', '10:15', 22007.3, 22027.6
insert into @trip select 'a', '2', '20150702', '11:00', '11:45', 22027.6, 22051.1
insert into @trip select 'a', '1', '20150702', '13:05', '13:20', 1022.3, 1029.8
insert into @trip select 'a', '1', '20150702', '13:55', '14:25', 1029.8, 1045.7
insert into @trip select 'a', '2', '20150702', '16:10', '16:40', 22091.5, 22103.9

if object_id('tempdb..#Route') is not null drop table #Route
select 
ROW_NUMBER ( ) OVER( ORDER BY drive_date ,start_drive_time )  num_row ,* 
into #Route
from @trip
order by drive_date ,start_drive_time




if object_id('tempdb..#Route2') is not null drop table #Route2

select
#Route.num_row
,#Route.id_trip 
,#Route.driver 
,#Route.vehicle 
,#Route.drive_date
,#Route.start_drive_time 
,#Route.end_drive_time 
,#Route.start_odom 
,#Route.end_odom 
,case when ISNULL(S.vehicle,-1)<>#Route.vehicle then 1 else 0 end isstart
,case when ISNULL(F.vehicle,-1)<>#Route.vehicle then 1 else 0 end isfinish
into #Route2
from 
  #Route
outer apply (select 
				#Route.num_row
				,vehicle
			from #Route M1
			where M1.num_row=#Route.num_row-1
			and M1.drive_date=#Route.drive_date) as S
outer apply (select 
				#Route.num_row
				,vehicle
			from #Route M1
			where M1.num_row=#Route.num_row+1
			and M1.drive_date=#Route.drive_date) as F	
			
select 
#Route2.driver
,#Route2.vehicle
,#Route2.drive_date
,#Route2.start_drive_time
,RF.end_drive_time
,#Route2.start_odom
,RF.end_odom

from #Route2
outer apply (select top 1
				R.num_row finishID  
				,R.end_drive_time
				,R.end_odom
				from #Route2 R
				where R.isfinish=1 and
				      R.driver=#Route2.driver and
				      R.drive_date=#Route2.drive_date and
				      R.vehicle=#Route2.vehicle and
					  R.num_row>=#Route2.num_row and 
					  R.isfinish=1
				order by num_row	  
					  
				)  RF
where 	#Route2.isstart=1		
30 июл 15, 14:59    [17955472]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
DECLARE @trip TABLE (
	id_trip INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	driver NVARCHAR(10) NOT NULL,
	vehicle NVARCHAR(10) NOT NULL,
	drive_date SMALLDATETIME NOT NULL, --<
	start_drive_time SMALLDATETIME NOT NULL, --<
	end_drive_time SMALLDATETIME NOT NULL, --<
	start_odom DECIMAL(10,1) NOT NULL,
	end_odom DECIMAL(10,1) NOT NULL
)

INSERT INTO @trip
SELECT 'a', '1', '20150701', '08:10', '08:30', 1000.1, 1005.2
UNION ALL
SELECT 'a', '1', '20150701', '08:35', '08:55', 1005.2, 1009.4
UNION ALL
SELECT 'a', '1', '20150701', '09:00', '09:35', 1009.4, 1022.3
UNION ALL
SELECT 'a', '2', '20150701', '10:05', '10:15', 22007.3, 22027.6
UNION ALL
SELECT 'a', '2', '20150701', '11:00', '11:45', 22027.6, 22051.1
UNION ALL
SELECT 'a', '1', '20150701', '13:05', '13:20', 1022.3, 1029.8
UNION ALL
SELECT 'a', '1', '20150701', '13:55', '14:25', 1029.8, 1045.7
UNION ALL
SELECT 'a', '2', '20150701', '16:10', '16:40', 22091.5, 22103.9

SELECT MAX(t.driver), MAX(t.vehicle), MAX(t.drive_date), MIN(t.start_drive_time), MAX(t.end_drive_time), MIN(t.start_odom), MAX(t.end_odom)
FROM (
	SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY t.driver ORDER BY t.start_drive_time) - ROW_NUMBER() OVER (PARTITION BY t.driver, t.vehicle ORDER BY t.start_drive_time)
	FROM @trip t
) t
GROUP BY t.rn
ORDER BY MIN(t.start_drive_time)


оконный функции как вариант :)

в итоге получите вот такой результат:

--- ---- ----------------------- ----------------------- ----------------------- ---------- ----------
a   1    2015-07-01 00:00:00     1900-01-01 08:10:00     1900-01-01 09:35:00     1000.1     1022.3
a   2    2015-07-01 00:00:00     1900-01-01 10:05:00     1900-01-01 11:45:00     22007.3    22051.1
a   1    2015-07-01 00:00:00     1900-01-01 13:05:00     1900-01-01 14:25:00     1022.3     1045.7
a   2    2015-07-01 00:00:00     1900-01-01 16:10:00     1900-01-01 16:40:00     22091.5    22103.9
30 июл 15, 15:33    [17955747]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Да... забыл сказать. Чтобы все быстро шевелилось можно еще покрывающий индекс добавить на столбцы, которые в ROW_NUMBER используются.
30 июл 15, 15:37    [17955782]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Еще наверное будет корректнее (учитывать drive_date):

SELECT MAX(t.driver), MAX(t.vehicle), MAX(t.drive_date), MIN(t.start_drive_time), MAX(t.end_drive_time), MIN(t.start_odom), MAX(t.end_odom)
FROM (
	SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY t.driver ORDER BY t.drive_date, t.start_drive_time) - ROW_NUMBER() OVER (PARTITION BY t.driver, t.vehicle ORDER BY t.drive_date, t.start_drive_time)
	FROM @trip t
) t
GROUP BY t.rn
ORDER BY MIN(t.start_drive_time)


И, если честно, довольно странно - почему drive_date отдельный столбец. Что мешает хранить дату в start_drive_... и end_drive_... ведь табличка чуточку меньше весить будет.

Из этой же оперы можно поменять decimal(10,1) на decimal(9,1) - весить будет 9 байт, а только 5. В итоге меньше логических чтений.
30 июл 15, 15:49    [17955856]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
Okmor
Member

Откуда:
Сообщений: 132
AlanDenton. На наборе данных представленных ниже будет не правильный результат.
Я никогда не нуждался в оконных функциях и узнал что то новое.
- Спасибо.


if object_id('tempdb..#trip') is not null drop table #trip

create table #trip  (
id_trip int identity(1, 1) not null primary key,
driver nvarchar(10) not null,
vehicle nvarchar(10) not null,
drive_date datetime not null,
start_drive_time datetime not null,
end_drive_time datetime not null,
start_odom decimal(10,1) not null,
end_odom decimal(10,1) not null)

insert into #trip select 'a', '1', '20150701', '08:10', '08:30', 1000.1, 1005.2
insert into #trip select 'a', '1', '20150701', '08:35', '08:55', 1005.2, 1009.4
insert into #trip select 'a', '1', '20150701', '09:00', '09:35', 1009.4, 1022.3
insert into #trip select 'a', '2', '20150701', '10:05', '10:15', 22007.3, 22027.6
insert into #trip select 'a', '2', '20150701', '11:00', '11:45', 22027.6, 22051.1
insert into #trip select 'a', '1', '20150701', '13:05', '13:20', 1022.3, 1029.8
insert into #trip select 'a', '1', '20150701', '13:55', '14:25', 1029.8, 1045.7
insert into #trip select 'a', '2', '20150701', '16:10', '16:40', 22091.5, 22103.9

insert into #trip select 'a', '1', '20150702', '08:10', '08:30', 1000.1, 1005.2
insert into #trip select 'a', '1', '20150702', '08:35', '08:55', 1005.2, 1009.4
insert into #trip select 'a', '1', '20150702', '09:00', '09:35', 1009.4, 1022.3
insert into #trip select 'a', '2', '20150702', '10:05', '10:15', 22007.3, 22027.6
insert into #trip select 'a', '2', '20150702', '11:00', '11:45', 22027.6, 22051.1
insert into #trip select 'a', '1', '20150702', '13:05', '13:20', 1022.3, 1029.8
insert into #trip select 'a', '1', '20150702', '13:55', '14:25', 1029.8, 1045.7
insert into #trip select 'a', '2', '20150702', '16:10', '16:40', 22091.5, 22103.9
30 июл 15, 18:19    [17956807]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
Okmor
Member

Откуда:
Сообщений: 132
Вот мой вариант.
if object_id('tempdb..#trip') is not null drop table #trip

create table #trip  (
id_trip int identity(1, 1) not null primary key,
driver nvarchar(10) not null,
vehicle nvarchar(10) not null,
drive_date datetime not null,
start_drive_time datetime not null,
end_drive_time datetime not null,
start_odom decimal(10,1) not null,
end_odom decimal(10,1) not null)

insert into #trip select 'a', '1', '20150701', '08:10', '08:30', 1000.1, 1005.2
insert into #trip select 'a', '1', '20150701', '08:35', '08:55', 1005.2, 1009.4
insert into #trip select 'a', '1', '20150701', '09:00', '09:35', 1009.4, 1022.3
insert into #trip select 'a', '2', '20150701', '10:05', '10:15', 22007.3, 22027.6
insert into #trip select 'a', '2', '20150701', '11:00', '11:45', 22027.6, 22051.1
insert into #trip select 'a', '1', '20150701', '13:05', '13:20', 1022.3, 1029.8
insert into #trip select 'a', '1', '20150701', '13:55', '14:25', 1029.8, 1045.7
insert into #trip select 'a', '2', '20150701', '16:10', '16:40', 22091.5, 22103.9

insert into #trip select 'a', '1', '20150702', '08:10', '08:30', 1000.1, 1005.2
insert into #trip select 'a', '1', '20150702', '08:35', '08:55', 1005.2, 1009.4
insert into #trip select 'a', '1', '20150702', '09:00', '09:35', 1009.4, 1022.3
insert into #trip select 'a', '2', '20150702', '10:05', '10:15', 22007.3, 22027.6
insert into #trip select 'a', '2', '20150702', '11:00', '11:45', 22027.6, 22051.1
insert into #trip select 'a', '1', '20150702', '13:05', '13:20', 1022.3, 1029.8
insert into #trip select 'a', '1', '20150702', '13:55', '14:25', 1029.8, 1045.7
insert into #trip select 'a', '2', '20150702', '16:10', '16:40', 22091.5, 22103.9

;
with T as (
		select
		ROW_NUMBER() OVER (PARTITION BY driver          ORDER BY drive_date, start_drive_time) -
		ROW_NUMBER() OVER (PARTITION BY drive_date,driver, vehicle ORDER BY drive_date, start_drive_time) num_wind
		,*
		from #trip
		)
select 
T.driver
,T.vehicle
,T.drive_date
,min(T.start_drive_time)  start_drive_time
,max(T.end_drive_time) end_drive_time
,min(T.start_odom) start_odom
,max(T.end_odom) end_odom
from T		
group by 
 T.num_wind 
,T.driver
,T.vehicle
,T.drive_date
30 июл 15, 18:21    [17956814]     Ответить | Цитировать Сообщить модератору
 Re: Объединение соседних записей в одну  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
@Okmor, пожалуйста.

По поводу Вашего замечания правы конечно же, то я это из виду вчера упустил. Вообще если бы не было хранения даты в отдельном столбце... то ROW_NUMBER бы быстрее отрабатывал.
31 июл 15, 09:32    [17958445]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить