如何寫好5000行的SQL程式碼

uu365發表於2021-05-31

  上千行的 SQL 程式碼常見,且永不過時!

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

  如何去閱讀和拆解一個上千行的 SQL 儲存過程,有四大步驟 :理解程式碼,分拆程式碼,改寫程式碼和儲存程式碼。拆過無數的程式碼,從上千行縮減到 2 成,也組裝過無數的程式碼,從上百行塞成了上千行,業務所需。見過最長的 SQL 程式碼超 5000 行,已簡無所簡,那就實事求是了。人有分分合合,有生命力的程式碼也一樣。

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

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

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

  理解業務

  快速實現

  重構與測試

  版本控制

  覆盤記錄

  1、理解業務:

  你肯定不會去寫沒有業務邏輯的程式碼。充分理解業務邏輯對你有兩個好處:一是寫出可執行的並且可擴充套件的程式碼;二是主動了解業務將有利於職業生涯升級。

  第一個好處不言而喻,寫程式碼寫出頸椎病的程式設計師,肯定意識到程式碼的擴充套件性,可以節省去醫院的時間,可以霸屏更多次王者。

  舉例說說什麼是程式碼的擴充套件性?

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

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

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

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

  話說到這份上,大家可以明白,我們寫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

  再好比,有很多的關鍵步驟,其實我們可以拆分開來,直到一個儲存過程完成一個功能,這樣既完成程式碼簡化,還可以提供複用的介面,還可以使得組裡的小夥伴協同作戰。一舉三得,這樣的事情才值得花時間。

  最後,將所有的測試分支跑完測試,提交!

  4、版本控制:

  如果你的團隊沒有 git, SVN, TFS 這些 Source Code Version Control, 趕緊上一個。沒有自動化部署工具,自己想辦法整一個。都 2020 年了,別偷懶吧。

  為什麼一定要版本控制呢?這,應該在剛入門程式設計的時候就知道。

  好比你覺得越發討厭現在的自己,或是太胖,或是太文弱,或是太沒文化,好想要一臺時光穿梭機,回到15,16歲,重新再來。你會告訴自己多吃蔬菜和水果,堅持每天鍛鍊,堅持每天看書寫字讀報。

  雖然我們不能實現穿越,但程式碼可以。使用上述提到的軟體,就可以幫助我們回退到想要重新開始的那個版本,修正程式碼。

  5、覆盤記錄

  做好上面4步,對公司專案是有個交代了。但做這一步,才是對自己有交代。

  就好比剛才重構的時候,提到 CTE, UNPIVOT , 程式碼簡化的策略,可能因為一時靈感或責任心爆棚,反正你當時想到了,但你不及時記錄下來,可能很久過後就忘記你曾做過這麼神奇的操作。

  所以,等你費盡心思寫完很長的程式碼,一定要透過覆盤記錄下來,放到你的 blog, github, 等你以後碰到類似情況,卻想不出來如何解,你可以隨時拿出來用上。


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69997029/viewspace-2774724/,如需轉載,請註明出處,否則將追究法律責任。

相關文章