ITPUB SQL大賽第三期（二）

SQL大賽第三期解法的最終答案。

第三期題目參考：http://www.itpub.net/thread-1408182-1-1.html

版主newkid點評參考：http://www.itpub.net/thread-1415335-1-1.html

ITPUB SQL大賽第三期：http://yangtingkun.itpub.net/post/468/516164

由於時間的關係，一直沒有時間去檢查第三期到底哪裡出現了錯誤。

檢查檢視了一下程式碼，發現問題出在一個偷懶的想法上。

由於上一篇文章最後貼出的程式碼就是存在問題的程式碼，這裡就不重複貼出了，下面給出的是修改之後的程式碼：

SQL> WITH ROUTE_D AS
  2  (
  3   SELECT CITY1 R, CITY2 T, DISTANCE DIS
  4   FROM ROUTES
  5   UNION ALL
  6   SELECT CITY2, CITY1, DISTANCE
  7   FROM ROUTES
  8  ),
  9  ROUTE_ALL_D (C1, C2, LINES, DISTANCE) AS 
 10  (
 11   SELECT R, T, CAST('"' || R || '"' || T || '"' AS VARCHAR2(4000)), DIS
 12   FROM ROUTE_D
 13   WHERE R != (SELECT MAX(GREATEST(CITY1, CITY2)) FROM ROUTES)
 14   UNION ALL
 15   SELECT A.C1, T, LINES || T || '"', DIS + DISTANCE
 16   FROM ROUTE_D R, ROUTE_ALL_D A
 17   WHERE A.C2 = R.R
 18   AND INSTR(LINES, '"' || T || '"', 1, 1) = 0
 19   AND DISTANCE + DIS <=
 20    NVL
 21    (
 22     (
 23      SELECT DISTANCE
 24      FROM ROUTES RS
 25      WHERE (A.C1 = RS.CITY1
 26       AND R.T = RS.CITY2)
 27      OR (A.C1 = RS.CITY2
 28       AND R.T = RS.CITY1)
 29     ),
 30     9.9E38
 31    )
 32  ),
 33  RESULT_HALF AS
 34  (
 35   SELECT C1 R, C2 T, MIN(DISTANCE) DIS
 36   FROM ROUTE_ALL_D
 37   WHERE C1 < C2
 38   GROUP BY C1, C2
 39  ),
 40  RESULT_ALL AS
 41  (
 42   SELECT R, T, DIS
 43   FROM RESULT_HALF
 44   UNION ALL
 45   SELECT T, R, DIS
 46   FROM RESULT_HALF
 47  ),
 48  RESULT AS
 49  (
 50   SELECT R, T,
 51    DIS * 2 * C.MEMBERS COST,
 52    SUM(DIS * 2 * C.MEMBERS) OVER(PARTITION BY R) COST_CITY
 53   FROM RESULT_ALL R, CITIES C
 54   WHERE R.T = C.CITY_NAME(+)
 55  )
 56  SELECT R, NVL(T, 'TOTAL') T, NVL(SUM(COST), 0) COST
 57  FROM
 58  (
 59   SELECT R, T, COST, RANK() OVER(ORDER BY COST_CITY) RN
 60   FROM RESULT
 61  )
 62  WHERE RN = 1
 63  GROUP BY GROUPING SETS ((R, T), R)
 64  ORDER BY R, DECODE(T, 'TOTAL', CHR(0), T)
 65  ;

R          T                COST
---------- ---------- ----------
D          TOTAL           68356
D          A                3200
D          B                3224
D          C                3634
D          E                7300
D          F                7598
D          G                3840
D          H               14580
D          I                4400
D          J               12352
D          K                8228
D          L                   0

已選擇12行。

和SQL大賽中提交的程式碼的唯一區別是第13行，這裡是“WHERE R != (SELECT MAX(GREATEST(CITY1, CITY2)) FROM ROUTES)”，而SQL大賽中出現問題的語句是“WHERE R != (SELECT MAX(CITY_NAME) FROM CITIES)”。

這裡的本意是指，由於A到B的距離和B到A的距離是相等的，所以在獲取全路徑的時候，沒有必要將所有的CITY都作為起點，可以有一個城市不作為起點進行遞迴，獲取到其他城市的路徑，因為這個城市所有的路徑都可以從其他城市的結果中獲取。

而這個城市取最大的城市，SQL相對更容易實現，因為隨後的RESULT_HALF會取所有C1 < C2的結果，然後調換起點、終點來得到全路徑。

舉個例子，對於A、B、C、D四個城市，獲取全路徑後，結果為AB、AC、AD、BA、BC、BD、CA、CB、CD和DA、DB、DC。而D作為起點的所有情況，都可以從其他城市為起點到D為終點的情況來獲取。

這個最佳化的思路並沒有問題，問題在於開始本打算用ROUTES表中最大的列來進行過濾，但是發現CITY資訊存在於ROUTES表中的CITY1和CITY2兩個列中，要不然需要使用UNION ALL要不然需要上面這樣使用GREATEST方式，這顯得比較麻煩，由於CITIES表中所有城市存在一個列中，於是偷懶使用了這張表。但是忘記了這張表中的資料可能並不完全，比如預設資料的例子中就缺少了L。可惜的是，這個錯誤並沒有導致上面的查詢結果發生改變，因此這個錯誤在測試的時候並沒有檢查出來。