时间戳

首页 > 解决方案 > Oracle中自己的聚合函数

sql - Oracle中自己的聚合函数

问题描述

我的数据库中的客户可以拥有（无，一种或多种）产品（产品按重要性排序）。产品为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

如何实现这样的自定义聚合功能？

标签： sqloracleaggregate

解决方案

如果您真的想使用自定义聚合函数来解决这个问题，那么（借用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版本编写类似的代码。

推荐阅读