根据二代人行征信,写3个指标的中英文名,并对其写伪代码指标逻辑和sql逻辑
时间: 2024-06-12 09:07:51 浏览: 16
1. 逾期次数 Overdue Count
伪代码指标逻辑:
```
for each loan in loans:
if loan.status == "Overdue":
overdue_count += 1
```
SQL逻辑:
```
SELECT COUNT(*) AS overdue_count
FROM loan
WHERE status = 'Overdue'
```
2. 贷款总额 Total Loan Amount
伪代码指标逻辑:
```
for each loan in loans:
total_loan_amount += loan.amount
```
SQL逻辑:
```
SELECT SUM(amount) AS total_loan_amount
FROM loan
```
3. 信用卡额度总额 Total Credit Line
伪代码指标逻辑:
```
for each credit_card in credit_cards:
total_credit_line += credit_card.credit_limit
```
SQL逻辑:
```
SELECT SUM(credit_limit) AS total_credit_line
FROM credit_card
```
相关问题
根据人行征信,做特征衍生每类特征中,写2-5个指标的中英文名,对其中的1个写伪代码指标逻辑和sql逻辑,可以复杂一点
一、个人基本信息类特征
1. 性别
English name: Gender
伪代码指标逻辑:
IF gender == "男" THEN gender_code = 1
ELSEIF gender == "女" THEN gender_code = 0
ELSE gender_code = NULL
SQL逻辑:
CASE WHEN gender = '男' THEN 1
WHEN gender = '女' THEN 0
ELSE NULL END AS gender_code
2. 年龄
English name: Age
伪代码指标逻辑: age = current_year - birth_year
SQL逻辑: YEAR(CURRENT_DATE()) - YEAR(birth_date)
3. 婚姻状况
English name: Marital status
伪代码指标逻辑:
IF marital_status == "未婚" THEN marital_status_code = 0
ELSEIF marital_status == "已婚" THEN marital_status_code = 1
ELSEIF marital_status == "离婚" THEN marital_status_code = 2
ELSEIF marital_status == "丧偶" THEN marital_status_code = 3
ELSE marital_status_code = NULL
SQL逻辑:
CASE WHEN marital_status = '未婚' THEN 0
WHEN marital_status = '已婚' THEN 1
WHEN marital_status = '离婚' THEN 2
WHEN marital_status = '丧偶' THEN 3
ELSE NULL END AS marital_status_code
二、信用卡类特征
1. 信用卡额度
English name: Credit card limit
伪代码指标逻辑: credit_card_limit = max(credit_limit)
SQL逻辑: MAX(credit_limit)
2. 信用卡最近6个月平均使用额度
English name: Average credit card usage in the last 6 months
伪代码指标逻辑:
IF usage_in_month_1 != NULL AND usage_in_month_2 != NULL AND usage_in_month_3 != NULL AND usage_in_month_4 != NULL AND usage_in_month_5 != NULL AND usage_in_month_6 != NULL
THEN average_usage_last_6_months = (usage_in_month_1 + usage_in_month_2 + usage_in_month_3 + usage_in_month_4 + usage_in_month_5 + usage_in_month_6) / 6
ELSE average_usage_last_6_months = NULL
SQL逻辑:
CASE WHEN usage_in_month_1 IS NOT NULL AND usage_in_month_2 IS NOT NULL AND usage_in_month_3 IS NOT NULL AND usage_in_month_4 IS NOT NULL AND usage_in_month_5 IS NOT NULL AND usage_in_month_6 IS NOT NULL
THEN (usage_in_month_1 + usage_in_month_2 + usage_in_month_3 + usage_in_month_4 + usage_in_month_5 + usage_in_month_6) / 6
ELSE NULL END AS average_usage_last_6_months
三、贷款类特征
1. 贷款总额
English name: Loan amount
伪代码指标逻辑: loan_amount = sum(loan_balance)
SQL逻辑: SUM(loan_balance)
2. 贷款逾期期数
English name: Number of overdue loans
伪代码指标逻辑:
IF overdue_month_1 > 0 THEN overdue_count = overdue_count + 1
IF overdue_month_2 > 0 THEN overdue_count = overdue_count + 1
IF overdue_month_3 > 0 THEN overdue_count = overdue_count + 1
IF overdue_month_4 > 0 THEN overdue_count = overdue_count + 1
IF overdue_month_5 > 0 THEN overdue_count = overdue_count + 1
IF overdue_month_6 > 0 THEN overdue_count = overdue_count + 1
SQL逻辑:
(CASE WHEN overdue_month_1 > 0 THEN 1 ELSE 0 END
+ CASE WHEN overdue_month_2 > 0 THEN 1 ELSE 0 END
+ CASE WHEN overdue_month_3 > 0 THEN 1 ELSE 0 END
+ CASE WHEN overdue_month_4 > 0 THEN 1 ELSE 0 END
+ CASE WHEN overdue_month_5 > 0 THEN 1 ELSE 0 END
+ CASE WHEN overdue_month_6 > 0 THEN 1 ELSE 0 END) AS overdue_count
根据人行征信,做特征衍生,写3个指标的中英文名,并且写出指标的特征类型,以及特征的风险类型,对每个特效写伪代码指标逻辑和sql逻辑,特征衍生的经典一点,独特一点
指标一:负债比例(Debt Ratio)
特征类型:数值型
风险类型:风险越高,负债比例越高
伪代码指标逻辑:
- 计算个人所有贷款的总额
- 计算个人所有信用卡的总额
- 计算个人所有负债的总额
- 计算个人所有资产的总额
- 计算负债比例 = 总负债 / 总资产
SQL逻辑:
```
SELECT SUM(loan_amount) AS total_loan, SUM(credit_card_amount) AS total_credit_card,
SUM(loan_amount + credit_card_amount) AS total_debt, SUM(asset_amount) AS total_asset,
SUM(loan_amount + credit_card_amount) / SUM(asset_amount) AS debt_ratio
FROM personal_credit_info
WHERE user_id = 'xxx';
```
指标二:信用卡使用率(Credit Utilization Ratio)
特征类型:数值型
风险类型:风险越高,信用卡使用率越高
伪代码指标逻辑:
- 计算个人信用卡已使用额度
- 计算个人信用卡总额度
- 计算信用卡使用率 = 已使用额度 / 总额度
SQL逻辑:
```
SELECT SUM(used_credit) AS total_used_credit, SUM(total_credit) AS total_credit,
SUM(used_credit) / SUM(total_credit) AS credit_utilization_ratio
FROM personal_credit_info
WHERE user_id = 'xxx' AND product_type = 'credit_card';
```
指标三:逾期次数(Overdue Times)
特征类型:离散型
风险类型:风险越高,逾期次数越多
伪代码指标逻辑:
- 统计个人所有贷款和信用卡的逾期次数
SQL逻辑:
```
SELECT COUNT(*) AS overdue_times
FROM personal_credit_info
WHERE user_id = 'xxx' AND overdue_days > 0;
```
相关推荐
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)