eu tenho um subselect que demora quaze 20 minutos para completar a consulta, ele usa de 2 subqueries , gostaria de saber se há um metodo que substitua essas 2 subqueries que estão sendo usadas. Query: SELECT DISTINCT on (q.agent)
rm.nome as Agent,
(
Select count(event)
from queue_log
where
(event LIKE '%ABANDON%')
AND
CASE
WHEN ${prmIntervalo} = '4' THEN time::date between to_date(${prmDataInicial},'YYYY-mm-dd') and to_date(${prmDataFinal},'YYYY-mm-dd')
WHEN ${prmIntervalo} = '1' THEN time::date = CURRENT_DATE - 1
WHEN ${prmIntervalo} = '2' THEN time::date = CURRENT_DATE - 7
WHEN ${prmIntervalo} = '3' THEN time::date = CURRENT_DATE - 30
END
AND
queuename = q.queuename
) as perdida,
(
Select count(event)
from queue_log
where
(event LIKE '%COMPLETE%')
AND
CASE
WHEN ${prmIntervalo} = '4' THEN time::date between to_date(${prmDataInicial},'YYYY-mm-dd') and to_date(${prmDataFinal},'YYYY-mm-dd')
WHEN ${prmIntervalo} = '1' THEN time::date = CURRENT_DATE - 1
WHEN ${prmIntervalo} = '2' THEN time::date = CURRENT_DATE - 7
WHEN ${prmIntervalo} = '3' THEN time::date = CURRENT_DATE - 30
END
AND agent = q.agent
) as Atendida,
(
Select count(event)
from queue_log
where
event = 'TRANSFER'
AND
CASE
WHEN ${prmIntervalo} = '4' THEN time::date between to_date(${prmDataInicial},'YYYY-mm-dd') and to_date(${prmDataFinal},'YYYY-mm-dd')
WHEN ${prmIntervalo} = '1' THEN time::date = CURRENT_DATE - 1
WHEN ${prmIntervalo} = '2' THEN time::date = CURRENT_DATE - 7
WHEN ${prmIntervalo} = '3' THEN time::date = CURRENT_DATE - 30
END
AND agent = q.agent
) as Transferida ,
q.queuename as queuename
FROM
queue_log q
INNER JOIN
f_ramal_virtual rm ON rm.ramal_virtual = q.agent
WHERE
q.event != 'RINGNOANSWER'
AND
CASE
WHEN NULLIF(${prmNomeFila},NULL) is NOT NULL THEN (q.queuename = ${prmNomeFila})
END
AND
CASE
WHEN ${prmIntervalo} = '4' THEN time::date between to_date(${prmDataInicial},'YYYY-mm-dd') and to_date(${prmDataFinal},'YYYY-mm-dd')
WHEN ${prmIntervalo} = '1' THEN time::date = CURRENT_DATE - 1
WHEN ${prmIntervalo} = '2' THEN time::date = CURRENT_DATE - 7
WHEN ${prmIntervalo} = '3' THEN time::date = CURRENT_DATE - 30
END