数据表结构:RECORD_DT(日期)、BANK_NO (机构号)、ITEM_TP(统计类型)、CURR_CD(币种)、KPI(指标值)
- 首先筛选出上月底指标值,上季度指标值,去年底指标值,去年同期指标值
--dataofdate = '2022-06-08',每日跑上月底的数据 即 record_dt = '2022-05-31'
select
*
from xxx.quota_orc
where (record_dt = trunc(to_date('#dataofdate#','yyyymmdd') -1 month,'mm') -1 day --上月底 --2022-04-30
or record_dt = trunc(date('#dataofdate#')-1 month,'q') -1 day --上季度(最后一天) 2022-03-31
or record_dt = trunc(date('#dataofdate#')-1 month,'y') -1 day --去年底 2021-12-31
or record_dt = date(trunc(date('#dataofdate#'),'mm')-1 year) -1 day --去年同期的数据 2021-05-31
)
and bank_no in ('xxxxxxx') and curr_cd = 'bwb'
数据样例
RECORD_DT | BANK_NO | ITEM_TP | CURR_CD | KPI |
---|---|---|---|---|
2022-05-31 | xxxxxxx | 2022654 | BWB | 585984.00 |
2022-05-31 | xxxxxxx | 2022653 | BWB | 345984.00 |
2021-12-31 | xxxxxxx | 2022654 | BWB | 5342984.00 |
2021-05-31 | xxxxxxx | 2022654 | BWB | 583424.00 |
2022-04-30 | xxxxxxx | 2022654 | BWB | 4325984.00 |
2022-04-30 | xxxxxxx | 2022653 | BWB | 342984.00 |
最后统计成的结果,每行结构为:
RECORD_DT(日期)、BANK_NO (机构号)、ITEM_TP(统计类型)、CURR_CD(币种)、KPI(指标值)、LST_MTH_KPI、LST_QTR_KPI、YR_BEGIN_KPI、T_KPI
综上所述,就是以**BANK_NO(机构号)、ITEM_TP(统计类型)、CURR_CD(币种)**为key(唯一),汇总不同时期的指标,到一行。
select
t1.bank_no,t1.item_tp,t1.curr_cd,
sum(case when record_dt = trunc(to_date('#dataofdate#','yyyymmdd') -1 month,'mm') -1 day then t1.kpi else 0 end) LST_MTH_KPI,
sum(case when record_dt = trunc(date('#dataofdate#')-1 month,'q') -1 day then t1.kpi else 0 end) LST_QTR_KPI,
sum(case when record_dt = trunc(date('#dataofdate#')-1 month,'y') -1 day then t1.kpi else 0 end) YR_BEGIN_KPI,
sum(case when record_dt = date(trunc(date('#dataofdate#'),'mm')-1 year) -1 day then t1.kpi else 0 end) T_KPI
from xxx.quota_orc
where (record_dt = trunc(to_date('#dataofdate#','yyyymmdd') -1 month,'mm') -1 day --上月底 --2022-04-30
or record_dt = trunc(date('#dataofdate#')-1 month,'q') -1 day --上季度(最后一天) 2022-03-31
or record_dt = trunc(date('#dataofdate#')-1 month,'y') -1 day --去年底 2021-12-31
or record_dt = date(trunc(date('#dataofdate#'),'mm')-1 year) -1 day --去年同期的数据 2021-05-31
)
and bank_no in ('xxxxxxx') and curr_cd = 'bwb'
group by t1.bank_no,t1.item_tp,t1.curr_cd;