RWAServer/DBScript/李庚谱/010-DQL.sql

-- 首页视图的查询语句

-- -- 查询可用期次，根据当前日期查询可用的上一期数据
select *
from sys_noun a
where a.NOUNITEM = 'TERM';
-- 风险暴露分类字典
select *
from RWA_CALC_CONF_RISKEXPOSURE;



-- -- 根据期次将数据从计算结果表中查询出来

-- 表内
-- 表内业务风险暴露（一级）风险加权资产占比
select onmaster.RISK_EXPOSURE_LEV01_CODE, onmaster.RISK_EXPOSURE_LEV01_CODE_NAME, sum(RWA) as rwasum
from rwa_apm_bus_rs_onbusinessrwa_master onmaster
where onmaster.CALCINDEX = (SELECT CALCINDEX FROM RWA_CALC_RS_TASK_MASTER A WHERE A.TERM = (getLastTerm()))
group by onmaster.RISK_EXPOSURE_LEV01_CODE, onmaster.RISK_EXPOSURE_LEV01_CODE_NAME
order by onmaster.RISK_EXPOSURE_LEV01_CODE
;
-- 表外
-- 表外业务风险暴露（一级）风险加权资产占比
SELECT RISK_EXPOSURE_LEV01_CODE, RISK_EXPOSURE_LEV01_CODE_NAME, sum(RWA) as rwasum
FROM rwa_apm_bus_rs_offbusinessrwa_master offmaster
where offmaster.CALCINDEX = (SELECT CALCINDEX FROM RWA_CALC_RS_TASK_MASTER A WHERE A.TERM = (getLastTerm()))
group by offmaster.RISK_EXPOSURE_LEV01_CODE, offmaster.RISK_EXPOSURE_LEV01_CODE_NAME
order by offmaster.RISK_EXPOSURE_LEV01_CODE
;

-- 机构
-- 前十大机构信用风险加权资产合计
select *
from (select allrwa.ORGCODE, allrwa.ORGNAME, sum(sumrwa) as allSumRwa
      from (SELECT so.ORGCODE, so.ORGNAME, sum(offmaster.RWA) as sumrwa
            FROM rwa_apm_bus_rs_offbusinessrwa_master offmaster
                     left join sys_organization so on offmaster.MGMT_BRANCH_NO = so.ORGCODE
            where offmaster.CALCINDEX = (SELECT CALCINDEX FROM RWA_CALC_RS_TASK_MASTER A WHERE A.TERM = (getLastTerm()))
            group by so.ORGCODE, so.ORGNAME
            union all
            select so.ORGCODE, so.ORGNAME, sum(onmaster.RWA) as sumrwa
            from rwa_apm_bus_rs_onbusinessrwa_master onmaster
                     left join sys_organization so on onmaster.MGMT_BRANCH_NO = so.ORGCODE
            where onmaster.CALCINDEX = (SELECT CALCINDEX FROM RWA_CALC_RS_TASK_MASTER A WHERE A.TERM = (getLastTerm()))
            group by so.ORGCODE, so.ORGNAME) allrwa
      group by allrwa.ORGCODE, allrwa.ORGNAME) data
order by data.allSumRwa
limit 10
;

-- -- 全部RWA
-- -- 风险暴露（一级）风险加权资产占比

select allrwa.RISK_EXPOSURE_LEV01_CODE, allrwa.RISK_EXPOSURE_LEV01_CODE_NAME, sum(sumrwa) as allsumrwa
from (select onmaster.RISK_EXPOSURE_LEV01_CODE, onmaster.RISK_EXPOSURE_LEV01_CODE_NAME, sum(RWA) as sumrwa
      from rwa_apm_bus_rs_onbusinessrwa_master onmaster
      where onmaster.CALCINDEX = (SELECT CALCINDEX FROM RWA_CALC_RS_TASK_MASTER A WHERE A.TERM = (getLastTerm()))
      group by onmaster.RISK_EXPOSURE_LEV01_CODE, onmaster.RISK_EXPOSURE_LEV01_CODE_NAME
      union all
      SELECT RISK_EXPOSURE_LEV01_CODE, RISK_EXPOSURE_LEV01_CODE_NAME, sum(RWA) as sumrwa
      FROM rwa_apm_bus_rs_offbusinessrwa_master offmaster
      where offmaster.CALCINDEX = (SELECT CALCINDEX FROM RWA_CALC_RS_TASK_MASTER A WHERE A.TERM = (getLastTerm()))
      group by offmaster.RISK_EXPOSURE_LEV01_CODE, offmaster.RISK_EXPOSURE_LEV01_CODE_NAME) allrwa
group by allrwa.RISK_EXPOSURE_LEV01_CODE, allrwa.RISK_EXPOSURE_LEV01_CODE_NAME
order by allrwa.RISK_EXPOSURE_LEV01_CODE
;

-- -- 风险暴露（一级）缓释前风险暴露占比
select allrwa.RISK_EXPOSURE_LEV01_CODE, allrwa.RISK_EXPOSURE_LEV01_CODE_NAME, sum(sumead) as allsumead
from (select onmaster.RISK_EXPOSURE_LEV01_CODE, onmaster.RISK_EXPOSURE_LEV01_CODE_NAME, sum(EAD) as sumead
      from rwa_apm_bus_rs_onbusinessrwa_master onmaster
      where onmaster.CALCINDEX = (SELECT CALCINDEX FROM RWA_CALC_RS_TASK_MASTER A WHERE A.TERM = (getLastTerm()))
      group by onmaster.RISK_EXPOSURE_LEV01_CODE, onmaster.RISK_EXPOSURE_LEV01_CODE_NAME
      union all
      SELECT RISK_EXPOSURE_LEV01_CODE, RISK_EXPOSURE_LEV01_CODE_NAME, sum(EAD) as sumead
      FROM rwa_apm_bus_rs_offbusinessrwa_master offmaster
      where offmaster.CALCINDEX = (SELECT CALCINDEX FROM RWA_CALC_RS_TASK_MASTER A WHERE A.TERM = (getLastTerm()))
      group by offmaster.RISK_EXPOSURE_LEV01_CODE, offmaster.RISK_EXPOSURE_LEV01_CODE_NAME) allrwa
group by allrwa.RISK_EXPOSURE_LEV01_CODE, allrwa.RISK_EXPOSURE_LEV01_CODE_NAME
order by allrwa.RISK_EXPOSURE_LEV01_CODE
;


-- 缓释
-- -- 信用风险缓释工具当期缓释覆盖金额占比
select reg.QUALFIED_CREDIT_TOOL_TYPE, reg.QUALFIED_CREDIT_TOOL_TYPE_NAME, sum(reg.COVERAGE_RISK) as SUMCOVERAGE_RISK
from rwa_apm_bus_rs_regularrcs_master reg
where reg.CALCINDEX = (SELECT CALCINDEX FROM RWA_CALC_RS_TASK_MASTER A WHERE A.TERM = (getLastTerm()))
group by reg.QUALFIED_CREDIT_TOOL_TYPE, reg.QUALFIED_CREDIT_TOOL_TYPE_NAME
order by reg.QUALFIED_CREDIT_TOOL_TYPE
;


-- RWA 缓释
-- --风险暴露分类（一级）当期缓释情况
select allrwa.RISK_EXPOSURE_LEV01_CODE,
       allrwa.RISK_EXPOSURE_LEV01_CODE_NAME,
       sum(sumead)               as allsumead,
       sum(ALLSUM_COVERAGE_RISK) as sumALLSUM_COVERAGE_RISK,
       sum(ALLUNFINISH_EAD)      as SUMALLUNFINISH_EAD
from (select onmaster.RISK_EXPOSURE_LEV01_CODE
           , onmaster.RISK_EXPOSURE_LEV01_CODE_NAME
           , sum(EAD)               as sumead
           , sum(SUM_COVERAGE_RISK) as ALLSUM_COVERAGE_RISK
           , sum(UNFINISH_EAD)      as ALLUNFINISH_EAD
      from rwa_apm_bus_rs_onbusinessrwa_master onmaster
      where onmaster.CALCINDEX = (SELECT CALCINDEX FROM RWA_CALC_RS_TASK_MASTER A WHERE A.TERM = (getLastTerm()))
      group by onmaster.RISK_EXPOSURE_LEV01_CODE, onmaster.RISK_EXPOSURE_LEV01_CODE_NAME
      union all
      SELECT RISK_EXPOSURE_LEV01_CODE
           , RISK_EXPOSURE_LEV01_CODE_NAME
           , sum(EAD)               as sumead
           , sum(SUM_COVERAGE_RISK) as ALLSUM_COVERAGE_RISK
           , sum(UNFINISH_EAD)      as ALLUNFINISH_EAD
      FROM rwa_apm_bus_rs_offbusinessrwa_master offmaster
      where offmaster.CALCINDEX = (SELECT CALCINDEX FROM RWA_CALC_RS_TASK_MASTER A WHERE A.TERM = (getLastTerm()))
      group by offmaster.RISK_EXPOSURE_LEV01_CODE, offmaster.RISK_EXPOSURE_LEV01_CODE_NAME) allrwa
group by allrwa.RISK_EXPOSURE_LEV01_CODE, allrwa.RISK_EXPOSURE_LEV01_CODE_NAME
order by allrwa.RISK_EXPOSURE_LEV01_CODE
;


-- 计算任务过程管理

SELECT CALCINDEX        AS calcIndex,
       SDATE            AS startDate,
       EDATE            AS endDate,
       TERM             AS term,
       DATA_DATE        AS dataDate,
       CALCTASK_TYPE_ID AS calcTaskTypeId,
       CALC_TOOK        AS calcTook,
       CALC_START_TIME  AS calcStartTime,
       CALC_END_TIME    AS calcEndTime,
       CALC_START_TYPE  AS calcStartType,
       CALC_ACCOUNT     AS calcAccount,
       CALC_STATE       AS calcState
FROM RWA_CALC_RS_TASK