Select com subconsulta

[Matheus Suffi]

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

Editado Fevereiro 27, 2015 por Kakao
formatação de código

[Dr. House]

seria legal usar o format de codigo aqui, fica mais faicl de ler...

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

[Kakao]

Parece que é mais ou menos isso:

select
    q.agent,
    q.queuename as queuename,
    rm.nome as Agent,
    count(event like '%ABANDON%' or null) as perdida,
    count(event like '%COMPLETE%' or null) as atendida,
    count(event = 'TRANSFER' or null) as transferida,
from
    queue_log q  
    inner join
    f_ramal_virtual rm on rm.ramal_virtual = q.agent  
where
    q.event != 'RINGNOANSWER' 
    and
    (${prmnomefila} is null or q.queuename = ${prmnomefila})
    and
    array[
        time::date = current_date - 1,
        time::date = current_date - 7,
        time::date = current_date - 30,
        time::date between ${prmdatainicial} and ${prmdatafinal}
    ][${prmintervalo}]
group by 1, 2, 3