【金融OG教你-Python金融风控项目】03.信贷业务全链路数据库打通实战（SQL篇）

本文链接：https://blog.csdn.net/weixin_39521144/article/details/144694697

- 1. 信贷业务介绍
- - 1.1 信贷业务运行
  - 1.2 数据驱动精细化运营
  - 1.3 信贷业务的完整流程
- 2. 风控报表指标
- - 2.1 市场部考核指标
  - 2.2 风控部门考核指标
  - 2.3 催收部门考核指标
- 3. 业务报表介绍
- - 3.1 注册表（u_user）
  - 3.2 用户信息表（personal_info）
  - 3.3 借款表（loan_list）
  - 3.4 放款表（loan_debt）
  - 3.5 还款表（tb_repayment_master_order）
- 4. 风控报表指标的代码实现
- - 4.1 市场部考核指标的代码实现
  - 4.2 风控部门考核指标的代码实现
  - 4.3 催收部门考核指标的代码实现
- 5. 文章总结

1. 信贷业务介绍

1.1 信贷业务运行

信贷业务涉及三个主要部门，每个部门都有其特定的职责：

市场部门：负责通过多种渠道（如地推、电销、平台广告、营销短信等）获取新客户，并激活现有客户的借贷需求。市场部门关注新客转化率和存量客户的活跃度。
风控部门：评估用户的风险水平，决定是否给予贷款，以及确定贷款额度、利率和期限。风控部门在确保资金安全的同时，也要保证一定的贷款发放量以赚取利息收入。
催收部门：专注于逾期贷款的回收工作，包括内部催收（内催）和外部催收（外催）。催收部门的目标是最大限度地减少坏账损失。

1.2 数据驱动精细化运营

数据是精细化运营的基础，各部门通过数据分析优化业务流程：

市场部门：利用营销响应模型提高新客转化率和存量客户的活跃度。通过精准营销，市场部门可以更有效地吸引潜在客户并提升用户体验。
风控部门：通过数据分析找到最优的额度与利率组合，平衡风险与收益。风控部门使用机器学习算法和统计模型来预测用户的违约概率，从而做出更明智的决策。
催收部门：根据不同类型的客户分配最适合的催收员，使用失联修复模型提高催收效率。催收部门还会根据客户的还款行为调整催收策略，以提高回收率。

1.3 信贷业务的完整流程

信贷业务的完整流程从用户注册到用户还款，涵盖了多个环节：

用户注册：用户通过线上或线下渠道完成注册，系统生成唯一的用户ID。
借款申请：用户发起借款申请，填写相关信息，提交审核。
审核放款：风控部门对申请进行审核，评估用户信用状况，决定是否放款及放款金额、利率和期限。
放款执行：审核通过后，资金转入用户账户，生成放款记录。
还款管理：用户按照约定的还款计划进行还款，系统记录每一笔还款情况。
逾期处理：对于逾期未还款的用户，催收部门将介入，采取相应的催收措施。

2. 风控报表指标

2.1 市场部考核指标

市场部的考核指标主要包括各个阶段的转化率，帮助评估市场活动的效果：

注册-申请转化率：注册用户中有多少人发起了借款申请。
申请-通过转化率：申请用户中有多少人的申请通过了审核。
通过-放贷转化率：通过审核的用户中有多少人最终获得了贷款。
首贷转化率：首次借款用户的比例，反映市场活动对新客户的吸引力。
复贷率：老用户再次借款的比例，反映客户忠诚度和产品满意度。

2.2 风控部门考核指标

风控部门的考核指标主要用于衡量风控策略的有效性和贷款质量：

通过率：申请通过的比例，反映风控系统的宽松程度。
逾期率：贷款逾期的比例，反映风控系统的有效性。
坏账率：无法收回的贷款比例，反映贷款质量。
vintage分析：按贷款发放时间分组，跟踪每一批贷款的表现，帮助识别潜在风险。
审批时间：平均审批时间，反映风控系统的效率。

2.3 催收部门考核指标

催收部门的考核指标主要用于评估催收效果和资源分配的合理性：

催收率：成功回收的逾期贷款比例，反映催收工作的成效。
分阶段催收率：不同阶段（如M1、M2、M3等）的催收成功率，帮助优化催收策略。
分员工催收率：不同催收员的催收成功率，帮助评估员工表现。
催收成本：催收过程中产生的成本，包括人力、电话费等，反映催收效率。
失联修复率：成功找回失联客户的比例，反映失联修复模型的效果。

信贷业务漏斗图：

3. 业务报表介绍

3.1 注册表（u_user）

注册表（u_user）记录了用户的基本信息，包括但不限于以下字段：

id：自增主键，唯一标识每个用户。
username：用户名。
mobile：手机号码，用于用户身份验证。
password：加密后的密码。
nickname：用户昵称。
role_type：用户角色类型（如普通用户、管理员等）。
status：用户状态（如正常、禁用等）。
inserttime：用户注册时间。
updatetime：用户信息最后更新时间。
isactive：逻辑删除标志，表示用户是否有效。

Mysql建表语句案例：

-- ----------------------------
-- Table structure for u_user
-- ----------------------------
DROP TABLE IF EXISTS `u_user`;
CREATE TABLE `u_user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键，唯一标识每个用户',
  `username` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
  `mobile` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机号码，用于用户身份验证',
  `password` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '加密后的密码',
  `nickname` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户昵称',
  `role_type` INT NOT NULL DEFAULT 0 COMMENT '用户角色类型（如普通用户、管理员等）',
  `status` INT NOT NULL DEFAULT 0 COMMENT '用户状态（如正常、禁用等）',
  `on_off` CHAR(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1' COMMENT '开关 (0:关闭, 1:开启)',
  `inserttime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户注册时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户信息最后更新时间',
  `isactive` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '逻辑删除标志，表示用户是否有效 (1: 有效, 0: 无效)',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_mobile` (`mobile`, `on_off`, `status`, `isactive`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户注册表';

3.2 用户信息表（personal_info）

用户信息表（personal_info）记录了用户在申请信贷产品时填写的详细信息，部分字段可用于风控规则的制定：

user_id：与注册表中的用户ID关联。
loan_purpose：借款目的，如消费、教育、医疗等。
sex：性别（男/女）。
birthdate：出生日期。
education：教育程度。
province、city、district：居住地址的省市区。
address：居住详细地址。
children_number：孩子数量。
number_of_provide：需供养人数。
phone_use_duration：当前手机使用时长。
address_live_duration：当前地址居住时长。
credit_card_number：信用卡数量。
house_status：住房状态（如自有、租房等）。
other_phone_no：其他联系电话。
email：电子邮箱。
zalo_id、facebook_id：社交媒体账号。

Mysql建表语句案例：

-- ----------------------------
-- Table structure for personal_info
-- ----------------------------
DROP TABLE IF EXISTS `personal_info`;
CREATE TABLE `personal_info` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `user_id` BIGINT NOT NULL COMMENT '用户ID，与注册表中的用户ID关联',
  `loan_purpose` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '借款目的（如消费、教育、医疗等）',
  `sex` VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '性别（男/女）',
  `birthdate` DATE DEFAULT NULL COMMENT '出生日期',
  `education` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '教育程度',
  `province` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '居住省',
  `city` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '居住市',
  `district` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '居住区',
  `address` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '居住详细地址',
  `children_number` INT DEFAULT NULL COMMENT '孩子数量',
  `number_of_provide` INT DEFAULT NULL COMMENT '需供养人数',
  `phone_use_duration` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '当前手机使用时长',
  `address_live_duration` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '当前地址居住时长',
  `credit_card_number` INT DEFAULT NULL COMMENT '信用卡数量',
  `house_status` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '住房状态（如自有、租房等）',
  `other_phone_no` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '其他联系电话',
  `email` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '电子邮箱',
  `zalo_id` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Zalo账号',
  `facebook_id` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Facebook账号',
  `inserttime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `isactive` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否逻辑删除 (1: 不删除, 0: 删除)',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `idx_user_id` (`user_id`, `isactive`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户信息表';

3.3 借款表（loan_list）

借款表（loan_list）记录了用户的借款申请详情，包括但不限于以下字段：

id：自增主键，唯一标识每次借款申请。
borrower_id：借款人ID，与注册表中的用户ID关联。
apply_amount：用户申请的借款金额。
period_no：期数，即贷款的分期次数。
term_quantity：每期贷款时长。
term_unit：贷款时长单位（如天、月等）。
product_id：产品ID，标识不同的信贷产品。
prod_type：产品类型（如PDL、分期贷款等）。
interest：利息总额。
interest_rate：借款利率。
service_fee：应收服务费。
service_fee_discount：服务费优惠减免。
overdue_penalty_rate：逾期罚息费率。
overdue_notify_rate：逾期催收费率。
overdue_fixed_charge：滞纳金。
status：借款申请的状态（如审核中、已通过、已拒绝等）。
stage：借款申请所处的生命周期阶段（如初始化、审核、放款、还款等）。
current_stage_status：当前生命周期的状态（如进行中、成功、失败等）。
audit_time：审核系统给出审核结果的时间。
full_bid_time：成标时间，即贷款申请被批准的时间。
effective_time：用户收到款项的时间。
risk_level：标的风险等级，由风控系统评估得出。

Mysql建表语句案例：

-- ----------------------------
-- Table structure for loan_list
-- ----------------------------
DROP TABLE IF EXISTS `loan_list`;
CREATE TABLE `loan_list` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键，唯一标识每次借款申请',
  `borrower_id` BIGINT NOT NULL COMMENT '借款人ID，与注册表中的用户ID关联',
  `apply_amount` DECIMAL(15,2) NOT NULL COMMENT '用户申请的借款金额',
  `period_no` INT NOT NULL COMMENT '期数，即贷款的分期次数',
  `term_quantity` INT NOT NULL COMMENT '每期贷款时长',
  `term_unit` VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '贷款时长单位（如天、月等）',
  `product_id` BIGINT NOT NULL COMMENT '产品ID，标识不同的信贷产品',
  `prod_type` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '产品类型（如PDL、分期贷款等）',
  `interest` DECIMAL(15,2) DEFAULT NULL COMMENT '利息总额',
  `interest_rate` DECIMAL(5,2) DEFAULT NULL COMMENT '借款利率',
  `service_fee` DECIMAL(15,2) DEFAULT NULL COMMENT '应收服务费',
  `service_fee_discount` DECIMAL(15,2) DEFAULT NULL COMMENT '服务费优惠减免',
  `overdue_penalty_rate` DECIMAL(5,2) DEFAULT NULL COMMENT '逾期罚息费率',
  `overdue_notify_rate` DECIMAL(5,2) DEFAULT NULL COMMENT '逾期催收费率',
  `overdue_fixed_charge` DECIMAL(15,2) DEFAULT NULL COMMENT '滞纳金',
  `status` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '借款申请的状态（如审核中、已通过、已拒绝等）',
  `stage` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '借款申请所处的生命周期阶段（如初始化、审核、放款、还款等）',
  `current_stage_status` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '当前生命周期的状态（如进行中、成功、失败等）',
  `audit_time` TIMESTAMP NULL DEFAULT NULL COMMENT '审核系统给出审核结果的时间',
  `full_bid_time` TIMESTAMP NULL DEFAULT NULL COMMENT '成标时间，即贷款申请被批准的时间',
  `effective_time` TIMESTAMP NULL DEFAULT NULL COMMENT '用户收到款项的时间',
  `risk_level` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '标的风险等级，由风控系统评估得出',
  `inserttime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `isactive` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否逻辑删除 (1: 不删除, 0: 删除)',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_borrower_id` (`borrower_id`, `isactive`) USING BTREE,
  INDEX `idx_product_id` (`product_id`, `isactive`) USING BTREE,
  INDEX `idx_status` (`status`, `isactive`) USING BTREE,
  INDEX `idx_stage` (`stage`, `isactive`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='借款表';

3.4 放款表（loan_debt）

放款表（loan_debt）也称为还款计划表，记录了放款信息及相应的还款计划：

loan_id：与借款表中的借款ID关联。
borrower_id：借款人ID，与注册表中的用户ID关联。
loan_amount：实际放款金额。
repayment_plan：还款计划，包括每期还款金额、还款日期等。
repayment_status：还款状态（如未还、已还、逾期等）。
due_date：每期还款的到期日。
actual_repayment_date：实际还款日期。
overdue_days：逾期天数。

Mysql建表语句案例：

-- ----------------------------
-- Table structure for loan_debt
-- ----------------------------
DROP TABLE IF EXISTS `loan_debt`;
CREATE TABLE `loan_debt` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `loan_id` BIGINT NOT NULL COMMENT '与借款表中的借款ID关联',
  `borrower_id` BIGINT NOT NULL COMMENT '借款人ID，与注册表中的用户ID关联',
  `loan_amount` DECIMAL(15,2) NOT NULL COMMENT '实际放款金额',
  `repayment_plan` JSON DEFAULT NULL COMMENT '还款计划，包括每期还款金额、还款日期等',
  `repayment_status` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '还款状态（如未还、已还、逾期等）',
  `due_date` DATE NOT NULL COMMENT '每期还款的到期日',
  `actual_repayment_date` DATE DEFAULT NULL COMMENT '实际还款日期',
  `overdue_days` INT DEFAULT 0 COMMENT '逾期天数',
  `inserttime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `isactive` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否逻辑删除 (1: 不删除, 0: 删除)',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_loan_id` (`loan_id`, `isactive`) USING BTREE,
  INDEX `idx_borrower_id` (`borrower_id`, `isactive`) USING BTREE,
  INDEX `idx_due_date` (`due_date`, `isactive`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='放款表（还款计划表）';

3.5 还款表（tb_repayment_master_order）

还款表（tb_repayment_master_order）记录了每一笔还款的情况，同一个订单可能会有多次还款：

order_id：还款订单ID，唯一标识每次还款。
borrower_id：借款人ID，与注册表中的用户ID关联。
loan_id：与借款表中的借款ID关联。
repayment_amount：本次还款金额。
repayment_date：还款日期。
repayment_method：还款方式（如银行卡、支付宝、微信等）。
repayment_status：还款状态（如成功、失败、部分还款等）。
overdue_fee：逾期费用。
penalty_fee：罚息。
total_amount：总还款金额（包括本金、利息、罚息等）。

Mysql建表语句案例：

-- ----------------------------
-- Table structure for tb_repayment_master_order
-- ----------------------------
DROP TABLE IF EXISTS `tb_repayment_master_order`;
CREATE TABLE `tb_repayment_master_order` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `order_id` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '还款订单ID，唯一标识每次还款',
  `borrower_id` BIGINT NOT NULL COMMENT '借款人ID，与注册表中的用户ID关联',
  `loan_id` BIGINT NOT NULL COMMENT '与借款表中的借款ID关联',
  `repayment_amount` DECIMAL(15,2) NOT NULL COMMENT '本次还款金额',
  `repayment_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '还款日期',
  `repayment_method` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '还款方式（如银行卡、支付宝、微信等）',
  `repayment_status` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '还款状态（如成功、失败、部分还款等）',
  `overdue_fee` DECIMAL(15,2) DEFAULT 0.00 COMMENT '逾期费用',
  `penalty_fee` DECIMAL(15,2) DEFAULT 0.00 COMMENT '罚息',
  `total_amount` DECIMAL(15,2) NOT NULL COMMENT '总还款金额（包括本金、利息、罚息等）',
  `inserttime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
  `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `isactive` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否逻辑删除 (1: 不删除, 0: 删除)',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uniq_order_id` (`order_id`, `isactive`) USING BTREE,
  INDEX `idx_borrower_id` (`borrower_id`, `isactive`) USING BTREE,
  INDEX `idx_loan_id` (`loan_id`, `isactive`) USING BTREE,
  INDEX `idx_repayment_date` (`repayment_date`, `isactive`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='还款表';

4. 风控报表指标的代码实现

4.1 市场部考核指标的代码实现

注册-申请转化率，需求拆解：
- 注册用户数：从u_user表中统计所有有效用户的数量。
- 申请用户数：从personal_info表中统计所有提交了个人信息的用户数量（即有记录的用户）。
- 转化率计算：将申请用户数除以注册用户数，得到转化率。
- 假设我们只考虑有效用户（即isactive = 1），并且personal_info表中的每条记录代表一次完整的信贷产品申请。
- 以下是实现这一需求的MySQL查询语句：

-- 计算注册-申请转化率

WITH 
-- 统计有效注册用户数
registered_users AS (
    SELECT 
        COUNT(id) AS total_registered
    FROM u_user
    WHERE isactive = 1
),
-- 统计有效申请用户数
applied_users AS (
    SELECT 
        COUNT(DISTINCT user_id) AS total_applied
    FROM personal_info
    WHERE isactive = 1
)
-- 计算转化率
SELECT 
    ru.total_registered,
    au.total_applied,
    ROUND((au.total_applied / ru.total_registered) * 100, 2) AS conversion_rate_percent
FROM 
    registered_users ru
CROSS JOIN 
    applied_users au;

申请-通过转化率，需求拆解：
- 申请用户数：从personal_info表中统计所有提交了个人信息的用户数量（即有记录的用户）。
- 通过用户数：从loan_list表中统计状态为“已通过”的借款申请数量。
- 转化率计算：将通过用户数除以申请用户数，得到转化率。
- 以下是实现这一需求的MySQL查询语句：

-- 计算申请-通过转化率

WITH 
-- 统计有效申请用户数
applied_users AS (
    SELECT COUNT(DISTINCT user_id) AS total_applied
    FROM personal_info
    WHERE isactive = 1
),
-- 统计有效通过的借款申请数
approved_loans AS (
    SELECT COUNT(DISTINCT borrower_id) AS total_approved
    FROM loan_list
    WHERE status = '已通过' AND isactive = 1
)
-- 计算转化率
SELECT 
    au.total_applied,
    al.total_approved,
    ROUND((al.total_approved / NULLIF(au.total_applied, 0)) * 100, 2) AS conversion_rate_percent
FROM 
    applied_users au
CROSS JOIN 
    approved_loans al;

通过-放贷转化率，需求拆解：
- 通过用户数：从loan_list表中统计状态为“已通过”的借款申请数量。
- 放贷用户数：从loan_debt表中统计有放款记录的用户数量。
- 转化率计算：将放贷用户数除以通过用户数，得到转化率。
- 以下是实现这一需求的MySQL查询语句：

-- 计算通过-放贷转化率

WITH 
-- 统计有效通过的借款申请数
approved_loans AS (
    SELECT COUNT(DISTINCT borrower_id) AS total_approved
    FROM loan_list
    WHERE status = '已通过' AND isactive = 1
),
-- 统计有效放款的用户数
funded_loans AS (
    SELECT COUNT(DISTINCT borrower_id) AS total_funded
    FROM loan_debt
    WHERE isactive = 1
)
-- 计算转化率
SELECT 
    al.total_approved,
    fl.total_funded,
    ROUND((fl.total_funded / NULLIF(al.total_approved, 0)) * 100, 2) AS conversion_rate_percent
FROM 
    approved_loans al
CROSS JOIN 
    funded_loans fl;

首贷转化率，需求拆解：
- 首贷用户数：从loan_list和loan_debt表中统计每个用户首次成功放款的记录。即每个用户第一次有effective_time不为空且状态为“已通过”的借款申请。
- 总用户数：从u_user表中统计所有有效用户的数量。
- 转化率计算：将首贷用户数除以总用户数，得到首贷转化率。
- 以下是实现这一需求的MySQL查询语句：

-- 计算首贷转化率

WITH 
-- 统计所有有效用户数
total_users AS (
    SELECT COUNT(id) AS total_users
    FROM u_user
    WHERE isactive = 1
),
-- 统计每个用户的首次成功放款记录
first_loan AS (
    SELECT 
        l.borrower_id,
        MIN(l.effective_time) AS first_effective_time
    FROM loan_list l
    JOIN loan_debt d ON l.id = d.loan_id
    WHERE l.status = '已通过' AND l.isactive = 1 AND d.isactive = 1
    GROUP BY l.borrower_id
),
-- 统计首贷用户数
first_loan_users AS (
    SELECT COUNT(DISTINCT borrower_id) AS total_first_loan_users
    FROM first_loan
)
-- 计算转化率
SELECT 
    tu.total_users,
    fl.total_first_loan_users,
    ROUND((fl.total_first_loan_users / NULLIF(tu.total_users, 0)) * 100, 2) AS first_loan_conversion_rate_percent
FROM 
    total_users tu
CROSS JOIN 
    first_loan_users fl;

复贷率，需求拆解：
- 首贷用户数：从loan_list和loan_debt表中统计每个用户首次成功放款的记录。即每个用户第一次有effective_time不为空且状态为“已通过”的借款申请。
- 复贷用户数：从loan_list和loan_debt表中统计每个用户第二次及以后成功放款的记录。即每个用户第二次及以后有effective_time不为空且状态为“已通过”的借款申请。
- 复贷率计算：将复贷用户数除以总用户数（包括首贷和复贷用户），得到复贷率。
- 以下是实现这一需求的MySQL查询语句：

-- 计算复贷率

WITH 
-- 统计每个用户的首次成功放款记录
first_loan AS (
    SELECT 
        l.borrower_id,
        MIN(l.effective_time) AS first_effective_time
    FROM loan_list l
    JOIN loan_debt d ON l.id = d.loan_id
    WHERE l.status = '已通过' AND l.isactive = 1 AND d.isactive = 1
    GROUP BY l.borrower_id
),
-- 统计每个用户的放款次数
loan_count AS (
    SELECT 
        l.borrower_id,
        COUNT(*) AS loan_times
    FROM loan_list l
    JOIN loan_debt d ON l.id = d.loan_id
    WHERE l.status = '已通过' AND l.isactive = 1 AND d.isactive = 1
    GROUP BY l.borrower_id
),
-- 统计复贷用户数
repeat_loan_users AS (
    SELECT COUNT(DISTINCT borrower_id) AS total_repeat_loan_users
    FROM loan_count
    WHERE loan_times > 1
),
-- 统计总用户数（包括首贷和复贷用户）
total_loan_users AS (
    SELECT COUNT(DISTINCT borrower_id) AS total_loan_users
    FROM loan_count
)
-- 计算复贷率
SELECT 
    tl.total_loan_users,
    rl.total_repeat_loan_users,
    ROUND((rl.total_repeat_loan_users / NULLIF(tl.total_loan_users, 0)) * 100, 2) AS repeat_loan_rate_percent
FROM 
    total_loan_users tl
CROSS JOIN 
    repeat_loan_users rl;

4.2 风控部门考核指标的代码实现

通过率，需求拆解：
- 总申请数：从loan_list表中统计所有状态为“审核中”、“已通过”或“已拒绝”的借款申请数量。
- 通过申请数：从loan_list表中统计状态为“已通过”的借款申请数量。
- 通过率计算：将通过申请数除以总申请数，得到通过率。
- 以下是实现这一需求的MySQL查询语句：

-- 计算通过率

WITH 
-- 统计总申请数
total_applications AS (
    SELECT COUNT(*) AS total_applications
    FROM loan_list
    WHERE status IN ('审核中', '已通过', '已拒绝') AND isactive = 1
),
-- 统计通过的申请数
approved_applications AS (
    SELECT COUNT(*) AS approved_applications
    FROM loan_list
    WHERE status = '已通过' AND isactive = 1
)
-- 计算通过率
SELECT 
    ta.total_applications,
    aa.approved_applications,
    ROUND((aa.approved_applications / NULLIF(ta.total_applications, 0)) * 100, 2) AS approval_rate_percent
FROM 
    total_applications ta
CROSS JOIN 
    approved_applications aa;

逾期率，需求拆解：
- 总放款数：从loan_debt表中统计所有有效的放款记录数量。
- 逾期放款数：从loan_debt表中统计逾期天数大于0的放款记录数量。
- 逾期率计算：将逾期放款数除以总放款数，得到逾期率。
- 以下是实现这一需求的MySQL查询语句：

-- 计算逾期率

WITH 
-- 统计总放款数
total_loans AS (
    SELECT COUNT(*) AS total_loans
    FROM loan_debt
    WHERE isactive = 1
),
-- 统计逾期放款数
overdue_loans AS (
    SELECT COUNT(*) AS overdue_loans
    FROM loan_debt
    WHERE overdue_days > 0 AND isactive = 1
)
-- 计算逾期率
SELECT 
    tl.total_loans,
    ol.overdue_loans,
    ROUND((ol.overdue_loans / NULLIF(tl.total_loans, 0)) * 100, 2) AS overdue_rate_percent
FROM 
    total_loans tl
CROSS JOIN 
    overdue_loans ol;

坏账率，需求拆解：
- 总放款金额：从loan_list表中统计所有成功放款的借款金额总和。
- 坏账金额：从loan_debt表中统计逾期天数超过一定阈值（例如90天）且未还款的放款金额总和。或者，根据业务定义，统计还款状态为“未还”或“逾期”的放款金额总和。
- 坏账率计算：将坏账金额除以总放款金额，得到坏账率。
- 以下是实现这一需求的MySQL查询语句：

-- 计算坏账率

WITH 
-- 统计总放款金额
total_loan_amount AS (
    SELECT SUM(apply_amount) AS total_loan_amount
    FROM loan_list
    WHERE status = '已通过' AND isactive = 1
),
-- 统计坏账金额
bad_debt_amount AS (
    SELECT SUM(ld.loan_amount) AS bad_debt_amount
    FROM loan_debt ld
    JOIN loan_list ll ON ld.loan_id = ll.id
    WHERE ld.isactive = 1
      AND ll.isactive = 1
      AND (ld.repayment_status = '未还' OR (ld.repayment_status = '逾期' AND ld.overdue_days >= 90))
)
-- 计算坏账率
SELECT 
    tla.total_loan_amount,
    bda.bad_debt_amount,
    ROUND((bda.bad_debt_amount / NULLIF(tla.total_loan_amount, 0)) * 100, 2) AS bad_debt_rate_percent
FROM 
    total_loan_amount tla
CROSS JOIN 
    bad_debt_amount bda;

vintage分析，需求拆解：
- Vintage分析：Vintage分析用于评估贷款组合在不同时间段内的表现。通常按贷款发放月份分组，并跟踪每个组在后续各月的表现，例如逾期率、坏账率等。
- 分析指标：可以选择不同的分析指标，如逾期率、坏账率、累计还款率等。这里以逾期率为例进行说明。
- 时间窗口：选择一个时间窗口（例如6个月或12个月），跟踪每个vintage组在这段时间内的逾期情况。
- 以下是实现这一需求的MySQL查询语句：

-- Vintage分析：按贷款发放月份分组，跟踪每月的逾期率

WITH 
-- 统计每个贷款发放月份的贷款
vintage_loans AS (
    SELECT 
        DATE_FORMAT(ll.effective_time, '%Y-%m') AS vintage_month,
        ll.id AS loan_id,
        ll.apply_amount AS loan_amount,
        ld.due_date,
        ld.actual_repayment_date,
        ld.overdue_days,
        ld.repayment_status
    FROM loan_list ll
    JOIN loan_debt ld ON ll.id = ld.loan_id
    WHERE ll.status = '已通过' AND ll.isactive = 1 AND ld.isactive = 1
),
-- 计算每个vintage组在每个月末的逾期情况
vintage_overdue AS (
    SELECT 
        vl.vintage_month,
        DATE_FORMAT(DATE_ADD(vl.due_date, INTERVAL 1 MONTH) - INTERVAL 1 DAY, '%Y-%m') AS month_end,
        COUNT(*) AS total_loans,
        SUM(CASE WHEN vl.overdue_days > 0 THEN 1 ELSE 0 END) AS overdue_loans,
        SUM(CASE WHEN vl.repayment_status = '未还' OR (vl.repayment_status = '逾期' AND vl.overdue_days >= 30) THEN vl.loan_amount ELSE 0 END) AS overdue_amount
    FROM vintage_loans vl
    GROUP BY vl.vintage_month, month_end
)
-- 计算每个vintage组的逾期率
SELECT 
    vo.vintage_month,
    vo.month_end,
    vo.total_loans,
    vo.overdue_loans,
    vo.overdue_amount,
    ROUND((vo.overdue_loans / vo.total_loans) * 100, 2) AS overdue_rate_percent
FROM vintage_overdue vo
ORDER BY vo.vintage_month, vo.month_end;

审批时间，需求拆解：
- 审批时间：计算每个借款申请从提交到审核完成的时间差。具体来说，审批时间是从full_bid_time（成标时间）减去audit_time（审核时间）。如果audit_time为空，则使用effective_time（用户收到款项的时间）作为替代。
- 统计指标：可以按不同维度（如按天、按月、按年）统计平均审批时间、最短审批时间、最长审批时间等。
- 以下是实现这一需求的MySQL查询语句：

-- 计算审批时间

WITH 
-- 统计每个借款申请的审批时间
approval_times AS (
    SELECT 
        id,
        borrower_id,
        status,
        isactive,
        audit_time,
        full_bid_time,
        effective_time,
        TIMESTAMPDIFF(SECOND, COALESCE(audit_time, effective_time), full_bid_time) AS approval_duration_seconds
    FROM loan_list
    WHERE status = '已通过' AND isactive = 1
      AND full_bid_time IS NOT NULL
)
-- 计算统计指标
SELECT 
    DATE_FORMAT(full_bid_time, '%Y-%m') AS month,  -- 按月分组
    COUNT(*) AS total_approvals,
    AVG(approval_duration_seconds) / 60 AS avg_approval_minutes,  -- 平均审批时间（分钟）
    MIN(approval_duration_seconds) / 60 AS min_approval_minutes,  -- 最短审批时间（分钟）
    MAX(approval_duration_seconds) / 60 AS max_approval_minutes   -- 最长审批时间（分钟）
FROM approval_times
GROUP BY DATE_FORMAT(full_bid_time, '%Y-%m')
ORDER BY month;

4.3 催收部门考核指标的代码实现

催收率，需求拆解：
- 总逾期贷款数：从loan_debt表中统计所有逾期天数大于0的放款记录数量。
- 催收贷款数：从tb_repayment_master_order表中统计有逾期费用或罚息的还款记录对应的贷款数量。这表示这些贷款已经进入了催收流程。
- 催收率计算：将催收贷款数除以总逾期贷款数，得到催收率。
- 以下是实现这一需求的MySQL查询语句：

-- 计算催收率

WITH 
-- 统计总逾期贷款数
total_overdue_loans AS (
    SELECT 
        ld.loan_id,
        COUNT(DISTINCT ld.loan_id) AS total_overdue_loans
    FROM loan_debt ld
    WHERE ld.overdue_days > 0 AND ld.isactive = 1
    GROUP BY ld.loan_id
),
-- 统计催收贷款数
collected_loans AS (
    SELECT 
        rmo.loan_id,
        COUNT(DISTINCT rmo.loan_id) AS collected_loans
    FROM tb_repayment_master_order rmo
    JOIN loan_debt ld ON rmo.loan_id = ld.loan_id
    WHERE (rmo.overdue_fee > 0 OR rmo.penalty_fee > 0) AND rmo.isactive = 1 AND ld.isactive = 1
    GROUP BY rmo.loan_id
)
-- 计算催收率
SELECT 
    IFNULL(SUM(collected_loans), 0) AS total_collected_loans,
    IFNULL(SUM(total_overdue_loans), 0) AS total_overdue_loans,
    ROUND(IFNULL(SUM(collected_loans) / SUM(total_overdue_loans), 0) * 100, 2) AS collection_rate_percent
FROM 
    (SELECT COALESCE(c.collected_loans, 0) AS collected_loans, COALESCE(t.total_overdue_loans, 0) AS total_overdue_loans
     FROM total_overdue_loans t
     LEFT JOIN collected_loans c ON t.loan_id = c.loan_id) AS combined;

分阶段催收率，需求拆解：
- 分阶段催收率：根据逾期天数将贷款分为不同的催收阶段（例如：0-30天、31-60天、61-90天、90天以上），并计算每个阶段的催收率。催收率定义为有逾期费用或罚息的贷款数量占该阶段总逾期贷款数量的比例。
- 以下是实现这一需求的MySQL查询语句：

-- 计算分阶段催收率

WITH 
-- 统计每个阶段的总逾期贷款数
total_overdue_loans AS (
    SELECT 
        CASE
            WHEN ld.overdue_days BETWEEN 1 AND 30 THEN '1-30天'
            WHEN ld.overdue_days BETWEEN 31 AND 60 THEN '31-60天'
            WHEN ld.overdue_days BETWEEN 61 AND 90 THEN '61-90天'
            WHEN ld.overdue_days > 90 THEN '90天以上'
            ELSE '未逾期'
        END AS overdue_stage,
        COUNT(DISTINCT ld.loan_id) AS total_overdue_loans
    FROM loan_debt ld
    WHERE ld.overdue_days > 0 AND ld.isactive = 1
    GROUP BY overdue_stage
),
-- 统计每个阶段的催收贷款数
collected_loans AS (
    SELECT 
        CASE
            WHEN ld.overdue_days BETWEEN 1 AND 30 THEN '1-30天'
            WHEN ld.overdue_days BETWEEN 31 AND 60 THEN '31-60天'
            WHEN ld.overdue_days BETWEEN 61 AND 90 THEN '61-90天'
            WHEN ld.overdue_days > 90 THEN '90天以上'
            ELSE '未逾期'
        END AS overdue_stage,
        COUNT(DISTINCT rmo.loan_id) AS collected_loans
    FROM tb_repayment_master_order rmo
    JOIN loan_debt ld ON rmo.loan_id = ld.loan_id
    WHERE (rmo.overdue_fee > 0 OR rmo.penalty_fee > 0) AND rmo.isactive = 1 AND ld.isactive = 1
    GROUP BY overdue_stage
)
-- 计算分阶段催收率
SELECT 
    t.overdue_stage,
    t.total_overdue_loans,
    IFNULL(c.collected_loans, 0) AS collected_loans,
    ROUND(IFNULL(c.collected_loans / t.total_overdue_loans, 0) * 100, 2) AS collection_rate_percent
FROM 
    total_overdue_loans t
LEFT JOIN 
    collected_loans c ON t.overdue_stage = c.overdue_stage
ORDER BY 
    FIELD(t.overdue_stage, '1-30天', '31-60天', '61-90天', '90天以上');

分员工催收率，需求拆解：
- 分员工催收率：根据每个催收员统计其负责的逾期贷款中，有逾期费用或罚息的贷款数量占总逾期贷款数量的比例。
- 以下是实现这一需求的MySQL查询语句：

-- 计算分员工催收率

WITH 
-- 统计每个员工的总逾期贷款数
total_overdue_loans AS (
    SELECT 
        ld.collector_id,
        COUNT(DISTINCT ld.loan_id) AS total_overdue_loans
    FROM loan_debt ld
    WHERE ld.overdue_days > 0 AND ld.isactive = 1
    GROUP BY ld.collector_id
),
-- 统计每个员工的催收贷款数
collected_loans AS (
    SELECT 
        ld.collector_id,
        COUNT(DISTINCT rmo.loan_id) AS collected_loans
    FROM tb_repayment_master_order rmo
    JOIN loan_debt ld ON rmo.loan_id = ld.loan_id
    WHERE (rmo.overdue_fee > 0 OR rmo.penalty_fee > 0) AND rmo.isactive = 1 AND ld.isactive = 1
    GROUP BY ld.collector_id
)
-- 计算分员工催收率
SELECT 
    t.collector_id,
    t.total_overdue_loans,
    IFNULL(c.collected_loans, 0) AS collected_loans,
    ROUND(IFNULL(c.collected_loans / t.total_overdue_loans, 0) * 100, 2) AS collection_rate_percent
FROM 
    total_overdue_loans t
LEFT JOIN 
    collected_loans c ON t.collector_id = c.collector_id
ORDER BY 
    t.collector_id;

催收成本，需求拆解：
- 催收成本：统计每个催收员负责的贷款中，所有催收活动的成本总和。包括逾期费用、罚息以及具体的催收成本（如电话费、人工费等）。
- 以下是实现这一需求的MySQL查询语句：

-- 计算催收成本

WITH 
-- 统计每个催收员的逾期费用和罚息总和
overdue_and_penalty_costs AS (
    SELECT 
        ld.collector_id,
        SUM(rmo.overdue_fee + rmo.penalty_fee) AS total_overdue_penalty
    FROM tb_repayment_master_order rmo
    JOIN loan_debt ld ON rmo.loan_id = ld.loan_id
    WHERE (rmo.overdue_fee > 0 OR rmo.penalty_fee > 0) AND rmo.isactive = 1 AND ld.isactive = 1
    GROUP BY ld.collector_id
),
-- 统计每个催收员的具体催收成本
collection_costs AS (
    SELECT 
        cc.collector_id,
        SUM(cc.cost_amount) AS total_collection_cost
    FROM collection_cost cc
    WHERE cc.isactive = 1
    GROUP BY cc.collector_id
)
-- 计算每个催收员的总催收成本
SELECT 
    collector_id,
    SUM(total_overdue_penalty) AS total_overdue_penalty,
    SUM(total_collection_cost) AS total_collection_cost,
    SUM(total_overdue_penalty) + SUM(total_collection_cost) AS total_collection_expense
FROM (
    SELECT 
        oc.collector_id,
        oc.total_overdue_penalty,
        IFNULL(cc.total_collection_cost, 0) AS total_collection_cost
    FROM overdue_and_penalty_costs oc
    LEFT JOIN collection_costs cc ON oc.collector_id = cc.collector_id
    UNION ALL
    SELECT 
        cc.collector_id,
        IFNULL(oc.total_overdue_penalty, 0) AS total_overdue_penalty,
        cc.total_collection_cost
    FROM collection_costs cc
    LEFT JOIN overdue_and_penalty_costs oc ON cc.collector_id = oc.collector_id
) AS combined
GROUP BY collector_id
ORDER BY collector_id;

失联修复率，需求拆解：
- 失联修复率：统计所有失联的借款人中，成功修复联系的借款人数量占总失联借款人数量的比例。
- 以下是实现这一需求的MySQL查询语句：

-- 计算失联修复率

WITH 
-- 统计所有失联的借款人
total_lost_contact AS (
    SELECT 
        ld.borrower_id,
        COUNT(DISTINCT ld.borrower_id) AS total_lost_contact
    FROM loan_debt ld
    WHERE ld.repayment_status = '未还' AND ld.overdue_days > 30 AND ld.isactive = 1
    GROUP BY ld.borrower_id
),
-- 统计成功修复联系的借款人
recovered_contacts AS (
    SELECT 
        cr.borrower_id,
        COUNT(DISTINCT cr.borrower_id) AS recovered_contacts
    FROM contact_recovery cr
    JOIN loan_debt ld ON cr.borrower_id = ld.borrower_id
    WHERE cr.isactive = 1 AND ld.repayment_status = '未还' AND ld.overdue_days > 30 AND ld.isactive = 1
    GROUP BY cr.borrower_id
)
-- 计算失联修复率
SELECT 
    IFNULL(SUM(recovered_contacts), 0) AS total_recovered_contacts,
    IFNULL(SUM(total_lost_contact), 0) AS total_lost_contacts,
    ROUND(IFNULL(SUM(recovered_contacts) / SUM(total_lost_contact), 0) * 100, 2) AS recovery_rate_percent
FROM 
    (SELECT COALESCE(r.recovered_contacts, 0) AS recovered_contacts, COALESCE(t.total_lost_contact, 0) AS total_lost_contact
     FROM total_lost_contact t
     LEFT JOIN recovered_contacts r ON t.borrower_id = r.borrower_id) AS combined;