PostgreSQL 用戶定義的聚集

聚集函數可以選擇性地支持移動聚集模式，這種模式允許很大程度上提高在具有移動幀起點的窗口中執(zhí)行的聚集函數的速度（有關把聚集函數用作窗口函數請見第 3.5 節(jié)和第 4.2.8 節(jié)）。基本思想是在通常的“前向”轉換函數之外，聚集提供一個逆向轉換函數，該函數允許當行退出窗口幀時從聚集的運行狀態(tài)值中移除它們的值。例如一個sum聚集使用加法作為前向轉換函數，它可以使用減法作為逆向轉換函數。如果沒有一個逆向轉換函數，每一次幀起點移動時，窗口函數機制必須重新從頭計算該聚集，這會導致運行時間與輸入行的數量乘以平均幀長度成比例。如果有一個逆向轉換函數，運行時間只與輸入行的數量成比例。

當前狀態(tài)值和包含在當前狀態(tài)中最早的行的聚集輸入值被傳遞給逆向轉換函數。它必須重新構建出如果給定的輸入行不再被聚集（只聚集其后的行）時狀態(tài)值會是什么樣。這有時要求前向轉換函數保存比普通聚集模式下更多的狀態(tài)。因此，移動聚集模式使用一種完全不同于普通模式的實現：它有自己的狀態(tài)數據類型、自己的前向轉換函數以及自己的狀態(tài)函數（如果需要）。如果不需要額外的狀態(tài)，這些可以和普通模式的數據類型和函數相同。

作為一個例子，我們可以把上面給定的sum聚集擴展成支持移動聚集模式：

CREATE AGGREGATE sum (complex)
(
    sfunc = complex_add,
    stype = complex,
    initcond = '(0,0)',
    msfunc = complex_add,
    minvfunc = complex_sub,
    mstype = complex,
    minitcond = '(0,0)'
);

名稱以m開始的參數定義移動聚集實現。除了逆向轉換函數minvfunc，它們都對應于沒有m的普通聚集參數。

用于移動聚集模式的前向轉換函數不允許返回空值作為新狀態(tài)值。如果逆向轉換函數返回空值，這被當作一種指示，它表明該逆向函數無法為這個特定輸入逆轉狀態(tài)計算，因此該聚集計算將根據當前的幀開始位置重新從頭計算。這種習慣允許移動聚集模式被用在一些不適合逆轉運行狀態(tài)值的少數情況下。逆向轉換函數在這些情況下可以“撒手不管”，然后在它能夠工作的大部分情況中再出來干活。例如，一個浮點數的聚集可能會在必須從運行狀態(tài)值中移除一個 NaN（不是一個數字）輸入時撒手不管。

在編寫移動聚集支持函數時，很重要的是確保逆向轉換函數能夠準確地重構正確的狀態(tài)值。否則會導致用不用移動聚集模式時結果中產生用戶可見的差別。為一個聚集增加一個逆向轉換函數的例子最初看起來很簡單，但是卻無法滿足float4或者float8輸入上的sum的要求。sum(float8)的一種未經考慮的定義可以是

CREATE AGGREGATE unsafe_sum (float8) ( stype = float8, sfunc = float8pl, mstype = float8, msfunc = float8pl, minvfunc = float8mi );

但是，這個聚集可能給出與沒有逆向轉換函數時很不同的結果。例如，考慮

SELECT
  unsafe_sum(x) OVER (ORDER BY n ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM (VALUES (1, 1.0e20::float8),
             (2, 1.0::float8)) AS v (n,x);

這個查詢返回0作為它的第二個結果，而不是我們期待的1。其原因是浮點值的有限精度：把1加到1e20還是會得到1e20，因此從中減去1e20會得到 0而不是1。這是對于浮點計算的一種一般性限制，而不是PostgreSQL的限制。

聚集函數可以使用多態(tài)狀態(tài)轉換函數或最終函數，這樣同樣的函數能被用來實現多個聚集。關于多態(tài)函數的解釋可參見第 37.2.5 節(jié)。更進一步，聚集函數本身可以被指定為具有多態(tài)輸入類型和狀態(tài)類型，允許一個聚集函數服務于多種輸入數據類型。這里是一個多態(tài)聚集的例子：

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

這里，每一次給定聚集調用的實際狀態(tài)類型是把實際輸入類型作為元素的數組類型。該聚集的行為是串接所有輸入成一個該類型的數組（注意：內建的聚集array_agg提供了相似的功能，但是具有比上述定義更好的性能）。

這里是使用兩種不同實際數據類型作為參數的輸出：

SELECT attrelid::regclass, array_accum(attname)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
    GROUP BY attrelid;

   attrelid    |              array_accum              
---------------+---------------------------------------
 pg_tablespace | {spcname,spcowner,spcacl,spcoptions}
(1 row)

SELECT attrelid::regclass, array_accum(atttypid::regtype)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
    GROUP BY attrelid;

   attrelid    |        array_accum        
---------------+---------------------------
 pg_tablespace | {name,oid,aclitem[],text[]}
(1 row)

如上述例子所示，通常一個具有多態(tài)結果類型的聚集函數有一個多態(tài)狀態(tài)類型。這是必須的，因為否則就無法有意義地聲明最終函數：它會需要有一個多態(tài)結果類型但是不能有多態(tài)參數類型，CREATE FUNCTION將當場拒絕那些無法從調用中推斷結果類型的函數。但是使用一個多態(tài)狀態(tài)類型有時并不方便。最常見的情況是，聚集支持函數使用 C 編寫并且狀態(tài)類型應該被聲明為internal，因為在 SQL 層面上沒有與它等效的類型。為了表述這種情況，可以聲明最終函數為接受額外的匹配該聚集輸入參數的 “dummy”參數。這種假參數總是被傳遞為空值，因為當最終函數被調用時沒有特定的值可用。它們的唯一用途是允許一個多態(tài)最終函數的結果類型被連接到該聚集的輸入類型。例如，內建聚集array_agg的定義等效于：

CREATE FUNCTION array_agg_transfn(internal, anynonarray)
  RETURNS internal ...;
CREATE FUNCTION array_agg_finalfn(internal, anynonarray)
  RETURNS anyarray ...;

CREATE AGGREGATE array_agg (anynonarray)
(
    sfunc = array_agg_transfn,
    stype = internal,
    finalfunc = array_agg_finalfn,
    finalfunc_extra
);

這里，finalfunc_extra選項指定該最終函數接收除了狀態(tài)值之外，還接收對應于該聚集輸入參數的額外假參數。額外的anynonarray參數允許array_agg_finalfn的聲明成為合法。

與常規(guī)函數的習慣大致相同，可以通過把一個聚集函數的最后一個參數聲明為一個VARIADIC數組，這樣可以讓該函數接受可變數量的參數（見第 37.5.5 節(jié)）。該聚集的轉換函數也必須有相同的數組類型作為它們的最后一個參數。通常這類轉換函數也會被標上 VARIADIC，但這不被嚴格要求。

SELECT myaggregate(a ORDER BY a, b, c) FROM ...

SELECT myaggregate(a, b, c ORDER BY a) FROM ...

目前為止我們已經描述的聚集都是“普通”聚集。PostgreSQL還支持有序集聚集，它和普通聚集在兩個關鍵點上相區(qū)別。首先，除了對每個輸入行都要計算一次的普通聚集參數之外，一個有序集聚集可以有“直接”參數，這類參數針對每次聚集操作只計算一次。其次，用于普通聚集參數的語法需要顯式地為它們指定一個排序順序。一個有續(xù)集聚集通常被用來實現一種依賴于特定行序的計算（例如排名或者百分位數），因此排序是任何調用都要求的。例如， percentile_disc的內建定義等效于：

CREATE FUNCTION ordered_set_transition(internal, anyelement)
  RETURNS internal ...;
CREATE FUNCTION percentile_disc_final(internal, float8, anyelement)
  RETURNS anyelement ...;

CREATE AGGREGATE percentile_disc (float8 ORDER BY anyelement)
(
    sfunc = ordered_set_transition,
    stype = internal,
    finalfunc = percentile_disc_final,
    finalfunc_extra
);

這個聚集接受一個float8直接參數（百分位數分數）以及一個可以是任意可排序數據類型的聚集輸入。它可以用來得到一個家庭收入的中位數：

SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY income) FROM households;
 percentile_disc
-----------------
           50489

這里0.5是一個直接參數，它對于要作為一個在行之間變化的百分位數分數沒有意義。

和普通聚集的情況不同，用于有序集聚集的輸入行排序不是在幕后完成的，而是由該聚集的支持函數負責完成。典型的實現方法是在該聚集的狀態(tài)值中保持對于一個“tuplesort”對象的引用，把到來的行輸入給該對象，然后完成排序并且在最終函數中讀出該數據。這種設計允許最終函數能夠執(zhí)行特殊操作，例如把附加的“假想”行注入到被排序的數據中。雖然用由 PL/pgSQL或另一種 PL 語言編寫的支持函數通常能夠實現普通聚集，但是有序集聚集通常必須用 C 編寫，因為它們的狀態(tài)值無法用任何 SQL 數據類型來定義（在上面的例子中，注意狀態(tài)值被聲明為類型internal — 這很典型）。此外，由于最終函數會執(zhí)行排序，后面就不能繼續(xù)通過再次執(zhí)行轉移函數增加輸入行。這意味著最終函數不是READ_ONLY的，它必須在CREATE AGGREGATE中被聲明為 READ_WRITE，或者在可以有額外的最終函數調用利用已經排序好的狀態(tài)時聲明為SHAREABLE。

用于一個有序集聚集的狀態(tài)轉移函數接收當前狀態(tài)值外加對于每一行的聚集輸入值，并且返回更新后的狀態(tài)值。這和普通聚集的定義相同，但是注意沒有提供直接參數（如果有）。最終函數接收最后的狀態(tài)值、直接參數（如果有）的值以及對應于聚集輸入的空值（如果指定了finalfunc_extra）。正如普通聚集，只有聚集是多態(tài)時finalfunc_extra才真正有用，那時就需要額外的假參數把最終函數的結果類型連接到該聚集的輸入類型。

當前，有序集聚集不能被用做窗口函數，并且因此沒有必要讓它們支持移動聚集模式。

可選地，一個聚集函數可以支持部分聚集。部分聚集的思想是在輸入數據的不同子集上獨立的運行該聚集的狀態(tài)轉移函數，然后把從這些子集得到的狀態(tài)值組合起來產生最終的狀態(tài)值，這樣得到的狀態(tài)值與在單次聚集操作中掃描所有輸入得到的狀態(tài)值相同。這種模式可以被用來進行并行聚集，用不同的工作者進程掃描表的不同部分。每一個工作者產生一個部分狀態(tài)值，最后把這些部分狀態(tài)值組合產生最終狀態(tài)值（在未來，這種模式可能也會被用于組合在本地表和遠程表上的聚集，但目前還未實現）。

為了支持部分聚集，聚集定義必須提供一個組合函數，這個函數接收兩個該聚集的狀態(tài)類型（表示在輸入行的兩個不同子集上得到的聚集結果）并且產生一個該狀態(tài)類型的新值，該結果表示組合哪些聚集結果后的狀態(tài)。至于來自兩個集合的輸入行的相對順序則并沒有指定。這意味著通常不可能為對輸入行順序敏感的聚集定義出可用的組合函數。

作為簡單的例子，通過指定組合函數為與其轉移函數中相同的“兩者中較大者”和“兩者中較小者”比較函數，MAX和MIN聚集可以支持部分聚集。SUM聚集則只需要一個額外的函數作為組合函數（同樣，組合函數與其轉移函數相同，除非狀態(tài)值的寬度比輸入數據類型更寬）。

組合函數很像一個把狀態(tài)類型值而不是底層輸入類型值作為其第二個參數的轉移函數。尤其是處理空值和嚴格函數的規(guī)則是相似的。此外，如果聚集定義指定了非空的initcond，記住那不僅會被作為每一次部分聚集運行的初始狀態(tài)，還會被作為組合函數的初始狀態(tài)，對每一個部分結果都會調用組合函數將部分結果組合到該初始狀態(tài)中。

如果聚集的狀態(tài)類型被聲明為internal，則組合函數應負責在用于聚集狀態(tài)值的內存上下文中分配其結果。這意味著當第一個輸入為NULL時，不能簡單地返回第二個輸入，因為那個值將會在錯誤的上下文中并且將不具有足夠的壽命。

當聚集的狀態(tài)類型被聲明為internal時，通常聚集定義提供序列化函數和反序列化函數也是合適的，這兩個函數允許這樣一種狀態(tài)值被從一個進程復制到另一個進程。如果沒有這些函數就無法執(zhí)行并行聚集，并且未來的本地/遠程聚集之類的應用也可能無法工作。

一個序列化函數必須接收一個單一的internal類型參數并且返回一個bytea類型的結果，它表示把狀態(tài)值打包成一個平面化的字節(jié)串。反過來，反序列化函數是上述轉換的逆變換。反序列化函數必須接收兩個類型為bytea和internal的參數，并且返回類型為internal的結果（第二個參數沒有被使用并且總是為零，它的存在是由于類型安全性的原因）。反序列化函數的結果應該直接在當前內存上下文中分配，這與組合函數的結果不同，因為它不需要長期存在。

還有一點值得提示的是關于要被并行執(zhí)行的聚集，聚集本身必須被標記上PARALLEL SAFE。其支持函數上的并行安全性標記不會被參考。

用 C 編寫的函數能夠通過調用AggCheckCallContext檢測它是作為聚集支持函數調用的，例如：

if (AggCheckCallContext(fcinfo, NULL))

檢查這個區(qū)別的原因是當它為真時，第一個輸入必須是一個臨時狀態(tài)值并且可以因此安全地被就地修改而不是分配一個新的副本。例子可見int8inc()（雖然聚集的轉移函數總是被允許就地修改轉移值，但不鼓勵聚集的最終函數這樣做。如果最終函數要這樣做，必須在創(chuàng)建聚集時聲明這種行為。更多細節(jié)請參考CREATE AGGREGATE）。

AggCheckCallContext的第二個參數可以被用來檢索保存有聚集狀態(tài)值的內存上下文。這對希望把“擴展”對象（見第 37.13.1 節(jié)）用作狀態(tài)值的轉移函數有用。在第一次調用時，轉移函數應該返回一個擴展對象，其內存上下文是聚集狀態(tài)上下文的一個子節(jié)點，然后在后續(xù)的調用中都保持返回同一個擴展對象。示例請見 array_append()（array_append()不是任意內建聚集的轉移函數，但其編寫的目的就是在被用作一種自定義聚集的轉移函數時表現得有效）。

另一種可用于由 C 編寫的聚集函數的支持例程是AggGetAggref，它返回定義該聚集調用的Aggref解析節(jié)點。這主要對有序集聚集有用，它能檢查Aggref的子結構來找出它們本應實現的排序順序。在PostgreSQL源代碼的orderedsetaggs.c中可以找到例子。