302
【數據蔣堂】第27期：非常規聚合
標準SQL中提供了五種最常用的聚合運算：SUM/COUNT/AVG/MIN/MAX。觀察這幾個運算，我們發現它們都可以看成是一個以集合為參數返回單值的函數，我們就先把這個共同點理解為聚合運算的定義，把集合變成單值，多個值變成一個值，也就是發生了"聚合“，所以叫聚合運算。
那麼很顯然，有集合的時候就可以應用聚合運算了，所以SUM/COUNT這些運算可以針對一個數據表（記錄集合）實施。
分組運算的結果是一批分組子集，那麼每個子集上也可以應用聚合運算，這也就是SQL的分組運算了。其實針對全集的聚合運算也可以理解為隻分了一個組的特殊分組（也是個完全劃分），這樣理解後，我們可以認為聚合運算總是發生在分組運算之後（但分組運算後不一定總有聚合運算，前麵已說過）。而且，還可以反過來說，隻要被認定為是聚合運算（符合前述定義的運算），就一定可以用在分組之後。我們在下麵會看到，這個理解將大幅度地擴展分組+聚合運算的應用範圍。
除了這五種聚合運算外，有的數據庫還提供了方差、標準差等聚合函數，其性質和這五種差不多，可以稱為是常規的聚合運算。我們下麵來研究業務上有意義的其它形式聚合運算。
1. 返回記錄
上述的常規聚合都是針對數值的運算，特別地，對於結構化數據來說，是針對某個字段（或表達式）的運算，返回值也是這些數值的運算結果。但有時候我們關心的不是結果數值本身，而是與結果數值相關的信息。
比如我們想從日誌表中找出某個用戶第一次登錄時用的IP地址，而不是登錄時刻。用標準SQL寫這個運算大概是這樣：
SELECT ip_address FROM LogTable WHERE user=? AND logintime=
(SELECT MIN(logintime) FROM LogTable WHERE user=?)用子查詢先計算出該用戶的第一次登錄的時刻，再查找出該時刻時用到的IP地址，這要把數據集遍曆兩次。
ORACLE提供了一個KEEP函數，可以不用子查詢寫出這樣的運算：
SELECT MIN(ip_address) KEEP(DENSE_RANK FIRST ORDER BY logintime) FROM LogTable WHERE user=?但是，我們關心的可能還不止是IP地址，還可能是日誌表中的其它字段，比如所用瀏覽器、是否移動端等，其實就是關心最小值對應的那條完整記錄。而由於SQL缺乏離散性，即使有KEEP函數，也不容易寫出這種運算，要麼每個字段分別用KEEP，要麼還是用子查詢遍曆兩次，都很繁瑣。
如果有一個用於返回最大值/最小值對應記錄而非值本身的聚合函數，那這個運算寫起來就簡單了，也隻要遍曆一次：
=LogTable.select(user=?).minp(logintime)像前麵說的，這樣的聚合運算還可以用在GROUP中，比如找出每個用戶首次登錄的日誌記錄
=LogTable.group(user).(~.minp(logintime))類似地，還可以有maxp方法用於返回最大值對應記錄。
日誌記錄常常本來就是按事件發生時刻有序，利用這個特點時就不需要再用比較來計算最小值了，而是直接取出第一條即可。
=LogTable.select(user=?).first() // 聚合函數first返回第1個成員在分組中也可以：
=LogTable.group(user).(~.first())當然實際編碼時也可以直接取集合成員，這裏寫成first隻是為了強調可以把取某成員的動作理解為一種聚合運算。
這種運算較為常用，我們可以為group函數做一個選項：
=LogTable.group@1(user)SQL建立在無序集合概念上，無法保證返回記錄的次序，想寫出這種運算就又需要人為製造序號後再用過濾條件來做。
2. 返回集合
我們把上麵的問題改一下：找出一群人中年齡最小的那些人的姓名。
和前述問題不同的是，同一個用戶不會有多個相同的登錄時間，但一批人中則可能有年齡相同的人，年齡最小的人可能不止一個。minp函數的返回值應當是一個集合才合理。
仔細觀察我們在文章開始對聚合運算的定義，我們會發現，其實返回單值的要求並無必要，隻要參數是集合，隨便返回什麼東西都可以認定為是聚合運算，這種定義下，返回集合的minp/maxp仍然可以作為聚合運算處理。
需要返回集合的聚合運算中，更常見是topN。
SQL並不把topN理解成一種聚合運算，而隻是返回結果集時的一種修飾符。原理上，SQL會先把完整的結果集計算出來，然後再隻取前N條返回。topN總是在排序動作之後，大集合的排序是個時間成本很高的動作，但其實隻做topN並不需要全集的排序。這時候隻能依靠數據庫在工程上的優化，但這並不是總能做好的。另外，隻作為結果集的修飾，那就不能把這個運算實施到分組子集上了，而且運算複雜化後優化也很難做了。
把topN理解成聚合運算後，一切都變得很輕鬆
=a=LogTable.select(user=?).top(logingtime,-2), a(2)-a(1) //某用戶最後的兩次登錄時間間隔
=LogTable.groups(user;(a=~.top(logintime,-2),a(2)-a(1))) //每個用戶最後的兩次登錄時間間隔而且實施計算也不需要刻意地工程上優化，在分組後使用也能獲得高性能。
topN也有返回記錄的情況，即取出某個字段（表達式）在前N名的對應記錄。和minp/maxp類似地，這需要再設計一個函數。
同樣的，有序情況也會發生，像前麵的日誌計算，如果假定日誌表已經針對事件時刻有序，那可以不必再用topN去做比較運算了。
=a=LogTable.select(user=?).last(2),a(2)-a(1) //聚合函數last(n)返回最後n個成員
=LogTable.groups(user;(a=~.last(2),a(2)-a(1)))類似地，last函數也可以寫成取集合成員的形式。
這裏討論了非常規聚合的兩種常見情況，都是SQL不易支持的。當然按照定義還會有更多形式的聚合運算，即使這兩種情況也還會有許多變種，比如取出排序位置居中的成員、取出針對某一字段的唯一值（DISTINCT）集合等。深入理解聚合運算及其與分組運算的關係，將能夠擴展這些運算的應用範圍，對計算的描述和實施都有不小的意義。
原文發布時間為：2017-10-24
本文作者：蔣步星
本文來自雲棲社區合作夥伴“數據蔣堂”，了解相關信息可以關注“數據蔣堂”微信公眾號
最後更新：2017-10-25 12:04:08