优化笔记：jsyhjkzqxx_D_20140916.gz
有几张表没有权限，所以跑不起来。
目测黄色部分比较坑爹，死了n多脑细胞才看懂，又死了n多脑细胞才改出来。对5034进行了2次扫描，并多次分组排序求和。（分组和排序算法相对来说比较耗性能）
改为只扫描一次，一次编号操作。
这个没有什么既定的规则，就是使劲想各种奇葩办法简化。（这个改写就是利用了oracle在排序的时候，把null当成无穷大。而在sqlserver上就不适用了，因为sqlserver在排序的时候把null当无穷小）
黄色部分可改为下面的代码。
select *
FROM (select f1_5034,
decode(min(rn_a) - 1, 1, 0, min(rn_a) - 1) ylqk,
min(rn_b) - 1 bbqk
from (select case
when nvl(f25_5034, 0) > 0 then null
else rn end rn_a,
case
when f25_5034 Is not Null then null
else rn end rn_b,
f1_5034
from (Select f1_5034,
f3_5034,
f25_5034,
row_number() Over(partition By f1_5034 Order By f3_5034 Desc) rn
From wind.tb_object_5034
Where f3_5034 Like '%1231'))
group by f1_5034)
where bbqk is not null
--------------------原代码-------------------------
Select fdate,zqdm,zqmc,jysc,xzjq,dqsyl,fsynx,ffxjg,fpmje,fsqpmlv,ffxcs,ffxfs,fscfxr,
fssdd,fjxqsr,jxjzr,fxgm,cqxypj,dqxypj,'NULL' gjxypj,jhht,fldzrdb,ffxgsdm,fgsjc,zqlx,nvl(bbqk,0) bbqk,fxrq,
Case When nvl(zzc,0)<>0 Then 1 Else 0 End As xxplqk,ylqk ylqk,
hxzbczl,zzc,jzc,DbrXyPj,rzzt,shzt,'1' lsyjs,Jjbz, cxsmc, cxqsr,cxjzr,bjyy,lvlx,syyhdb,FCqXypj,FDqXypj,FGjxypj,FGnxypjJgd,FGjxypjJgd,DCqXypj,
'NULL' DGjxypj,flvjz,'NULL' FYsSyr, '0' fsdq ,'NULL' fsdqsr ,'NULL' fsdjzr ,'NULL' Fsdjzr2 ,fjcjq,flljq, '1' ffxzt,flvjzlb,'1' Ffxlx, fdbrdm,fdbr,Fdbrxz,FdbrSnmJzc,fsndgncqxy,Fdbrgnpjjg,
Ffxrlb,fzgr,fzgjzr,x.f13_5015 fzgbl,x.f12_5015 fzgjg, 'NULL' fzgzqdm,fsfsh,'NULL' fjkyy,fdbrzt,fggxx,
(Select F68_1854
From wind.tb_object_1854 T
Where nvl(BbQk, 0) >= 1
And F1_1854 = F25_4516
And F4_1854 = '合并报表'
And substr(F2_1854, 5, 8) = '1231'
And f2_1854 = (Select Max(f2_1854)
From Wind.tb_object_1854
Where T.f1_1854 = f1_1854
And substr(F2_1854, 5, 8)='1231'
And f4_1854 = '合并报表')) fsnlr,
(Select F68_1854
From wind.tb_object_1854 T
Where nvl(BbQk,0) >= 2
And F1_1854 =F25_4516
And F4_1854 ='合并报表'
And substr(F2_1854, 5, 8) = '1231'
And f2_1854 = (Select substr(Max(f2_1854),1,4)-1||'1231'
From Wind.tb_object_1854
Where T.f1_1854 = f1_1854
And substr(F2_1854, 5, 8)='1231'
And f4_1854 = '合并报表')) fqnlr,
(Select F68_1854
From wind.tb_object_1854 T
Where nvl(BbQk,0) >= 3
And F1_1854 =F25_4516
And F4_1854 ='合并报表'
And substr(F2_1854, 5, 8) = '1231'
And f2_1854 = (Select substr(Max(f2_1854),1,4)-2||'1231'
From Wind.tb_object_1854
Where T.f1_1854 = f1_1854
And substr(F2_1854, 5, 8)='1231'
And f4_1854 = '合并报表')) fdqnlr,
(Select F140_1853
From wind.tb_object_1853 T
Where nvl(BbQk, 0) >= 1
And F1_1853 = F25_4516
And F4_1853 = '合并报表'
And substr(F2_1853, 5, 8) = '1231'
And f2_1853 = (Select Max(f2_1853)
From Wind.tb_object_1853
Where T.f1_1853 = f1_1853
And substr(F2_1853, 5, 8)='1231'
And f4_1853 = '合并报表')) FsnmfxrNav
, Ffxrdchqyz
From (
(SELECT to_char(&PlanTime - 1, 'YYYYMMDD') fdate,
f1_4516 zqdm,
f2_4516 zqmc,
f43_4516,
f25_4516,
f40_1429,
Case
When f12_4516 = '上交所' Then '1'
When f12_4516 = '深交所' Then '2'
When f12_4516 = '银行间' Then '3'
Else '4' End As jysc,
Case
When f2_1639 = to_char(&PlanTime - 1, 'YYYYMMDD') Then f5_1639
Else 0
End As xzjq,
Case
When f2_1639 = to_char(&PlanTime - 1, 'YYYYMMDD') Then f3_1639 Else Null End As dqsyl,
Case
When f2_1639 = to_char(&PlanTime - 1, 'YYYYMMDD') Then f9_1639
Else 0
End As fsynx,
f8_4516 ffxjg,
f7_4516 fpmje,
f5_4516 fsqpmlv,
f9_4516 ffxcs,
Case
When F10_4516 = '4' Then '1'
When F10_4516 = '0' Then '2'
When F10_4516 = '1' Then '3'
When F10_4516 = '2' Then '4'
When F10_4516 = '3' Then '5'
Else Null End As ffxfs,
(Select Min(f8_1270) From wind.tb_object_1270 Where F1_1270 = F43_4516) fscfxr,
Case f13_4516
When '上交所' Then 'H'
When '深交所' Then 'S'
When '银行间' Then 'Y'
Else Null End As fssdd,
f3_4516 fjxqsr,
f4_4516 jxjzr,
F26_4516 * 100000000 fxgm,
Case
When f47_1429 Is Not Null Then '1'
Else '0' End As jhht,
Case
When F48_1429 = '不可撤销连带责任担保' Then '1'
End As fldzrdb,
f25_4516 ffxgsdm,
f24_4516 fgsjc,
Case
When F17_4516 = '321' Then '1'
When F17_4516 = '333' Then '3'
When F17_4516 = '322' And F29_1429 Is Null Then '2'
When F17_4516 = '322' And F29_1429 = '公司债' Then '11'
When F17_4516 = '322' And F29_1429 = '中期票据' Then '13'
When F17_4516 = '323' And F29_1429 <> '可分离转债' Then '4'
When F17_4516 = '323' And F29_1429 = '可分离转债' Then '12'
When F17_4516 = '310' And F29_1429 Is Null Then '6'
When F17_4516 = '310' And F29_1429 Is Not Null Then '14'
When F17_4516 In ('331', '340') Then '7'
When F17_4516 = '341' Then '8'
When F17_4516 = '342' Then '9'
Else '0 '
End As zqlx,
f20_4516 fxrq,
Case
When F29_1429 = '可转债' Then
'中国证券监督管理委员会'
When F4_1090 = 'Q' And F29_1429 Not In ('中期票据', '短期融资券') Then
'发展和改革委员会'
Else 'NULL'
End As shzt,
Case
When (F32_1429 Like '%铁路%' And F47_1429 = '2002638') Or
(F32_1429 Like '%三峡%' And F47_1429 = '2002650') Then '1'
Else '0' End As Jjbz,
nvl((Select t.ob_object_name_1020 From Wind.tb_object_1020 t
Where f27_1429=t.ob_object_id),'NULL') CxsMc,
f15_4516 cxqsr,
f16_4516 cxjzr,
Null bjyy,
Case
When f34_1429 = '贴现' Then '贴现债券'
When f11_1429 = '固定利率' Then '固定利率'
When f11_1429 = '累进利率' Then '浮动利率-固定利率'
When f11_1429 = '浮动利率' Then '浮动利率-市场利率'
Else Null End As lvlx,
Case
When f48_1429 = '不可撤销连带责任担保' Then 1 Else 0 End As syyhdb,
NVL((SELECT f4_1703 - f39_1429
FROM wind.tb_object_1703
Where f1_1703 = f1_1429
And f3_1703 = (SELECT Min(f3_1703)
FROM wind.tb_object_1703
Where f1_1703 = f1_1429)),0) flvjz,
NVL(f15_1639,0) fjcjq,
NVL(f16_1639,0) flljq,
Case
When f47_1429 Is Not Null Then f47_1429 Else 'N' End As fdbrdm,
Case
When f34_1018 = F47_1429 Then ob_object_name_1018 Else 'N' End As fdbr,
Case
When f34_1018 = F47_1429 And F40_1018 = 2 Then '1' Else '2' End As Fdbrxz,
Case
When f34_1018 = F47_1429 And F40_1018 = '2' And
F41_1018 = 49 Then '105'
When f34_1018 = F47_1429 And F40_1018 <> '2' And
F41_1018 = 49 Then '205'
When f34_1018 = F47_1429 And F40_1018 = '2' And
F41_1018 <> 49 Then '1'
When f34_1018 = F47_1429 And F40_1018 <> '2' And
F41_1018 <> 49 Then '2'
When f34_1018 = F47_1429 And F41_1018 = '25' Then '3'
When f40_1429='2000850' Then '2'
Else '4' End As Ffxrlb,
Case
When Exists (Select 1
From wind.tb_object_1269
Where f1_1269 = f25_4516
And f85_1269 Is Null) Then 1
Else 0 End As fsfsh,
Case
When f34_1018 = F47_1429 And F41_1018 = '11' Then '1'
When f34_1018 = F47_1429 And F41_1018 = '29' Then '2'
When f34_1018 = F47_1429 And F41_1018 = '4' Then '3'
When f34_1018 = F47_1429 And F41_1018 = '27' Then '4'
When f34_1018 = F47_1429 And ob_object_name_1018 = '中国人民银行' Then '5'
When f34_1018 = F47_1429 And F41_1018 = '25' Then '6'
When f34_1018 = F47_1429 And F41_1018 = '3' Then '7'
Else '8' End As fdbrzt,
(Select f7_1271
From wind.tb_object_1271
Where f1_1271 = f43_4516) fzgjzr,
nvl((SELECT f3_1735
FROM wind.tb_object_1735 a
Where F1_1735 = F43_4516
And F2_1735 = '长期信用评级'
And f4_1735 = (SELECT Max(f4_1735)
FROM wind.tb_object_1735 b
Where a.f1_1735 = b.f1_1735
And b.f2_1735 = '长期信用评级')
And rownum = 1),'NULL') cqxypj,
NVL((SELECT f3_1735
FROM wind.tb_object_1735 c
Where F1_1735 = F43_4516
And F2_1735 = '短期信用评级'
And f4_1735 = (SELECT Max(f4_1735)
FROM wind.tb_object_1735 d
Where F43_4516 = d.f1_1735
And F2_1735 = '短期信用评级')
And rownum = 1),'NVLL') dqxypj,
NVL((SELECT f4_1454
FROM wind.tb_object_1454 c
Where F1_1454 = F25_4516
And F54_1454 = '408001000'
And f2_1454 = (SELECT Max(f2_1454)
FROM wind.tb_object_1454 d
Where F25_4516 = d.f1_1454
And F54_1454 = '408001000')),0) hxzbczl,
NVL((SELECT f145_1853
FROM wind.tb_object_1853 c
Where F1_1853 = F25_4516
And F4_1853 = '合并报表'
And f2_1853 = (SELECT Max(f2_1853)
FROM wind.tb_object_1853 d
Where F25_4516 = d.f1_1853
And F4_1853 = '合并报表')),0) zzc,
NVL((SELECT f140_1853
FROM wind.tb_object_1853 c
Where F1_1853 = F25_4516
And F4_1853 = '合并报表'
And f2_1853 = (SELECT Max(f2_1853)
FROM wind.tb_object_1853 d
Where F25_4516 = d.f1_1853
And F4_1853 = '合并报表')),0) jzc,
NVL((Select f4_1734
From wind.tb_object_1734 a, wind.tb_object_0004
Where ob_object_name_1018 = f1_0004
And f3_0004 = f2_1734
And f5_1734 = (Select Max(f5_1734)
From wind.tb_object_1734 b
Where b.f1_1734 = a.f1_1734)
And rownum = 1),'NULL') DbrXyPj,
Case
When f41_1018 = '11' Then '1'
When f41_1018 = '29' Then '2'
When f41_1018 In ('4', '5') Then '3'
When f41_1018 = '49' And OB_OBJECT_NAME_1018 = '中国人民银行' Then '5'
When f41_1018 = '17' Then '6'
Else '7' End As rzzt,
nvl((SELECT f4_1734
FROM wind.tb_object_1734 c
Where F2_1734 = F25_4516
And F3_1734 = '长期信用评级'
And f5_1734 = (SELECT Max(f5_1734)
FROM wind.tb_object_1734 d
Where c.f1_1734 = d.f1_1734
And d.F3_1734 = '长期信用评级')
And rownum = 1),'NULL') FCqXypj,
NVL((SELECT f4_1734
FROM wind.tb_object_1734 c
Where F2_1734 = F25_4516
And F3_1734 = '短期信用评级'
And f5_1734 = (SELECT Max(f5_1734)
FROM wind.tb_object_1734 d
Where c.f1_1734 = d.f1_1734
And d.F3_1734 = '短期信用评级')
And rownum = 1),'NULL') FDqXypj,
'NULL' FGjxypj,
NVL((Select f6_1734
FROM wind.tb_object_1734 c
Where F2_1734 = F25_4516
And f5_1734 = (Select Max(f5_1734)
From wind.tb_object_1734 d
Where c.f1_1734= d.f1_1734)
And rownum = 1),'NULL') FGnxypjJgd,
'NULL' FGjxypjJgd,
nvl((SELECT f4_1734
FROM wind.tb_object_1734 c
Where F2_1734 = F25_4516
And F3_1734 = '长期信用评级'
And f5_1734 = (SELECT Max(f5_1734)
FROM wind.tb_object_1734 d
Where c.f1_1734 = d.f1_1734
And d.F3_1734 = '长期信用评级')
And rownum = 1
),'NULL') DCqXypj,
(SELECT Case
When F4_1641 = '1年期定期存款利率' Then 2082
When F4_1641 = '半年期定期存款利率' Then 2061
When F4_1641 = '三个月定期存款利率' Then 2070
When F4_1641 = '7天回购定盘平均值' Then 4016
When F4_1641 = '7天回购利率平均值' And f6_1641 = 'B2W' Then 4003
When F4_1641 = '7天回购利率平均值' And f6_1641 = 'B_2W' Then 4002
When F4_1641 = '7天回购利率平均值' And f6_1641 = 'B_1M' Then 3976
When F4_1641 = 'LIBOR' And f6_1641 = 'LIBOR_6M-USD' Then 5041
When F4_1641 = 'SHIBOR' And f6_1641 = 'SHIBOR_3M(10)' Then 7480
When F4_1641 = 'SHIBOR' And f6_1641 = 'SHIBOR_3M(5)' Then 7606
When F4_1641 = 'SHIBOR' And
f6_1641 = 'SHIBOR_1W(120)' Then 7942
When F4_1641 = 'SHIBOR' And f6_1641 = 'SHIBOR_1Y(10)' Then 9595
When F4_1641 = 'SHIBOR' And f6_1641 = 'SHIBOR_1Y(20)' Then 13216
End x FROM wind.tb_object_1641 Where f1_1641 = f43_4516) flvjzlb,
NVL((SELECT f140_1853
FROM wind.tb_object_1853
Where F1_1853 = F47_1429
And F4_1853 = '合并报表'
And f2_1853 = (SELECT Max(f2_1853)
FROM wind.tb_object_1853
Where F1_1853 = F47_1429
And F4_1853 = '合并报表')),0) FdbrSnmJzc,
NVL((SELECT f4_1734
FROM wind.tb_object_1734 c
Where F2_1734 = F25_4516
And F3_1734 = '长期信用评级'
And f5_1734 = (SELECT Max(f5_1734)
FROM wind.tb_object_1734 d
Where c.f1_1734 = d.f1_1734
And c.F3_1734 = '长期信用评级')
And rownum = 1),'NULL') fsndgncqxy,
NVL((Select f6_1734
FROM wind.tb_object_1734 c
Where F2_1734 = F25_4516
And F3_1734 = '长期信用评级'
And f5_1734 = (Select Max(f5_1734)
From wind.tb_object_1734 d
Where c.f1_1734 = d.f1_1734
And F3_1734 = '长期信用评级')
And rownum = 1),'NULL') Fdbrgnpjjg,
(Select Max(f3_5015) From wind.tb_object_5015 Where f2_5015 =f43_4516 And f12_5015 Is Not Null And f13_5015 Is Not Null ) fzgr
From wind.tb_object_4516
Left Join wind.tb_object_1639 On F1_1639 = F43_4516
And f2_1639 =
to_char(&PlanTime - 1, 'YYYYMMDD')
Left Join wind.tb_object_1429 On F1_1429 = F43_4516 And (F30_1429>=to_char(&PlanTime,'yyyymmdd') Or F30_1429 Is Null)
Inner Join wind.tb_object_1090 On f1_1429 = f2_1090
Left Join wind.tb_object_1018 On F25_4516 = F34_1018) a
--取报表情况和盈利情况
Left Join (Select a.f1_5034, decode(ylqk, 1, 0, ylqk) ylqk, bbqk
From (Select f1_5034, nvl(Min(rn) - 1, Sum) ylqk
From (Select f1_5034,f3_5034,f25_5034,row_number() Over(partition By f1_5034 Order By f3_5034 Desc) rn,
Sum(1) Over(partition By f1_5034) Sum
From wind.tb_object_5034 Where f3_5034 Like '%1231') t
Where nvl(f25_5034, 0) <= 0
Group By f1_5034, Sum) a,
(Select f1_5034, nvl(Min(rn) - 1, Sum) bbqk
From (Select f1_5034, f3_5034, f25_5034,row_number() Over(partition By f1_5034 Order By f3_5034 Desc) rn,
Sum(1) Over(partition By f1_5034) Sum
From wind.tb_object_5034 Where f3_5034 Like '%1231') t
Where f25_5034 Is Null
Group By f1_5034, Sum) b
Where a.f1_5034 = b.f1_5034) tt On a.ffxgsdm = tt.f1_5034
--取fggxx字段
Inner Join (Select Distinct f1_4516, f2_4516,
Case When F37_1673 Is Null And Not Exists
(Select 1
From wind.tb_object_1673 b
Where f37_1673 Is Not Null
And b.f1_1673 = a.f1_1673) Then 1 Else 0 End As fggxx
From wind.tb_object_4516
Left Join wind.tb_object_1673 a On f1_1673 = f25_4516) xx On a.zqdm =
xx.f1_4516
And a.zqmc =
xx.f2_4516
-- 取转股比例和转股价格字段
Left Join wind.tb_object_5015 x On a.f43_4516 =x.f2_5015 And x.f3_5015=a.fzgr
--取企业（公司）债券发行人待偿还的企业债券余额
Left Join (Select f40_1429 ,Sum(f3_1640*10000) Ffxrdchqyz From wind.tb_object_1429,wind.tb_object_1640 Where (F30_1429> to_char(&PlanTime-1,'yyyymmdd') Or F30_1429 Is Null)
And F1_1640=F1_1429
Group By f40_1429) yy On a.f40_1429=yy.f40_1429
)
|
|
|
