SQL——以面向集合的思維方式來思考

譯文來源：圖靈社群郭志敏

為了以有趣的方式更好地幫助你形成面向集合的思維方式，我將給出自己最喜歡的遊戲之一——集合。你可以線上玩這個遊戲，網址是www.setgame.com/puzzle/set.htm，每天都會貼出一個新的集合謎題。集合遊戲是一個每張卡片上有4個特徵的謎題：顏色、符號、底紋以及符號的個數。顏色有紅、綠和紫色。符號有花體、方塊以及橢圓。底紋可以是實的、斑點的及外輪廓線。至於符號個數，每張卡上可能有一個、兩個或三個符號。要成為集合只有一條規則：一個集合只需要3張卡上的每一種單獨的特徵要麼都一樣要麼全不相同。因此，一個有效的集合的例子就是3張卡上有相同的符號（例如方塊），3張卡上都有兩個符號，3張卡的顏色各不相同，並且3張卡都是實心圖案。所有使得3張卡上具有相同特徵或者完全不同特徵的組合都能構成集合。遊戲的目標就是在12張卡中尋找集合。當找到一個集合後，這3張卡片就將被去掉，並加入3張新卡片。在線上版本中，12張卡片總會有6個集合，你的目標就是要找出所有這6個集合。

圖4-1給出了12張卡片，從中你應該可以找出6個集合。每張卡片左上角的字母表示顏色（R=紅色，G=綠色，P=紫色）。試試吧。

答案在本章的最後（可別作弊哦！）。我首先給出第一個集合：第1行第2列（實心綠色花體），第3行第2列（實心紅色花體）和第3行第4列（實心紫色花體）。這個遊戲迫使你按集合來思考，除此以外別無他法。如果你覺得找出集合很困難，我打賭你在寫SQL語句的時候就會覺得以面向集合的方式來思考更困難。SQL語句的書寫工作與這個遊戲是具有同樣的前提的（必須要有面向集合的思維方式！），只不過是另外一個不同的遊戲罷了。既然你已經在面向集合的思考方面熱過身了，讓我們來看看從程式導向的思維方式轉變到面向集合的思維方式的幾種方法。

從程式導向轉變為基於集合的思維方式

你首先需要做的是停止那些一次處理一行資料的過程化步驟思維。如果你一次只想處理一行，實現你的想法將會使用短語如“for each row do x”或者 “while value is y do x”。試著把思路轉移到使用類似於“for all”的短語上來。有關於此的一個簡單的例子就是加數字。當你按過程化來考慮的時候，你就會想把一行的數值與另一行的數值加起來直到把所有行加到一起。對所有行求和的思維與此不同。正如我所說的，這是個非常簡單的例子，但類似的思維方式的轉變同樣適用於更復雜的不是那麼明顯的情況下。

例如，如果我讓你生成一個所有在公司裡每個工作崗位上幹了同樣年數的員工列表，你會怎麼做？如果你按照過程化的思維方式來進行，你可能需要去檢視每個工作崗位，計算出在這個崗位上的工作年限，然後與在其他各個崗位的工作年限比較。如果年數不匹配，那麼你就不會把這個員工放到結果列表中。這種方法將會通過如下的一個自聯結的查詢來進行：

　　相反，如果你使用面向集合的觀點來看待這個問題，你就會寫出對錶只進行一次訪問的查詢，按照員工進行分組，然後篩選出那些在某個崗位上工作的最短年數與某個崗位上工作的最長年數相一致的員工。

程式碼清單4-1分別列出了這兩種選擇的執行過程。你可以看到基於集合的方法使用了較少的邏輯讀取並擁有更簡潔的計劃。

 

　　關鍵是要開始以完成後的結果的形式（而不是以處理步驟的形式）來思考。要找集合的特徵而不是單獨的步驟或行為。在基於集合的思維方式中，所有事物都以應用於集合的篩選條件或約束所定義的狀態存在。你不再按照過程步驟來思考而是要按照集合的狀態來思考。圖4-2給出了處理步驟圖與巢狀集合圖之間的一個比較用來說明我的觀點。

　　處理流程圖表明結果集（A）是通過一系列以其他步驟為基礎的處理步驟來產生的最終答案。B通過遍歷C和D得出，然後A通過遍歷B和E得出。但是，巢狀集合圖中將A看做是不同集合的組合的結果。

另一種常見的但卻是錯誤的思考方式就是將表看做是排過序的行的集合。想想你所看到的典型的表的內容。它們是在一個表格或者類似於工作表的檢視中展示出來的。但是，一張表代表一個集合，集合是無序的。通過表明一定順序的方法來展示表可能會引起混淆。回憶一下在第2章中ORDER BY子句是在一個SQL語句執行的最後來實現的。SQL是基於集合理論的，正因為集合中的行沒有預先確定的順序，排序就必須在符合查詢條件的資料行都被從集合中抽取出來之後再單獨進行。圖4-3給出了一種更恰當的方法來說明表中的內容是無序的。

　　看上去區分這些你在思考問題方式上的細小差別並不是那麼重要，但是這些細小的轉變是正確理解SQL的基礎。讓我們來看一個例子，通過程式導向的思維方式和基於集合的思維方式分別來寫一個SQL語句，以幫助你弄清楚二者之間的區別。

程式導向vs.基於集合的思維方式：一個例子

在這個例子中，任務是要計算出一個顧客在各個訂單之間的平均天數。程式碼清單4-2給出了通過程式導向的思維方式的實現方法。為了讓例子的輸出較短，我將只計算一個顧客的，但是由此可以很容易地轉變為計算所有顧客的。

程式碼清單4-2　程式導向的思維方式

　　這看上去相當優雅，不是嗎？在這個例子中，我依次執行了一系列查詢來展示我是如何思考的，並按照逐步進行的過程方法來書寫查詢語句。如果你對分析函式LAG的使用方法還不是很熟悉，不必擔心，分析函式將在第8章進行講解。簡單來說，我所做的事情就是按照orderdate的順序讀取102號顧客的每一行訂單資訊，然後使用LAG函式，回過頭再看前一行的訂單資料以獲得該行的orderdate。當得到這兩個order_date（當前訂單行的日期以及前一行訂單的日期）以後，利用這兩個日期相減得出中間相差的天數就非常簡單了。最後，我使用求平均值聚合函式來得到最終的答案。

你可能會指出這個查詢是按照非常過程化的方式建立起來的。理解這種方式最好的辦法就是依次來看幾個不同的查詢以展示如何建立最終結果集的。在這個過程中我可以看到相關詳細資訊。當以基於集合的思維方式進行思考的時候，你會發現並不需要去關心每一個單獨的元素。程式碼清單4-3給出了一個按照基於集合的思維方式來寫的查詢例子。

　　這樣怎麼樣？我根本不需要任何花哨的技巧來解決這個問題。我用來計算訂單之間的平均天數所要做的事情就是計算出第一筆和最後一筆訂單之間的天數以及總的訂單數。我不需要一步一步地來考慮問題，正如我也不會寫一個一行一行讀取資料然後計算出結果的程式。我所需要的就是把我考慮問題的思維方式轉變到將集合資料作為一個整體來考慮。

我並不是完全無視過程化方法。可能有的時候你不得不採用這樣的方法來完成工作。然而，我想鼓勵你進行思維方式的轉變：首先尋找基於集合的方式，只有在需要的時候才採用更大程度的過程化方法。通過這樣做，你可能會發現自己可以得到更簡單、直接的，通常效能也更好的解決方案。