信用卡常用回收率sql
时间: 2023-07-11 12:17:46 浏览: 58
以下是信用卡常用的回收率SQL语句:
1. 计算信用卡回收率:
```
SELECT SUM(payment_amount) / SUM(amount) AS recovery_rate
FROM credit_card_statement
JOIN credit_card_payment ON credit_card_statement.card_number = credit_card_payment.card_number
AND credit_card_statement.statement_date = credit_card_payment.payment_date
WHERE credit_card_statement.card_number = 'xxxx-xxxx-xxxx-xxxx';
```
2. 计算信用卡最近三个月的回收率:
```
SELECT SUM(payment_amount) / SUM(amount) AS recovery_rate
FROM credit_card_statement
JOIN credit_card_payment ON credit_card_statement.card_number = credit_card_payment.card_number
AND credit_card_statement.statement_date = credit_card_payment.payment_date
WHERE credit_card_statement.card_number = 'xxxx-xxxx-xxxx-xxxx'
AND credit_card_statement.statement_date BETWEEN DATE_SUB(NOW(), INTERVAL 3 MONTH) AND NOW();
```
3. 计算信用卡不同消费类型的回收率:
```
SELECT category, SUM(payment_amount) / SUM(amount) AS recovery_rate
FROM credit_card_statement
JOIN credit_card_payment ON credit_card_statement.card_number = credit_card_payment.card_number
AND credit_card_statement.statement_date = credit_card_payment.payment_date
WHERE credit_card_statement.card_number = 'xxxx-xxxx-xxxx-xxxx'
GROUP BY category;
```
4. 计算信用卡不同持卡人的回收率:
```
SELECT card_holder, SUM(payment_amount) / SUM(amount) AS recovery_rate
FROM credit_card_statement
JOIN credit_card_payment ON credit_card_statement.card_number = credit_card_payment.card_number
AND credit_card_statement.statement_date = credit_card_payment.payment_date
WHERE credit_card_statement.card_number = 'xxxx-xxxx-xxxx-xxxx'
GROUP BY card_holder;
```