如何寫好 5000 行的 SQL 代碼

作者： Lenis 2021-10-13 08:16:14

經歷了大大小小的 MIS 系統，小到幾人用的協作系統，幾十人用的 OA 系統，到上千人用的 MES/ERP 系統，再到百萬人用的電商系統，存儲過程的影子在半個世紀(20世紀70年代末開始)以來從未淡出它的戰場。

本文轉載自微信公眾號「有關SQL」，作者 Lenis。轉載本文請聯系有關SQL公眾號。

上千行的 SQL 代碼常見，且永不過時!

經歷了大大小小的 MIS 系統，小到幾人用的協作系統，幾十人用的 OA 系統，到上千人用的 MES/ERP 系統，再到百萬人用的電商系統，存儲過程的影子在半個世紀(20世紀70年代末開始)以來從未淡出它的戰場。我們幾個 SQL 老玩家經常自吹， SQL 是半衰期最長的編程語言。玩會它不用擔心失業。

我之前寫過如何去閱讀和拆解一個上千行的 SQL 存儲過程，詳情可見以下兩篇文章：

如何提高閱讀 SQL 源代碼的快感
如何寫好上千行的 SQL 存儲過程(附代碼規范)

這兩文中提到了四大步驟：理解代碼，分拆代碼，改寫代碼和保存代碼。拆過無數的代碼，從上千行縮減到 2 成，也組裝過無數的代碼，從上百行塞成了上千行，業務所需。見過最長的 SQL 代碼超 5000 行，已簡無所簡，那就實事求是了。人有分分合合，有生命力的代碼也一樣。

但裝和拆并不是一個逆反的過程!

就像我們能讀懂村上春樹的小說《且聽風吟》、《刺殺騎士團長》一樣，但我們無法寫出來或者說無法寫的那么好。當然那畢竟是村上賴以為生的技能，老人家寫了30多年的小說，我們可能一部都沒完整的寫完過，沒法兒比。既然如此，在我們賴以為生的SQL陣營，這門吃飯的技能一定是要好好磨練的。

下面的領悟來自我實戰中真實的想法，趟過無數次的坑，用教訓總結出來的幾條自認為極有用的經驗。

理解業務
快速實現
重構與測試
版本控制
復盤記錄

1.理解業務

你肯定不會去寫沒有業務邏輯的代碼。充分理解業務邏輯對你有兩個好處：一是寫出可執行的并且可擴展的代碼;二是主動了解業務將有利于職業生涯升級。

第一個好處不言而喻，寫代碼寫出頸椎病的程序員，肯定意識到代碼的擴展性，可以節省去醫院的時間，可以霸屏更多次王者。

舉例說說什么是代碼的擴展性?

比如產品的價格。電商時代，產品的價格擁有明顯的擴展屬性。也就是說，今天是這個價，明天又是另一個價。電商時代給雙11，雙12附上了商業促銷標簽，對產品價格提出了高要求。此時，你去設定一個商品價格，你會怎么設計?是在原來的價格基礎上直接更新，還是另起一列，承載新價格?這類價格設計，會直接影響對電商促銷活動的成果分析。

如果我們直接更新價格，就會失去與歷史銷售對比的便捷，如果不隨單記錄單價，更是丟失了與歷史的對比。從設計角度，這很失敗，失去了靈活性，擴展性。這樣的設計，每次更換價格，都需要大量更新產品價格表和銷售歷史表，對已有的商業活動造成干擾。更好的辦法是，增加價格的有效使用日期。比如在這段時間內這個價格生效，在促銷階段又是另一個價格。并采用視圖(view)的方式去提供產品數據，而不是直接從原表直接讀取數據，失去中間業務的緩沖。

對這類業務的理解，kimball 最有說服力，他的《Dimensional modeling》(《維度建模》)總結了幾十個行業的通用設計模型，堪稱數據模型界的設計模式。

第二個好處可不是人人都能意識到了。雖然 SQL 是擁有最長職業生涯的編程語言，比如與其一起出現的 VFP 大概 90 后聞所未聞，但顯然沒人一輩子愿意鼓搗 CRUD 。玩吃雞的同學把你的 iPhone 13 放下，家里有礦沒說你。理解業務使你成為整個應用生態中不可缺少的一環。信息化的目的不是寫代碼，最終落腳點還是利潤。我覺得二爺(邱岳)肯定能贊同我這話。

話說到這份上，大家可以明白，我們寫SQL就是在通曉一個行業的數據流，資金流，做好大盤的監控。那么還有誰比我們更了解一個企業的真實經營情況呢，沒有，完全沒有。前提是，你要做對，要通曉。當你還只是把自己定位成一個碼工，那真是大材小用。追逐SQL的技巧可以，但最終還是商業會支持你走的更遠。你永遠不可能20歲，30歲，總有一天你會被希望擁有開拓事業的本領，擁有可以指導后生的經驗。到那時，技術經驗就很泛泛了。甚至有可能技能上完全不如年輕人。唯一能給你樹立權威的，還在于你在其他方向上能夠走的多遠。

2.快速實現

很多朋友(包括我)有時候碰到需求，苦思冥想，要的是一口氣把 SQL 從頭到尾完整的，暢快淋漓的寫出來。“Wow” 和漂亮的回車，就是憋著這口氣的期待。

但現實無數次打了我的臉!

越是有這種想法，越是憋得時間很長才寫那么一點。總覺得這里不好，那里不行，這里的變量名稱寫得不夠爽朗，那邊的 Pivot 寫得不夠優化。結果往往是一個上午就在那里糾結，什么都沒完成。

你是不是也有類似的經歷?不孤獨

村上春樹、海明威、博爾赫斯，從來寫小說都是第一遍爽快的寫下去了，一旦寫得卡殼了怎么辦，束之高閣，明兒繼續。我這里想說的策略，大家都可以猜得到了。先把業務實現了再說，命名規則，變量申明，事務控制以及性能優化，統統先放起來。寫好 CRUD 交上第一稿，存檔，Over!

作家們要是等靈感來了再動筆寫，我們哪能看到那么多有趣的故事。同樣，我們寫代碼哪能等到全盤都考慮好了再動手呢。想到一個數據流，用到哪些表，直接就可以寫了。等著等著就慌了，寫著寫著思路就來了。

比如實現下面的CRUD，你會花多少時間?

如果一開始，盯著這圖你開始考慮日志怎么記，檢查用戶是否單點登錄，用戶是否用促銷券，訂單怎么撤回，要不要控制并發，那么無疑是給自己加了很多戲，很多無形的壓力使得你自己無法動手做，越想越宏大，越覺得自己做不來。在你迷茫同時，如果有個會議，有個熱鬧的新聞，一開小差，再想回到你的宏偉藍圖上來，就難了。

怎么辦?抓大放小

此時，你要做的第一件事，就是快速去實現這么幾個關鍵點的CRUD代碼。比如購物車的增刪改查，用戶登錄，填寫訂單信息，還有結單。等到這一系列操作都完成，你對整個業務流，數據流都熟悉了，第二遍再去增加附加的功能。

3.重構與測試

終于，在第一版本時，你增加好了附加功能。實現了絕大多數的業務功能。

那這個時候，是不是可以交稿，checkin你的代碼了呢?并不是!

如果此時你就認為高枕無憂，那會死的很慘。你會成為別人口中的“豬一樣的隊友，坑貨……”

《巴黎評論》中，村上春樹提到他的小說經常修改 4 - 5 遍才交稿，而且編輯還需要修改。我們一遍過的 SQL 就免檢了?這個時候才考驗你 SQL 真實功底和編碼素質。

再檢查命名規則，變量申明，事務控制以及性能優化。你會發現還有很多事情要做。

比如原本有很多次的嵌套

我知道很多朋友會這么寫：

SELECT * FROM  ( SELECT *  FROM (SELECT * FROM BASE ) T1 )T2

如果繼續放任你的項目里存在這樣的代碼，那項目很快就失控了。

至少，第一遍走讀代碼，我們需要完成格式上的美化：

SELECT * FROM  
    ( SELECT *  
      FROM ( 
        SELECT * FROM BASE 
      ) T1 
     )T2

這樣即使代碼不夠優雅，別人在閱讀這塊代碼時，也不至于罵娘。

第二遍動手重構的時候，可以考慮減少嵌套，或加上 CTE 封裝嵌套：

; WITH BASE_TABLE AS ( SELECT * FROM BASE ) 
 
SELECT * FROM BASE_TABLE

再比如，unpivot 之后的聚合：

一開始我們能把 unpivot 寫出來就很好了,然后嵌套一層做聚合，如下：

SELECT Convert(Date,OrderDate) as OrderDate 
    , Sum(Amount) AS Amount  
FROM ( 
    SELECT  
        OrderDate, 
        Unp.Amount AS Amount 
    FROM FctOrderAmounts 
    UNPIVOT( Amount for Type in(Shipment,UnitCost) ) Unp  
) RSL  
GROUP BY Convert(Date,OrderDate)

這么一看特別清晰，但是信息量大，結構復雜，加上中間可能有其他字段或者Join,變得復雜，那我們至少還需再一次簡化：

SELECT  
    OrderDate, 
    Sum(Unp.Amount) AS Amount 
FROM FctOrderAmounts 
UNPIVOT( Amount for Type in(Shipment,UnitCost) ) Unp  
Group by Unp.Amount