Ir para conteúdo
Fórum Script Brasil
  • 0

Select com subconsulta


Matheus Suffi

Pergunta

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 por Kakao
formatação de código
Link para o comentário
Compartilhar em outros sites

2 respostass a esta questão

Posts Recomendados

  • 0

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
Link para o comentário
Compartilhar em outros sites

  • 0

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
Link para o comentário
Compartilhar em outros sites

Participe da discussão

Você pode postar agora e se registrar depois. Se você já tem uma conta, acesse agora para postar com sua conta.

Visitante
Responder esta pergunta...

×   Você colou conteúdo com formatação.   Remover formatação

  Apenas 75 emoticons são permitidos.

×   Seu link foi incorporado automaticamente.   Exibir como um link em vez disso

×   Seu conteúdo anterior foi restaurado.   Limpar Editor

×   Você não pode colar imagens diretamente. Carregar ou inserir imagens do URL.



  • Estatísticas dos Fóruns

    • Tópicos
      152k
    • Posts
      651,8k
×
×
  • Criar Novo...