Oracle如何实现自定义聚合函数计算客户最高优先级产品对应类别

不同数据库的用户自定义聚合函数（UDAF）实现语法存在差异，以下给出主流数据库的实现方式，同时也提供无需创建函数的通用简化方案：

通用简化方案（无函数创建权限时推荐） #

不需要创建自定义函数，只需新增一张产品权重映射表，即可简化所有查询的逻辑：

1. 新建权重映射表并写入规则：

CREATE TABLE product_weight (
  product_name VARCHAR(32) PRIMARY KEY,
  weight INT NOT NULL
);
INSERT INTO product_weight VALUES ('ISP',3),('SOP',2),('MAP',1);

2. 查询时关联该表即可，所有查询无需重复写权重判断逻辑：

SELECT 
  c.id,
  -- Oracle写法
  MAX(pw.product_name) KEEP (DENSE_RANK LAST ORDER BY pw.weight) AS category
  -- PostgreSQL/MySQL写法可替换为
  -- (ARRAY_AGG(pw.product_name ORDER BY pw.weight DESC NULLS LAST) FILTER (WHERE pw.product_name IS NOT NULL))[1] AS category
FROM customers c
LEFT JOIN products p ON p.id_customer=c.id
LEFT JOIN product_weight pw ON p.name = pw.product_name
GROUP BY c.id

各数据库自定义聚合函数实现 #

Oracle #

需要先创建聚合实现类型，再绑定到自定义聚合函数：

1. 创建聚合逻辑类型

CREATE OR REPLACE TYPE CategoryAggImpl AS OBJECT
(
  max_weight NUMBER, -- 存储当前分组的最大权重
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT CategoryAggImpl) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT CategoryAggImpl, value IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT CategoryAggImpl, ctx2 IN CategoryAggImpl) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN CategoryAggImpl, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER
);
/

2. 实现类型逻辑

CREATE OR REPLACE TYPE BODY CategoryAggImpl IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT CategoryAggImpl) RETURN NUMBER IS
  BEGIN
    sctx := CategoryAggImpl(0);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT CategoryAggImpl, value IN VARCHAR2) RETURN NUMBER IS
    current_weight NUMBER;
  BEGIN
    current_weight := CASE value WHEN 'ISP' THEN 3 WHEN 'SOP' THEN 2 WHEN 'MAP' THEN 1 ELSE 0 END;
    IF current_weight > self.max_weight THEN
      self.max_weight := current_weight;
    END IF;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT CategoryAggImpl, ctx2 IN CategoryAggImpl) RETURN NUMBER IS
  BEGIN
    IF ctx2.max_weight > self.max_weight THEN
      self.max_weight := ctx2.max_weight;
    END IF;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN CategoryAggImpl, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnValue := CASE self.max_weight WHEN 3 THEN 'ISP' WHEN 2 THEN 'SOP' WHEN 1 THEN 'MAP' ELSE NULL END;
    RETURN ODCIConst.Success;
  END;
END;
/

3. 创建最终聚合函数

CREATE OR REPLACE FUNCTION MyOwnCategory(p_product_name VARCHAR2) RETURN VARCHAR2
AGGREGATE USING CategoryAggImpl;
/

PostgreSQL #

需要分别定义迭代函数、最终转换函数，再绑定为聚合函数：

1. 定义迭代逻辑：每次传入产品名更新当前分组的最大权重

CREATE OR REPLACE FUNCTION category_agg_iter(state integer, value varchar)
RETURNS integer AS $$
BEGIN
  RETURN GREATEST(state, CASE value WHEN 'ISP' THEN 3 WHEN 'SOP' THEN 2 WHEN 'MAP' THEN 1 ELSE 0 END);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

2. 定义最终转换逻辑：把最大权重映射回产品名称

CREATE OR REPLACE FUNCTION category_agg_final(state integer)
RETURNS varchar AS $$
BEGIN
  RETURN CASE state WHEN 3 THEN 'ISP' WHEN 2 THEN 'SOP' WHEN 1 THEN 'MAP' ELSE NULL END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

3. 创建最终聚合函数

CREATE AGGREGATE MyOwnCategory(varchar) (
  SFUNC = category_agg_iter,
  STYPE = integer,
  INITCOND = '0',
  FINALFUNC = category_agg_final
);

MySQL 8.0+ #

MySQL原生SQL层面暂不支持直接创建自定义聚合函数，需要通过C/C++编写UDF编译后加载。如果不想编译二进制，也可以封装两个标量函数简化逻辑：

-- 产品名转权重
CREATE FUNCTION get_product_weight(p_name VARCHAR(32)) RETURNS INT
DETERMINISTIC
RETURN CASE p_name WHEN 'ISP' THEN 3 WHEN 'SOP' THEN 2 WHEN 'MAP' THEN 1 ELSE 0 END;

-- 权重转客户类别
CREATE FUNCTION weight_to_category(p_weight INT) RETURNS VARCHAR(32)
DETERMINISTIC
RETURN CASE p_weight WHEN 3 THEN 'ISP' WHEN 2 THEN 'SOP' WHEN 1 THEN 'MAP' ELSE NULL END;

调用时简化为：

SELECT 
  c.id, 
  weight_to_category(MAX(get_product_weight(p.name))) AS category
FROM customers c
LEFT JOIN products p ON p.id_customer=c.id
GROUP BY c.id

内容的提问来源于stack exchange，提问作者sbrbot