如何寫好 5000 行的 SQL 代碼

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

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

本文轉(zhuǎn)載自微信公眾號「有關(guān)SQL」，作者 Lenis。轉(zhuǎn)載本文請聯(lián)系有關(guān)SQL公眾號。

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

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

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

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

但裝和拆并不是一個(gè)逆反的過程!

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

下面的領(lǐng)悟來自我實(shí)戰(zhàn)中真實(shí)的想法，趟過無數(shù)次的坑，用教訓(xùn)總結(jié)出來的幾條自認(rèn)為極有用的經(jīng)驗(yàn)。

理解業(yè)務(wù)
快速實(shí)現(xiàn)
重構(gòu)與測試
版本控制
復(fù)盤記錄

1.理解業(yè)務(wù)

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

第一個(gè)好處不言而喻，寫代碼寫出頸椎病的程序員，肯定意識到代碼的擴(kuò)展性，可以節(jié)省去醫(yī)院的時(shí)間，可以霸屏更多次王者。

舉例說說什么是代碼的擴(kuò)展性?

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

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

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

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

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

2.快速實(shí)現(xiàn)

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

但現(xiàn)實(shí)無數(shù)次打了我的臉!

越是有這種想法，越是憋得時(shí)間很長才寫那么一點(diǎn)。總覺得這里不好，那里不行，這里的變量名稱寫得不夠爽朗，那邊的 Pivot 寫得不夠優(yōu)化。結(jié)果往往是一個(gè)上午就在那里糾結(jié)，什么都沒完成。

你是不是也有類似的經(jīng)歷?不孤獨(dú)

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

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

比如實(shí)現(xiàn)下面的CRUD，你會花多少時(shí)間?

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

怎么辦?抓大放小

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

3.重構(gòu)與測試

終于，在第一版本時(shí)，你增加好了附加功能。實(shí)現(xiàn)了絕大多數(shù)的業(yè)務(wù)功能。

那這個(gè)時(shí)候，是不是可以交稿，checkin你的代碼了呢?并不是!

如果此時(shí)你就認(rèn)為高枕無憂，那會死的很慘。你會成為別人口中的“豬一樣的隊(duì)友，坑貨……”

《巴黎評論》中，村上春樹提到他的小說經(jīng)常修改 4 - 5 遍才交稿，而且編輯還需要修改。我們一遍過的 SQL 就免檢了?這個(gè)時(shí)候才考驗(yàn)?zāi)?SQL 真實(shí)功底和編碼素質(zhì)。

再檢查命名規(guī)則，變量申明，事務(wù)控制以及性能優(yōu)化。你會發(fā)現(xiàn)還有很多事情要做。

比如原本有很多次的嵌套

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

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

如果繼續(xù)放任你的項(xiàng)目里存在這樣的代碼，那項(xiàng)目很快就失控了。

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

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

這樣即使代碼不夠優(yōu)雅，別人在閱讀這塊代碼時(shí)，也不至于罵娘。

第二遍動手重構(gòu)的時(shí)候，可以考慮減少嵌套，或加上 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)

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

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