--查询时间点按金额区间分段的户数和金额
select d.bank_cd,
e.sname,
sum(d.count1),
sum(d.curr_bal1),
sum(d.count2),
sum(d.curr_bal2),
sum(d.count3),
sum(d.curr_bal3),
sum(d.count4),
sum(d.curr_bal4),
sum(d.count5),
sum(d.curr_bal5),
sum(d.count6),
sum(d.curr_bal6),
sum(d.count7),
sum(d.curr_bal7),
sum(d.count8),
sum(d.curr_bal8),
sum(d.count9),
sum(d.curr_bal9),
sum(d.count10),
sum(d.curr_bal10),
sum(d.count11),
sum(d.curr_bal11),
sum(d.count12),
sum(d.curr_bal12)
from (select c.bkcd bank_cd,
----------活期10万以下--------
case
when deposit_prd = '0' and amtype = '0' then
count(1)
end count1,
case
when deposit_prd = '0' and amtype = '0' then
sum(abs(c.curr_bal))
end curr_bal1,
--------活期10-20万-----------------
case
when deposit_prd = '0' and amtype = '1' then
count(1)
end count2,
case
when deposit_prd = '0' and amtype = '1' then
sum(abs(c.curr_bal))
end curr_bal2,
--------活期20-30万-----------------------------
case
when deposit_prd = '0' and amtype = '2' then
count(1)
end count3,
case
when deposit_prd = '0' and amtype = '2' then
sum(abs(c.curr_bal))
end curr_bal3,
----------活期30-50万--------------------------------------
case
when deposit_prd = '0' and amtype = '3' then
count(1)
end count4,
case
when deposit_prd = '0' and amtype = '3' then
sum(abs(c.curr_bal))
end curr_bal4,
---------活期50-100万-------------------------------------
case
when deposit_prd = '0' and amtype = '4' then
count(1)
end count5,
case
when deposit_prd = '0' and amtype = '4' then
sum(abs(c.curr_bal))
end curr_bal5,
---------活期100万以上----------------------------------------
case
when deposit_prd = '0' and amtype = '5' then
count(1)
end count6,
case
when deposit_prd = '0' and amtype = '5' then
sum(abs(c.curr_bal))
end curr_bal6,
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------- ----------定期10万以下---------------------
case
when deposit_prd <> '0' and amtype = '0' then
count(1)
end count7,
case
when deposit_prd <> '0' and amtype = '0' then
sum(abs(c.curr_bal))
end curr_bal7,
----------定期10-20万----------------------------
case
when deposit_prd <> '0' and amtype = '1' then
count(1)
end count8,
case
when deposit_prd <> '0' and amtype = '1' then
sum(abs(c.curr_bal))
end curr_bal8,
---------定期20-30万-----------------------------------------
case
when deposit_prd <> '0' and amtype = '2' then
count(1)
end count9,
case
when deposit_prd <> '0' and amtype = '2' then
sum(abs(c.curr_bal))
end curr_bal9,
---------定期30-50万---------------------------------
case
when deposit_prd <> '0' and amtype = '3' then
count(1)
end count10,
case
when deposit_prd <> '0' and amtype = '3' then
sum(abs(c.curr_bal))
end curr_bal10,
---------定期50-100万--------------------------------------------------
case
when deposit_prd <> '0' and amtype = '4' then
count(1)
end count11,
case
when deposit_prd <> '0' and amtype = '4' then
sum(abs(c.curr_bal))
end curr_bal11,
----------定期100万以上---------------------------------------------
case
when deposit_prd <> '0' and amtype = '5' then
count(1)
end count12,
case
when deposit_prd <> '0' and amtype = '5' then
sum(abs(c.curr_bal))
end curr_bal12
from (select bank_cd * 10000 + sbank_cd bkcd,
curr_bal,
deposit_prd,
case
when curr_bal < 100000 then
'0'
when curr_bal >= 100000 and curr_bal < 200000 then
'1'
when curr_bal >= 200000 and curr_bal < 300000 then
'2'
when curr_bal >= 300000 and curr_bal < 500000 then
'3'
when curr_bal >= 500000 and curr_bal < 1000000 then
'4'
when curr_bal >= 1000000 then
'5'
end as amtype
from bank.lnrecd_bal
where bal_date = '20120930' and bank_cd = 201) c
group by c.bkcd, c.amtype, deposit_prd) d,
qinfo.cibkinfo e
where d.bank_cd = e.bank_cd
group by d.bank_cd, e.sname
order by d.bank_cd;加红这段 加了限制条件 也有很35万条数据,真正应用到业务里面后,更具查询条件的不同数据将会更多。求解这SQL有什么方法解决查询速度吗? 有些时候数据多了 还要内存溢出
select d.bank_cd,
e.sname,
sum(d.count1),
sum(d.curr_bal1),
sum(d.count2),
sum(d.curr_bal2),
sum(d.count3),
sum(d.curr_bal3),
sum(d.count4),
sum(d.curr_bal4),
sum(d.count5),
sum(d.curr_bal5),
sum(d.count6),
sum(d.curr_bal6),
sum(d.count7),
sum(d.curr_bal7),
sum(d.count8),
sum(d.curr_bal8),
sum(d.count9),
sum(d.curr_bal9),
sum(d.count10),
sum(d.curr_bal10),
sum(d.count11),
sum(d.curr_bal11),
sum(d.count12),
sum(d.curr_bal12)
from (select c.bkcd bank_cd,
----------活期10万以下--------
case
when deposit_prd = '0' and amtype = '0' then
count(1)
end count1,
case
when deposit_prd = '0' and amtype = '0' then
sum(abs(c.curr_bal))
end curr_bal1,
--------活期10-20万-----------------
case
when deposit_prd = '0' and amtype = '1' then
count(1)
end count2,
case
when deposit_prd = '0' and amtype = '1' then
sum(abs(c.curr_bal))
end curr_bal2,
--------活期20-30万-----------------------------
case
when deposit_prd = '0' and amtype = '2' then
count(1)
end count3,
case
when deposit_prd = '0' and amtype = '2' then
sum(abs(c.curr_bal))
end curr_bal3,
----------活期30-50万--------------------------------------
case
when deposit_prd = '0' and amtype = '3' then
count(1)
end count4,
case
when deposit_prd = '0' and amtype = '3' then
sum(abs(c.curr_bal))
end curr_bal4,
---------活期50-100万-------------------------------------
case
when deposit_prd = '0' and amtype = '4' then
count(1)
end count5,
case
when deposit_prd = '0' and amtype = '4' then
sum(abs(c.curr_bal))
end curr_bal5,
---------活期100万以上----------------------------------------
case
when deposit_prd = '0' and amtype = '5' then
count(1)
end count6,
case
when deposit_prd = '0' and amtype = '5' then
sum(abs(c.curr_bal))
end curr_bal6,
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------- ----------定期10万以下---------------------
case
when deposit_prd <> '0' and amtype = '0' then
count(1)
end count7,
case
when deposit_prd <> '0' and amtype = '0' then
sum(abs(c.curr_bal))
end curr_bal7,
----------定期10-20万----------------------------
case
when deposit_prd <> '0' and amtype = '1' then
count(1)
end count8,
case
when deposit_prd <> '0' and amtype = '1' then
sum(abs(c.curr_bal))
end curr_bal8,
---------定期20-30万-----------------------------------------
case
when deposit_prd <> '0' and amtype = '2' then
count(1)
end count9,
case
when deposit_prd <> '0' and amtype = '2' then
sum(abs(c.curr_bal))
end curr_bal9,
---------定期30-50万---------------------------------
case
when deposit_prd <> '0' and amtype = '3' then
count(1)
end count10,
case
when deposit_prd <> '0' and amtype = '3' then
sum(abs(c.curr_bal))
end curr_bal10,
---------定期50-100万--------------------------------------------------
case
when deposit_prd <> '0' and amtype = '4' then
count(1)
end count11,
case
when deposit_prd <> '0' and amtype = '4' then
sum(abs(c.curr_bal))
end curr_bal11,
----------定期100万以上---------------------------------------------
case
when deposit_prd <> '0' and amtype = '5' then
count(1)
end count12,
case
when deposit_prd <> '0' and amtype = '5' then
sum(abs(c.curr_bal))
end curr_bal12
from (select bank_cd * 10000 + sbank_cd bkcd,
curr_bal,
deposit_prd,
case
when curr_bal < 100000 then
'0'
when curr_bal >= 100000 and curr_bal < 200000 then
'1'
when curr_bal >= 200000 and curr_bal < 300000 then
'2'
when curr_bal >= 300000 and curr_bal < 500000 then
'3'
when curr_bal >= 500000 and curr_bal < 1000000 then
'4'
when curr_bal >= 1000000 then
'5'
end as amtype
from bank.lnrecd_bal
where bal_date = '20120930' and bank_cd = 201) c
group by c.bkcd, c.amtype, deposit_prd) d,
qinfo.cibkinfo e
where d.bank_cd = e.bank_cd
group by d.bank_cd, e.sname
order by d.bank_cd;加红这段 加了限制条件 也有很35万条数据,真正应用到业务里面后,更具查询条件的不同数据将会更多。求解这SQL有什么方法解决查询速度吗? 有些时候数据多了 还要内存溢出
--活期10以下--------
sum( case when deposit_prd = '0'and curr_bal < 100000 then 1 else 0 end ) count1,
sum( case when deposit_prd = '0'and curr_bal < 100000 then abs(curr_bal) else 0 end ) curr_bal1,
--活期10-20
sum( case when deposit_prd = '0'and curr_bal >= 100000 and curr_bal < 200000 then 1 else 0 end),
sum( case when deposit_prd = '0'and curr_bal >= 100000 and curr_bal < 200000 then abs(curr_bal) else 0 end),
--活期20-30
sum( case when deposit_prd = '0'and curr_bal >= 200000 and curr_bal < 300000 then 1 else 0 end),
sum( case when deposit_prd = '0'and curr_bal >= 200000 and curr_bal < 300000 then abs(curr_bal) else 0 end),
--活期30-50
sum( case when deposit_prd = '0'and curr_bal >= 300000 and curr_bal < 500000 then 1 else 0 end),
sum( case when deposit_prd = '0'and curr_bal >= 300000 and curr_bal < 500000 then abs(curr_bal) else 0 end),
--活期50-100
sum( case when deposit_prd = '0'and curr_bal >= 500000 and curr_bal < 1000000 then 1 else 0 end),
sum( case when deposit_prd = '0'and curr_bal >= 500000 and curr_bal < 1000000 then abs(curr_bal) else 0 end),
--活期100以上
sum( case when deposit_prd = '0'and curr_bal >= 1000000 then 1 else 0 end),
sum( case when deposit_prd = '0'and curr_bal >= 1000000 then abs(curr_bal) else 0 end),
--定期10以下
sum( case when deposit_prd <> '0'and curr_bal < 100000 then 1 else 0 end ),
sum( case when deposit_prd <> '0'and curr_bal < 100000 then abs(curr_bal) else 0 end ),
--定期10-20
sum( case when deposit_prd <> '0'and curr_bal >= 100000 and curr_bal < 200000 then 1 else 0 end),
sum( case when deposit_prd <> '0'and curr_bal >= 100000 and curr_bal < 200000 then abs(curr_bal) else 0 end),
--定期20-30
sum( case when deposit_prd <> '0'and curr_bal >= 200000 and curr_bal < 300000 then 1 else 0 end),
sum( case when deposit_prd <> '0'and curr_bal >= 200000 and curr_bal < 300000 then abs(curr_bal) else 0 end),
--定期30-50
sum( case when deposit_prd <> '0'and curr_bal >= 300000 and curr_bal < 500000 then 1 else 0 end),
sum( case when deposit_prd <> '0'and curr_bal >= 300000 and curr_bal < 500000 then abs(curr_bal) else 0 end),
--定期50-100
sum( case when deposit_prd <> '0'and curr_bal >= 500000 and curr_bal < 1000000 then 1 else 0 end),
sum( case when deposit_prd <> '0'and curr_bal >= 500000 and curr_bal < 1000000 then abs(curr_bal) else 0 end),
--定期100以上
sum( case when deposit_prd <> '0'and curr_bal >= 1000000 then 1 else 0 end),
sum( case when deposit_prd <> '0'and curr_bal >= 1000000 then abs(curr_bal) else 0 end) from qinfo.cibkinfo e,bank.lnrecd_bal d
where e.bank_cd = d.bank_cd * 10000 + d.sbank_cd
and d.bal_date = '20120930' and d.bank_cd = 201
group by e.bank_cd, e.sname