【每周一期-數據蔣堂】非常規(guī)聚合

標準SQL中提供了五種最常用的聚合運算：SUM/COUNT/AVG/MIN/MAX。觀察這幾個運算，我們發(fā)現它們都可以看成是一個以集合為參數返回單值的函數，我們就先把這個共同點理解為聚合運算的定義，把集合變成單值，多個值變成一個值，也就是發(fā)生了"聚合“，所以叫聚合運算。

那么很顯然，有集合的時候就可以應用聚合運算了，所以SUM/COUNT這些運算可以針對一個數據表（記錄集合）實施。

分組運算的結果是一批分組子集，那么每個子集上也可以應用聚合運算，這也就是SQL的分組運算了。其實針對全集的聚合運算也可以理解為只分了一個組的特殊分組（也是個完全劃分），這樣理解后，我們可以認為聚合運算總是發(fā)生在分組運算之后（但分組運算后不一定總有聚合運算，前面已說過）。而且，還可以反過來說，只要被認定為是聚合運算（符合前述定義的運算），就一定可以用在分組之后。我們在下面會看到，這個理解將大幅度地擴展分組+聚合運算的應用范圍。

除了這五種聚合運算外，有的數據庫還提供了方差、標準差等聚合函數，其性質和這五種差不多，可以稱為是常規(guī)的聚合運算。我們下面來研究業(yè)務上有意義的其它形式聚合運算。

1、返回記錄

上述的常規(guī)聚合都是針對數值的運算，特別地，對于結構化數據來說，是針對某個字段（或表達式）的運算，返回值也是這些數值的運算結果。但有時候我們關心的不是結果數值本身，而是與結果數值相關的信息。

比如我們想從日志表中找出某個用戶第一次登錄時用的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方法用于返回最大值對應記錄。

日志記錄常常本來就是按事件發(fā)生時刻有序，利用這個特點時就不需要再用比較來計算最小值了，而是直接取出第一條即可。

=LogTable.select(user=?).first() 　　// 聚合函數first返回第1個成員

在分組中也可以：

=LogTable.group(user).(~.first())

當然實際編碼時也可以直接取集合成員，這里寫成first只是為了強調可以把取某成員的動作理解為一種聚合運算。

這種運算較為常用，我們可以為group函數做一個選項：

=LogTable.group@1(user)

SQL建立在無序集合概念上，無法保證返回記錄的次序，想寫出這種運算就又需要人為制造序號后再用過濾條件來做。

2、返回集合

我們把上面的問題改一下：找出一群人中年齡最小的那些人的姓名。

和前述問題不同的是，同一個用戶不會有多個相同的登錄時間，但一批人中則可能有年齡相同的人，年齡最小的人可能不止一個。minp函數的返回值應當是一個集合才合理。

仔細觀察我們在文章開始對聚合運算的定義，我們會發(fā)現，其實返回單值的要求并無必要，只要參數是集合，隨便返回什么東西都可以認定為是聚合運算，這種定義下，返回集合的minp/maxp仍然可以作為聚合運算處理。

需要返回集合的聚合運算中，更常見是topN。

SQL并不把topN理解成一種聚合運算，而只是返回結果集時的一種修飾符。原理上，SQL會先把完整的結果集計算出來，然后再只取前N條返回。topN總是在排序動作之后，大集合的排序是個時間成本很高的動作，但其實只做topN并不需要全集的排序。這時候只能依靠數據庫在工程上的優(yōu)化，但這并不是總能做好的。另外，只作為結果集的修飾，那就不能把這個運算實施到分組子集上了，而且運算復雜化后優(yōu)化也很難做了。

把topN理解成聚合運算后，一切都變得很輕松

=a=LogTable.select(user=?).top(logingtime,-2), a(2)-a(1)　//某用戶最后的兩次登錄時間間隔
=LogTable.groups(user;(a=~.top(logintime,-2),a(2)-a(1))) 　//每個用戶最后的兩次登錄時間間隔

而且實施計算也不需要刻意地工程上優(yōu)化，在分組后使用也能獲得高性能。

topN也有返回記錄的情況，即取出某個字段（表達式）在前N名的對應記錄。和minp/maxp類似地，這需要再設計一個函數。

同樣的，有序情況也會發(fā)生，像前面的日志計算，如果假定日志表已經針對事件時刻有序，那可以不必再用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函數也可以寫成取集合成員的形式。

這里討論了非常規(guī)聚合的兩種常見情況，都是SQL不易支持的。當然按照定義還會有更多形式的聚合運算，即使這兩種情況也還會有許多變種，比如取出排序位置居中的成員、取出針對某一字段的唯一值（DISTINCT）集合等。深入理解聚合運算及其與分組運算的關系，將能夠擴展這些運算的應用范圍，對計算的描述和實施都有不小的意義。