除了內建的 Aggregation 之外，Postgres 提供自訂 Aggregation 的方式，可以自訂自己需要的條件的 Aggregation。
但是在建立 Aggregation 函數之前，首先要先好好了解一點 Aggregation 的概念。
一個 Aggregation 函數，由狀態值（State Value）與狀態躍遷函數（state transition function）所組成。
Aggregation 內部會紀錄當前運算狀態值，然後每次吃一個資料值，透過狀態躍遷函數運算，算出新狀態。然後對整筆資料用迴圈運算。
自訂 Aggregation 和一般自訂函數的差異：一般函數填寫一個欄位當參數時，作用是將逐筆資料用函數進行操作，而 Aggregation 函數則是整個欄位的資料一起拿來演算。因此在 Aggregation 函數內，還能夠增加一些修飾字，如 SUM(DISTINCT col_name)；而一般函數則不允許這種指令。
在 PGSQL 9.6，Aggregation 有以下類型（我看得懂的～）
- 一般 Aggregate：資料吃進來，算一算，跑出一個結果就對了
- Moving Aggregate：概念如同移動平均（Moving Average），指的是固定筆數資料就進行一個 Aggregation 運算
- Ordered-set Aggregate：可以用 ORDER BY 修飾字。這類計算中，資料排序是重點
- Partial Aggregate：指的是運算上可以先拆分不同小筆資料運算完再合併也沒關係的運算。因此能夠把參數中欄位的資料拆分數筆集合，用多個執行緒分別計算完，再把各級和計算結果處理成最後輸出值。這種 Aggregate 類型便能支援多執行緒（PGSQL 9.6 引入的功能）
其中 Partial Aggregate 是 PGSQL 9.6 才有的功能。
在自訂 Aggregate 時，能使用下面的指定狀態函數類型實作出上述的功能
- sfunc：每個 Aggregate 的狀態躍遷函數。吃（Set-Valued Function）下一筆資料，和當前狀態值一起運算後，變成下一步驟的狀態值。格式為雙值函數，第一個參數是當前狀態值，第二個參數是下一筆資料值。
- ffunc：狀態躍遷函數運算完，還要進行後續加工，例如算平均時在加總後的除法運算
- msfunc：Moving Aggregate 使用的函數
- combinefunc：Partial Aggregate 使用的函數
而 Order-Set Aggregation 則以語法宣告達成。
知道以上的資訊，就足夠弄懂下面的範例了～
下面將建立的範例，把一個欄位的多筆字串資料值，轉換成單一筆使用逗號分隔的資料。
在之前的筆記中，有用過以下的函數，把多筆的單欄位資料，用逗號分隔的方式呈現
在之前的筆記中，有用過以下的函數，把多筆的單欄位資料，用逗號分隔的方式呈現
edb=# select array_to_string( array_agg(ename), ',') from emp;
array_to_string
-------------------------------------------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
(1 row)
edb=# select array_to_string( array_agg(distinct ename), ',') from emp;
array_to_string
-------------------------------------------------------------------------------------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD
(1 row)
edb=# select array_to_string( array_agg(distinct ename), ',') from emp group by job;
array_to_string
--------------------------
FORD,SCOTT
ADAMS,JAMES,MILLER,SMITH
BLAKE,CLARK,JONES
KING
ALLEN,MARTIN,TURNER,WARD
(5 rows)
可是如果直接把這個函數塞到 SQL Function 裡面會有問題
edb=# CREATE FUNCTION arr2str_concat_try(value Varchar2)
RETURNS Varchar2 AS $$
SELECT array_to_string( array_agg(value), ',');
$$ LANGUAGE SQL;
CREATE FUNCTION
edb=# SELECT arr2str_concat_try(ename) FROM emp;
arr2str_concat_try
--------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
(14 rows)
edb=# SELECT arr2str_concat_try(distinct ename) FROM emp;
ERROR: DISTINCT specified, but arr2str_concat_try is not an aggregate function
LINE 1: SELECT arr2str_concat_try(distinct ename) FROM emp;
^
結果執行的不如預期，還告知這函數不是 Aggregation 函數，因此把該功能直接包裝成函數是行不通的。
原因是因為這不是狀態躍遷函數～只會一筆筆資料餵進去函數，沒辦法進行 Aggregation。
於是，現在利用自訂 Aggregation 函數來進行 DIY，做出一樣的功能。但因為上面使用的函數沒辦法直接使用，因此要自己處理～
edb=# CREATE FUNCTION my_concat(state varchar2, value varchar2)
RETURNS varchar2 AS $$
SELECT state||','||value;
$$ LANGUAGE SQL;
CREATE AGGREGATE str_concat (Varchar2)
(
sfunc = my_concat,
stype = Varchar2,
initcond = ''
);
CREATE FUNCTION
edb=# select str_concat(distinct ename) from emp;
str_concat
--------------------------------------------------------------------------------------
,ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD
(1 row)
edb=# select str_concat(distinct ename) from emp group by job;
str_concat
---------------------------
,FORD,SCOTT
,ADAMS,JAMES,MILLER,SMITH
,BLAKE,CLARK,JONES
,KING
,ALLEN,MARTIN,TURNER,WARD
(5 rows)
上面使用 Query Language 函數處理，把每次吃進來的狀態值，接到現存字串的後面。然後宣告 Aggregate 的資訊時，指定初始值為空字串，吃進來的狀態值的資料型態為 EDB 企業版相容的 VARCHAR2 型態。
上面的結果看來快要成功了，差在每次結果前面都有逗號，因此稍微使用 CASE . . . WHEN 調整一下內容：
edb=# CREATE OR REPLACE FUNCTION my_concat(state varchar2, value varchar2)
RETURNS varchar2 AS $$
SELECT
CASE WHEN state=''
THEN value
ELSE
concat_ws(',', state, value) -- concat_ws() 函數等同 state||','||value
END;
$$ LANGUAGE SQL;
CREATE AGGREGATE str_concat (Varchar2)
(
sfunc = my_concat,
stype = Varchar2,
initcond = ''
);
CREATE FUNCTION
edb=# select str_concat(ename) from emp;
str_concat
-------------------------------------------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER
(1 row)
edb=# select str_concat(distinct ename) from emp;
str_concat
-------------------------------------------------------------------------------------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD
(1 row)
edb=# select str_concat(distinct ename) from emp group by job;
str_concat
--------------------------
FORD,SCOTT
ADAMS,JAMES,MILLER,SMITH
BLAKE,CLARK,JONES
KING
ALLEN,MARTIN,TURNER,WARD
(5 rows)
大功告成！
最後提醒一下，建立自訂 Aggregation，只要有辦法建立所需要的狀態躍遷函數就能產生，並不限定使用那一種 Stored Procedure，因此可以使用 PL/Python 或 PL/R 等實現
參考資料：
沒有留言:
張貼留言