Search Postgresql Archives

How to save the results of replace(split_part(trim(both ' ' from "vx6000__12channel"),'__',2),'channel','myChannel') in my query into a temp variable?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux