今天看到一個(gè)關(guān)于自動(dòng)化生成SQL的視頻（加班1周，我被這個(gè)需求搞瘋了?。?！），視頻up需要實(shí)現(xiàn)一個(gè)中國(guó)式復(fù)雜報(bào)表，因SQL邏輯復(fù)雜且代碼較長(zhǎng)，up通過(guò)JSON配置方式實(shí)現(xiàn)了自動(dòng)生成SQL代碼，從而按期完成工作。粗看沒(méi)啥毛病，但是只要稍微多想想，就會(huì)發(fā)現(xiàn)很多問(wèn)題：

1. 架構(gòu)奇怪，為啥要在單個(gè)查詢(xún)實(shí)現(xiàn)？up所需結(jié)果是個(gè)報(bào)表，一般而言需要在大數(shù)據(jù)平臺(tái)中批處理計(jì)算好落表存儲(chǔ)，可視化工具只需查詢(xún)結(jié)果表。如果需要查詢(xún)結(jié)果實(shí)時(shí)更新，也可以通過(guò)流處理軟件實(shí)現(xiàn)，查詢(xún)的也是流處理計(jì)算好的表，而不是對(duì)Rawdata進(jìn)行一遍又一遍的讀??；

2. 項(xiàng)目層面考慮，拼接SQL的做法并不鮮見(jiàn)，orm框架已經(jīng)很成熟，這個(gè)項(xiàng)目只能解決此類(lèi)單一場(chǎng)景的問(wèn)題，考慮投入產(chǎn)出，真的有必要造輪子嗎？

3. 核心功能上，該項(xiàng)目的JSON轉(zhuǎn)SQL功能，本質(zhì)上是在實(shí)現(xiàn)模板引擎，現(xiàn)成的工具也非常多，為什么要從零寫(xiě)一個(gè)？

4. 解決方案分析，自動(dòng)生成SQL是最優(yōu)解嗎？3000行的SQL性能如何？能否對(duì)問(wèn)題進(jìn)行抽象再寫(xiě)代碼？

如此這般，雖然做的很好，請(qǐng)以后別這么做了。

我們不妨嘗試一下手寫(xiě)SQL的方案，先分析一下需求：

1. 統(tǒng)計(jì)指標(biāo)分布在不同的數(shù)據(jù)源中，需要將基本信息表、三連表、學(xué)習(xí)表、電腦表關(guān)聯(lián)拼接到一起

2. 報(bào)表行包含了匯總和透視，不同行的統(tǒng)計(jì)條目不同

3. 全校指標(biāo)列需要局部上卷

4. 全表指標(biāo)均需要計(jì)算環(huán)比

我們嘗試來(lái)逐步分解并實(shí)現(xiàn)它：

第一步：補(bǔ)齊寬表，將基本信息、三連、學(xué)習(xí)、電腦等表JOIN起來(lái)，生成一個(gè)包含指標(biāo)所需原始字段的寬表，這一步的實(shí)現(xiàn)代碼略過(guò)，后續(xù)我們將直接在這個(gè)表的基礎(chǔ)上實(shí)現(xiàn)；

我們假設(shè)這個(gè)表的結(jié)構(gòu)如下：

通過(guò)excel公式，我們準(zhǔn)備隨機(jī)數(shù)據(jù)導(dǎo)入到表中:?

鏈接: https://pan.baidu.com/s/1QWF8o15CRjKfxXVQUYk0Fg?提取碼: 9aj9

第二步：生成包含不同條目和類(lèi)別匯總的透視表：

這里邏輯稍微解釋下：

• students表將學(xué)生特征向量化，準(zhǔn)備一個(gè)長(zhǎng)度為枚舉數(shù)量的向量(代碼中是2+2+3)，每一位按照特征值標(biāo)記為0或1，存放在feature字段中，這種編碼方式叫做one-hot編碼

• template表是報(bào)表的模板，記錄每行需要的特征和排序

• 二者取笛卡爾積關(guān)聯(lián)后，通過(guò)布爾向量與“&”運(yùn)算，符合特征的行結(jié)果將不為零，我們將這些行標(biāo)記為1，否則標(biāo)記為0，實(shí)際匯總指標(biāo)時(shí)，只需要將該因數(shù)和指標(biāo)相乘再作匯總即可

• 結(jié)果使用模板的seq列排序，就實(shí)現(xiàn)整個(gè)報(bào)表70%以上的需求了。這時(shí)候你可能已經(jīng)發(fā)現(xiàn)，“年級(jí)”我并沒(méi)有增加匯總行，通過(guò)修改版本模板和特征也可以用上述方法實(shí)現(xiàn)。事實(shí)上，這種方式模板表的量級(jí)不宜過(guò)大，需要注意數(shù)據(jù)量膨脹帶來(lái)的性能下降問(wèn)題，因此在實(shí)際開(kāi)發(fā)中，一般會(huì)放在后續(xù)步驟處理。

  該步驟的結(jié)果如下：

第三步: 實(shí)現(xiàn)局部上卷和環(huán)比。一般會(huì)使用窗口函數(shù)sum() over()的方式從而實(shí)現(xiàn)局部上卷，環(huán)比的情況也可以通過(guò)lag()實(shí)現(xiàn)分組排序的上一條記錄。由于我建表時(shí)忘記加日期字段，就到此為止吧，這部分也不難。

我們常常因?yàn)槟繕?biāo)明確，采用了過(guò)程化的解決方案，而忽略了數(shù)據(jù)思維。作為團(tuán)隊(duì)技術(shù)Leader，我也是常常在Review新人代碼時(shí)，看著代碼迷惑不解，然后若有所思，最后陷入反思：技術(shù)人掌握著大量的算法抽象，為啥他們只看到了處理過(guò)程。

視頻中up主的加班造輪子的過(guò)程固然可歌可泣，但我們顯然有更好的辦法來(lái)降低復(fù)雜度（不只是計(jì)算層面，架構(gòu)尤甚）。通過(guò)構(gòu)造模板和布爾運(yùn)算，我們幾乎用zero effort就脫離了堆疊查詢(xún)的困境。

數(shù)據(jù)崗位的面試中，往往會(huì)用“股票的最大連續(xù)漲停天數(shù)”（變體如“APP用戶(hù)最大連續(xù)簽到天數(shù)”）等SQL題來(lái)考察候選人的SQL水平，盡管該題的通過(guò)率不高，但候選人只要被問(wèn)過(guò)一遍，應(yīng)該以后都有“標(biāo)準(zhǔn)答案”了。如果本著尋找高效工具人的角度，這類(lèi)面試題就夠了。相比于固定套路，我更愿意考慮算法抽象能力和解決方案設(shè)計(jì)能力強(qiáng)的候選人。

技術(shù)人掌握著工具和方法，往往容易陷入有一把錘子后看什么都像釘子的困境。淘汰掉眾多的低級(jí)錯(cuò)誤和技能、認(rèn)知限制的案例，?Odoo復(fù)雜審批流終極實(shí)現(xiàn) - 01 源起不適?堪稱(chēng)典型。如果你看完這個(gè)視頻，你也就明白算法抽象能力和解決方案設(shè)計(jì)能力遠(yuǎn)比代碼能力重要的原因了。