Svaka cast na resenju. Doduse, tesko je razmeti bez aktuelnih podataka, ali se nazire sustina:
1. za bilo koju zadtu tacku, dobije se tacka udaljena od nje zadatih X metara pomocu funkcije
LocationsGetNextLocation
2. CTE pod imenom 'e' (WITH e) prodje kroz sve tacke koje su povezan u nizu, i za ceo niz izvrsi funkciju
LocationsGetNextLocation
, uz pomoc CROSS APPLY
Posto
LocationsGetNextLocation vraca tacno jedan red, mozda nije moralo CROSS APPLY, mozda je mogao obican JOIN (mozda, nisma siguran, ne vidim stvarnu tabelu).
Ne verujem da ce ovo razumeti puno ljudi, pa da bi bar nesto izaslo opste orisno, evo kako se pomocu rekurzivnog CTE racuna kumulativ. Probacu zatim da dam primer koji odedjuje za svaku tacku neku sledecu tacku koja je na daljini D>= const.
Prvo da napravimo tabelu sa nekoliko parova tacaka:
Code:
IF OBject_ID('tempdb..#Distances') IS NOT NULL DROP TABLE #Distances
GO
CREATE TABLE #Distances (T1 varchar(1), T2 varchar(1), Distance int)
;
INSERT INTO #Distances VALUES ('A','B',120);
INSERT INTO #Distances VALUES ('B','C',23);
INSERT INTO #Distances VALUES ('C','D',56);
INSERT INTO #Distances VALUES ('D','E',14);
INSERT INTO #Distances VALUES ('E','F',78);
INSERT INTO #Distances VALUES ('F','G',13);
INSERT INTO #Distances VALUES ('G','H',65);
INSERT INTO #Distances VALUES ('H','I',12);
INSERT INTO #Distances VALUES ('I','J',89);
INSERT INTO #Distances VALUES ('J','K',85);
INSERT INTO #Distances VALUES ('K','M',74);
INSERT INTO #Distances VALUES ('M','N',52);
INSERT INTO #Distances VALUES ('N','O',16);
INSERT INTO #Distances VALUES ('O','P',37);
INSERT INTO #Distances VALUES ('P','Q',29);
;
Onda u istoj skripti izvrsite ovo:
Code:
WITH RowOrder AS
(-- potrebno je dodeliti Seq - redni broj redovima u tabeli Distance
-- posto ORDER BY ne ide, koristiimo Row_number funkciju
SELECT
Seq = row_number() OVER (ORDER BY T1)
, T1, T2, Distance
FROM #Distances
)
, CumulativeCTE AS
( -- racunanje kumulativne daljine medju tackama
-- klasican priemr rekurzije u CTE
SELECT Seq, T1, T2, Distance, Cumul = Distance
FROM RowOrder
WHERE Seq=1
UNION ALL
SELECT R1.Seq, R1.T1, R1.T2, R1.Distance
, Cumul = C.Cumul + R1.Distance
FROM CumulativeCTE AS C
JOIN RowOrder AS R1 ON R1.Seq = C.Seq + 1
)
SELECT Seq,T1,T2,Distance, Cumul
FROM CumulativeCTE
ORDER BY 1,2
Sad dolazi zapetljani deo - kako izracunati za svakod zadatih tacaka koja je njena najlbliza tacka takva da je daljina barem 150 jedinica.
Na kod za racunanje kumulativa dodacemo jos nekoliko koraka. Tako radde CTE (common table expressions) - resavamo problem korak po korak i koraci se zapisuju u redosledu resavanja. Svaki korak poziva neki od prethodnih koraka.
Rezultat ovog kverija je niz parova tacaka, takvih da je tacka T2 udaljena od T1 barem 150 i to je prva takva tacka (sve ostale su takodje udaljene vise od 150 jedinica ali ih ne prikazujemo). Rezultat je prikazan na dnu prozora sa kodom. Moze se rucno proveriti lako da je rezultat tacan.
Code:
;
SET NOCOUNT OFF
;
WITH RowOrder AS
(-- potrebno je dodeliti Seq - redni broj redovima u tabeli Distance
-- posto ORDER BY ne ide, koristiimo Row_number funkciju
SELECT
Seq = row_number() OVER (ORDER BY T1)
, T1, T2, Distance
FROM #Distances
)
, CumulativeCTE AS
( -- racunanje kumulativne daljine medju tackama
-- klasican priemr rekurzije u CTE
SELECT Seq, T1, T2, Distance, Cumul = Distance
FROM RowOrder
WHERE Seq=1
UNION ALL
SELECT R1.Seq, R1.T1, R1.T2, R1.Distance
, Cumul = C.Cumul + R1.Distance
FROM CumulativeCTE AS C
JOIN RowOrder AS R1 ON R1.Seq = C.Seq + 1
)
--SELECT * FROM CumulativeCTE ORDER BY 1,2
, DistanceBetweenPoints AS
(-- D = daljina svake tacke od svih tacaka koje dodju posle nje:
-- ovo vraca 105 redova, daljina izmedju svaka dve tacke
SELECT A.Seq, A.T1 AS PointA, B.T1 As PointB,
A.Cumul AS A_Cumul, B.Cumul AS B_Cumul
, DistanceT1_T2 = B.Cumul - A.Cumul
FROM CumulativeCTE A
JOIN CumulativeCTE AS B
ON A.Seq < B.Seq
)
, ValidDistantPairs AS
( -- Parovi tacaka koji su udaljeni D >= 150
-- Za svaku PointA treba izabrati onaj par koji imam minimalno D
-- Ovaj deo moze da se preskoci, tu je samo za prikaz, ko hoce da ide korak po korak
-- Ovo vraca 72 reda, izbacili smo sve daljine koje su manje od 150 jedinica
SELECT
Seq, PointA, PointB, DistanceT1_T2
FROM DistanceBetweenPoints
WHERE DistanceT1_T2 >= 150 -- neka se traze tacke koje su na daljini vecoj od 150
)
--SELECT * FROM DistantPairs ORDER BY 1,2
, MinDist AS
(
SELECT Seq, MIN(DistanceT1_T2) AS MinDist
FROM ValidDistantPairs
GROUP BY Seq
)
--SELECT * FROM MinDist
-- Konacno, od svih tacaka koje su od zdatae tacke udaljene vise nego 150 jedinica
-- izabiramo najblizu
SELECT
V.Seq, PointA AS T1, PointB AS T2, DistanceT1_T2
FROm ValidDistantPairs AS V
JOIN MinDist AS M ON V.Seq = M.Seq AND V.DistanceT1_T2 = M.MinDist
;
/* Rezultat
Seq T1 T2 DistanceT1_T2
-------------------- ---- ---- -------------
1 A E 171
2 B F 161
3 C G 170
4 D G 156
5 E I 179
6 F I 166
7 G J 186
8 H J 174
9 I K 159
10 J O 179
(10 row(s) affected)
*/
:-)
Hvala Vujkevu na odlicnom pitanju. Ko bi rekao za sta se sve moze SQL upotrebiti.....I bez kursora.
:-)
TOP u rekurzivnom delu CTE
