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
;
-- 表外
-- 表外业务风险暴露（一级）风险加权资产占比
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
;

-- 机构
-- 前十大机构信用风险加权资产合计
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
;

-- -- 风险暴露（一级）缓释前风险暴露占比
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
;


-- 缓释
-- -- 信用风险缓释工具当期缓释覆盖金额占比
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
;


-- 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 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
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
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
;