用SQL计算客户级过账单日未还款客户的1-30天回收率

要计算客户级过账单日未还款客户的1-30天回收率，需要先确定哪些客户在过账单日当天有未还款项，然后计算这些客户在1-30天内回收的贷款金额占总未还款额的比例。可以使用以下SQL语句进行计算：

SELECT customer_id, 
       SUM(CASE WHEN overdue_days <= 30 THEN recovered_amount ELSE 0 END) AS m1_recovered_amount, 
       SUM(CASE WHEN overdue_days > 0 THEN overdue_amount ELSE 0 END) AS total_overdue_amount,
       (SUM(CASE WHEN overdue_days <= 30 THEN recovered_amount ELSE 0 END) / SUM(CASE WHEN overdue_days > 0 THEN overdue_amount ELSE 0 END)) AS m1_recovery_rate
FROM (
  SELECT customer_id, 
         loan_amount AS overdue_amount, 
         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, 
         CASE WHEN recovered_date IS NOT NULL THEN loan_amount ELSE 0 END AS recovered_amount
  FROM loan_table
  WHERE due_date <= CURDATE() AND recovered_date IS NULL
) AS temp
GROUP BY customer_id;

在以上SQL语句中，loan_table是包含贷款信息的表，包括贷款金额、到期日、客户ID、还款日期等字段。首先使用WHERE子句筛选出在过账单日当天有未还款项的贷款，并为每个未还款贷款计算逾期天数和回收金额，然后使用GROUP BY子句按客户ID进行分组，计算每个客户的1-30天回收率。其中，m1_recovered_amount表示1-30天内回收的贷款金额，total_overdue_amount表示总未还款额，m1_recovery_rate表示1-30天回收率。