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

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

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

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

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

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

但是，我們仍然有對這些分組子集而不是聚合值更感興趣的時候。

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

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

SELECT * FROM employee WHERE birthday IN

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

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

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

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

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

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

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

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

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

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

這里GROUP動作就要被執行兩遍。

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

g=employee.group(department)

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

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

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

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

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

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

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

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

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

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

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

2000年創立潤乾公司，首次在潤乾報表中提出非線性報表模型，完美解決了中國式復雜報表制表難題，目前該模型已經成為報表行業的標準。

2008年開始研發不依賴關系型數據的計算引擎，歷經多個版本后，于2014年集算器正式發布。有效地提高了復雜結構化大數據計算的開發速度和運算效率。

2016年榮獲中國電子信息產業發展研究院評選的“2016年中國軟件和信息服務業 ? 十大領軍人物”。

2017年將帶領潤乾軟件朝著擁有自主產權的非關系型強計算數據倉庫、云數據庫等產品邁進。