Punkty na płaszczyźnie

Chcę prosić o sugestie przy obliczaniu odległości między punktami poszczególnych grup, przy czym współrzędne punktów znajdują się w jednej tabeli, która wygląda mniej więcej tak:

select SQRT(POWER(p2.cX - p1.cX, 2) + POWER(p2.cY - p1.cY)) as Odleglosc
from
(
)

Docelowo chcę obliczyć odległości pomiędzy wszystkimi grupami wyrażone jako średnia wszystkich odległości pomiędzy punktami każdej z grup.

Ile w grupie jest punktów, dokładnie dwa czy może być więcej?

Liczba grup i punktów w nich może być dowolna

bez procedury składowanej, która będzie obliczać średnią odległość w danej grupie się nie obejdzie. BTW wymyśliłeś już algorytm obliczania odległości każdego z każdym?

Niestety jeszcze nie, całość rozwiązania ma być pojedynczym zapytaniem.

Myślę że jest szansa żeby zrobić to za pomocą jednego zapytania, złożonego. Zależy to od sposobu liczenia odległości między wieloma punktami w grupie (bo liczba grup nie ma znaczenia, tylko liczba punktów w grupie). Jeśli podasz sposób w jaki chcesz to policzyć spróbuję pomóc przy zapytaniu.

Na początek chciałbym wybrać wszystkie punkty z poszczególnych par grup i obliczyć odległości między nimi, potem potraktować to średnią co da odległość między grupami.

Struktura tabeli wygląda w tej chwili następująco:

 create table pts
(
pointID int not null primary key identity (1,1),
cX float not null,
cY float not null,
classN varchar(1) not null
)
GO
insert into pts values (1.1, 2.3, 'A')
insert into pts values (1.8, 3.2, 'A')
insert into pts values (2.1, 2.5, 'B')
insert into pts values (1.3, 2.7, 'C')
insert into pts values (2.1, 4.5, 'B')
insert into pts values (1.9, 3.7, 'A')
insert into pts values (3.1, 2.5, 'A')
insert into pts values (3.0, 1.6, 'B')
insert into pts values (4.2, 3.4, 'C')

Spróbowałem tego:

 SELECT distinct SQRT(POWER(t.x2 - t.x1, 2) + POWER(t.y2 - t.y1, 2)) AS Odleglosc, pts.classN, x1, y1, x2, y2
FROM
(	-- kombinacje punktow tych samych klas
	select p.cX as x1, p.cY as y1, pts.cX as x2, pts.cY as y2, p.classN from pts p, pts
	where p.classN = pts.classN
	and p.pointID <> pts.pointID -- nie liczy odleglosci punktu samego ze soba
	 -- by nie liczyl odleglosci miedzy dwoma punktami dwukrotnie ?
) t
join pts on t.classN = pts.classN
group by pts.classN, t.x1, t.x2, t.y1, t.y2

ale DISTINCT nie pomaga mi w tym, by nie liczył odległości między analogicznymi punktami dwukrotnie.

Dla punktów różnych klas: [edit2] - nie wiem, jak ograniczyć powtórne obliczanie odległości między tymi samymi parami poszczególnych klas

select t3.c1, t3.c2, avg(t3.Odleglosc) as Odleglosc from 
(
	select t2.c1 as c1, t2.c2 as c2, SUM(t2.Odleglosc) as Odleglosc from
	(
		select SQRT(POWER(t.x2 - t.x1, 2) + POWER(t.y2 - t.y1, 2)) as Odleglosc, x1, y1, x2, y2, c1, c2
		from
		(	-- kombinacje punktow roznych samych klas
			select p.cX as x1, p.cY as y1, pts.cX as x2, pts.cY as y2, p.classN as c1, pts.classN c2 from pts p, pts
			where p.classN <> pts.classN -- rozne klasy
			-- and p.pointID <> pts.pointID -- nie liczy odleglosci punktu samego ze soba
			 -- by nie liczyl odleglosci miedzy dwoma punktami dwukrotnie ?
		) t
		join pts on t.c1 <> pts.classN and t.c2 <> pts.classN
		group by t.x1, t.x2, t.y1, t.y2, t.c1, t.c2
		-- order by c1,c2
	) t2
	group by t2.c1, t2.c2
) t3
group by t3.c1, t3.c2
order by t3.c1, t3.c2

Mogę prosić o pomoc lub komentarz? Żaden ze sposobów pozbycia się zdublowanych odległości, których próbowałem nie daje oczekiwanego rezultatu.

Dodatkowo w wewnętrznym podzapytaniu spróbowałem dorzucić:

 (select SUM(cX)/COUNT(cX) from pts group by classN), (select SUM(cY)/COUNT(cY) from pts group by classN) as Y

by obliczyć środek ciężkości grupy/klasy, ale tutaj problem jest z kolei z kwestią wyniku, który nie jest skalarem

Jest tu sztuczka z sumą bitową. Tu bazuję na ID punktu, bo nie mam pod ręką systemu z funkcjami rankingującymi (http://msdn.microsoft.com/en-us/library/ms189798.aspx).
Ale poprawniej będzie użyć row_number(), dzięki temu przy użyciu long'a w grupie będzie mogło być max 63 punkty.

select classN, sum(sqrt(power(cx2-cx1, 2) + power(cy2-cy1, 2)))/count(*) as avgDist
from
(
    select classN, cx1, cy1, cx2, cy2
    from
    (
        select  
                p1.classN,
                case when power(2, p1.pointid) < power(2, p2.pointid) then p1.cx else p2.cx end as cx1,
                case when power(2, p1.pointid) < power(2, p2.pointid) then p1.cy else p2.cy end as cy1,
                case when power(2, p1.pointid) < power(2, p2.pointid) then p2.cx else p1.cx end as cx2,
                case when power(2, p1.pointid) < power(2, p2.pointid) then p2.cy else p1.cy end as cy2,
                power(2, p1.pointid) + power(2, p2.pointid) as bitsum
        from pts p1
        inner join pts p2 on p1.pointid<>p2.pointid and p1.classN=p2.classN
    ) as T
    group by bitsum, classN, cx1, cy1, cx2, cy2
) as T
group by classN

Z ręki (bez testowania) modyfikacja z row_number()

select classN, sum(sqrt(power(cx2-cx1, 2) + power(cy2-cy1, 2)))/count(*) as avgDist
from
(
    select classN, cx1, cy1, cx2, cy2
    from
    (
        select p1.classN, 
                  case when p1.rn < p2.rn then p1.cx else p2.cx end as cx1,
                  case when p1.rn < p2.rn then p1.cy else p2.cy end as cy1,
                  case when p1.rn < p2.rn then p2.cx else p1.cx end as cx2,
                  case when p1.rn < p2.rn then p2.cy else p1.cy end as cy2,
                  power(2, p1.rn) + power(2, p2.rn) as bitsum
        from        (select  row_number() over (partition by classN order by pointId) as rn, cx, cy, classN from pts) as p1
        inner join (select  row_number() over (partition by classN order by pointId) as rn, cx, cy, classN from pts) as p2
            on p1.rn<>p2.rn and p1.classN=p2.classN
    ) as T
    group by bitsum, classN, cx1, cy1, cx2, cy2
) as T
group by classN

Nie chce testować mi się tego dla dużej ilości danych, a nie wiem ilu danych się spodziewasz.
Żeby było w jednym zapytaniu to na razie nic więcej nie przychodzi mi do głowy.
Ale na pewno nie możesz użyć zmiennej tabelarycznej? Dlaczego musi to być jeden select? Czy ma to być jedno zapytanie? Bo to co innego, zapytanie może być złożone.

To rozwiązanie działa dobrze, ale dla punktów tych samych klas, niestety ja potrzebuję obliczenia odległości pomiędzy różnymi klasami, więc szukam punktów z różnych klas i odległości między nimi. Zmodyfikowałem Twój kod do postaci, która miała odpowiadać właśnie wybieraniu punktów z różnych klas, ale zrobiłem to chyba źle, bo znów odległość między powiedzmy a1 z grupy/klasy A i b1 z grupy/klasy B jest liczona dwa razy - od a1 bo b1 i od b1 do a1 (chociaż obliczanie u mnie było niepoprawne, co zauważyłem dzięki Twojemu kodowi). Ale wciąż pary punktów wybierane są dwukrotnie.

 SELECT T.c1, T.c2, SUM(SQRT(POWER(cx2-cx1, 2) + POWER(cy2-cy1, 2)))/COUNT(*) AS avgDist
FROM
(
	SELECT T.c1, cx1, cy1,T.c2, cx2, cy2
	FROM
	(
		SELECT
		p1.classN as c1, 
		CASE WHEN POWER(2, p1.pointid) < POWER(2, p2.pointid) THEN p1.cx ELSE p2.cx END AS cx1,
		CASE WHEN POWER(2, p1.pointid) < POWER(2, p2.pointid) THEN p1.cy ELSE p2.cy END AS cy1,
		p2.classN as c2,
		CASE WHEN POWER(2, p1.pointid) < POWER(2, p2.pointid) THEN p2.cx ELSE p1.cx END AS cx2,
		CASE WHEN POWER(2, p1.pointid) < POWER(2, p2.pointid) THEN p2.cy ELSE p1.cy END AS cy2,
		POWER(2, p1.pointid) + POWER(2, p2.pointid) AS bitsum
		FROM pts p1
		INNER JOIN pts p2 ON p1.pointid<>p2.pointid AND p1.classN<>p2.classN
		--order by classN
	) AS T
	GROUP BY bitsum, c1, c2, cx1, cy1, cx2, cy2
	--order by T.c1, T.c2, cx1, cy1, cx2, cy2
) AS T
GROUP BY T.c1, T.c2
order by T.c1, T.c2

Pewnie chodzi o użycie sztuczki z sumą bitową, ale nie zrozumiałem jej, podejrzewam, że służyła właśnie do wybierania różnych punktów z tych samych klas bez powtórzeń.

W warunkach rozwiązania powiedziano: pojedyncze zapytanie. Próbowałem użyć zmiennej tabelarycznej, ale nie wiem jeszcze jak się nią posługiwać

Czy można uniknąć powtórzeń warunkując to x1+x2<>x2+x1 na zewnątrz zapytania? Dostaję błąd 'Invalid object name 't1'.', nie mogę w where korzystać z tabeli t1?

select * from
(
				select p1.classN as c1, p1.cX as x1, p1.cY as y1, p2.classN c2, p2.cX as x2, p2.cY as y2,
				cast(p1.cX as varchar(3))+'_'+cast(p1.cY as varchar(3)) as pattern1,
				cast(p2.cX as varchar(3))+'_'+cast(p2.cY as varchar(3)) as pattern2
				from pts p1, pts p2
				where p1.classN <> p2.classN-- rozne klasy
) t1
where -- para p1+p2<>p2+p1
(
	select cast(t1.pattern1 as varchar(7))+cast(t1.pattern2 as varchar(7))
	from t1
) <>
(
	select cast(t1.pattern2 as varchar(7))+cast(t1.pattern1 as varchar(7))
	from t1
)

Kiedy zostawiłem dwukrotne wyświetlanie punktów, spróbowałem dorzucić obliczanie środka ciężkości każdej z grup/klas, ale nie mogłem dodać nowej pozycji w select by nie zwiększyć ilości powtórzeń wyświetlanych danych, ani z join, ani z union nie udało mi się tego uzyskać i z żalem muszę chyba poddać to zadanie ze względu na czas, zbyt mało ćwiczeń i wiedzy. To moje ostatnie 'pomysły':

select t2.c1 as c1, t2.c2 as c2, cast(SUM(t2.Odleglosc)/COUNT(*) as varchar) as Odleglosc
	from
	(
		select SQRT(POWER(t.x2 - t.x1, 2) + POWER(t.y2 - t.y1, 2)) as Odleglosc, x1, y1, x2, y2, c1, c2
		from
		(	-- kombinacje punktow roznych klas
			select p.classN as c1, p.cX as x1, p.cY as y1, pts.classN c2, pts.cX as x2, pts.cY as y2
			from pts p, pts
			where p.classN <> pts.classN -- rozne klasy
		) t
		join pts on t.c1 <> pts.classN and t.c2 <> pts.classN
		group by t.c1, t.c2, t.x1, t.x2, t.y1, t.y2
		-- order by c1,c2
	) t2
	/*convert(varchar, a.mX)+', '+convert(varchar, a.mY) as "Wspolrzedne srodka ciezkosci"*/
	union
	--join 
	(
		SELECT classN as c1, classN as c2, cast(SUM(cX)/COUNT(cX) as varchar)+', '+cast(SUM(cY)/COUNT(cY)as varchar) as src FROM pts group by classN
	)as a-- on 1=1
	group by t2.c1, t2.c2--, a.mX, a.mY
	order by t2.c1, t2.c2

"wybrać wszystkie punkty z poszczególnych par grup i obliczyć odległości między nimi, potem potraktować to średnią co da odległość między grupami"
INSERT INTO pts VALUES (1.1, 2.3, 'A') pkt1
INSERT INTO pts VALUES (1.8, 3.2, 'A') 2
INSERT INTO pts VALUES (2.1, 2.5, 'B') 3
INSERT INTO pts VALUES (1.3, 2.7, 'C') 4
INSERT INTO pts VALUES (2.1, 4.5, 'B') 5
INSERT INTO pts VALUES (1.9, 3.7, 'A') 6
INSERT INTO pts VALUES (3.1, 2.5, 'A') 7
INSERT INTO pts VALUES (3.0, 1.6, 'B') 8
INSERT INTO pts VALUES (4.2, 3.4, 'C') 9

Czyli chcesz najpierw wybrać takie pary:
grupa AB
1-3
1-5
1-8
2-3
2-5
2-8
6-3
6-5
6-8
7-3
7-5
7-8
grupa AC
1-4
1-9
2-4
2-9
6-4
6-9
7-4
7-9
itd.?
policzyć odległości między nimi i policzyć średnią, to da ci średnią odległość między grupą AB, ...
Teraz dobrze zrozumiałem?

Tak, średnia z odległości pomiędzy wszystkimi punktami z klasy A i wszystkimi punktami z klasy B, potem AC i BC. Zależało mi na wyeliminowaniu powtórzeń typu (punkt1, punkt2) - (punkt2, punkt1) - by nie dostawać drugi raz tej samej pary.

Za pomocą kodu zamieszczonego poniżej dostałem wynik, ale wyświetlane są odległości A-B i B-A.

select t3.c1 as Klasa1, (convert(varchar, t3.Odleglosc)) as Odleglosc, t3.c2 as Klasa2 from
(
	select t2.c1 as c1, t2.c2 as c2, convert(varchar, SUM(t2.Odleglosc)/COUNT(*)) as Odleglosc
	from
	(
		select SQRT(POWER(t.x2 - t.x1, 2) + POWER(t.y2 - t.y1, 2)) as Odleglosc, x1, y1, x2, y2, c1, c2
		from
		(
			select p.classN as c1, p.cX as x1, p.cY as y1, pts.classN c2, pts.cX as x2, pts.cY as y2
			from pts p, pts
			where p.classN <> pts.classN
		) t
		join pts on t.c1 <> pts.classN and t.c2 <> pts.classN
		group by t.c1, t.c2, t.x1, t.x2, t.y1, t.y2
	) t2
	group by t2.c1, t2.c2
) as t3
group by t3.c1, t3.c2, t3.Odleglosc
order by t3.c1, t3.c2