Eve-Online
哪個高秒系統對所有高秒的平均跳躍次數最少?
哪個高秒 EVE 系統對高秒最核心——或者換句話說,與其他高秒系統的平均跳躍最少?
如果可能的話,我也想知道:
- 哪個高 sec 系統對所有其他高 sec 區域最中心(區域中最近的系統)
- 哪個高 sec 系統對所有其他高 sec 區域最中心(每個區域的中心)
- 哪個高 sec 系統對所有其他系統最重要
- 哪個系統對所有其他系統最重要
資訊可以從http://community.eveonline.com/community/toolkit.asp生成。
我不精通 T-SQL,任何專家都可以向您保證。也就是說,我認為這些資訊是正確的(無論如何現在對我來說已經足夠了),但我很樂意接受更好(更易讀、更完整等)的答案。
手動排除明顯的島嶼系統(低平均和最大跳躍),這裡是前十個系統,可以快速訪問不在島嶼中的所有高 sec 系統(106 個島嶼系統):
System Avg Max ------------------- Sivala 12 30 Uedama 12 31 Inaro 12 31 Kaaputenen 12 31 Kamio 12 31 Ikao 12 32 Niarja 12 32 Madirmilire 12 33 Ashab 12 34 Algogille 13 27
下面是我執行的(我
exec dbo.usp_Eve_BFS
在另一個查詢中呼叫)。請注意,此 T-SQL 是由 T-SQL 新手從線上教程中破解而成的。它在我的機器上執行了 4:21 分鐘。CREATE PROCEDURE dbo.usp_Eve_BFS AS BEGIN SET XACT_ABORT ON BEGIN TRAN SET NOCOUNT ON; CREATE TABLE #Result (Id int NOT NULL PRIMARY KEY, Name nvarchar(100), Average int, Maximum int) INSERT INTO #Result (Id, Name) SELECT solarSystemID, solarSystemName FROM mapSolarSystems WHERE security >= 0.45 DECLARE @StartNode int DECLARE c CURSOR FOR SELECT Id FROM #Result OPEN c FETCH NEXT FROM c INTO @StartNode WHILE @@FETCH_STATUS=0 BEGIN CREATE TABLE #Discovered (Id int NOT NULL PRIMARY KEY, Parent int NULL, Depth int) INSERT INTO #Discovered (Id, parent, depth) VALUES (@StartNode, NULL, 0) WHILE @@ROWCOUNT > 0 BEGIN INSERT INTO #Discovered (Id, Parent, Depth) SELECT toSolarSystemID, MAX(fromSolarSystemID), MIN(Depth) + 1 FROM #Discovered d JOIN mapSolarSystemJumps ON d.Id = fromSolarSystemID JOIN #Result ON (mapSolarSystemJumps.toSolarSystemID = #Result.Id) WHERE toSolarSystemID NOT IN (SELECT Id From #Discovered) AND Depth = ( -- make sure the fromSolarSystemID matches the MIN(depth)! SELECT MIN(Depth) FROM #Discovered d JOIN mapSolarSystemJumps ON d.Id = fromSolarSystemID JOIN #Result ON (mapSolarSystemJumps.toSolarSystemID = #Result.Id) WHERE toSolarSystemID NOT IN (SELECT Id From #Discovered) ) GROUP BY toSolarSystemID END; WITH BacktraceCTE(Id, Depth) AS ( SELECT Id, Depth FROM #Discovered d WHERE d.Id = @StartNode UNION ALL SELECT d.Id, d.Depth FROM #Discovered d JOIN BacktraceCTE cte ON d.Parent = cte.Id ) UPDATE #Result SET Average = a.Average, Maximum = a.Maximum FROM (SELECT AVG(Depth) AS Average, MAX(Depth) AS Maximum FROM BacktraceCTE) AS a WHERE #Result.Id = @StartNode DROP TABLE #Discovered FETCH NEXT FROM c INTO @StartNode END SELECT * FROM #Result ORDER BY Average, Maximum; DROP TABLE #Result COMMIT TRAN RETURN 0 END