SQL如何用还款计划表计算客户M1回收率

要使用还款计划表计算客户M1回收率，需要进行以下几个步骤：

1. 根据还款计划表计算每个贷款的还款状态和逾期天数。

SELECT loan_id, due_date, amount, 
  CASE 
    WHEN paid_date IS NOT NULL THEN '已还清'
    WHEN DATEDIFF(CURDATE(), due_date) > 30 THEN 'M2+'
    WHEN DATEDIFF(CURDATE(), due_date) > 0 THEN 'M1'
    ELSE '正常'
  END AS repay_status,
  CASE 
    WHEN DATEDIFF(CURDATE(), due_date) > 30 THEN DATEDIFF(CURDATE(), due_date) - 30
    WHEN DATEDIFF(CURDATE(), due_date) > 0 THEN DATEDIFF(CURDATE(), due_date)
    ELSE 0
  END AS overdue_days
FROM repayment_plan
WHERE due_date <= CURDATE();

在以上SQL语句中，repayment_plan是还款计划表，包含贷款ID、到期日、应还款金额、实还款日期等字段。使用CASE语句计算每个还款计划的还款状态，即已还清、正常、M1、M2+，并使用DATEDIFF函数计算逾期天数，如果逾期天数大于30天，则标记为M2+，否则标记为M1。

2. 根据贷款ID计算每个客户的M1回收金额和总逾期金额。

SELECT customer_id, 
  SUM(CASE WHEN repay_status = 'M1' THEN amount ELSE 0 END) AS m1_recovered_amount,
  SUM(CASE WHEN repay_status IN ('M1', 'M2+') THEN amount ELSE 0 END) AS total_overdue_amount
FROM (
  SELECT loan_id, customer_id, due_date, amount, 
    CASE 
      WHEN paid_date IS NOT NULL THEN '已还清'
      WHEN DATEDIFF(CURDATE(), due_date) > 30 THEN 'M2+'
      WHEN DATEDIFF(CURDATE(), due_date) > 0 THEN 'M1'
      ELSE '正常'
    END AS repay_status,
    CASE 
      WHEN DATEDIFF(CURDATE(), due_date) > 30 THEN DATEDIFF(CURDATE(), due_date) - 30
      WHEN DATEDIFF(CURDATE(), due_date) > 0 THEN DATEDIFF(CURDATE(), due_date)
      ELSE 0
    END AS overdue_days
  FROM repayment_plan
  WHERE due_date <= CURDATE()
) AS temp
GROUP BY customer_id;

在以上SQL语句中，使用子查询计算每个贷款的还款状态和逾期天数，然后按贷款ID和客户ID进行分组，使用SUM函数计算每个客户的M1回收金额和总逾期金额。

3. 计算每个客户的M1回收率。

SELECT customer_id, 
  m1_recovered_amount / total_overdue_amount AS m1_recovery_rate
FROM (
  SELECT customer_id, 
    SUM(CASE WHEN repay_status = 'M1' THEN amount ELSE 0 END) AS m1_recovered_amount,
    SUM(CASE WHEN repay_status IN ('M1', 'M2+') THEN amount ELSE 0 END) AS total_overdue_amount
  FROM (
    SELECT loan_id, customer_id, due_date, amount, 
      CASE 
        WHEN paid_date IS NOT NULL THEN '已还清'
        WHEN DATEDIFF(CURDATE(), due_date) > 30 THEN 'M2+'
        WHEN DATEDIFF(CURDATE(), due_date) > 0 THEN 'M1'
        ELSE '正常'
      END AS repay_status,
      CASE 
        WHEN DATEDIFF(CURDATE(), due_date) > 30 THEN DATEDIFF(CURDATE(), due_date) - 30
        WHEN DATEDIFF(CURDATE(), due_date) > 0 THEN DATEDIFF(CURDATE(), due_date)
        ELSE 0
      END AS overdue_days
    FROM repayment_plan
    WHERE due_date <= CURDATE()
  ) AS temp
  GROUP BY customer_id
) AS temp2;

在以上SQL语句中，再次使用子查询计算每个客户的M1回收金额和总逾期金额，然后计算每个客户的M1回收率。