数仓搭建实操(传统数仓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_RAT

FROM 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_TYPE
  FROM DWD.CI_CIPH_HIGH_CUST_INFO HC 
  UNION ALL 
  SELECT
       LCI.ECIF_CUST_NO,
       1 AS CUST_TYPE
  FROM 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 )

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