根据人行征信，做特征衍生每类特征中，写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