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

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

那么很顯然，有集合的時(shí)候就可以應(yīng)用聚合運(yùn)算了，所以SUM/COUNT這些運(yùn)算可以針對一個(gè)數(shù)據(jù)表（記錄集合）實(shí)施。

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

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

1、返回記錄

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

比如我們想從日志表中找出某個(gè)用戶第一次登錄時(shí)用的IP地址，而不是登錄時(shí)刻。用標(biāo)準(zhǔn)SQL寫這個(gè)運(yùn)算大概是這樣：

SELECT ip_address FROM  LogTable WHERE user=? AND logintime=
    (SELECT MIN(logintime) FROM LogTable WHERE user=?)

用子查詢先計(jì)算出該用戶的第一次登錄的時(shí)刻，再查找出該時(shí)刻時(shí)用到的IP地址，這要把數(shù)據(jù)集遍歷兩次。

ORACLE提供了一個(gè)KEEP函數(shù)，可以不用子查詢寫出這樣的運(yùn)算：

SELECT MIN(ip_address) KEEP(DENSE_RANK   FIRST ORDER BY logintime) FROM LogTable WHERE user=?

但是，我們關(guān)心的可能還不止是IP地址，還可能是日志表中的其它字段，比如所用瀏覽器、是否移動(dòng)端等，其實(shí)就是關(guān)心最小值對應(yīng)的那條完整記錄。而由于SQL缺乏離散性，即使有KEEP函數(shù)，也不容易寫出這種運(yùn)算，要么每個(gè)字段分別用KEEP，要么還是用子查詢遍歷兩次，都很繁瑣。

如果有一個(gè)用于返回最大值/最小值對應(yīng)記錄而非值本身的聚合函數(shù)，那這個(gè)運(yùn)算寫起來就簡單了，也只要遍歷一次：

=LogTable.select(user=?).minp(logintime)

像前面說的，這樣的聚合運(yùn)算還可以用在GROUP中，比如找出每個(gè)用戶首次登錄的日志記錄

=LogTable.group(user).(~.minp(logintime))

類似地，還可以有maxp方法用于返回最大值對應(yīng)記錄。

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

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

在分組中也可以：

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

當(dāng)然實(shí)際編碼時(shí)也可以直接取集合成員，這里寫成first只是為了強(qiáng)調(diào)可以把取某成員的動(dòng)作理解為一種聚合運(yùn)算。

這種運(yùn)算較為常用，我們可以為group函數(shù)做一個(gè)選項(xiàng)：

=LogTable.group@1(user)

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

2、返回集合

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

和前述問題不同的是，同一個(gè)用戶不會(huì)有多個(gè)相同的登錄時(shí)間，但一批人中則可能有年齡相同的人，年齡最小的人可能不止一個(gè)。minp函數(shù)的返回值應(yīng)當(dāng)是一個(gè)集合才合理。

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

需要返回集合的聚合運(yùn)算中，更常見是topN。

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

把topN理解成聚合運(yùn)算后，一切都變得很輕松

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

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

topN也有返回記錄的情況，即取出某個(gè)字段（表達(dá)式）在前N名的對應(yīng)記錄。和minp/maxp類似地，這需要再設(shè)計(jì)一個(gè)函數(shù)。

同樣的，有序情況也會(huì)發(fā)生，像前面的日志計(jì)算，如果假定日志表已經(jīng)針對事件時(shí)刻有序，那可以不必再用topN去做比較運(yùn)算了。

=a=LogTable.select(user=?).last(2),a(2)-a(1) 　　//聚合函數(shù)last(n)返回最后n個(gè)成員

=LogTable.groups(user;(a=~.last(2),a(2)-a(1)))

類似地，last函數(shù)也可以寫成取集合成員的形式。

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