【每周一期-數(shù)據(jù)蔣堂】還原分組運算的本意

【每周一期-數(shù)據(jù)蔣堂】還原分組運算的本意

分組是SQL中常見的運算，但未必所有人都能深刻地理解它。

分組運算的實質(zhì)是將一個集合按照某種規(guī)則拆分成若干個子集，也就是說，返回值應(yīng)當(dāng)是一個由集合構(gòu)成的集合，但人們一般并不太關(guān)心構(gòu)成這個集合的成員集合（我們稱為分組子集），而是對這些子集的聚合值更感興趣，因此，分組運算常常伴隨著對子集的進一步匯總計算。

SQL就是這么做的，在寫有GROUP BY子句時，SELECT部分除了分組字段外，就只能寫入聚合運算表達式了。當(dāng)然還有個原因是SQL沒有顯式的集合數(shù)據(jù)類型，無法返回集合的集合這類數(shù)據(jù)，也只能強迫實施聚合運算了。

久而久之，人們會認(rèn)為分組總是需要配合后續(xù)的聚合運算，而忘記了分組和聚合其實是兩個獨立的步驟。

但是，我們?nèi)匀挥袑@些分組子集而不是聚合值更感興趣的時候。

比如，我們想找出公司里有哪些員工和其他員工會在同一天過生日，很簡單的思路是將員工按生日分組，然后找出成員數(shù)大于1的分組子集，再合并起來。這時候我們就不是只對聚合值（分組子集的成員數(shù)）感興趣，而是對分組子集本身更感興趣。

這個運算用SQL寫起來就會比較啰嗦，需要用子查詢，并且要遍歷兩次原集合。

SELECT * FROM employee WHERE birthday IN

( SELECT birthday FROM employee GROUP BY birthday HAVING COUNT(*)>1 )

（題外話：這里假定birthday字段就是生日，其實我們?nèi)粘Ｒ饬x的生日是沒有年份的，而數(shù)據(jù)表中的birthday字段則會有，這時候還需要把birthday轉(zhuǎn)換成月和日再做GROUP和WHERE，但對于集合化不徹底的SQL，涉及兩個成員的IN運算很難寫，上面的birthday要改寫類似month(birthday()*100+day(birthday)的樣子，拼成一個單獨的表達式才能使用IN來判斷，書寫要繁瑣很多。）

有集合化更徹底的語法時，就可以保持住分組子集。這就是需要離散性來支持了，分組子集仍然是原集合成員構(gòu)成。這樣，分組和聚合還原成兩個步驟，上面的運算就可以很清晰地寫出來：

employee.group(month(birthday),day(birthday)).select(~.len()>1).conj()

（在這個表達式中我們使用了前面講遍歷語法時的~符號表示當(dāng)前成員，也就是遍歷過程中的某個分組子集。）

按birthday的月/日分組，過濾出成員數(shù)大于1的分組子集，然后求并集。事實上在做過濾時仍然要再二次遍歷數(shù)據(jù)，但只是計數(shù)，不需要象SQL那樣做比較，性能要好很多。

退一步講，就算我們只對聚合值感興趣，我們也可能需要保持住這些分組子集以便反復(fù)利用，計算出多種聚合值，而不是完成一次聚合后就將其丟棄，下次再計算時又要重新分組。分組是個成本不低的運算，現(xiàn)在一般使用HASH方法實現(xiàn)分組，計算和比較HASH值都要比簡單遍歷復(fù)雜很多。有些優(yōu)化不好的計算方案還會使用排序的方法實現(xiàn)分組（很多報表工具是這么做的），性能更會差出一個級別來。

比如我們計算每個部門的人數(shù)，再計算出10人以上部門的人員平均年齡。這在SQL中就要寫成兩句，因為后者需要一個HAVING條件：

SELECT department, COUNT(*) FROM employee GROUP BY department

SELECT department,AVERAGE(age) FROM employee GROUP BY department HAVING COUNT(*)>=10

這里GROUP動作就要被執(zhí)行兩遍。

而如果能夠保持分組子集，則只要做一次group就可以了：

g=employee.group(department)

g.new(~.department,~.len())

g.select(~.len()>=10).new(~.department,~.avg(age))

還有的可能是，我們確實只對一個聚合值感興趣，但這個聚合值很難計算，并不能簡單地用SUM/COUNT計算出來的，需要編段程序才行，這時候也需要保留分組子集，而用SQL就很難實現(xiàn)這種運算了。我們會在后續(xù)文章中舉例。

分組的結(jié)果是集合的集合，它仍然是個集合，那顯然還可以進一步分組。

g1=employee.group(year(birthday))          //按出生年份分組

g2=g1.group(year(birthday)%100\10)       //將所有分組子集按年代分組

g3=g1.(~.group(month(birthday))              //將每個分組子集按出生月份分組

后兩步運算都會得到集合的集合的集合，三層或更深的情況在現(xiàn)實業(yè)務(wù)中很少碰到，但可以用來體會集合的思維方式以及分組運算的本質(zhì)。

我們知道，SQL針對GROUP后的結(jié)果集過濾專門設(shè)計了HAVING關(guān)鍵字，許多初學(xué)者對HAVING的理解和運用都不到位。其實，HAVING從概念上講是多余的，它和WHERE并沒有任何差別，只是因為SQL無法保持分組子集，要把分組和聚合寫在一句話中，又要和WHERE區(qū)分，然后硬造出來的一個關(guān)鍵字。如果能夠保持分組子集后實現(xiàn)分步計算，HAVING是沒有必要的。

蔣步星，清華大學(xué)計算機碩士，著有《非線性報表模型原理》等

1989年中國國際奧林匹克數(shù)學(xué)競賽團體冠軍成員，個人金牌。

2000年創(chuàng)立潤乾公司，首次在潤乾報表中提出非線性報表模型，完美解決了中國式復(fù)雜報表制表難題，目前該模型已經(jīng)成為報表行業(yè)的標(biāo)準(zhǔn)。

2008年開始研發(fā)不依賴關(guān)系型數(shù)據(jù)的計算引擎，歷經(jīng)多個版本后，于2014年集算器正式發(fā)布。有效地提高了復(fù)雜結(jié)構(gòu)化大數(shù)據(jù)計算的開發(fā)速度和運算效率。

2016年榮獲中國電子信息產(chǎn)業(yè)發(fā)展研究院評選的“2016年中國軟件和信息服務(wù)業(yè) ? 十大領(lǐng)軍人物”。

2017年將帶領(lǐng)潤乾軟件朝著擁有自主產(chǎn)權(quán)的非關(guān)系型強計算數(shù)據(jù)倉庫、云數(shù)據(jù)庫等產(chǎn)品邁進。