MySQL - 信贷业务报表

表结构与字段解释

注册表 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

贷款管理

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

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;

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

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';

3. 查询所有分期贷款（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;

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

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;

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

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;

客户分析

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

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

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

SELECT
  CASE
    WHEN 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;

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

SELECT l.borrower_id, AVG(l.apply_amount) AS avg_amount, COUNT(*)

 AS loan_count
FROM loan_list l
GROUP BY l.borrower_id;

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

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

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

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';

交易分析

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

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

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

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;

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

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

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

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;

15. 查询每月的还款总金额。

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

风险管理

16. 查询逾期超过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;

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

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;

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

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;

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

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;

20. 查询每个产品的逾期率。

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;

财务绩效

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

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

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

综合分析

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

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

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;

更多问题咨询

Cos机器人