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

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

分組是SQL中常見(jiàn)的運(yùn)算，但未必所有人都能深刻地理解它。

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

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

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

但是，我們?nèi)匀挥袑?duì)這些分組子集而不是聚合值更感興趣的時(shí)候。

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

這個(gè)運(yùn)算用SQL寫(xiě)起來(lái)就會(huì)比較啰嗦，需要用子查詢，并且要遍歷兩次原集合。

SELECT * FROM employee WHERE birthday IN

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

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

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

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

（在這個(gè)表達(dá)式中我們使用了前面講遍歷語(yǔ)法時(shí)的~符號(hào)表示當(dāng)前成員，也就是遍歷過(guò)程中的某個(gè)分組子集。）

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

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

比如我們計(jì)算每個(gè)部門(mén)的人數(shù)，再計(jì)算出10人以上部門(mén)的人員平均年齡。這在SQL中就要寫(xiě)成兩句，因?yàn)楹笳咝枰粋€(gè)HAVING條件：

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

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

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

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

g=employee.group(department)

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

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

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

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

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

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

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

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

我們知道，SQL針對(duì)GROUP后的結(jié)果集過(guò)濾專(zhuān)門(mén)設(shè)計(jì)了HAVING關(guān)鍵字，許多初學(xué)者對(duì)HAVING的理解和運(yùn)用都不到位。其實(shí)，HAVING從概念上講是多余的，它和WHERE并沒(méi)有任何差別，只是因?yàn)?a href='/map/sql/' style='color:#000;font-size:inherit;'>SQL無(wú)法保持分組子集，要把分組和聚合寫(xiě)在一句話中，又要和WHERE區(qū)分，然后硬造出來(lái)的一個(gè)關(guān)鍵字。如果能夠保持分組子集后實(shí)現(xiàn)分步計(jì)算，HAVING是沒(méi)有必要的。

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

1989年中國(guó)國(guó)際奧林匹克數(shù)學(xué)競(jìng)賽團(tuán)體冠軍成員，個(gè)人金牌。

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

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

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

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