SQL的全稱(chēng)是Structured Query Language（結構化查詢(xún)語(yǔ)言），是一種古老而簡(jiǎn)潔的程序設計語(yǔ)言?？此破狡綗o(wú)奇，一直被各種吐槽，但卻有著(zhù)眾多語(yǔ)言所難得的漫長(cháng)壽命，并展現出極好的拓展性，在不同時(shí)期衍生出不同的子語(yǔ)言。筆者作為騰訊TDW體系下的SQL現役運動(dòng)員，對日常工作中常用的基礎知識和展開(kāi)的業(yè)務(wù)實(shí)踐予以了總結，可供讀者參考。
結構化查詢(xún)語(yǔ)言，顧名思義，它的基礎在于結構化的數據庫表，最主要的應用場(chǎng)景在于數據查詢(xún)，雖然SQL也可以像其它語(yǔ)言一樣有一些高級的寫(xiě)法，但它的主戰場(chǎng)并不在此，仍要回歸到對數據庫表的操作和處理中。以下分為基礎知識篇和業(yè)務(wù)實(shí)踐篇展開(kāi)介紹，其中基礎知識篇盤(pán)點(diǎn)了一些常用的技能點(diǎn)，業(yè)務(wù)實(shí)踐篇則總結了幾點(diǎn)日常工作里的思考。
第一部分 基礎知識篇
圍繞著(zhù)數據庫表，可以展開(kāi)許多的主題工作，有些是比較專(zhuān)業(yè)性的領(lǐng)域，如事務(wù)處理和權限管控等，這些更多是面向底層的技術(shù)基礎，部分屬于DBA的工作范疇。對于使用SQL的很大部分用戶(hù)群體來(lái)說(shuō)，則集中于對數據庫表的增刪查改，聚合匯總里，這些是面向業(yè)務(wù)的數據工作。針對這一塊的內容，繼續將其細分到不同的子場(chǎng)景里，逐一展開(kāi)介紹。
1.庫表基本操作
庫表預覽： SQL最基礎和最核心的兩個(gè)對象，便是數據庫和數據表，基于一個(gè)業(yè)務(wù)場(chǎng)景，可以有N個(gè)數據庫，在一個(gè)數據庫里面，又可以有N張數據表。數據庫的連接與切換，數據表的創(chuàng )建與刪除，是使用SQL進(jìn)行庫表預覽的基本操作。 這些基本操作，可以通過(guò)前端的可視化界面進(jìn)行，也可以從后臺直連數據庫展開(kāi)，需由使用者所擁有的權限級別來(lái)選擇。
數據增刪： 除了一些常規的每日運行的計算任務(wù)外，很多時(shí)候我們只是單純地想對一張表進(jìn)行處理，比如插入幾條數據，更新某個(gè)字段值，或者剔除幾條數據。這些操作往往是單次的，局部的，目的清晰，所以掌握幾個(gè)關(guān)鍵字就可以實(shí)現，如INSERT/UPDATE/DELETE等。
視圖應用： 視圖的引入，相當于在統計邏輯和實(shí)際庫表之間提供了一種折中的方案。完成這個(gè)功能，邏輯上是必須有這么幾道工序的，但又不想在每一道工序里都落地一張實(shí)際的數據表，顯得繁瑣而臃腫，那就引入視圖吧，把這些中間的工序用視圖的形式去實(shí)現和替代。
關(guān)鍵字： 其實(shí)SQL真的是一門(mén)很簡(jiǎn)潔的語(yǔ)言，市面上也不會(huì )有大本的書(shū)籍專(zhuān)門(mén)講述SQL的書(shū)寫(xiě)方式，因為相對于其它語(yǔ)言來(lái)說(shuō)，SQL歸根到底，只是圍繞著(zhù)幾個(gè)關(guān)鍵字的一些基礎語(yǔ)句而已。只要把這幾個(gè)關(guān)鍵字掌握了，SQL的大部分內容其實(shí)就已經(jīng)展開(kāi)了。
2.數據查詢(xún)語(yǔ)句
SQL作為面向數據庫表的基礎語(yǔ)言，用戶(hù)群體具有多樣性，從技術(shù)底層往業(yè)務(wù)層走，往往會(huì )有DBA，數據開(kāi)發(fā)，數據分析，產(chǎn)品經(jīng)理等這些用戶(hù)角色。不同用戶(hù)群體對SQL的側重點(diǎn)是有差異的，但無(wú)論是哪一個(gè)群體，基本都繞不開(kāi)數據查詢(xún)語(yǔ)句，是一塊必要內容。
簡(jiǎn)單查詢(xún)： 能寫(xiě)一個(gè)簡(jiǎn)單查詢(xún)語(yǔ)句，其實(shí)就已展開(kāi)了和數據庫表的對話(huà)過(guò)程。不管是哪種SQL的拓展語(yǔ)言，簡(jiǎn)單查詢(xún)里的語(yǔ)法基本都還是一致的。比如用*代表全量查詢(xún)，用distinct去重，用top和limit對數據條數做基本限制，以及用as對原表字段名進(jìn)行替換更新等。
過(guò)濾查詢(xún)：在簡(jiǎn)單查詢(xún)的基礎上，添加一些約束條件，也就是過(guò)濾查詢(xún)。比如你可以用關(guān)鍵字where查看其中某天的數據，用between或者in來(lái)限制一個(gè)范圍，用like或者relike來(lái)做正則匹配，也可以用and或者or這兩個(gè)通配符對這些約束條件進(jìn)行排列組合。
排序查詢(xún)： 排序查詢(xún)可以細分為兩個(gè)場(chǎng)景，一個(gè)是在查詢(xún)內部的排序，即根據某個(gè)字段的屬性值進(jìn)行表內部分區，對分區進(jìn)行排序查詢(xún)后輸出，可以用row_number的形式來(lái)實(shí)現；另一個(gè)是把整個(gè)查詢(xún)當做一個(gè)整體，對結果表進(jìn)行排序查詢(xún)后輸出，用order by來(lái)實(shí)現即可。
3.數據聚合與連接
前面講數據查詢(xún)語(yǔ)句，不管怎么查詢(xún)，其實(shí)并不影響原生的表結構，即原來(lái)的表是按照什么邏輯寫(xiě)的數據，查詢(xún)結果里的數據也是基于這種邏輯，只是篩選了局部數據而已。但數據聚合與連接就不一樣了，聚合會(huì )在縱向上改變原生表結構，連接則在橫向上拓展了表結構。
數據聚合： 要對一張表做數據聚合，其實(shí)理解了兩個(gè)概念即可，維度和指標。維度是你要基于哪些字段來(lái)做聚合，指標是在這個(gè)維度之上，你想用什么匯總函數生成哪些指標。數據聚合的關(guān)鍵字是group by，維度里的屬性值仍來(lái)自于原生表，指標則是新生成的匯總值。
數據連接： 對兩張表或者N張表做連接，是SQL里面非常重要的一個(gè)內容，也是最容易埋坑的一個(gè)坑點(diǎn)。盡管數據連接只涉及四種方式，七個(gè)語(yǔ)法，但其仍然是絕大部分SQL腳本的核心內容。選擇合適的可靠的數據連接方式，應該是一個(gè)SQL運動(dòng)員的基本功了。
4.函數應用
函數庫，其實(shí)就像是一個(gè)數據處理與分析的百寶箱，收藏著(zhù)各種場(chǎng)景下需要用到的車(chē)輪子。對函數庫的熟悉和掌握，可以較好地提升工作效率，也讓計算腳本顯得輕量而簡(jiǎn)潔。畢竟站在通用的函數的肩膀上，很多統計邏輯是可以一步到位的，不需要沉迷于山重水復的自主構造里。以下參考TDW的函數庫分類(lèi)，將日常所用的函數細分為幾個(gè)子類(lèi)別。
4.1數學(xué)函數： SQL里的數學(xué)函數主要和數值處理有關(guān)，有取值函數和變換函數等。取值函數包括round四舍五入，abs取絕對值，ceil向上取整等，主要用于對具體數值的細節調整；變換函數則會(huì )改變該字段的數據分布形態(tài)，如正弦sin，余弦cos，或者開(kāi)根號sqrt等。
4.2聚合函數： 在數據聚合中，選擇了具體字段作為聚合維度后，之后便是應用各種聚合函數得到匯總值的過(guò)程。其中有簡(jiǎn)單聚合函數如count計數，sum求和，avg求平均，也可以基于分布特征，max/min取極值，std取標準差，variance取方差，另外若在聚合過(guò)程中涉及分區處理的話(huà)，也有rank，first/last_value，row_number等函數可以應用。
4.3時(shí)間和日期函數： 對時(shí)間數據的處理，同樣也是SQL里的一個(gè)重要課題，主要細分為時(shí)間的加減，取值和轉換這么三類(lèi)。其中時(shí)間加減里，又涉及不同的時(shí)間維度，比如按日維度有date_diff，date_add，date_sub等，按月維度有month_between，add_months等。時(shí)間取值函數則是在一個(gè)詳細的時(shí)間戳里，取出自己想要的部分，如year，month，day，hour等。時(shí)間轉換函數則是時(shí)間形式的切換，如日期格式，格林尼治時(shí)間戳格式等。
4.4文本處理： 數據類(lèi)型可以粗糙地分為數值數據和文本數據，對于文本數據的處理，也有很多對應的函數。其中有一些簡(jiǎn)單取值函數，如通過(guò)length和size獲得字段長(cháng)度和數組大小，通過(guò)upper和lower可以切換大小寫(xiě)；字符串的切割與拼接，由淺入深有split，substr，concat，wm_concat等；最后正則表達式也是文本處理中一個(gè)特別重要的模塊。
4.5其它函數： 除了以上所盤(pán)點(diǎn)的一些通用函數外，其實(shí)在日常工作中會(huì )有很多垂直的業(yè)務(wù)場(chǎng)景，在這些特定場(chǎng)景下也有一些特定的函數邏輯。比如涉及數組結構拆分與重構時(shí)，可以應用later view函數；涉及字段編碼時(shí)，也有加密與解析函數；除此之外，還有邏輯函數，轉換函數等多種特殊函數，在特定的場(chǎng)景下，這些函數其實(shí)也是必要的。
5.具體開(kāi)發(fā)環(huán)境的注意點(diǎn)
和其它眾多語(yǔ)言一樣，SQL的編寫(xiě)也不能脫離其自身的開(kāi)發(fā)環(huán)境，不同的數據庫形態(tài)，不同的IDE，都會(huì )有一些差異點(diǎn)和新特性。同樣的一段腳本，在A(yíng)環(huán)境下可能跑的疾速如飛，在B環(huán)境下卻可能滿(mǎn)屏報錯，可以拿出來(lái)討論的，往往只是一些通用的邏輯和思考。除此之外，具體開(kāi)發(fā)環(huán)境里的注意事項，一些細節的加速點(diǎn)，則是要在具體環(huán)境里去發(fā)現和探索。
第二部分 業(yè)務(wù)實(shí)踐篇
一種語(yǔ)言，一個(gè)函數庫，就像是廚房里的各種廚具，它們可能或優(yōu)良或劣質(zhì)，但本質(zhì)上還是一些標準化的組件?；谶@些公共的組件，如何去烹制自己的美食，以及在烹制過(guò)程中的心得和思考，則屬于業(yè)務(wù)實(shí)踐的篇章。在業(yè)務(wù)實(shí)踐的過(guò)程中，不管怎樣去規范化，標準化，每個(gè)人輸出的腳本內容，難免還是要帶上私人的特質(zhì)的，這同樣也是一個(gè)需要修習的部分。
以下通過(guò)三個(gè)問(wèn)題點(diǎn)，來(lái)引出筆者在實(shí)際工作中的一些反思。其中如何盡量地少給未來(lái)挖坑，介紹了一些反面的案例，這些反面的細節在積累之后，容易引起整個(gè)系統的不穩定性；如何健康地做數據規劃，則是從一個(gè)創(chuàng )建者的身份，展開(kāi)幾點(diǎn)數據規劃的思考；但不管做了多么縝密和豐富的準備，隨著(zhù)時(shí)間推移，總還是會(huì )出現變化的，所以在破舊與立新之間要找到平衡點(diǎn)。這些反思是基于工作實(shí)踐的層面，難免會(huì )有幼稚和紕漏之處，還請讀者輕拍。
1.如何盡量地少給未來(lái)挖坑
不要起一些奇奇怪怪的名字： SQL里的數據庫表，就像是其它語(yǔ)言里的對象，往往是數量極大的，并在時(shí)間的推進(jìn)，業(yè)務(wù)的發(fā)展中，基數會(huì )持續放大。所以對于數據庫名，數據表名，字段名，尤其是一些主鍵，索引的命名，務(wù)必要有一套相對統一的規范。缺乏規范的約束時(shí)，你無(wú)法想象人的想象力會(huì )多么發(fā)散，這些奇奇怪怪的名字，最終會(huì )把人深深地困住的。
不要并行維護多個(gè)版本的數據： 因為業(yè)務(wù)的拓展，數據背后的口徑可能有所變更，基于舊有的數據報表，簡(jiǎn)單修改后出一份新數據，是一種成本較低的實(shí)現方式。但最好不要并行維護多個(gè)版本的數據，當版本超過(guò)3個(gè)的時(shí)候，維護的成本是直線(xiàn)拉升的。所以當要做數據變更時(shí)，一方面可以降低變更的頻率，另一方面盡量在原有報表里修改，并替換掉原有口徑。
不要在單個(gè)腳本里寫(xiě)過(guò)多內容： 統計邏輯的實(shí)現，就像是傳統工業(yè)里的不同工序，這個(gè)過(guò)程里存在兩種極端。一種是把一個(gè)邏輯在橫向/縱向細分為太多的工序，部署過(guò)多計算任務(wù)，形成很大冗余；另一種是完全打包在一個(gè)大腳本里，這種情況也不利于問(wèn)題定位和中間數據處理。所以不要在單個(gè)腳本里寫(xiě)過(guò)多內容，可以將它拆分進(jìn)最優(yōu)數量的計算任務(wù)中。
要有一些基本的約束條件： 做一些事情時(shí)，不僅要立足于眼下的問(wèn)題點(diǎn)，也要考慮一下未來(lái)可能發(fā)生的變化。就像是造一座橋，修一條路，總要考慮極限情況下的壓力。很多的數據異常，往往是在業(yè)務(wù)變化時(shí)，舊有的邏輯不能適應當前的場(chǎng)景。所以在一開(kāi)始寫(xiě)腳本時(shí)，要考慮一下未來(lái)的場(chǎng)景，有一些基本的約束條件，這樣會(huì )讓所部署的任務(wù)會(huì )有較好的穩定性。
要采用盡量簡(jiǎn)潔的寫(xiě)法： 能夠一步到位的統計邏輯，就采用盡量簡(jiǎn)潔的寫(xiě)法，千萬(wàn)不要去繞圈子。尤其是一些核心腳本，是要在不同環(huán)節，不同階段的同事之間傳承的，很多人并不了解當時(shí)的業(yè)務(wù)背景和需求邏輯，如果寫(xiě)法太繞圈子的話(huà)，最終就把大家一起繞進(jìn)去了。
2.如何健康地做數據規劃
數據規劃是一個(gè)層級比較中等的概念，往下一層，做需求開(kāi)發(fā)時(shí)，往往只聚焦于特定的需求點(diǎn)，并不涉及其它內容；往上一層，做數據工程的話(huà)，又是基于整個(gè)部門(mén)，整個(gè)產(chǎn)品形態(tài)的框架搭建。但數據規劃更多是應對一個(gè)相對獨立的業(yè)務(wù)場(chǎng)景，所做的規劃與設計。
一個(gè)不夠好的數據規劃，可能會(huì )引發(fā)后續的諸多問(wèn)題點(diǎn)，比如：
痛點(diǎn)1： PM提出要在視圖上擴展一個(gè)細分字段，覺(jué)得很簡(jiǎn)單。我也覺(jué)得很簡(jiǎn)單，但就是更改不了，因為這個(gè)字段在數據源處理中就舍棄了，無(wú)法從上一層數據表中獲得。
痛點(diǎn)2： 想要重跑一個(gè)時(shí)間范圍內的數據，但這張表不是分區表，無(wú)法并行處理；想要剔除某個(gè)日期內的數據，但不同表中時(shí)間格式不一致，導致處理結果有差漏等。
痛點(diǎn)3： 同樣的一條統計鏈路，部分為了保障每日推送而獨立出去，部分為了特性統計而獨立出去，由此產(chǎn)生了眾多的細分鏈路，此后的變更也要在不同鏈路之間同步處理。
以上列出的三個(gè)痛點(diǎn)，分別對應了原始信息的保留，技術(shù)實(shí)現的最優(yōu)路徑，以及計算任務(wù)的細分問(wèn)題等，不過(guò)也只是數據規劃需要思考的其中一部分問(wèn)題點(diǎn)。在不同的業(yè)務(wù)場(chǎng)景下里，可以有不同的數據規劃思路。粗糙地講，可以分為數據基礎層和業(yè)務(wù)細分層獨立處理。
數據基礎層： 做一個(gè)數據規劃，首先應該要考慮數據本身，在數據基礎層里，應暫時(shí)拋開(kāi)具體的業(yè)務(wù)細節，以數據為重。這個(gè)時(shí)候應在處理中盡量地保留原始信息，同時(shí)要對數據源做好質(zhì)量檢驗，第一道防線(xiàn)，往往是很重要的一道防線(xiàn)。原始信息要完整，數據質(zhì)量要合格，任務(wù)部署要輕便，這些是數據基礎層的一些目標，也是后續工作的一個(gè)前提。
業(yè)務(wù)細分層： 數據基礎要獨立而完整，面向數據本身；業(yè)務(wù)細分層則可以去細分實(shí)現，面向業(yè)務(wù)細節?；诓煌臉I(yè)務(wù)目標，可以從源表中篩選不同的內容，用于應對特定的場(chǎng)景。這樣的數據+業(yè)務(wù)層級，形成了一種“總-分”結構，是數據規劃的其中一種實(shí)現方式。
3.如何在破舊與立新之間尋找平衡點(diǎn)
很多的工作，都是基于當下的場(chǎng)景，即使做了詳盡的規劃和思考，也不可能應對未來(lái)的所有問(wèn)題。當業(yè)務(wù)逐漸地深入發(fā)展時(shí)，很多的內容也需要做一些同步處理，小的層面上是一些數據表和視圖的表更，大的層面上可能涉及計算平臺的遷移，視圖系統的重建等。
破舊與立新，往往是一個(gè)長(cháng)期存在的問(wèn)題點(diǎn)。不需要每天都進(jìn)行自我“革命”，但改良和優(yōu)化，則是一個(gè)長(cháng)期過(guò)程。在這個(gè)長(cháng)期過(guò)程里，我們需要在破舊與立新之間尋找到平衡點(diǎn)。
聯(lián)系客服
