PostgresSQL 的自定义聚合函数

这一章想写自定义聚合函数，主要是想起来刚入这行两年多的时候，遇到个面试问我取一行完整的数据，需要用到什么类型。当时是半路出家，没遇到过这种场景啊。我记得当时先回的any之类的泛（伪）类型数据然后又回答了一个自定义数据类型，最后给我的答案是record的类型，我当时一愣，这他喵不还是伪类型吗？其实主要是这玩意以前没使用过不敢答，当时用的更多的是结构固定的自定义数据类型，还主要是因为要写自定义聚合函数要用到这玩意。

说起来这个事主要是为了引出今天的话题： 自定义聚合函数。当时我觉得这个难爆了，虽然能按照需求写出来函数，但是还是不太清楚初始函数和FF函数中间是如何通过自定义数据类型转化的。当时又没有ai，遇到问题只能自己去查，不仅资料少，而且有些地方讲的还是乱七八糟的。就这样一路将就着，好几年之后才慢慢搞懂里面的逻辑。

好，下面正式开始今天的话题,下面的车轱辘话还是我青葱年少的时候写的🙈：

自定义聚合函数

一、应用场景和优缺点概述

1. 应用场景

说到自定义聚合函数，就要先说聚合函数，聚合函数在数据库中扮演着汇总和二次计算的作用，常见的自定义聚合函数包括：SUM、AVG、COUNT 等。但是常规的聚合函数并不能满足相对于某些复杂的生产需要，如金融业业务里各种复杂利率计算，传统工业里各种传感器的采集数据，判断异常值并剔除，二次整合等使用复杂算法对原始数据进行二次运算的业务。这个时候就需要自定义聚合函数出场了。

2.优缺点概览

优点:

1.灵活性：自定义聚合函数允许你根据具体业务需求实现复杂的聚合逻辑，超越了内置聚合函数的局限性。例如，你可以实现加权平均值、字符串连接、复杂的统计计算等。

2.性能优化：通过自定义聚合函数，你可以在数据库层面进行复杂计算，减少了从数据库传输大量数据到应用层进行处理的开销，从而提高整体系统性能3.代码重用和维护：将复杂的聚合逻辑封装在数据库函数中，可以减少应用程序代码的重复，简化代码维护。此外，聚合逻辑的变更只需在数据库中修改.而无需更改应用程序代码。版本迭代也比较容易。

4.一致性：在数据库层面实现复杂的聚合逻辑，可以确保所有相关查询使用相同的逻辑，保证数据处理的一致性。

3.代码重用和细分：聚合逻辑的更改只需更新数据库而无需更改应用程序代码，重新部署。版本迭代也比较容易。简化代码维护。

4.一致性：在数据库层面实现复杂的聚合逻辑，可以确保所有相关查询使用相同的逻辑，保证数据处理的一致性。

缺点:

1.学习曲线：定义和调试自定义聚合函数需要熟悉PostgreSQL的函数和聚合机制，对开发人员提出了较高的技术要求，增加了学习和开发的时间成本，普通的开发人员一般不会这个。

2.调试困难：数据库层面的错误往往不如应用层错误容易调试，尤其是当聚合函数逻辑复杂时，可能需要更多的调试时间和技巧，这里可没有断点调试啥的。

3.性能瓶颈：虽然自定义聚合函数可以提高性能，但在某些情况下，复杂的聚合逻辑可能导致数据库性能瓶颈。需要谨慎设计和测试，确保不会对数据库造成过大负担。简单来说：写这个更需要很好的sq1优化能力，菜的话不仅不会提高性能还可能压垮数据库服务器。

4.版本难管控：数这个和普通的sql类似，修改只需要在数据库执行下sql即可调整，而且也没有单独回滚这一说，而且看函数使用场景，如果是业务用来整合进行一次入库，可能还需要对污染的数据进行二次清洗。一定要做好数据库版本程序的管控。

二、 聚合函数结构组成详解

1、聚合结构概述(AGGREGATE)：

自定义聚合函数，一般有三部分组成，sf状态转换函数用来做数据统计和初步处理，中间态转换传输数据，ff函数用来进行最后的公式计算，通过他的创建方法页可以见一斑。sf和ff函数是普通的函数，stype可以是自定义数据类型，也可以是传统的数据类型。

CREATE AGGREGATE my_avg(numeric) (  
SFUNC = avg_sfunc,  －－数据一条条的统计整合
STYPE = avg_state,  －－中间态数据转换
FINALFUNC = avg_final －－最终函数进行运算 
);

其实说sf负责转换也不太对，准确说应该是sf和中间态数据配合进行转换，sf函数每行数据运行一次，每运行一次就更新一次中间态数据，等运行完所有行了，再把结果交给最终函数进行演算。结构图类似这种：

注： 无论是官方的还是自定义的聚合函数，函数本身永远是不管的分组的，分组是由group by来完成。函数只负责逐行处理过滤出来的数据。

2、 自定义数据类型（STYPE）：

说起来聚合函数，那自定义数据类型就是不可不尝的一环。自定义数据类型是承载聚合过程中产生的复杂中间态数据。虽然聚合函数也可以使用传统数据类型进行承载，但是这种仅限于聚合态是单值的时候。对于要上自定义聚合的场景来说，如果是夹杂了业务需求算法，单值是大概率不太够的，这个时候就需要自定义数据类型上场了。

i.composite 类型

自定义数据类型也有好几种，比如 composite, enum, domain, internal。我们在这里只说最常见的composite类型（最常见，最容易学习），语法结构很像java的对象声明，如下所示：

create type my_type as (
param1 int,
param2 float,
param3 varchar(20),
param4 text
)

ii.附加 hstore 类型

附加：这里再介绍一个聚合函数常用的扩展数据类型hstore，hstore 是 PostgreSQL 的一个扩展模块，提供了一种在单列中存储任意键值对（key-value pair）的数据类型。键和值都是文本字符串，非常适合存储“属性集”“标签组”或“自定义字段”这类结构不固定、但深度较浅的半结构化数据。（其实这个数据类型才是最常用的）下面是使用方法：

create extension hstore; --如果没开的话

create type my_type as (
param1 int,
param2 hstore  --使用方法
);  

-- 键值编辑判断方式

=>         创建单键值对  eg: SELECT 'name' => 'Alice'    输出：  "name"=>"Alice"
||         添加/更新键值对 eg: 'age=>30'::hstore || 'name=>Bob'::hstore   输出： "age"=>"30","name"=>"Bob"
-          删除键值对 eg: 'a=>1,b=>2'::hstore - 'a'      输出：  "b"=>"2"

注：使用 || 运算符连接 hstore 时，如果右侧的键在左侧已存在，其值会覆盖左侧的值；如果键不存在，则会添加新的键值对。


->         按键取值   eg：'a=>x, b=>y'::hstore -> 'a'   输出：x (text)
->         获取多个键的值   eg: 'a=>x, b=>y'::hstore -> ARRAY['b','a']   输出： {"y","x"}
akeys()    获取所有键    eg: akeys('a=>x, b=>y'::hstore)   输出：{a,b}
avals()    获取所有值    eg:avals('a=>x, b=>y'::hstore)    输出：{x,y}

?          键是否存在    eg: 'a=>1'::hstore ? 'a'     输出：t (true)
?&         包含所有指定键    eg: 'a=>1,b=>2'::hstore ?& ARRAY['a','b']     输出：t (true)
?|         包含任一指定键    eg: 'a=>1,b=>2'::hstore ?| ARRAY['b','c']    输出：t (true)
@>         检查包含关系 (A ⊇ B)   eg: 'a=>1,b=>2'::hstore @> 'b=>2'     输出：t (true)

hstore_to_jsonb()  转换为JSONB  hstore_to_jsonb('a=>x, b=>y'::hstore) {"a": "x", "b": "y"}

PS： 如果数据库的内核版本再pg12之上的话，其实可以考虑使用自带的json或者jsonb类型了，12版本之后json的数据类型已经优化的相当可以了，而且支持多样的方法。但是如果是旧版本，或者现存场景中有大量的hstore，那就当我没说。

PPS： 其实也有hstore到jsonb的数据转换函数，如果实在看着碍眼，可以上这个函数： hstore 与 JSON/JSONB 的相互转换

转化方式	描述	示例	结果
::	直接转化	'a=>1, b=>hello'::hstore::json	{"a": "1", "b": "hello"}
hstore_to_json(hstore)	函数转化，val全是字符串	hstore_to_json('a=>1, b=>hello'::hstore)	{"a": "1", "b": "hello"}
hstore_to_json_loose(hstore)	智能转换：数字和布尔值会转为 JSONB 原生类型	hstore_to_json_loose('a=>1, b=>hello'::hstore)	{"a": "1", "b": "hello"}

如果需要保留数字类型，推荐使用宽松转换函数；如果是临时查询，用 ::jsonb 强制转换即可。反过来从 JSONB 转 hstore 时，需提前将数值处理为字符串。

3、 sf & ff 函数（SFUNC & FINALFUN）：

i.函数内部常见操作语法

其实sf和ff函数都是普通的函数，基本没啥讲的，只要会写普通函数，就会写这两种函数，格式是一模一样的，我在这里列下常见的写法，像是 与或非，赋值，case条件，if判断，loop循环等。

功能	句法
与	AND
或	OR
非	NOT
变量赋值	var := value;
条件判断	IF ... THEN ... ELSIF ... ELSE ... END IF;
多分支（返回值）	CASE WHEN ... THEN ... ELSE ... END
多分支（执行）	CASE ... WHEN ... THEN ... END CASE;
无限循环	LOOP ... EXIT WHEN ... END LOOP;
条件循环	WHILE ... LOOP ... END LOOP;
计数循环	FOR i IN 1..10 LOOP ... END LOOP;
遍历结果集	FOR record IN SELECT ... LOOP ... END LOOP;

|打印信息 |RAISE NOTICE 'msg: %', var;| |抛出异常 |RAISE EXCEPTION 'error msg';| |执行无返回 |PERFORM func();| |获取影响行数 |GET DIAGNOSTICS var = ROW_COUNT;|

下面是句式的示例：

--1. 判断

IF age < 18 THEN
    status := 'minor';
ELSIF age < 60 THEN
    status := 'adult';
ELSE
    status := 'senior';
END IF;

--2. 简单case语句

-- 常规case
status := CASE age
    WHEN 18 THEN 'just adult'
    WHEN 60 THEN 'just senior'
    ELSE 'other'
END;

-- 搜索式 CASE
score_desc := CASE 
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'D'
END;

-- 分支式 CASE
CASE 
    WHEN status = 'active' THEN 
        PERFORM active_handler();
    WHEN status = 'pending' THEN
        UPDATE users SET status = 'active';
    ELSE
        RAISE NOTICE 'Unknown status';
END CASE;

--3. while循环

WHILE counter < 10 LOOP
    counter := counter + 1;
    -- 执行操作
END LOOP;

--4. for循环

-- 正向循环（包含终止值）
FOR i IN 1..10 LOOP
    RAISE NOTICE 'i: %', i;
END LOOP;

-- 反向循环
FOR i IN REVERSE 10..1 LOOP
    RAISE NOTICE 'i: %', i;
END LOOP;

-- 带步长（实际需用其他方式，PostgreSQL原生不支持STEP语法）
FOR i IN 1..10 LOOP
    IF MOD(i, 2) = 0 THEN
        CONTINUE;
    END IF;
    -- 相当于步长为2
END LOOP;

--5. 遍历循环

-- 遍历查询结果
FOR user_record IN SELECT id, name FROM users WHERE active = true LOOP
    RAISE NOTICE 'User: %, %', user_record.id, user_record.name;
END LOOP;

-- 遍历数组
FOREACH val IN ARRAY ARRAY[1,2,3,4] LOOP
    RAISE NOTICE 'val: %', val;
END LOOP;

--6. 无限循环loop

LOOP
    counter := counter + 1;
    EXIT WHEN counter > 10;  -- 退出条件
    -- 或 EXIT; 直接退出
    -- 或 CONTINUE WHEN counter = 5;  -- 跳过本次
END LOOP;

--7. 与或非拼接

IF condition1 AND condition2 THEN ... END IF;

-- OR（或）
IF condition1 OR condition2 THEN ... END IF;

-- NOT（非）
IF NOT condition THEN ... END IF;

-- 组合示例
IF (a > 10 AND b < 20) OR (c IS NULL) THEN
    -- 条件体
END IF;

-- 与 NULL 的比较注意事项
IF value IS NULL THEN ... END IF;      -- 正确
IF value = NULL THEN ... END IF; 

--8. 信息弹出

-- 打印通知
RAISE NOTICE 'Processing user: %', user_name;

-- 调试信息
RAISE DEBUG 'Variable value: %', var_value;

-- 抛出异常
RAISE EXCEPTION 'Invalid input: %', input_value;

-- 预定义异常码
RAISE EXCEPTION 'User not found' USING ERRCODE = 'P0001';

ii.函数架构和各模块讲解示例

下面是函数整体的句式和自定义聚合下注意要点

--1.函数标准结构

-- 标量函数（返回单个值）
CREATE OR REPLACE FUNCTION func_name(param1 text, param2 int)
RETURNS text AS $$
DECLARE
    result text;
BEGIN
    -- 函数体
    RETURN result;
EXCEPTION
--异常传出
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero, returning NULL';
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

--2. 函数名与参数列表

作用：定义函数名称和输入参数（可多个，可无参数）。
格式：func_name(param1 type, param2 type, ...)
示例：

-- 多参数
CREATE FUNCTION find_user(age_min int, age_max int, city text) ...

-- 带默认值参数
CREATE FUNCTION greet(name text, greeting text DEFAULT 'Hello') ...

注：如果是自定义聚合函数，那他的SF函数里面第一个参数肯定是自定义数据类型或者STYPE里用到的参数，因为不传的话函数内部无法获得 上一次聚合的累积结果，每次调用都是全新开始，永远只能看到当前这一行的数据，聚合函数无法实现跨行的信息累加

--3. RETURNS 子句

作用：声明函数返回值的数据类型。
标量类型：text, int, boolean, numeric, date 等
复合类型：TABLE(...), SETOF record, json 等

void：无返回值

示例：

RETURNS int                    -- 返回整数
RETURNS text                   -- 返回文本
RETURNS boolean                -- 返回布尔
RETURNS TABLE(id int, name text) -- 返回结果集

--4. AS $$ ... $$

作用：AS 定义函数体内容。$$ 是美元符号引用的分隔符，用于避免字符串内单引号转义。也可以使用 '...'，但不推荐。
注意：$$ 可以自定义标签，如 $BODY$。

示例：

AS $func$
    -- 函数体
$func$

--5. DECLARE 块（可选）

作用：声明函数内部使用的局部变量、常量、游标等。

• 变量声明：var_name data_type [:= default_value];
• 常量：CONSTANT
• 游标：cursor_name CURSOR FOR select_statement;

变量声明示例：

DECLARE
    user_count int;                     -- 未初始化，默认为 NULL
    full_name text := 'John Doe';       -- 初始化
    tax_rate CONSTANT numeric := 0.08;   -- 常量
    user_record RECORD;                 -- 通用记录类型
    my_cursor CURSOR FOR SELECT id FROM users;  -- 游标

--6. BEGIN ... END; 块

作用：函数体核心执行部分。所有逻辑（赋值、条件、循环、SQL 语句、异常处理等）都写在这里。

• BEGIN 标志执行块开始
• END; 结束
• 中间可以嵌套子块（使用额外的 BEGIN ... END 创建作用域）

示例（简单的计算逻辑）：

BEGIN
    -- 赋值
    result := 'Processed: ' || param1;
    -- 条件判断
    IF param2 > 0 THEN
        result := result || ' (positive)';
    END IF;
    -- 返回
    RETURN result;
END;

--7. RETURN 语句

作用：函数返回值并退出函数。对于标量函数，必须返回与 RETURNS 声明类型兼容的值。

• 标量函数：RETURN expression;
• 表函数：RETURN NEXT expression; 或 RETURN QUERY query;
• void 函数：可以只用 RETURN; 提前退出

示例：

-- 标量函数直接返回
RETURN 'Hello';

-- 根据条件返回不同值
IF is_valid THEN
    RETURN 'OK';
ELSE
    RETURN 'ERROR';
END IF;

-- 表函数返回一行
RETURN NEXT row_record;

--8. LANGUAGE plpgsql

作用：指定函数使用的编程语言。最常用的是 plpgsql（PostgreSQL 专用过程语言），也支持 sql, python（需扩展）, c 等，写前面也行。

示例：

LANGUAGE plpgsql;   -- 过程语言，支持变量、循环、条件
LANGUAGE sql;       -- 纯 SQL，只能包含一个 SQL 语句

三、示例

1.函数题目

--1.需求描述

某银行风控团队需要根据个人征信报告上的交易行为数据，计算每个客户的信用评分。信用评分模型是一个加权求和线性公式，权重随着银行的政策调整而动态变化，银行将评分细则统一存储在一个 权重配置表 中。

银行的信用评分基于以下几个维度，每个维度权重可以根据政策灵活调整（满分按 1000 分计算）：

维度	聚合方式	说明
还款历史（满分400分）	加权得分	每笔还款行为影响分数，违约金越高扣分越多，权重受政策影响
账户活跃度（满分200分）	计数加权	账户越老、活跃度越高得分越高，不同账户类型权重不同
信用利用率（满分300分）	比率计算	总负债 ÷ 总授信额度，比率越低得分越高
信用查询次数（满分100分）	计数加权	近期查询次数越多分数越低

--2.计算公式

1. 还款历史得分（满分400，允许负分，但最低0）

• 初始值：400 分
• 扣分规则：对每笔 late_fee > 0 的交易，扣 param1 分（即扣 15 分）
• 最大扣减：不得超过 |param2| 分（即最多扣 300 分，最低得分为 400 - 300 = 100？实际上公式允许更低，但规范要求不低于0）
• 最终得分：max(0, 400 - sum(每笔违约扣分))，其中每笔违约扣分 = param1（负值）

penalty_total = min( |param2|,  count(late_fee > 0) * |param1| )
payment_score = max(0, 400 - penalty_total)

例：3 笔违约金 → 扣 45 分 → 得分 355；20 笔违约金 → 扣 300 分（封顶）→ 得分 100。

---

2. 账户活跃度得分（满分200）

• 基础分：param1 = 100 分
• 额外加分：客户使用的不同产品类型数量 × 产品类型对应的权重因子（来自 products.weight_factor）
• 上限：200 分

activity_score = min(200, base_score + sum_over_distinct_products(weight_factor))

其中 base_score = param1（100 分）

例：客户使用了信用卡（weight_factor=1.2）、房贷（1.0）、透支（0.8） → 额外加分 = 1.2+1.0+0.8 = 3.0 → 总活跃度 = 100+3 = 103 分。

---

3. 信用利用率得分（满分300）

• 指标：utilization = total_balance / total_limit
  其中 total_balance 为所有产品最新 balance_after 之和（或整个时间窗口内最终余额）
  total_limit 为对应授信额度之和（若某产品 credit_limit = 0 则不参与分母计算）
• 最优利用率：optimal_rate = param1 = 0.30
• 惩罚系数：penalty_coef = param2 = -10（负值表示超出最优比率后线性扣分）

if total_limit = 0 then util_score = 0
else:
    excess = max(0, utilization - optimal_rate)
    deduction = excess * 100 * abs(penalty_coef)   # 将系数转为正数，单位：每1%超出扣 penalty_coef 分
    util_score = max(0, 300 - deduction)

注：excess 为小数（如 0.05 代表超出5个百分点），乘以100变成百分点，再乘以10（abs(penalty_coef)=10）得到扣分。

例：utilization=0.45，超出0.15，即15个百分点 → 扣 15×10=150分 → 得分 150。

---

4. 信用查询得分（满分100）

• 初始值：100 分
• 扣分规则：每笔 is_credit_inquiry = TRUE 的记录，扣 |param1| 分（即5分）
• 最大扣减：不得超过 |param2| 分（即最多扣50分）

inquiry_penalty = min( |param2|, count(inquiry) * |param1| )
inquiry_score = max(0, 100 - inquiry_penalty)

例：3 次查询 → 扣 15 分 → 得分 85；12 次查询 → 扣 50 分（封顶）→ 得分 50。

最终信用评分 = 还款历史得分 + 账户活跃度得分 + 信用利用率得分 + 信用查询得分

最终信用评分 =  
`(payment_score * weight_payment) + (activity_score * weight_activity) + (util_score * weight_util) + (inquiry_score * weight_inquiry)`

2.测试数据

--1.表结构

-- 评分细则配置表（多列版本）
CREATE TABLE credit_scoring_rules (
    rule_name TEXT PRIMARY KEY,
    category TEXT NOT NULL,          -- 维度分类：payment_history, account_activity, utilization, inquiry
    weight FLOAT NOT NULL,           -- 该维度的权重（0~1之间）
    param1 FLOAT,                    -- 第一参数（不同维度含义不同，如 per_late_fee, base_activity_score, optimal_rate, per_inquiry_deduction）
    param2 FLOAT,                    -- 第二参数（如 max_deduction, penalty_coef, max_deduction）
    description TEXT
);

-- 产品配置表
CREATE TABLE products (
    product_id TEXT PRIMARY KEY,
    product_type TEXT,
    weight_factor FLOAT DEFAULT 1.0
);


-- 征信交易明细表
CREATE TABLE credit_records (
    record_id SERIAL PRIMARY KEY,
    customer_id TEXT NOT NULL,
    product_id TEXT NOT NULL REFERENCES products(product_id),
    transaction_date DATE NOT NULL,
    transaction_amount FLOAT,
    late_fee FLOAT DEFAULT 0,          -- 违约金（还款历史维度）
    balance_after FLOAT,               -- 交易后负债余额
    credit_limit FLOAT DEFAULT 0,      -- 该账户授信额度（仅对循环贷有效）
    is_credit_inquiry BOOLEAN DEFAULT FALSE  -- 是否为信用查询记录
);

--2.测试数据

-- 评分细则配置表
INSERT INTO credit_scoring_rules VALUES
    ('payment_history_weight', 'payment_history', 0.40, 15, 300, '还款历史：每笔违约金扣分，最大扣减300分'),
    ('account_activity_weight', 'account_activity', 0.20, 100, 200, '账户活跃度：基础活跃分数100分'),
    ('utilization_weight', 'utilization', 0.30, 0.30, 10, '信用利用率：最优负债率30%，超出部分每1%扣罚系数-10'),
    ('inquiry_weight', 'inquiry', 0.10, 5, 50, '信用查询：每次查询扣5分，最大扣减50分');
    
-- 产品配置表
INSERT INTO products VALUES 
    ('CREDIT_CARD', 'revolving', 1.2),
    ('MORTGAGE', 'installment', 1.0),
    ('CAR_LOAN', 'installment', 1.0),
    ('OVERDRAFT', 'revolving', 0.8);  
    
INSERT INTO credit_records (customer_id, product_id, transaction_date, transaction_amount, late_fee, balance_after, credit_limit, is_credit_inquiry) VALUES
    ('CUST_1001', 'CREDIT_CARD', '2023-01-10', 10000, 0, 10000, 50000, FALSE),
    ('CUST_1001', 'CREDIT_CARD', '2023-06-15', -5000, 0, 5000, 50000, FALSE),
    ('CUST_1001', 'MORTGAGE', '2023-01-20', 500000, 0, 500000, 0, FALSE),
    ('CUST_1001', 'CREDIT_CARD', '2024-01-05', 2000, 0, 7000, 50000, FALSE),
    ('CUST_1001', 'CREDIT_CARD', '2024-02-01', NULL, 0, 7000, 50000, TRUE),
    ('CUST_1001', 'OVERDRAFT', '2024-03-01', -2000, 0, 2000, 10000, FALSE);

INSERT INTO credit_records (customer_id, product_id, transaction_date, transaction_amount, late_fee, balance_after, credit_limit, is_credit_inquiry) VALUES
    ('CUST_1002', 'CREDIT_CARD', '2023-03-10', 20000, 0, 20000, 40000, FALSE),
    ('CUST_1002', 'CREDIT_CARD', '2023-09-15', -5000, 0, 15000, 40000, FALSE),
    ('CUST_1002', 'CREDIT_CARD', '2023-12-20', 3000, 0, 18000, 40000, FALSE),
    ('CUST_1002', 'CREDIT_CARD', '2024-01-15', 2000, 500, 20000, 40000, FALSE),   -- 产生违约金
    ('CUST_1002', 'CREDIT_CARD', '2024-02-10', -3000, 0, 17000, 40000, TRUE),
    ('CUST_1002', 'OVERDRAFT', '2024-03-05', 5000, 0, 5000, 8000, FALSE),
    ('CUST_1002', 'CAR_LOAN', '2023-05-10', 100000, 0, 100000, 0, FALSE);

INSERT INTO credit_records (customer_id, product_id, transaction_date, transaction_amount, late_fee, balance_after, credit_limit, is_credit_inquiry) VALUES
    ('CUST_1003', 'CREDIT_CARD', '2023-07-10', 5000, 0, 5000, 30000, FALSE),
    ('CUST_1003', 'CREDIT_CARD', '2023-08-15', -1000, 0, 4000, 30000, FALSE),
    ('CUST_1003', 'CREDIT_CARD', '2023-10-20', 3000, 1200, 7000, 30000, FALSE), -- 违约金
    ('CUST_1003', 'CREDIT_CARD', '2023-11-15', 4000, 800, 11000, 30000, FALSE),  -- 违约金
    ('CUST_1003', 'CREDIT_CARD', '2023-12-10', 2000, 0, 13000, 30000, TRUE),
    ('CUST_1003', 'CREDIT_CARD', '2024-01-20', 5000, 0, 18000, 30000, TRUE),
    ('CUST_1003', 'CREDIT_CARD', '2024-02-25', 3000, 0, 21000, 30000, FALSE);
    
--再根据上面数据生成 5000 条随机记录，时间跨度最近半年
INSERT INTO credit_records (customer_id, product_id, transaction_date, transaction_amount, late_fee, balance_after, credit_limit, is_credit_inquiry)
SELECT
    c.customer_id,
    p.product_id,
    CURRENT_DATE - (random() * 180)::int AS transaction_date,          -- 最近180天随机日期
    round((random() * 20000 - 5000)::numeric, 2) AS transaction_amount, -- 金额范围 -5000 到 15000
    CASE WHEN random() < 0.1 THEN round((random() * 1000)::numeric, 2) ELSE 0 END AS late_fee,  -- 10%概率产生违约金
    round((random() * CASE WHEN p.product_type = 'revolving' THEN 100000 ELSE 0 END)::numeric, 2) AS balance_after,  -- 余额不超过授信额度
    CASE WHEN p.product_type = 'revolving' THEN 100000 ELSE 0 END AS credit_limit,   -- 循环贷固定授信10万，其他为0
    random() < 0.05 AS is_credit_inquiry      -- 5%概率为信用查询记录
FROM
    (SELECT DISTINCT customer_id FROM credit_records) c
    CROSS JOIN generate_series(1, 5000) AS g
    CROSS JOIN LATERAL (SELECT product_id, product_type FROM products ORDER BY random() LIMIT 1) AS p;
    

3.编写函数

--1.题目分析

这个是我让Chatgpt生成的题目，说了复杂点，结果计算公式我自己看的有点懵了，这是我根据表结构和题目描述二次整理的出的适用在函数里的公式。

1.历史还款分：

晚还款次数 * credit_scoring_rules.param1的单次扣分项，最多能扣credit_scoring_rules.param2 这么多分，最后在乘上维度的权重系数

函数公式： max(0, 400 - min(|r.param2|, late_fee > 0 * |r.param1| ))*r.weight

2.活跃度得分

credit_scoring_rules.param1(基础分) + 不同产品类型数量 × 产品类型对应的权重因子(来自 products.weight_factor), 最大不超200(credit_scoring_rules.param2)

函数公式： min(200, r.param1 + sum(val::numeric))*r.weight

3.信用分 new_balance / new_limit (credit_records统计结果，每个产品的最新值累加) - 最优比率(credit_scoring_rules.param1) 为基础信用比率，然后 * 100 * 惩罚比率(credit_scoring_rules.param2), 最后拿300减去这个值。

函数公式： max(0, 300 - 100 * r.param2 * max(0, h_store.t_balance/h_store.t_limit-r.param1))*r.weigh

4.信用查询扣分 查询次数 * credit_scoring_rules.param1, 最多能扣credit_scoring_rules.param2. 初始值100分减去这个分数。

max(0, 100- min(|r.param2|, h_store.s_inquery * |r.param1| ));

函数分析

基于上述的分析，我们就可以得出需要进行统计的条目： 1. 晚还款次数, 2. 产品类型集合, 3. 余额之和, 4. 授信之和, 5. 查询次数之和。 在SF函数里将上述内容统计出来，FF函数里完成公式计算，数据类型承接条目统计数据。

--2.函数编写

1. 需要的自定义数据类型

drop type if exists healthy_type cascade;

create type healthy_type as
(
s_history int, --late_fee 次数
s_product hstore, --产品集合
t_balance hstore,  --balance新 key=productid val=date_limit
t_limit hstore, --授信 key=productid val=date_limit
s_inquery int,  --查询和
n_time timestamp --数据时间
);

2. SF函数完成数据统计

CREATE OR REPLACE FUNCTION sf_start(h_store healthy_type, p_product_id varchar, p_late_fee float, p_balance_after float, p_credit_limit float, p_is_credit_inquiry bool, p_time timestamp)
RETURNS healthy_type AS $$
declare 
weight float;
temp_time timestamp;

BEGIN
	IF h_store.s_product IS NULL THEN
    	h_store := (0,''::hstore,''::hstore,''::hstore,0,p_time);
	END IF;

    if p_late_fee >0 then
		h_store.s_history = h_store.s_history+1;
    end if;
	

    if not (h_store.s_product ? p_product_id) then
		--取balance最新值
		h_store.t_balance := h_store.t_balance || hstore(p_product_id, p_time || '_' || p_balance_after);
		h_store.t_limit := h_store.t_limit || hstore(p_product_id, p_time || '_' || p_credit_limit);
		--取权重值 
        select weight_factor into weight from products where product_id = p_product_id;
        h_store.s_product := h_store.s_product || hstore(p_product_id, weight::text);
	else
		temp_time := split_part(h_store.t_balance -> p_product_id, '_', 1)::timestamp;
		if temp_time < p_time then
			h_store.t_balance := h_store.t_balance || hstore(p_product_id, p_time || '_' || p_balance_after);
			h_store.t_limit := h_store.t_limit || hstore(p_product_id, p_time || '_' || p_credit_limit);
		end if;
    end if;

    if p_is_credit_inquiry is true then
        h_store.s_inquery := h_store.s_inquery + 1;
    end if;

    RETURN h_store;
END;
$$ LANGUAGE plpgsql;

3. FF函数完成公式计算

CREATE OR REPLACE FUNCTION ff_end(h_store healthy_type)
RETURNS float AS $$
declare 
active_parm float := 0;
h_key text;
s_balance float := 0;
s_limit float := 0;

history_score float := 0;
activity_score float := 0;
query_score float := 0;
rate_score float := 0;

final_result float;

BEGIN
    
   --历史分
    select GREATEST(0, 400 - LEAST(r.param2,  h_store.s_history * r.param1))*r.weight into history_score from public.credit_scoring_rules r where rule_name='payment_history_weight'; 

   --活动分
    FOREACH h_key IN ARRAY akeys(h_store.s_product) LOOP
    	active_parm := active_parm + (h_store.s_product -> h_key)::float;
		s_balance := s_balance + split_part(h_store.t_balance -> h_key, '_', 2)::float;
		s_limit := s_limit + split_part(h_store.t_limit -> h_key, '_', 2)::float;
		raise info 'balance/limit: % / %', split_part(h_store.t_balance -> h_key, '_', 2)::float, split_part(h_store.t_limit -> h_key, '_', 2)::float;
    END LOOP;

    SELECT LEAST(r.param2, r.param1 + active_parm) * r.weight into activity_score FROM public.credit_scoring_rules r where rule_name='account_activity_weight'; 
 
   --比例分
    if s_limit::int = 0 then
	  rate_score := 0;
    else
	  select GREATEST(0, 300 - (100 * r.param2 * (GREATEST(0, s_balance/s_limit - r.param1)))) * r.weight into rate_score from public.credit_scoring_rules r where rule_name='utilization_weight';
	end if;

   --查询分
    select GREATEST(0, 100- LEAST(r.param2, h_store.s_inquery * r.param1)) * r.weight into query_score from public.credit_scoring_rules r where rule_name='inquiry_weight';

    final_result = history_score + activity_score + rate_score + query_score;  
	raise info '% + % + % + %', history_score , activity_score , rate_score , query_score;

	raise info 'h_store.s_history: %', h_store.s_history;
	raise info 'h_store.s_product: %', h_store.s_product;
	raise info 's_balance/s_limit: % / %', s_balance, s_limit;
	raise info 'h_store.s_inquery: %', h_store.s_inquery;
    RETURN final_result;
END;
$$ LANGUAGE plpgsql;

4. AGGREGATE完成聚合

DROP AGGREGATE IF EXISTS customer_credit_score(VARCHAR, FLOAT, FLOAT, FLOAT, BOOL, timestamp);
CREATE AGGREGATE customer_credit_score(product_id varchar, late_fee float, balance_after float, credit_limit float, is_credit_inquiry bool, p_time timestamp) (
    SFUNC = sf_start,
    STYPE = healthy_type,
    FINALFUNC  = ff_end
);

4.测试结果

--1. 测试语句

SELECT customer_credit_score(product_id, late_fee, balance_after, credit_limit, is_credit_inquiry)
FROM credit_records
GROUP BY customer_id;

调试的过程就不详细写了，无非就一遍遍运行，根据报错一点点修改。不过还是可以说下要注意的点：

1. 函数参数名最好不要和列名重合小心冲突。
2. 注意拼写问题，参数变量名打反是常事。
3. 写drop的时候如果删不掉可以加上cascade，不过加上后最好全执行一遍创建脚本，话说最近几个版本PG对于关联依赖的检查越来越严了
4. float是个对象初始是个null，0+null还是个null。
5. 该上变量就上变量，句子太长，括号太多自己都容易混了，公式那部分极容易出问题
6. 真看不出来了别绷着，小心变成蹦蹦炸弹。可以函数里多打几个raise就当断点用了，或者让ai帮你调试。虽然有的时候他说的不准，但是给你个方向还是没有问题的。

--2.测试结果