Hi everyone,
I have a big query (see below attached) in which all where clauses have sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel').
The value of ec.instantance_flux is like the format: "vx6000__12channel". I want to replace "channel" with "myChannel": "vx6000__12myChannel".
Since the query take all most 40 minutes, I want to "optimize" it to see if i can get it done in shorter time. My question is: are there any way just do once:
replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel')
and save its result into "temp". The other where clause just use
sc.channel = temp
instead of doing
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel')
for 12 times in my query.
Thanks for your help in advance.
OUyang
#####################################################333
select rt_data.r_flowmeter_caliber as r_flowmeter_caliber,
rt_data.r_max01_sloc as r_max01_sloc,
rt_data.r_max01_sdata as r_max01_sdata,
rt_data.r_max01_sdate as r_max01_sdate,
rt_data.r_min01_sdata as r_min01_sdata,
rt_data.r_min01_sdate as r_min01_sdate,
rt_data.r_avg01_sdata as r_avg01_sdata,
acc_data.r_end_sdate as r_end_sdate,
acc_data.r_end_sdata as r_end_sdata,
acc_data.r_start_sdate as r_start_sdate,
acc_data.r_start_sdata as r_start_sdata,
acc_data.r_acc_sdata as r_acc_sdata
from
( select ec.flowmeter_caliber as r_flowmeter_caliber,
max01.r_sloc as r_max01_sloc,
round(max01.r_sdata*100)/100 as r_max01_sdata,
max01.r_sdate as r_max01_sdate,
round(min01.r_sdata*100)/100 as r_min01_sdata,
min01.r_sdate as r_min01_sdate,
round(avg01.r_sdata*100)/100 as r_avg01_sdata,
max01.r_channel as r_channel,
max01.r_sid as r_sid,
max01.r_sloc as r_sloc
from (
select max(rd01.sensor_data) as r_sdata,
sc.external_ins as r_sloc,
rd01.sensor_id as r_sid,
(select rd02.sensor_date
from record_data rd02,
sensor_cfg sc,
energy_classification02 ec
where rd02.sensor_id = rd01.sensor_id and
rd02.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd02.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel = rd02.sensor_channel and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
order by rd02.sensor_data DESC limit 1
) as r_sdate,
rd01.sensor_channel as r_channel
from record_data rd01,
sensor_cfg sc,
energy_classification02 ec
where rd01.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd01.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel = rd01.sensor_channel and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
group by rd01.sensor_id, sc.external_ins, rd01.sensor_channel
) max01,
( select min(rd01.sensor_data) as r_sdata,
rd01.sensor_id as r_sid,
(select rd02.sensor_date
from record_data rd02, sensor_cfg sc, energy_classification02 ec
where rd02.sensor_id= rd01.sensor_id and
rd02.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd02.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel = rd02.sensor_channel and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
order by rd02.sensor_data ASC limit 1
) as r_sdate,
rd01.sensor_channel as r_channel
from record_data rd01, sensor_cfg sc, energy_classification02 ec
where rd01.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd01.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel = rd01.sensor_channel and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
group by rd01.sensor_id, rd01.sensor_channel
) min01,
( select avg(rd01.sensor_data) as r_sdata,
rd01.sensor_id as r_sid,
(select rd02.sensor_date from record_data rd02, sensor_cfg sc, energy_classification02 ec
where rd02.sensor_id = rd01.sensor_id and
rd02.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd02.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel = rd02.sensor_channel and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
order by rd02.sensor_data ASC limit 1
) as r_sdate,
rd01.sensor_channel as r_channel
from record_data rd01, sensor_cfg sc, energy_classification02 ec
where rd01.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd01.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel=rd01.sensor_channel and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
group by rd01.sensor_id,rd01.sensor_channel
) avg01,
energy_classification02 ec,
sensor_cfg sc
where max01.r_sid=min01.r_sid and
min01.r_sid=avg01.r_sid and
max01.r_sid=sc.sensor_id and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel= min01.r_channel and
sc.channel=max01.r_channel and
sc.channel=avg01.r_channel and
sc.external_ins=ec.measure_name and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
) rt_data,
( select round(max01.r_sdata-min01.r_sdata)*100/100 as r_acc_sdata,
max01.r_sid as r_sid, max01.r_sloc as r_sloc,
max01.r_sdate as r_end_sdate,
max01.r_sdata as r_end_sdata,
min01.r_sdate as r_start_sdate,
min01.r_sdata as r_start_sdata
from (
select max(rd01.sensor_date) as r_sdate,
sc.external_ins as r_sloc,
rd01.sensor_id as r_sid,
(select rd02.sensor_data
from record_data rd02,
sensor_cfg sc,
energy_classification02 ec
where rd02.sensor_id = rd01.sensor_id and
rd02.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd02.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.flow_accumulation),'__',2),'channel','myChannel') and
sc.channel = rd02.sensor_channel and
sc.remarks='K_FLOW' and
ec.flowmeter_caliber='I_FLOW'
order by rd02.sensor_date DESC limit 1
) as r_sdata,
rd01.sensor_channel as r_channel
from record_data rd01,
sensor_cfg sc,
energy_classification02 ec
where rd01.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd01.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.flow_accumulation),'__',2),'channel','myChannel') and
sc.channel = rd01.sensor_channel and
sc.remarks='K_FLOW' and
ec.flowmeter_caliber='I_FLOW'
group by rd01.sensor_id, sc.external_ins, rd01.sensor_channel
) max01,
( select min(rd01.sensor_date) as r_sdate,
rd01.sensor_id as r_sid,
(select rd02.sensor_data
from record_data rd02,
sensor_cfg sc,
energy_classification02 ec
where rd02.sensor_id= rd01.sensor_id and
rd02.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd02.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.flow_accumulation),'__',2),'channel','myChannel') and
sc.channel = rd02.sensor_channel and
sc.remarks='K_FLOW' and
ec.flowmeter_caliber='I_FLOW'
order by rd02.sensor_date ASC limit 1
) as r_sdata,
rd01.sensor_channel as r_channel
from record_data rd01,
sensor_cfg sc,
energy_classification02 ec
where rd01.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd01.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.flow_accumulation),'__',2),'channel','myChannel') and
sc.channel = rd01.sensor_channel and
sc.remarks='K_FLOW' and
ec.flowmeter_caliber='I_FLOW'
group by rd01.sensor_id, rd01.sensor_channel
) min01,
energy_classification02 ec,
sensor_cfg sc
where max01.r_sid=min01.r_sid and
max01.r_sid=sc.sensor_id and
sc.channel = replace(split_part(trim(both ' ' from ec.flow_accumulation),'__',2),'channel','myChannel') and
sc.channel= min01.r_channel and
sc.channel=max01.r_channel and
sc.external_ins=ec.measure_name and
sc.remarks='K_FLOW' and
ec.flowmeter_caliber='I_FLOW') acc_data
where acc_data.r_sloc = rt_data.r_sloc
order by r_max01_sloc desc
