0
0
0
1. 云栖社区>
2. 博客>
3. 正文

# 都是标量子查询惹的祸

--201406 XX

select SUM(UNPOSTING_AMT_CYC) 分期未结清余额,

SUM(greatest(nvl(BAL_TOTAL,0),0)) 余额,COUNT(1),DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end 额度区间,

CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

else '非小企业主' end 小企业主标识

from riskpubstrategy.lwt_ambs_cc_201406 A

group by DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end,

CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

else '非小企业主' end;

--201406 XX

select SUM(UNPOSTING_AMT_CYC) 分期未结清余额,

SUM(greatest(nvl(BAL_TOTAL,0),0)) 余额,COUNT(1),DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end 额度区间/*,

CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

else '非小企业主' end 小企业主标识*/

from riskpubstrategy.lwt_ambs_cc_201406 A

group by DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end/*,

CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

else '非小企业主' end*/

create index ind_MICRO_BUSI_DATABASE_acct on RISKPUBSC.MICRO_BUSI_DATABASE(acct) NOLOGGING parallel 8;

alter index ind_MICRO_BUSI_DATABASE_acct NOPARALLEL;

--201406 XX

select SUM(UNPOSTING_AMT_CYC) 分期未结清余额,

SUM(greatest(nvl(BAL_TOTAL,0),0)) 余额,COUNT(1),DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end 额度区间,

CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

else '非小企业主' end 小企业主标识

from riskpubstrategy.lwt_ambs_cc_201406 A

group by DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end,

CASE WHEN EXISTS (SELECT 1 FROM RISKPUBSC.MICRO_BUSI_DATABASE B WHERE STATUS_CURR IS NOT NULL AND A.ACCT= B.ACCT) THEN '小企业主'

else '非小企业主' end;

SELECT v.TABLE_NAME,

v.BLOCKS,

v.table_size2,

v.NUM_ROWS,

v.LAST_ANALYZED

FROM vw_table_lhr v

WHERE v.TABLE_NAME IN ('LWT_AMBS_CC_201406',

'MICRO_BUSI_DATABASE');

--201406 XX

select SUM(UNPOSTING_AMT_CYC) 分期未结清余额,

SUM(greatest(nvl(BAL_TOTAL,0),0)) 余额,COUNT(1),DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end 额度区间,

CASE WHEN b.ACCT is not null THEN '小企业主'

else '非小企业主' end 小企业主标识

from riskpubstrategy.lwt_ambs_cc_201406 A

LEFT outer join RISKPUBSC.MICRO_BUSI_DATABASE B

on (a.ACCT=b.ACCT)

group by DELQ_LEVEL_CI,city,FQ_TOTAL,

CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '1.[0,500)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 500

AND greatest(nvl(BAL_TOTAL,0),0) 5000 THEN '2.[500,5000)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 5000

AND greatest(nvl(BAL_TOTAL,0),0) 20000 THEN '3.[5000,2W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 20000

AND greatest(nvl(BAL_TOTAL,0),0) 50000 THEN '4[2W,5W)'

WHEN greatest(nvl(BAL_TOTAL,0),0) >= 50000 THEN '5[5W,+)'

else '其他' end,

CASE WHEN b.ACCT is not null THEN '小企业主'

else '非小企业主' end

①标量子查询的涉及的表的关联列必须要有索引或者关联列是主键列

②标量子查询的涉及的表的数据量不能太大，数据量太大即使关联列有索引依然很慢的

③对同一个表不能有多个标量子查询，这样性能也不会很高

Select (select a from taba t1 where t1.id=t.id) a,

(select b from taba t1 where t1.id=t.id) b,

(select c from taba t1 where t1.id=t.id) c

……….

From tabb ;

+ 关注