1 绑定参数之前------------------------94.45秒
2 区块日报表耗时-----------------------93.367秒
3 variable startdate varchar2(15);
4 variable enddate varchar2(15);
5 variable areaName varchar2(15);
6 exec :startdate := '2010-10';
7 exec :enddate := '2014-10';
8 select tt0.ny rq,
9 tt0.ycyl1 ycyl1,
10 tt0.ycyl ycyl,
11 tt0.ycsl ycsl,
12 tt0.ycql ycql,
13 tt0.hs ychs,
14 tt0.dym dym,
15 tt0.yy yy,
16 tt1.skcw cycx,
17 tt2.cyjzjs cyzjs,
18 tt3.cyjkjs cykjs,
19 tt4.dncy1 dncy1,
20 tt4.dncy dncy,
21 tt4.dncsl dncsl,
22 tt4.dncql dncql,
23 tt4.dnhs dnhs,
24 tt5.zjcyl zjcyl,
25 tt5.zjcy zjcy,
26 tt5.zjcs zjcs,
27 tt5.zjcq zjcq,
28 tt5.zjhs zjhs,
29 tt6.rzl rzl,
30 tt6.dylj dylj,
31 tt6.dnlj dnlj,
32 tt6.jkyl jkpjyl,
33 tt7.skcw zscx,
34 tt8.zsjzjs zsjzs,
35 tt9.zsjkjs zskjs
36 from (select to_char(t1.rq, 'yyyy-mm') ny,
37 sum(t1.rcyl1) ycyl1,
38 sum(t1.rcyl) ycyl,
39 sum(t1.rcsl) ycsl,
40 sum(t1.rcql) ycql,
41 avg(t1.hs) hs,
42 avg(t1.dym) dym,
43 avg(t1.yy) yy
44 from ycba01 t1, ycaa01 t2
45 where t1.jh = t2.jh
46 and t2.Qkmc =:areaName
47 and to_char(t1.rq, 'yyyy-mm') >= :startdate
48 and to_char(t1.rq, 'yyyy-mm') <=:enddate
49 group by to_char(t1.rq, 'yyyy-mm')
50 order by to_char(t1.rq, 'yyyy-mm')) tt0
51 left join (select t1.skcw, t1.skrq
52 from (select count(skcw) count,
53 skcw,
54 to_char(skrq, 'yyyy-mm') skrq
55 from ycaa11 tsk, ycaa01 tjw, ycba01 tcy
56 where tsk.jh = tcy.jh
57 and tsk.jh = tjw.jh
58 and tjw.qkmc =:areaName
59 and to_char(tsk.skrq, 'yyyy-mm') >= :startdate
60 and to_char(tsk.skrq, 'yyyy-mm') <= :enddate
61 group by skcw, skrq) t1
62 where t1.count =
63 (select max(t1.count) maxcount
64 from (select count(skcw) count,
65 skcw,
66 to_char(skrq, 'yyyy-mm') skrq
67 from ycaa11 tsk, ycaa01 tjw, ycba01 tcy
68 where tsk.jh = tcy.jh
69 and tsk.jh = tjw.jh
70 and tjw.qkmc =:areaName
71 and to_char(tsk.skrq, 'yyyy-mm') >= :startdate
72 and to_char(tsk.skrq, 'yyyy-mm-dd') <=
73 :enddate
74 group by skcw, skrq) t1)) tt1 on (tt0.ny =
75 tt1.skrq)
76 left join (select count(distinct(tcy.jh)) cyjzjs,
77 to_char(tcy.rq, 'yyyy-mm') nf
78 from ycba01 tcy, ycaa01 tjw
79 where tjw.jh = tcy.jh
80 and tjw.Qkmc =:areaName
81 and to_char(tcy.rq, 'yyyy-mm') >= substr( :startdate,0,4)
82 and to_char(tcy.rq, 'yyyy-mm') <= :enddate
83 group by to_char(tcy.rq, 'yyyy-mm')) tt2 on (tt0.ny = tt2.nf)
84 left join (select count(distinct(tcy.jh)) cyjkjs,
85 to_char(tcy.rq, 'yyyy-mm') nf
86 from ycba01 tcy, ycaa01 tjw
87 where tjw.jh = tcy.jh
88 and tjw.Qkmc = :areaName
89 and to_char(tcy.rq, 'yyyy-mm') >=substr(:startdate,0,4)
90 and to_char(tcy.rq, 'yyyy-mm') <=:enddate
91 group by to_char(tcy.rq, 'yyyy-mm')) tt3 on (tt0.ny = tt3.nf)
92 left join (select rq ny,
93 sum(rcyl1) over(partition by rq1 order by rq) dncy1,
94 sum(rcyl) over(partition by rq1 order by rq) dncy,
95 sum(rcsl) over(partition by rq1 order by rq) dncsl,
96 sum(rcql / 10000) over(partition by rq1 order by rq) dncql,
97 avg(hs) over(partition by rq1 order by rq) dnhs
98 from (select to_char(t1.rq, 'yyyy') rq1,
99 to_char(t1.rq, 'yyyy-mm') rq,
100 sum(rcyl1) rcyl1,
101 sum(rcyl) rcyl,
102 sum(rcsl) rcsl,
103 sum(rcql) rcql,
104 avg(hs) hs
105 from ycba01 t1, ycaa01 t2
106 where t1.jh = t2.jh
107 and t2.qkmc = :areaName
108 and to_char(t1.rq, 'yyyy-mm') >=:startdate
109 and to_char(t1.rq, 'yyyy-mm') <=:enddate
110 group by to_char(t1.rq, 'yyyy'),
111 to_char(t1.rq, 'yyyy-mm')
112 order by to_char(t1.rq, 'yyyy-mm'))) tt4 on (tt4.ny =
113 tt0.ny)
114 left join (select rq ny,
115 sum(rcyl1 / 10000) over(order by rq) zjcyl,
116 sum(rcyl / 10000) over(order by rq) zjcy,
117 sum(rcsl / 10000) over(order by rq) zjcs,
118 sum(rcql / 10000) over(order by rq) zjcq,
119 avg(hs) over(order by rq) zjhs
120 from (select to_char(t1.rq, 'yyyy-mm') rq,
121 sum(rcyl1) rcyl1,
122 sum(rcyl) rcyl,
123 sum(rcsl) rcsl,
124 sum(rcql) rcql,
125 avg(hs) hs
126 from ycba01 t1, ycaa01 t2
127 where t1.jh = t2.jh
128 and t2.qkmc =:areaName
129 and to_char(t1.rq, 'yyyy-mm') >= :startdate
130 and to_char(t1.rq, 'yyyy-mm') <=:enddate
131 group by to_char(t1.rq, 'yyyy'),
132 to_char(t1.rq, 'yyyy-mm')
133 order by to_char(t1.rq, 'yyyy-mm'))) tt5 on (tt5.ny =
134 tt0.ny)
135 left join (select t2.rq rq, t2.rzl, t2.jkyl, t3.dylj, t4.dnlj
136 from (select to_char(rq, 'yyyy-mm') rq,
137 sum(rzsl) rzl,
138 avg(jkyl) jkyl
139 from ycba02, ycaa01
140 where to_char(rq, 'yyyy-mm') >=
141 substr('2010-10', 0, 7)
142 and to_char(rq, 'yyyy-mm') <=
143 substr('2014-10', 0, 7)
144 and ycba02.jh = ycaa01.jh
145 and ycaa01.qkmc = :areaName
146 group by to_char(rq, 'yyyy-mm')
147 order by rq) t2
148 left join (select rq,
149 rq1,
150 sum(rzsl) over(partition by rq1 order by rq) dylj
151 from (select to_char(rq, 'yyyy-mm') rq,
152 to_char(rq, 'yyyy') rq1,
153 sum(rzsl) rzsl
154 from ycba02, ycaa01
155 where to_char(rq, 'yyyy-mm') >=
156 substr(:startdate, 0, 7)
157 and to_char(rq, 'yyyy-mm') <=
158 substr('2014-10', 0, 7)
159 and ycba02.jh = ycaa01.jh
160 and ycaa01.qkmc = :areaName
161 group by to_char(rq, 'yyyy-mm'),
162 to_char(rq, 'yyyy'))) t3 on (t2.rq =
163 t3.rq)
164 left join (select rq, sum(rzsl) over(order by rq) dnlj
165 from (select to_char(rq, 'yyyy-mm') rq,
166 sum(rzsl) rzsl
167 from ycba02, ycaa01
168 where ycba02.jh = ycaa01.jh
169 and ycaa01.qkmc = :areaName
170 and to_char(rq, 'yyyy-mm') >= :startdate
171 and to_char(rq, 'yyyy-mm') <=:enddate
172 group by to_char(rq, 'yyyy-mm'))) t4 on (t2.rq =
173 t4.rq)) tt6 on (tt0.ny =
174 tt6.rq)
175 left join (select t1.skcw, skrq
176 from (select count(skcw) count,
177 skcw,
178 to_char(skrq, 'yyyy-mm') skrq
179 from ycaa11 tsk, ycaa01 tjw, ycba02 tzs
180 where tsk.jh = tzs.jh
181 and tsk.jh = tjw.jh
182 and tjw.qkid = 2962
183 and to_char(tsk.skrq, 'yyyy/mm/dd') <= '2014/12/12'
184 group by skcw, skrq) t1
185 where t1.count =
186 (select max(t1.count) maxcount
187 from (select count(skcw) count,
188 skcw,
189 to_char(skrq, 'yyyy-mm') skrq
190 from ycaa11 tsk, ycaa01 tjw, ycba02 tzs
191 where tsk.jh = tzs.jh
192 and tsk.jh = tjw.jh
193 and tjw.qkmc=:areaName
194 and to_char(tsk.skrq, 'yyyy/mm/dd') <=
195 '2014/12/12'
196 group by skcw, skrq) t1)) tt7 on (tt7.skrq =
197 tt0.ny)
198 left join (select to_char(tzs.rq, 'yyyy-mm') rq,
199 count(distinct tzs.jh) zsjzjs
200 from ycba02 tzs, ycaa01 tjw
201 where tjw.jh = tzs.jh
202 and tjw.qkmc = :areaName
203 and to_char(tzs.rq, 'yyyy-mm') >= :startdate
204 and to_char(tzs.rq, 'yyyy-mm,') <= :enddate
205 group by to_char(tzs.rq, 'yyyy-mm')) tt8 on (tt0.ny = tt8.rq)
206 left join (select count(distinct jh) zsjkjs, rq
207 from (select sum(tcy.rzsl) rzsl,
208 tcy.jh,
209 to_char(tcy.rq, 'yyyy-mm') rq
210 from ycba02 tcy, ycaa01 tjw
211 where tjw.jh = tcy.jh
212 and tjw.Qkmc =:areaName
213 and to_char(tcy.rq, 'yyyy-mm') >=substr(:startdate,0,4)
214 and to_char(tcy.rq, 'yyyy-mm') <= :enddate
215 group by tcy.jh, to_char(tcy.rq, 'yyyy-mm'))
216 where rzsl > 0
217 group by rq) tt9 on (tt0.ny = tt9.rq);
218
219
220
221
222
223
224
225
226
227
228
229
油井日报表的绑定命令 执行63秒
1 variable startdate varchar2(15);
2 variable enddate varchar2(15);
3 variable wellname varchar2(15);
4 exec :startdate := '2014-01-01';
5 exec :enddate := '2015-01-01';
6 exec :wellname := 'DB定1151';
7 select t3.qkmc,
8 to_char(t1.rq, 'yyyy-mm-dd') rq,
9 t7.skcw,
10 t1.cyfs,
11 t1.scsj,
12 t7.hd,
13 t7.skcs,
14 t1.bj,
15 t1.cc,
16 t1.cc1,
17 t1.bs,
18 t1.dym,
19 t1.yy,
20 t1.bx,
21 t1.ty,
22 t1.rcyl1,
23 t1.rcyl,
24 t1.rcsl,
25 t1.rcql,
26 t1.hs,
27 t1.bz,
28 t4.ylcy1,
29 t4.ylcy,
30 t4.ylcs,
31 t4.ylcq,
32 t4.ylhs,
33 t5.nlcy1,
34 t5.nlcy,
35 t5.nlcs,
36 t5.nlcq,
37 t5.nlhs
38 from ycaa01 t2, ycab01 t3, ycba01 t1
39 left join (select rq,
40 sum(rcyl1) over(partition by rq1 order by rq) ylcy1,
41 sum(rcyl) over(partition by rq1 order by rq) ylcy,
42 sum(rcsl) over(partition by rq1 order by rq) ylcs,
43 sum(rcql / 10000) over(partition by rq1 order by rq) ylcq,
44 avg(hs * 100) over(partition by rq1 order by rq) ylhs
45 from (select to_char(rq, 'yyyy-mm') rq1,
46 rq,
47 jh,
48 sum(rcyl1) rcyl1,
49 sum(rcyl) rcyl,
50 sum(rcsl) rcsl,
51 sum(rcql) rcql,
52 avg(hs) hs
53 from ycba01
54 where ycba01.jh = :wellname
55 and to_date(substr(:enddate, 0, 7), 'yyyy/mm') <= rq
56 and to_date(substr(:startdate, 0, 7), 'yyyy/mm') >= rq
57 group by rq)) t4 on (t1.rq = t4.rq)
58 left join (select rq,
59 sum(rcyl1 / 10000) over(partition by rq1 order by rq) nlcy1,
60 sum(rcyl / 10000) over(partition by rq1 order by rq) nlcy,
61 sum(rcsl / 10000) over(partition by rq1 order by rq) nlcs,
62 sum(rcql / 1000) over(partition by rq1 order by rq) nlcq,
63 avg(hs * 100) over(order by rq) nlhs
64 from (select to_char(rq, 'yyyy') rq1,
65 rq,
66 jh,
67 sum(rcyl1) rcyl1,
68 sum(rcyl) rcyl,
69 sum(rcsl) rcsl,
70 sum(rcql) rcql,
71 avg(hs) hs
72 from ycba01
73 where ycba01.jh = :wellname
74 and to_date(substr(:startdate, 0, 4), 'yyyy') <= rq
75 and to_date(substr(:enddate, 0, 4), 'yyyy') >= rq
76 group by rq)) t5 on (t1.rq = t5.rq)
77 left join (select t1.rq, skcw, jdds1, (jdds2 - jdds1) hd, skcs
78 from ycaa11 t6, ycba01 t1
79 where t6.jh = t1.jh
80 and skrq = (select max(skrq)
81 from ycaa11
82 where t6.jh = t1.jh
83 and skrq < t1.rq)
84 and t1.jh = :wellname) t7 on (t1.rq = t7.rq)
85 where t1.jh =:wellname
86 and t1.jh = t2.jh
87 and t2.qkid = t3.qkid
88 and to_date(:startdate, 'yyyy/mm/dd') <= t1.rq
89 and to_date(:enddate, 'yyyy/mm/dd') >= t1.rq
90 order by t1.rq
