Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вычисление суммарного расстояния  [new]
?guest?
Guest
Есть таблица
Id, X, Y
Как вычислить суммарное расстояние между точками, последовательность точек задается Id.
Спасибо.
19 сен 09, 22:53    [7683331]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
kkv
Member

Откуда: Москва
Сообщений: 18677
Всем решать, до конца урока еще целых 40 минут!

С уважением, kkv
19 сен 09, 23:31    [7683420]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33348
Блог
Попробуйте применить теорему Пифагора (см учебник за 8й класс)
19 сен 09, 23:44    [7683462]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
?guest?
Guest
Сложность (для меня) в том, что:
таблица

Id X Y
Id1 X1 Y1
Id2 X2 Y2
Id3 X3 Y3
.........


формула: пифа(X1, Y1, X2, Y2) + пифа(X2, Y2, X3, Y3) + ...

т.е. как сджойнить эту таблицу саму на себя со сдвигом по Id
19 сен 09, 23:59    [7683482]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33348
Блог
принцип такой
select *, (select top 1 координата from t as t2 where t2.id<t1.id order by id)
  from t as t1
20 сен 09, 00:13    [7683505]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
demas
Member

Откуда:
Сообщений: 204
Блог
Мой вариант. Единственное, что я маршрут храню в отдельной табличке (my_route), а не вычисляю расстояние между всеми точками. Замечания и улучшения приветствуются.

create table pif (
	id varchar(2),
	x integer,
	y integer)

create table my_route (
	id varchar(2)
	)
	
insert into pif values ('a', 1, 5)
insert into pif values ('b', 9, 2)
insert into pif values ('c', 3, 3)
insert into pif values ('d', 6, 9)

insert into my_route values('a')
insert into my_route values('b')
insert into my_route values('c')


select sum(sqrt((c1.x - c2.x) * (c1.x - c2.x) + (c1.y - c2.y) * (c1.y - c2.y))) as distance
from
	(select t1.id as a, min(t2.id) as b
	from my_route as t1, my_route as t2
	where t1.id < t2.id
	group by t1.id) as lines, 
	pif as c1, pif as c2
where lines.a = c1.id and lines.b = c2.id
20 сен 09, 13:59    [7683975]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Оно ?
declare @t table(id int, x float,y float)
insert into @t select 12,19.1,11.6
insert into @t select  7,15.2,18.5
insert into @t select 19,13.3,17.4
insert into @t select  5,11.4, 7.3
insert into @t select 15,29.5, 3.2
insert into @t select 10,37.6, 8.1
insert into @t select 17,13.7,15.0
insert into @t select  3,29.8,19.9

;with
t as
(select id,x,y,r=row_number()over(order by id)
   from @t
)

select sum(sqrt(square(t2.y-t1.y)*square(t2.x-t1.x))) distance_sum
from t t1
join t t2
on t1.r+1=t2.r
20 сен 09, 14:04    [7683979]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
demas
Замечания и улучшения приветствуются.
Задлянафига для этой задачи группировка? да еще и derrived table? Критик же обозначил решение и, поверьте, это будет быстрее. Второе замечание: пользоваться неявным соединением - признак дурного тона.
20 сен 09, 14:46    [7684024]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Senya_L
demas
Замечания и улучшения приветствуются.
Задлянафига для этой задачи группировка? да еще и derrived table? Критик же обозначил решение и, поверьте, это будет быстрее. Второе замечание: пользоваться неявным соединением - признак дурного тона.
Про группировку погорячился, у Вас же еще точки подразделяются на маршруты.
20 сен 09, 14:50    [7684030]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
demas
Member

Откуда:
Сообщений: 204
Блог
А вариант Критика у меня банально не заработал, а адаптировать я его не смог. У Ozzy действительно красивое решение.
20 сен 09, 14:51    [7684031]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
demas
Member

Откуда:
Сообщений: 204
Блог
Вот:

create table pif (
	id varchar(2),
	x integer,
	y integer)
	
insert into pif values ('a', 1, 5)
insert into pif values ('b', 9, 2)
insert into pif values ('c', 3, 3)
insert into pif values ('d', 6, 9)


select *, (select top 1 x, y from pif as t2 where t2.id < t1.id order by id)
from pif as t1

Msg 116, Level 16, State 1, Line 3
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


Разобраться я не смог и написал свой вариант.
20 сен 09, 14:54    [7684036]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
demas
А вариант Критика у меня банально не заработал, а адаптировать я его не смог. У Ozzy действительно красивое решение.
Что не заработало - показывайте. А насчет красоты... красота - это когда работает и работает быстро. На больших объемах данных, подозреваю, будут тормоза.
20 сен 09, 16:04    [7684161]     Ответить | Цитировать Сообщить модератору
 Re: Почему Index Seek ?  [new]
demas
Member

Откуда:
Сообщений: 204
Блог
Что не заработало - показывайте.


Так я привел код - предыдущее сообщение.
20 сен 09, 16:05    [7684168]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
demas
Вот:

create table pif (
	id varchar(2),
	x integer,
	y integer)
	
insert into pif values ('a', 1, 5)
insert into pif values ('b', 9, 2)
insert into pif values ('c', 3, 3)
insert into pif values ('d', 6, 9)


select *, (select top 1 x, y from pif as t2 where t2.id < t1.id order by id)
from pif as t1

Msg 116, Level 16, State 1, Line 3
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


Разобраться я не смог и написал свой вариант.
Воспользутесь CROSS APPLY.
select sum(sqrt((t2.x1 - t1.x) *(t2.x1 - t1.x) + (t2.y1 - t1.y) * (t2.y1 - t1.y))
from pif as t1
   cross apply (
     select top 1 x, y from pif as t2 where t2.id < t1.id order by id
   ) t2
Это только набросок, а не готовое решение
20 сен 09, 16:08    [7684170]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Senya_L
demas
У Ozzy действительно красивое решение.
... красота - это когда работает и работает быстро. На больших объемах данных, подозреваю, будут тормоза.
А у меня закралось подозрение, что тормоза будут именно на cross apply :-)
Проверьте у себя, если есть желание:
1) Вариант с нумерацией via row_number и соединением, в таблице 50 (пятьдесят) тыс. строк:
+
declare @t table(id int, x float,y float)
declare @lim int,@i int,@dt0 datetime,@dt1 datetime,@result float
set @lim=50000
set @i=1
while @i<=@lim begin
  insert into @t select @i, rand()*1000, rand()*1000
  set @i=@i+1
end
set @dt0=getdate()

;with t as (select id,x,y,r=row_number()over(order by id) from @t)

select @result=sum(sqrt(square(t2.y-t1.y)*square(t2.x-t1.x))) from t t1 join t t2 on t1.r+1=t2.r
set @dt1=getdate()

select @dt0 sel_start,@dt1 sel_finish,datediff(ms,@dt0,@dt1) sel_ms
Result:
sel_startsel_finishsel_ms
2009-09-20 16:50:44.2932009-09-20 16:50:45.6401346


2) Вариант с cross apply, в таблице 5 (пять) тыс. строк :
+
declare @t table(id int, x float,y float)
declare @lim int,@i int,@dt0 datetime,@dt1 datetime,@result float
set @lim=5000
set @i=1
while @i<=@lim begin
  insert into @t select @i, rand()*1000, rand()*1000
  set @i=@i+1
end

set @dt0=getdate()
select @result=sum( sqrt((t2.x - t1.x) *(t2.x - t1.x) + (t2.y - t1.y) * (t2.y - t1.y)) )
from @t as t1
   cross apply (
     select top 1 x, y from @t as t2 where t2.id < t1.id order by id
   ) t2
set @dt1=getdate()

select @dt0 sel_start,@dt1 sel_finish,datediff(ms,@dt0,@dt1) sel_ms
Result:
sel_startsel_finishsel_ms
2009-09-20 16:53:59.7502009-09-20 16:54:15.31015560

-----------------------
Машина: P-IV 2.4GHz RAM 1GB
@@version = 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Express Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
20 сен 09, 16:56    [7684219]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Ozzy-Osbourne,

Я непрочь бы и проверить, но времени нет - пора уходить. Единственное, что могу сказать: сравнивать скорость запросов на табличных переменных, без индексов - дело тухлое.
20 сен 09, 18:23    [7684336]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
SET NOCOUNT ON;
DECLARE @T TABLE(ID INT NOT NULL IDENTITY, X INT, Y INT);
INSERT @T(X,Y) VALUES(1,5);
INSERT @T(X,Y) VALUES(9,2);
INSERT @T(X,Y) VALUES(3,3);
INSERT @T(X,Y) VALUES(6,9);

WITH Points(N,X,Y) AS(SELECT ROW_NUMBER()OVER(ORDER BY ID),X,Y FROM @T)
SELECT SUM(SQRT(SQUARE(P2.X-P1.X)+SQUARE(P2.Y-P1.Y)))
FROM Points P1 JOIN Points P2 ON P1.N=P2.N-1;
20 сен 09, 18:37    [7684354]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
SET NOCOUNT ON;
DECLARE @T TABLE(ID INT NOT NULL IDENTITY, X INT, Y INT);
INSERT @T(X,Y) VALUES(1,5);
INSERT @T(X,Y) VALUES(9,2);
INSERT @T(X,Y) VALUES(3,3);
INSERT @T(X,Y) VALUES(6,9);

WITH Points(N,X,Y) AS(SELECT ROW_NUMBER()OVER(ORDER BY ID),X,Y FROM @T)
SELECT SUM(SQRT(SQUARE(P2.X-P1.X)+SQUARE(P2.Y-P1.Y)))
FROM Points P1 JOIN Points P2 ON P1.N=P2.N-1;
Ой! Кажется, Ozzy-Osbourne это уже раньше предложил.
20 сен 09, 18:40    [7684355]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление суммарного расстояния  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
SQL2008
SET NOCOUNT ON;
DECLARE @T TABLE(ID INT NOT NULL IDENTITY, X INT, Y INT);
INSERT @T(X,Y) VALUES(1,5);
INSERT @T(X,Y) VALUES(9,2);
INSERT @T(X,Y) VALUES(3,3);
INSERT @T(X,Y) VALUES(6,9);

SELECT GEOMETRY::STGeomFromText
(
 'LINESTRING('
 +STUFF
  (
   (
    SELECT ','+STR(X)+STR(Y,12)
    FROM @T
    ORDER BY ID
    FOR XML PATH('')
   ),1,1,''
  )+')',0
).STLength();
20 сен 09, 20:22    [7684515]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить