PostgreSQL 的突出特點之一是廣泛支持用戶定義的函數和數據類型。這允許開發人員創建自定義轉換、運算符和聚合函數。聚合函數是對數據執行復雜計算和轉換的有效方式，超越了 SUM、AVG 和 COUNT 等標準 SQL 聚合函數。Navicat for PostgreSQL （最新版下載試用）和 Navicat Premium （最新版下載試用）都有專門的圖形用戶界面（GUI），可以輕松編寫與數據庫無縫集成的自定義函數和聚合。我們只需提供一些細節，Navicat 就會為我們生成 pgSQL 語句！在本文中，我們將創建一個在 DVD Rental database 中使用的聚合，按類別連接電影標題。

關于聚合

聚合是 SQL 的一項基本功能，允許對一組記錄執行計算或轉換，并返回單一結果。最常見的聚合函數是 SUM、AVG、COUNT、MIN 和 MAX，它們允許你分別通過計算總數、平均值、計數、最小值和最大值來快速匯總數據。

然而，SQL 提供的內置聚合函數并不總能滿足應用程序的特定需求。這時，創建自定義聚合的功能就變得非常有用。自定義聚合允許你按照自己的邏輯來匯總和轉換數據，超越了標準 SQL 聚合的范圍。 這一過程通常包括定義一個狀態轉換函數，每一行都要調用該函數來更新累加器，以及定義一個可選的最終函數，調用該函數來生成最終的聚合結果。

生成轉換函數和最終函數

轉換函數 array_append_state() 將為每一行調用，以更新聚合狀態。

訪問 Navicat 的函數編輯器，請單擊主按鈕欄中的函數按鈕，然后單擊對象工具欄中的“新建函數”：

Navicat 將從主函數定義開始。在這里，我們將提供函數名稱、輸入參數和主體：

CREATE FUNCTION "public"."array_append_state" (current_state text[], new_value text)
 RETURNS text[] AS $BODY$
BEGIN
 RETURN array_append(current_state, new_value);
END
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

當我們完成函數定義后，我們可以點擊“保存”來創建函數。

現在我們將返回到對象選項卡，并點擊“新建函數”來創建最終函數。

array_to_comma_string() 函數將獲取一個電影題目數組，并在每個元素之間插入逗號：

CREATE FUNCTION "public"."array_to_comma_string" (state text[])
 RETURNS text AS $BODY$
BEGIN
 RETURN array_append(state, ', ');
END
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

創建 comma_concat() 聚合函數

現在我們可以將兩個函數插入 Navicat 的聚合編輯器。我們可以通過單擊主按鈕欄中的“其它”按鈕，然后從彈出的菜單中選擇“聚合”來訪問編輯器：

在表單中，我們將輸入類型設置為“文本”，輸入狀態類型為“text[]”，并提供狀態和最終函數。同時，確保初始條件是一個空數組（“{}”）：

點擊“預覽”選項卡，我們就能看到生成的 SQL：

CREATE AGGREGATE "public"."Untitled" (In "pg_catalog"."text")
(
 SFUNC = "public"."array_append_state",
 STYPE = "pg_catalog"."text[]",
 FINALFUNC = "public"."array_to_comma_string",
 INITCOND = "{}",
 PARALLEL = UNSAFE
);
ALTER AGGREGATE "public"."Untitled"("pg_catalog"."text") OWNER TO "postgres";

請注意，聚合體的名稱是“無標題”。當我們點擊“保存”按鈕時，Navicat 將提示我們輸入名稱，并使用我們提供的名稱執行命令。

使用自定義聚合

現在，我們可以像調用其他函數一樣調用聚合函數。下面是一個按類別獲取電影列表的查詢：

SELECT
   c.name AS category,
   comma_concat(f.title) AS movies
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN film f ON fc.film_id = f.film_id
GROUP BY c.name
ORDER BY c.name;

結語

在文章中，我們在 Navicat Premium 創建了一個自定義 PostgreSQL 聚合，并在 DVD 租賃數據庫中使用它來將電影標題按類別進行連接。

產品試用下載、價格咨詢、優惠獲取，或其他任何問題，請聯系。