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

關(guān)于聚合

聚合是 SQL 的一項(xiàng)基本功能，允許對(duì)一組記錄執(zhí)行計(jì)算或轉(zhuǎn)換，并返回單一結(jié)果。最常見的聚合函數(shù)是 SUM、AVG、COUNT、MIN 和 MAX，它們?cè)试S你分別通過計(jì)算總數(shù)、平均值、計(jì)數(shù)、最小值和最大值來快速匯總數(shù)據(jù)。

然而，SQL 提供的內(nèi)置聚合函數(shù)并不總能滿足應(yīng)用程序的特定需求。這時(shí)，創(chuàng)建自定義聚合的功能就變得非常有用。自定義聚合允許你按照自己的邏輯來匯總和轉(zhuǎn)換數(shù)據(jù)，超越了標(biāo)準(zhǔn) SQL 聚合的范圍。 這一過程通常包括定義一個(gè)狀態(tài)轉(zhuǎn)換函數(shù)，每一行都要調(diào)用該函數(shù)來更新累加器，以及定義一個(gè)可選的最終函數(shù)，調(diào)用該函數(shù)來生成最終的聚合結(jié)果。

生成轉(zhuǎn)換函數(shù)和最終函數(shù)

轉(zhuǎn)換函數(shù) array_append_state() 將為每一行調(diào)用，以更新聚合狀態(tài)。

訪問 Navicat 的函數(shù)編輯器，請(qǐng)單擊主按鈕欄中的函數(shù)按鈕，然后單擊對(duì)象工具欄中的“新建函數(shù)”：

Navicat 將從主函數(shù)定義開始。在這里，我們將提供函數(shù)名稱、輸入?yún)?shù)和主體：

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;

當(dāng)我們完成函數(shù)定義后，我們可以點(diǎn)擊“保存”來創(chuàng)建函數(shù)。

現(xiàn)在我們將返回到對(duì)象選項(xiàng)卡，并點(diǎn)擊“新建函數(shù)”來創(chuàng)建最終函數(shù)。

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

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

創(chuàng)建 comma_concat() 聚合函數(shù)

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

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

點(diǎn)擊“預(yù)覽”選項(xiàng)卡，我們就能看到生成的 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";

請(qǐng)注意，聚合體的名稱是“無標(biāo)題”。當(dāng)我們點(diǎn)擊“保存”按鈕時(shí)，Navicat 將提示我們輸入名稱，并使用我們提供的名稱執(zhí)行命令。

使用自定義聚合

現(xiàn)在，我們可以像調(diào)用其他函數(shù)一樣調(diào)用聚合函數(shù)。下面是一個(gè)按類別獲取電影列表的查詢：

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;

結(jié)語

在文章中，我們?cè)?Navicat Premium 創(chuàng)建了一個(gè)自定義 PostgreSQL 聚合，并在 DVD 租賃數(shù)據(jù)庫中使用它來將電影標(biāo)題按類別進(jìn)行連接。

產(chǎn)品試用下載、價(jià)格咨詢、優(yōu)惠獲取，或其他任何問題，請(qǐng)聯(lián)系。