PostgreSQL CREATE AGGREGATE

描述

CREATE AGGREGATE定義一個新的聚集函數(shù)。CREATE OR REPLACE AGGREGATE將定義新的聚合函數(shù)或替換現(xiàn)有定義。 在發(fā)布中已經(jīng)包括了一些基本的和常用的聚集函數(shù)；它們的文檔請見第 9.21 節(jié)。 如果要定義一個新類型或者需要一個還沒有被提供的聚集函數(shù)，那么CREATE AGGREGATE就可以被用來提供想要的特性。

在替換現(xiàn)有定義時(shí)，參數(shù)類型、結(jié)果類型和直接參數(shù)的數(shù)量可能不會更改。 此外，新定義的類型（普通聚合、有序集聚合或假設(shè)集聚合）必須與舊定義相同。

如果給定了一個模式名（例如CREATE AGGREGATE myschema.myagg ...），那么該聚集會被創(chuàng)建在指定的模式中。否則它 會被創(chuàng)建在當(dāng)前模式中。

一個聚集函數(shù)需要用它的名稱和輸入數(shù)據(jù)類型標(biāo)識。同一個模式中的兩個聚集 可以具有相同的名稱，只要它們在不同的輸入類型上操作即可。一個聚集的名稱 和輸入數(shù)據(jù)類型必須與同一模式中的每一個普通函數(shù)區(qū)分開。這種行為與普通函 數(shù)名的重載完全一樣（見CREATE FUNCTION）。

一個簡單的聚集函數(shù)由一個或者多個普通函數(shù)組成： 一個狀態(tài)轉(zhuǎn)移函數(shù) sfunc和一個可選的最終 計(jì)算函數(shù) ffunc。 它們被這樣使用：

sfunc( internal-state, next-data-values ) ---> next-internal-state
ffunc( internal-state ) ---> aggregate-value

PostgreSQL創(chuàng)建一個數(shù)據(jù)類型 stype的臨時(shí)變量來 保持聚集的當(dāng)前內(nèi)部狀態(tài)。對每一個輸入行，聚集參數(shù)值會被計(jì)算并且狀態(tài) 轉(zhuǎn)移函數(shù)會被調(diào)用，它用當(dāng)前狀態(tài)值和新參數(shù)值計(jì)算一個新的內(nèi)部狀態(tài)值。 等所有行都被處理完后，最終函數(shù)會被調(diào)用一次來計(jì)算該聚集的返回值。如果 沒有最終函數(shù)，則最終的狀態(tài)值會被返回。

一個聚集函數(shù)可以提供一個初始條件，即一個用于內(nèi)部狀態(tài)值的初始值。它被 作為一個類型text的值指定并且存儲在數(shù)據(jù)庫中，但是它必須 是狀態(tài)值數(shù)據(jù)類型的一個常量的合法外部表示。如果沒有提供，則狀態(tài)值從空值 開始。

如果狀態(tài)轉(zhuǎn)移函數(shù)被聲明為“strict”，那么不能用空值輸入來 調(diào)用它。如果有這種轉(zhuǎn)移函數(shù)，聚集將按照下面的行為執(zhí)行。帶有任何空值的 行會被忽略（函數(shù)不被調(diào)用并且之前的狀態(tài)值被保持）。如果初始狀態(tài)值就是 空值，那么碰到第一個沒有空值的行時(shí)，狀態(tài)值會被替換成第一個參數(shù)值，并且 對于每一個后續(xù)的沒有空值的行都會調(diào)用該轉(zhuǎn)移函數(shù)。這對實(shí)現(xiàn) max這樣的聚集很方便。注意只有當(dāng) state_data_type 和第一個 arg_data_type相同時(shí)， 這種行為才可用。當(dāng)這些類型不同時(shí)，你必須提供一個非空初始條件或者使用 一個非嚴(yán)格轉(zhuǎn)移函數(shù)。

如果狀態(tài)轉(zhuǎn)移函數(shù)不是嚴(yán)格的，那么在碰到每個輸入行時(shí)都將會調(diào)用它，并且 它必須自行處理空值輸入和空狀態(tài)值。這允許聚集的作者完全控制該聚集如何 處理空值。

如果最終函數(shù)被聲明為“strict”，那么當(dāng)最終狀態(tài)值為空時(shí)將 不會調(diào)用它，而是自動地返回一個空結(jié)果（當(dāng)然，這只是嚴(yán)格函數(shù)的普通行為）。 在任何情況下最終函數(shù)都可以返回一個空值。例如，avg的最終函數(shù)會在看到零個 輸入行時(shí)返回空。

有時(shí)候把最終函數(shù)聲明成不僅采用狀態(tài)值還采用對應(yīng)于聚集輸入值的額外參數(shù) 是有用的。這樣做的主要原因是，如果最終函數(shù)是多態(tài)的，那么狀態(tài)值的數(shù)據(jù) 類型將不適合于用來確定結(jié)果類型。這些額外的參數(shù)總是以 NULL 形式傳遞 （因此使用FINALFUNC_EXTRA選項(xiàng)時(shí)，最終函數(shù)不能是嚴(yán)格的）， 但盡管如此它們都是合法參數(shù)。例如，最終函數(shù)可以利用 get_fn_expr_argtype來標(biāo)識當(dāng)前調(diào)用中的實(shí)際參數(shù)類型。

如第 37.12.1 節(jié)中所述，一個聚集可以 選擇支持moving-aggregate mode。這要求指定 MSFUNC、MINVFUNC以及 MSTYPE參數(shù)，并且參數(shù)MSSPACE、 MFINALFUNC、MFINALFUNC_EXTRA 和MINITCOND是可選的。除了MINVFUNC， 這些參數(shù)的工作都和對應(yīng)的不帶M的簡單聚集參數(shù)相似，它們 定義了包括一個逆向轉(zhuǎn)移函數(shù)的聚集的一種獨(dú)立實(shí)現(xiàn)。

在參數(shù)列表中帶有ORDER BY的語法會創(chuàng)建一種被稱為 有序集聚集的特殊聚集類型。如果指定了 HYPOTHETICAL，則會創(chuàng)建一個 假想集聚集。這些聚集以依賴排序的方法在排好序 的值上操作，因此指定一個輸入排序順序是調(diào)用過程的重要一環(huán)。還有，它們 可以有直接參數(shù)，這類參數(shù)只對每次聚集計(jì)算一次，而不是對 每一個輸入行計(jì)算一次。假想集聚集是有序集聚集的一個子類，其中一些直接 參數(shù)要求在數(shù)量和類型上都匹配被聚集的參數(shù)列。這允許這些直接參數(shù)的值被 當(dāng)做一個附加的“假想”行被加入到聚集輸入行的集合中。

如第 37.12.4 節(jié)中所示，一個聚集可以支持 部分聚集。這要求指定COMBINEFUNC參數(shù)。 如果state_data_type 為 internal，通常也可以提供SERIALFUNC和 DESERIALFUNC參數(shù)，這樣可以讓并行聚集成為可能。注意， 該聚集還必須被標(biāo)記為PARALLEL SAFE以啟用并行聚集。

行為與MIN或MAX相似的聚集有時(shí)可以通過 直接查看一個索引而不是掃描每一個輸入行來優(yōu)化。如果這個聚集可以被這樣 優(yōu)化，請通過指定一個排序操作符來指出?；疽笫牵?聚集必須得出由該操作符產(chǎn)生的排序順序中的第一個元素，換句話說：

SELECT agg(col) FROM tab;

必須等價(jià)于：

SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;

進(jìn)一步的假定是該聚集忽略空輸入，并且當(dāng)且僅當(dāng)沒有非空輸入時(shí)它才會返回 一個空結(jié)果。通常，一種數(shù)據(jù)類型的<操作符是 MIN的合適的排序操作符，而>是 MAX的合適的排序操作符。注意，除非指定的操作符是一個 B-樹索引操作符類的“小于”或者 “大于”策略成員，優(yōu)化將永遠(yuǎn)不會產(chǎn)生實(shí)際效果。

要能夠創(chuàng)建一個聚集函數(shù)，你必須具有參數(shù)類型、狀態(tài)類型和返回類型上的 USAGE特權(quán)，還有在支持函數(shù)上的 EXECUTE特權(quán)。

參數(shù)

CREATE AGGREGATE的參數(shù)可以用任意順序書寫， 而無需遵照以上說明的順序。

注解

在指定支持函數(shù)名的參數(shù)中，如果需要你可以寫一個模式名，例如 SFUNC = public.sum。在這里不能寫參數(shù)類型 — 支持函數(shù) 的參數(shù)類型是根據(jù)其他參數(shù)決定的。

通常，PostgreSQL函數(shù)是不要修改輸入值的真函數(shù)。然而，一個聚合遷移函數(shù)在聚合上下文中使用時(shí)被允許詐欺并修改已在遷移狀態(tài)的參數(shù)。因?yàn)榕c每次創(chuàng)建一個遷移狀態(tài)的新的拷貝相比，這樣可以提供實(shí)質(zhì)的性能提升。     

同樣，雖然人們一般不期望聚合最終函數(shù)修改它的輸入值，但有時(shí)回避修改遷移態(tài)參數(shù)是不切實(shí)際的。這種行為必須使用FINALFUNC_MODIFY參數(shù)聲明。READ_WRITE值表示最終函數(shù)以某種未指定的方式修改了遷移狀態(tài)值。這個值防止將聚合用作窗口函數(shù)，并且還可以防止因共用相同的輸入值和遷移函數(shù)的聚合調(diào)用而合并遷移狀態(tài)。SHAREABLE值表示過渡函數(shù)不能在最終功能之后使用用，但多重最終函數(shù)調(diào)用可以對最終的遷移狀態(tài)值執(zhí)行調(diào)用。這個值阻止將聚合用作窗口函數(shù)，但允許合并過渡狀態(tài)。（也就是說，此處所關(guān)注的優(yōu)化不是重復(fù)地使用相同的最終函數(shù)，而是把不同的最終函數(shù)應(yīng)用到相同的最終遷移狀態(tài)值。只要所有最終功能都沒有標(biāo)記為 READ_WRITE就被允許。）

如果一個聚集支持移動聚集模式，當(dāng)該聚集被用于一個具有移動幀起點(diǎn)（即幀起點(diǎn) 模式不是UNBOUNDED PRECEDING）的窗口函數(shù)時(shí)，它將提升計(jì) 算效率。在概念上，當(dāng)從底部進(jìn)入窗口幀時(shí)前向轉(zhuǎn)移函數(shù)會把輸入值加到聚集的狀 態(tài)上，而逆向轉(zhuǎn)移函數(shù)會在從頂部離開幀時(shí)再次移除輸入值。因此，當(dāng)值被移除時(shí)， 它們總是按照被加入的相同順序被移除。無論何時(shí)調(diào)用逆向轉(zhuǎn)移函數(shù)，它都將因此 接收最近增加但是還未被移除的參數(shù)值。逆向轉(zhuǎn)移函數(shù)可以假定在它移除最舊的行 之后至少有一行保留在當(dāng)前狀態(tài)中（當(dāng)情況不是這樣時(shí)，窗口函數(shù)機(jī)制會簡單地開 始一次新的聚集，而不是使用逆向轉(zhuǎn)移函數(shù)）。

用于移動聚集模式的前向轉(zhuǎn)移函數(shù)不允許返回 NULL 作為新的狀態(tài)值。如果逆向 轉(zhuǎn)移函數(shù)返回 NULL，這表明逆向函數(shù)無法為這個特定的輸入逆轉(zhuǎn)狀態(tài)計(jì)算，并且 該聚集計(jì)算因此必須從當(dāng)前幀的開始位置“從零開始”重新被計(jì)算。在一些少見的情 況中，逆轉(zhuǎn)正在計(jì)算中的狀態(tài)值是不切實(shí)際的，這種習(xí)慣可以允許在此類情形中使用 移動聚集模式。

如果沒有提供移動聚集實(shí)現(xiàn)，聚集仍然可以被用于移動幀，但是 只要幀起點(diǎn)移動，PostgreSQL都將會重新計(jì)算 整個聚集。注意不管聚集有沒有支持移動聚集模式， PostgreSQL都能處理一個移動幀結(jié)束而無需重 新計(jì)算，這可以通過增加新值到聚集狀態(tài)完成。這就是為什么使用聚合作為窗口函數(shù)需要最終函數(shù)只讀的原因。人們認(rèn)為最終函數(shù)不能破壞聚集的狀 態(tài)值，這樣即使已經(jīng)為一組幀邊界得到了一個聚集結(jié)果值，該聚集也能繼續(xù)下去。

有序集聚集的語法允許為最后一個直接參數(shù)以及最后一個聚集（ WITHIN GROUP）參數(shù)指定VARIADIC。但是，當(dāng)前的 實(shí)現(xiàn)限制只能以兩種方式使用VARIADIC。第一種，有續(xù)集聚集只能 使用VARIADIC "any"，不能使用其他可變數(shù)組類型。第二種，如果最 后一個直接參數(shù)是 VARIADIC "any"，那么只能有一個聚集參數(shù)并且它 也必須是VARIADIC "any"（在系統(tǒng)目錄中使用的表示中，這兩個參數(shù) 會被合并為一個單一的VARIADIC "any"項(xiàng)，因?yàn)?pg_proc無法表示具有超過一個VARIADIC參數(shù)的 函數(shù)）。如果該聚集是一個假想集聚集，匹配VARIADIC "any"參數(shù)的 直接參數(shù)就是假想?yún)?shù)。任何在前面的參數(shù)表示額外的直接參數(shù)，它們不被約束為需 要匹配聚集參數(shù)。

當(dāng)前，有序集聚集無須支持移動聚集模式，因?yàn)樗鼈儾荒鼙挥米鞔翱诤瘮?shù)。

部分（包括并行）聚集當(dāng)前不被有續(xù)集聚集支持。還有，包括 DISTINCT或者ORDER BY子句的聚集調(diào)用將不會使用 部分聚集，因?yàn)樵诓糠志奂瘯r(shí)無法支持那些語義。

示例

見第 37.12 節(jié)。

兼容性

CREATE AGGREGATE是一種 PostgreSQL的語言擴(kuò)展。SQL 標(biāo)準(zhǔn)沒有提供 用戶定義的聚集函數(shù)。

另見