数仓搭建实操(传统数仓orale):DM数据集市层

需求 :

根据映射表建表

个贷客户违约信息表

建表

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 )

主查询把表用内连接连接起来>>获取想要的表字段