我的資料庫中的客戶可以擁有（無、一種或多種）產品（產品按重要性排序）。產品為ISP(3)、SOP(2)、MAP(1)。客戶的類別是根據最重要的產品定義的。因此，如果客戶例如擁有 SOP 和 ISP 產品，則他的類別是 ISP（根據客戶擁有的最重要的產品）。
為了計算客戶的類別，我必須定義我自己的專有產品訂單（重量/重要性）。我是怎么做的 - 我為我的產品分配重要性/權重系數，從所有這些（對于客戶）中取 MIN（或 MAX），最后將權重/重要性系數恢復為產品名稱：
SELECT
c.id,
DECODE(
MAX(
DECODE(p.name,'ISP',3,'SOP',2,'MAP',1) --encoding my own order (importance)
), --taking product with highest importance
3,'ISP',2,'SOP',1,'MAP') --decoding MAX important product name back
AS category
FROM customers c
LEFT JOIN products p ON p.id_customer=c.id
GROUP BY c.id
由于我在許多查詢中使用此邏輯，因此我想定義自己的聚合函式，該函式將根據客戶最重要的產品回傳類別。
我想要這樣的查詢：
SELECT
c.id,
MyOwnCategory(p.name) category
FROM customers c
LEFT JOIN products p ON p.id_customer=c.id
GROUP BY c.id
如何實作這樣的自定義聚合功能？
uj5u.com熱心網友回復：
你并不需要一個定制的聚合功能，您可以使用MAX與KEEP有適用您的訂購：
SELECT c.id,
MAX(p.name) KEEP (
DENSE_RANK FIRST
ORDER BY DECODE(p.name,'ISP',3,'SOP',2,'MAP',1) DESC NULLS LAST
) AS category
FROM customers c
LEFT JOIN products p ON p.id_customer=c.id
GROUP BY c.id
如果要定義一個函式來處理單個位置的排序，則：
CREATE FUNCTION product_name_ordering(
p_name IN PRODUCTS.NAME%TYPE
) RETURN INT DETERMINISTIC
IS
BEGIN
RETURN CASE p_name
WHEN 'ISP' THEN 3
WHEN 'SOP' THEN 2
WHEN 'MAP' THEN 1
ELSE NULL
END;
END product_name_ordering;
/
然后：
SELECT c.id,
MAX(p.name) KEEP (
DENSE_RANK FIRST
ORDER BY product_name_ordering(p.name) DESC NULLS LAST
) AS category
FROM customers c
LEFT JOIN products p ON p.id_customer=c.id
GROUP BY c.id
uj5u.com熱心網友回復：
如果您真的想使用自定義聚合函式解決此問題，那么（從https://oracle-base.com/articles/misc/string-aggregation-techniques借用），您可以執行以下操作
CREATE OR REPLACE TYPE t_max_product_agg AS OBJECT
(
g_max_product VARCHAR2(32767),
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_max_product_agg)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_max_product_agg,
value IN VARCHAR2 )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_max_product_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_max_product_agg,
ctx2 IN t_max_product_agg)
RETURN NUMBER
);
/
SHOW ERRORS
CREATE OR REPLACE TYPE BODY t_max_product_agg IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_max_product_agg)
RETURN NUMBER IS
BEGIN
sctx := t_max_product_agg(NULL);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_max_product_agg,
value IN VARCHAR2 )
RETURN NUMBER IS
BEGIN
IF CASE ODCIAggregateIterate.value WHEN 'ISP' THEN 3 WHEN 'SOP' THEN 2 WHEN 'MAP' THEN 1 ELSE NULL END > CASE SELF.g_max_product WHEN 'ISP' THEN 3 WHEN 'SOP' THEN 2 WHEN 'MAP' THEN 1 ELSE NULL END THEN
SELF.g_max_product := value;
ELSIF SELF.g_max_product IS NULL THEN
SELF.g_max_product := value;
END IF;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_max_product_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER IS
BEGIN
returnValue := SELF.g_max_product;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_max_product_agg,
ctx2 IN t_max_product_agg)
RETURN NUMBER IS
BEGIN
IF CASE ctx2.g_max_product WHEN 'ISP' THEN 3 WHEN 'SOP' THEN 2 WHEN 'MAP' THEN 1 ELSE NULL END > CASE SELF.g_max_product WHEN 'ISP' THEN 3 WHEN 'SOP' THEN 2 WHEN 'MAP' THEN 1 ELSE NULL END THEN
SELF.g_max_product := ctx2.g_max_product;
ELSIF SELF.g_max_product IS NULL THEN
SELF.g_max_product := ctx2.g_max_product;
END IF;
RETURN ODCIConst.Success;
END;
END;
/
SHOW ERRORS
CREATE OR REPLACE FUNCTION max_product (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_max_product_agg;
/
SHOW ERRORS
sql>select max_product(product) from (select 'SOP' product from dual union all select 'ISP' from dual);
MAX_PRODUCT(PRODUCT)
___________________________________________________________________________________________________________
ISP
您需要為某個MIN版本撰寫類似的代碼。
uj5u.com熱心網友回復：
我發現了如何創建自定義聚合函式：
CREATE TYPE MyCategory_impl AS OBJECT
(
cat VARCHAR2(100),
old_cat_weight NUMBER,
new_cat_weight NUMBER,
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT MyCategory_impl) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT MyCategory_impl,val IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN MyCategory_impl,returnvalue OUT VARCHAR2,flags IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT MyCategory_impl,ctx2 IN MyCategory_impl) RETURN NUMBER
);
CREATE TYPE BODY MyCategory_impl IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT MyCategory_impl) RETURN NUMBER IS
BEGIN
sctx := MyCategory_impl('MAP',0,0);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT MyCategory_impl,val IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELECT DECODE(self.cat,'ISP',3,'SOP',2,'MAP',1,0) INTO old_cat_weight FROM DUAL;
SELECT DECODE(val,'ISP',3,'SOP',2,'MAP',1,0) INTO new_cat_weight FROM DUAL;
IF new_cat_weight > old_cat_weight THEN
self.cat := val;
END IF;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN MyCategory_impl,returnValue OUT VARCHAR2,flags IN NUMBER) RETURN NUMBER IS
BEGIN
returnValue := self.cat;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT MyCategory_impl,ctx2 IN MyCategory_impl) RETURN NUMBER IS
BEGIN
RETURN ODCIConst.Success;
END;
END;
CREATE FUNCTION MyCategory (input VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING MyCategory_impl;
SELECT
c.id,
MyCategory(p.name) category
FROM customers c
LEFT JOIN products p ON p.id_customer=c.id
GROUPY BY c.id
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/346773.html
上一篇：Postgres-平均計算