需求 :
根据映射表建表
个贷客户违约信息表
建表
CREATE TABLE DM.PERSON_LOAN_WY_INFO(DATE_DT DATE,CUST_CUNT NUMBER,CUST_FIN NUMBER,CUST_BAD NUMBER,CUST_FIN_PER VARCHAR2(30),CUST_BAD_PER VARCHAR2(30),CUST_EXC_RAT VARCHAR2(30)
);
COMMENT ON COLUMN PERSON_LOAN_WY_INFO.DATE_DT IS '报告日期';
COMMENT ON COLUMN PERSON_LOAN_WY_INFO.CUST_CUNT IS '总客户数';
COMMENT ON COLUMN PERSON_LOAN_WY_INFO.CUST_FIN IS '好客户数';
COMMENT ON COLUMN PERSON_LOAN_WY_INFO.CUST_BAD IS '坏客户数';
COMMENT ON COLUMN PERSON_LOAN_WY_INFO.CUST_FIN_PER IS '好客户数占比';
COMMENT ON COLUMN PERSON_LOAN_WY_INFO.CUST_BAD_PER IS '坏客户数占比';
COMMENT ON COLUMN PERSON_LOAN_WY_INFO.CUST_EXC_RAT IS '逾期率';插入数据
INSERT INTO DM.PERSON_LOAN_WY_INFO
SELECT SYSDATE AS DATE_DT,COUNT(LA.RT_ACCT_NUM) AS CUST_CUNT,SUM(CASE WHEN TO_DATE(LA.TR_SYS_DATE,'YYYYMMDD') - TO_DATE(LA.SCHE_PAY_DATE,'YYYYMMDD') <= 40 THEN 1 ELSE 0 END ) AS CUST_FIN
----(交易日期-计划还款日期)<=40天 属于好客户,SUM(CASE WHEN TO_DATE(LA.TR_SYS_DATE,'YYYYMMDD') - TO_DATE(LA.SCHE_PAY_DATE,'YYYYMMDD') <= 40 THEN 0 ELSE 1 END ) AS CUST_BAD,ROUND(SUM(CASE WHEN TO_DATE(LA.TR_SYS_DATE,'YYYYMMDD') - TO_DATE(LA.SCHE_PAY_DATE,'YYYYMMDD') <= 40 THEN 1 ELSE 0 END ) / COUNT(LA.RT_ACCT_NUM) * 100,2) || '%' AS CUST_FIN_PER,ROUND(SUM(CASE WHEN TO_DATE(LA.TR_SYS_DATE,'YYYYMMDD') - TO_DATE(LA.SCHE_PAY_DATE,'YYYYMMDD') <= 40 THEN 0 ELSE 1 END ) / COUNT(LA.RT_ACCT_NUM) * 100,2) || '%' AS CUST_BAD_PER,ROUND(SUM(CASE WHEN TO_DATE(LA.TR_SYS_DATE,'YYYYMMDD') > TO_DATE(LA.SCHE_PAY_DATE,'YYYYMMDD') THEN 1 ELSE 0 END) / COUNT(LA.RT_ACCT_NUM) * 100,2) ||'%' AS CUST_EXC_RATFROM DWD.LN_LNP_ACCT_AMWKPL07 LA ;验证/查看表内数据
SELECT * FROM DM.PERSON_LOAN_WY_INFO;不良贷款信息表
建表
CREATE TABLE DM.BAD_LOAN_CUST(DATE_DT DATE,ECIF_CUST_NO VARCHAR2(20),CUSTNAME VARCHAR2(20),CUST_TYPE CHAR(1),RT_CUST_NUM VARCHAR2(50),ORG_INSTN_COD VARCHAR2(50),TR_TRAN_AMT NUMBER,CREDCAPI NUMBER,TCAPI NUMBER
);
COMMENT ON COLUMN BAD_LOAN_CUST.DATE_DT IS '报告日期';
COMMENT ON COLUMN BAD_LOAN_CUST.ECIF_CUST_NO IS '客户编号';
COMMENT ON COLUMN BAD_LOAN_CUST.CUSTNAME IS '客户姓名';
COMMENT ON COLUMN BAD_LOAN_CUST.CUST_TYPE IS '客户类型';
COMMENT ON COLUMN BAD_LOAN_CUST.RT_CUST_NUM IS '贷款账号';
COMMENT ON COLUMN BAD_LOAN_CUST.ORG_INSTN_COD IS '组织机构代码';
COMMENT ON COLUMN BAD_LOAN_CUST.TR_TRAN_AMT IS '放款金额';
COMMENT ON COLUMN BAD_LOAN_CUST.CREDCAPI IS '额度金额';
COMMENT ON COLUMN BAD_LOAN_CUST.TCAPI IS '贷款金额';插入数据
INSERT INTO DM.BAD_LOAN_CUST
WITH TEMP AS (SELECT HC.ECIF_CUST_NO,2 AS CUST_TYPEFROM DWD.CI_CIPH_HIGH_CUST_INFO HC UNION ALL SELECTLCI.ECIF_CUST_NO,1 AS CUST_TYPEFROM DWD.CI_CIPL_LOW_CUST_INFO LCI
)
SELECT SYSDATE AS DATA_DT,LC.ECIF_CUST_NO AS ECIF_CUST_NO,LC.CUSTNAME AS CUSTNAME,TEMP.CUST_TYPE,LC.LOANUSE AS RT_CUST_NUM,LCC.ACBANKID AS ORG_INSTN_COD,L06.TR_TRAN_AMT AS TR_TRAN_AMT,LC.CREDCAPI AS CREDCAPI,LC.TCAPI AS TCAPI
FROM DWD.LN_LNP_CTRT_CBAPP LC
INNER JOIN TEMP
ON LC.ECIF_CUST_NO = TEMP.ECIF_CUST_NO
INNER JOIN DWD.LN_LNP_CUST_CBCREDCUST LCC
ON LC.ECIF_CUST_NO = LCC.ECIF_CUST_NO
INNER JOIN DWD.LN_LNP_ACCT_AMWKPL06 L06
ON LC.LOANACNO = L06.RT_CUST_NUM;分析
需要的表
映射表里面给出的有:
LN_LNP_CTRT_CBAPP 个贷申请信息表;
LN_LNP_CUST_CBCREDCUST 个贷客户额度管理表;
LN_LNP_ACCT_AMWKPL06 个贷放款流水表;
目标表里面有客户类型 CUST_TYPE
CI_CIPH_HIGH_CUST_INFO 高端客户信息表>>获取高端客户信息
CI_CIPL_LOW_CUST_INFO 低端客户信息表>>获取低端客户信息
用union all 把两张表的数据上下拼接>>增加伪列 1 , 2 区分客户类型>>为了简化sql, 使用CTE公共表达式(with as )
主查询把表用内连接连接起来>>获取想要的表字段
