当前位置：首页 > news >正文

MySQL - 信贷业务报表

news 来源：原创 2024/9/21 18:43:49

表结构与字段解释

注册表 u_user

id：自增主键，唯一标识用户。
username：用户名。
mobile：手机号。
password：用户密码。
nickname：用户昵称。
role_type：角色类型（-1：普通用户）。
type：用户类型（0：借款用户, 1: 资金账户用户）。
status：用户状态。
on_off：开关（0:关闭, 1:开启）。
inserttime：插入时间。
updatetime：更新时间。
isactive：逻辑删除标识（1: 有效）。

用户信息表 u_personal_info

id：自增主键。
user_id：用户ID。
loan_purpose：借款目的。
sex：性别（0：男 1：女）。
birthdate：出生日期。
birthplace：出生地。
religion：宗教。
education：教育程度。
nation：民族。
tribe：部落。
living_state：居住状态。
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：Zalo ID。
facebook_id：Facebook ID。
inserttime：插入时间。
updatetime：更新时间。
isactive：逻辑删除标识（1: 不删除）。
app_version：应用版本。

借款表 loan_list

id：自增主键。
borrower_id：借款人ID。
apply_amount：用户申请的额度。
period_no：期数。
term_quantity：每期贷款时长。
term_unit：时长单位。
product_id：产品ID。
prod_type：产品类型（1=PDL, 2=INSTALLMENT）。
interest：利息。
interest_rate：借款利率。
service_fee：应收服务费。
service_fee_discount：服务费优惠减免。
service_rate：借款服务费率。
service_fee_type：服务费是否分期（1是不分，2是分）。
overdue_penalty_rate：逾期罚息费率。
overdue_notify_rate：逾期催收费率。
overdue_fixed_charge：滞纳金。
withdraw_adjust_amount：提现调整金额。
status：标的状态。
stage：生命周期阶段。
current_stage_status：当前生命周期状态。
list_title：列表标题。
list_desc：列表描述。
ass_type：攒标类型。
agreement_id：借款协议ID。
loan_title：借款标题。
loan_purpose_code：借款目的代码。
audit_time：审核时间。
full_bid_time：成标时间。
effective_time：生效时间。
inserttime：插入时间。
updatetime：更新时间。
isactive：逻辑删除标识。
risk_pass_time：风控通过时间。
amount：借款金额。
risk_level：风险等级。
result：结果。

放款表 loan_debt

id：自增主键。
list_id：标的ID。
list_amount：标的金额。
agreement_id：借款协议ID。
borrower_id：借款人ID。
due_date：应还时间。
period_no：分期期数。
period_seq：第几期。
principal：本金。
interest：利息。
service_fee：服务费。
pre_service_fee：前置服务费。
penalty_fee：罚息。
overdue_notify_fee：催收费。
amount：债务总额。
status：债务状态。
repay_code_status：还款码状态。
repay_code_time：还款码时间。
owing_principal：未付本金。
owing_interest：未付利息。
owing_service_fee：未付服务费。
owing_penalty_fee：未付罚息。
owing_overdue_notify_fee：未付催费。
overdue_day：违约天数。
owing_amount：未付债务总额。
payment_time：还款时间。
payment_amount：已付总额。
overdue_fixed_charge：滞纳金。
user_actual_payment_time：用户实际还款时间。
version：版本。
inserttime：插入时间。
updatetime：更新时间。
isactive：逻辑删除标识。
init_due_date：初始应还时间。
delay_count：成功展期次数。

还款表 tb_repayment_slave_order

id：自增主键。
master_order_id：主订单ID。
borrower_id：借款人ID。
debt_id：债务ID。
principal：还款中的本金。
interest：还款中的利息。
penalty_fee：还款中的罚息。
overdue_notify_fee：还款中的催费。
service_fee：还款中的手续费。
inserttime：插入时间。
updatetime：更新时间。
isactive：逻辑删除标识（1: 未删除）。

业务分析及SQL

贷款管理

查询所有借款成功且放款的用户信息，包括用户名、手机号、借款金额、借款期数。

SELECT u.username, u.mobile, l.apply_amount, l.period_no
FROM u_user u
JOIN loan_list l ON u.id = l.borrower_id
WHERE l.stage >= 70 AND l.isactive = 1;

查询某一时间段内所有申请的借款记录及其审核状态。

SELECT l.borrower_id, l.apply_amount, l.status, l.audit_time
FROM loan_list l
WHERE l.inserttime BETWEEN '2024-01-01' AND '2024-01-31';

查询所有分期贷款（prod_type=2）的总金额和平均利率。

SELECT SUM(l.apply_amount) AS total_amount, AVG(l.interest_rate) AS avg_interest_rate
FROM loan_list l
WHERE l.prod_type = 2;

查询用户每次借款的还款状态和逾期天数。

SELECT l.borrower_id, d.status, d.overdue_day
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
WHERE l.isactive = 1;

查询所有未还款的借款列表及其应还金额。

SELECT l.id, l.borrower_id, d.owing_amount
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
WHERE d.status = 0;

客户分析

查询每个省的借款用户数量。

SELECT p.province, COUNT(*) AS user_count
FROM u_personal_info p
GROUP BY p.province;

查询各年龄段的用户借款分布情况。

SELECTCASEWHEN TIMESTAMPDIFF(YEAR, p.birthdate, CURDATE()) BETWEEN 18 AND 25 THEN '18-25'WHEN TIMESTAMPDIFF(YEAR, p.birthdate, CURDATE()) BETWEEN 26 AND 35 THEN '26-35'WHEN TIMESTAMPDIFF(YEAR, p.birthdate, CURDATE()) BETWEEN 36 AND 45 THEN '36-45'WHEN TIMESTAMPDIFF(YEAR, p.birthdate, CURDATE()) > 45 THEN '46+'ELSE 'unknown'END AS age_group,COUNT(*) AS user_count
FROM u_personal_info p
JOIN u_user u ON p.user_id = u.id
GROUP BY age_group;

查询所有借款用户的平均借款金额及其借款次数。

SELECT l.borrower_id, AVG(l.apply_amount) AS avg_amount, COUNT(*)AS loan_count
FROM loan_list l
GROUP BY l.borrower_id;

查询拥有信用卡数量超过3张的用户信息。

SELECT p.user_id, p.credit_card_number
FROM u_personal_info p
WHERE p.credit_card_number > 3;

查询所有女性用户的借款情况。

SELECT p.user_id, l.apply_amount, l.status
FROM u_personal_info p
JOIN loan_list l ON p.user_id = l.borrower_id
WHERE p.sex = '1';

交易分析

查询每月的借款申请总金额。

SELECT DATE_FORMAT(l.inserttime, '%Y-%m') AS month, SUM(l.apply_amount) AS total_amount
FROM loan_list l
GROUP BY month;

查询每个借款产品的申请总金额和平均利率。

SELECT l.product_id, SUM(l.apply_amount) AS total_amount, AVG(l.interest_rate) AS avg_interest_rate
FROM loan_list l
GROUP BY l.product_id;

查询每个借款产品的借款用户数量。

SELECT l.product_id, COUNT(DISTINCT l.borrower_id) AS user_count
FROM loan_list l
GROUP BY l.product_id;

查询每个省的借款总金额和平均利率。

SELECT p.province, SUM(l.apply_amount) AS total_amount, AVG(l.interest_rate) AS avg_interest_rate
FROM u_personal_info p
JOIN loan_list l ON p.user_id = l.borrower_id
GROUP BY p.province;

查询每月的还款总金额。

SELECT DATE_FORMAT(d.payment_time, '%Y-%m') AS month, SUM(d.payment_amount) AS total_amount
FROM loan_debt d
GROUP BY month;

风险管理

查询逾期超过30天的用户及其逾期天数。

SELECT l.borrower_id, d.overdue_day
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
WHERE d.overdue_day > 30;

查询每个风险等级的借款总金额和逾期金额。

SELECT l.risk_level, SUM(l.apply_amount) AS total_loan_amount, SUM(d.owing_amount) AS total_overdue_amount
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
WHERE d.status = 3
GROUP BY l.risk_level;

查询每个用户的逾期记录及其逾期总金额。

SELECT l.borrower_id, SUM(d.owing_amount) AS total_overdue_amount
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
WHERE d.status = 3
GROUP BY l.borrower_id;

查询某一时间段内的逾期贷款总金额。

SELECT SUM(d.owing_amount) AS total_overdue_amount
FROM loan_debt d
WHERE d.due_date BETWEEN '2024-01-01' AND '2024-01-31' AND d.status = 3;

查询每个产品的逾期率。

SELECT l.product_id, COUNT(*) AS total_loans, SUM(CASE WHEN d.status = 3 THEN 1 ELSE 0 END) AS overdue_loans, (SUM(CASE WHEN d.status = 3 THEN 1 ELSE 0 END) / COUNT(*)) * 100 AS overdue_rate
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.product_id;

财务绩效

查询每月的利息收入总金额。

SELECT DATE_FORMAT(d.payment_time, '%Y-%m') AS month, SUM(d.interest) AS total_interest
FROM loan_debt d
GROUP BY month;

查询每个借款产品的服务费收入总金额。

SELECT l.product_id, SUM(d.service_fee) AS total_service_fee
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.product_id;

查询每个用户的累计借款金额和累计还款金额。

SELECT l.borrower_id, SUM(l.apply_amount) AS total_loan_amount, SUM(d.payment_amount) AS total_payment_amount
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.borrower_id;

查询每个借款期数的平均利息和服务费。

SELECT l.period_no, AVG(d.interest) AS avg_interest, AVG(d.service_fee) AS avg_service_fee
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.period_no;

查询每个用户的逾期罚息收入。

SELECT l.borrower_id, SUM(d.penalty_fee) AS total_penalty_fee
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
WHERE d.status = 3
GROUP BY l.borrower_id;

查询每月的罚息收入总金额。

SELECT DATE_FORMAT(d.payment_time, '%Y-%m') AS month, SUM(d.penalty_fee) AS total_penalty_fee
FROM loan_debt d
GROUP BY month;

查询每个借款产品的平均贷款时长和平均服务费率。

SELECT l.product_id, AVG(l.term_quantity) AS avg_term_quantity, AVG(l.service_rate) AS avg_service_rate
FROM loan_list l
GROUP BY l.product_id;

查询每个借款期数的平均还款金额和未还金额。

SELECT l.period_no, AVG(d.payment_amount) AS avg_payment_amount, AVG(d.owing_amount) AS avg_owing_amount
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.period_no;

查询所有借款用户的总借款金额和总还款金额。

SELECT SUM(l.apply_amount) AS total_loan_amount, SUM(d.payment_amount) AS total_payment_amount
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id;

查询每个风险等级的总借款金额和平均利率。

SELECT l.risk_level, SUM(l.apply_amount) AS total_loan_amount, AVG(l.interest_rate) AS avg_interest_rate
FROM loan_list l
GROUP BY l.risk_level;

综合分析

查询每个用户的借款次数、总借款金额及其平均借款金额。

SELECT l.borrower_id, COUNT(*) AS loan_count, SUM(l.apply_amount) AS total_loan_amount, AVG(l.apply_amount) AS avg_loan_amount
FROM loan_list l
GROUP BY l.borrower_id;

查询每个借款用户的还款次数、总还款金额及其平均还款金额。

SELECT d.borrower_id, COUNT(*) AS payment_count, SUM(d.payment_amount) AS total_payment_amount, AVG(d.payment_amount) AS avg_payment_amount
FROM loan_debt d
GROUP BY d.borrower_id;

查询每月的借款申请数量和借款成功数量。

SELECT DATE_FORMAT(l.inserttime, '%Y-%m') AS month, COUNT(*) AS apply_count, SUM(CASE WHEN l.stage >= 70 THEN 1 ELSE 0 END) AS success_count
FROM loan_list l
GROUP BY month;

查询每个产品的逾期用户数量及其逾期率。

SELECT l.product_id, COUNT(DISTINCT CASE WHEN d.status = 3 THEN d.borrower_id ELSE NULL END) AS overdue_user_count, (COUNT(DISTINCT CASE WHEN d.status = 3 THEN d.borrower_id ELSE NULL END) / COUNT(DISTINCT l.borrower_id)) * 100 AS overdue_rate
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.product_id;

查询每个用户的累计借款金额、累计还款金额及其还款率。

SELECT l.borrower_id, SUM(l.apply_amount) AS total_loan_amount, SUM(d.payment_amount) AS total_payment_amount, (SUM(d.payment_amount) / SUM(l.apply_amount)) * 100 AS payment_rate
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.borrower_id;

查询每个用户的平均借款金额及其利率。

SELECT l.borrower_id, AVG(l.apply_amount) AS avg_loan_amount, AVG(l.interest_rate) AS avg_interest_rate
FROM loan_list l
GROUP BY l.borrower_id;

查询每个用户的累计服务费收入。

SELECT l.borrower_id, SUM(d.service_fee) AS total_service_fee
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.borrower_id;

查询每月的逾期贷款数量及其总金额。

SELECT DATE_FORMAT(d.due_date, '%Y-%m') AS month, COUNT(*) AS overdue_loan_count, SUM(d.owing_amount) AS total_overdue_amount
FROM loan_debt d
WHERE d.status = 3
GROUP BY month;

查询每个借款期数的平均还款金额及其逾期金额。

SELECT l.period_no, AVG(d.payment_amount) AS avg_payment_amount, AVG(CASE WHEN d.status = 3 THEN d.owing_amount ELSE 0 END) AS avg_overdue_amount
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.period_no;

查询每个用户的累计罚息收入。

SELECT l.borrower_id, SUM(d.penalty_fee) AS total_penalty_fee
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.borrower_id;

查询每个借款产品的累计借款金额及其利率。

SELECT l.product_id, SUM(l.apply_amount) AS total_loan_amount, AVG(l.interest_rate) AS avg_interest_rate
FROM loan_list l
GROUP BY l.product_id;

查询每个借款产品的累计服务费收入及其服务费率。

SELECT l.product_id, SUM(d.service_fee) AS total_service_fee, AVG(d.service_fee / d.list_amount) * 100 AS avg_service_rate
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.product_id;

查询每个用户的累计借款金额、累计还款金额及其逾期金额。

SELECT l.borrower_id, SUM(l.apply_amount) AS total_loan_amount, SUM(d.payment_amount) AS total_payment_amount, SUM(CASE WHEN d.status = 3 THEN d.owing_amount ELSE 0 END) AS total_overdue_amount
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.borrower_id;

查询每个用户的累计借款金额及其平均借款金额。

SELECT l.borrower_id, SUM(l.apply_amount) AS total_loan_amount, AVG(l.apply_amount) AS avg_loan_amount
FROM loan_list l
GROUP BY l.borrower_id;

查询每个用户的累计还款金额及其平均还款金额。

SELECT d.borrower_id, SUM(d.payment_amount) AS total_payment_amount, AVG(d.payment_amount) AS avg_payment_amount
FROM loan_debt d
GROUP BY d.borrower_id;

查询每个用户的累计罚息收入及其平均罚息。

SELECT l.borrower_id, SUM(d.penalty_fee) AS total_penalty_fee, AVG(d.penalty_fee) AS avg_penalty_fee
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.borrower_id;

查询每个借款产品的累计逾期金额及其逾期率。

SELECT l.product_id, SUM(CASE WHEN d.status = 3 THEN d.owing_amount ELSE 0 END) AS total_overdue_amount, (SUM(CASE WHEN d.status = 3 THEN d.owing_amount ELSE 0 END) / SUM(l.apply_amount)) * 100 AS overdue_rate
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.product_id;

查询每个用户的累计服务费收入及其平均服务费。

SELECT l.borrower_id, SUM(d.service_fee) AS total_service_fee, AVG(d.service_fee) AS avg_service_fee
FROM loan_list l
JOIN loan_debt d ON l.id = d.list_id
GROUP BY l.borrower_id;

查询每月的累计借款金额及其平均借款金额。

SELECT DATE_FORMAT(l.inserttime, '%Y-%m') AS month, SUM(l.apply_amount) AS total_loan_amount, AVG(l.apply_amount) AS avg_loan_amount
FROM loan_list l
GROUP BY month;

查询每个省的累计借款金额及其逾期金额。

SELECT p.province, SUM(l.apply_amount) AS total_loan_amount, SUM(CASE WHEN d.status = 3 THEN d.owing_amount ELSE 0 END) AS total_overdue_amount
FROM u_personal_info p
JOIN loan_list l ON p.user_id = l.borrower_id
JOIN loan_debt d ON l.id = d.list_id
GROUP BY p.province;