Início / dba / Perguntas / 107339
Accepted
jackhammer013
Asked: 2015-07-18 05:06:31 +0800 CST

Uso adequado de Group by em Inner Join

  • 772

EDIT Atualizei minha pergunta com uma amostra do SQL Fiddle http://sqlfiddle.com/#!15/8d88b/1

Atualmente, estou fazendo um relatório a partir de registros de um banco de dados, mas não sei como minha consulta deve ficar, antes de mais nada, tenho 2 tabelas. Formulários de Candidatura e uma tabela de Horas de Login de cada utilizador

forms
->id
->agent_id
->SomeInfo
->created_at


loginhours
->id
->user_id
->loginhours (decimal)
->created_at

E eu tenho relatório com as seguintes colunas

UserID, TotalLoginHours, TotalApplication, Application Per Hour (aph), Revenue Per Hour (rph)

Então agora eu tenho essa consulta

SELECT a.agent_id, SUM(b.loginhours) as TotalLoginHours, COUNT(a.id) as TotalApplication, SUM(b.loginhours) / COUNT(a.id) as ApplicationPerHour,   (SUM(b.loginhours) / COUNT(a.id)) * 1.75 as RPH 
FROM forms a 
INNER JOIN loginhours b ON a.agent_id = b.user_id WHERE a.created_at = '2015-07-17' 
GROUP BY a.agent_id

Note que user_ide agent_idé o mesmo.

Quero obter o resultado com base na data selecionada, exemplo 2015-07-17, obtive resultados, mas meu problema é loginhoursque está sendo SUMbaseado no número de aplicativos de cada usuário. Então, por exemplo user1, tem 2 registros na formstabela e o loginhoursfrom dele 2015-07-17está 2no meu resultado, o que está loginhourserrado 4, acho que está na minha GROUP BYdeclaração. Você pode me ajudar a consultar isso corretamente? Obrigado

postgresql group-by

2 respostas

  1. Para evitar o efeito de multiplicação que você está experimentando, você pode fazer a agregação primeiro e depois juntar:

    select a.agent_id, a.TotalApplication, b.TotalLoginHours
     , b.TotalLoginHours / a.TotalApplication as ApplicationPerHour
     , 1.75 * b.TotalLoginHours / a.TotalApplication as RPH
from (
    SELECT a.agent_id as agent_id
         , COUNT(a.id) as TotalApplication
    FROM forms a
    WHERE a.created_at = '2015-07-17' 
    GROUP BY a.agent_id
 ) as a
 join (
     select b.user_id as agent_id
          , SUM(b.loginhours) as TotalLoginHours
     from loginhours b 
     group by b.user_id
 ) as b 
     ON a.agent_id = b.agent_id
 ;

    Não tenho certeza se acertei todos os detalhes, mas deve dar uma dica de uma técnica que pode ser usada. Modifiquei seu violino em: http://sqlfiddle.com/#!15/8d88b/9

    • 2
  2. Best Answer

    Consulta por agente por dia com nome de usuário.

    Reenquadre a consulta de acordo com sua tabela de usuário.

        SELECT
        A.agent_id,
        users.NAME,
        A.created_at,
        SUM (TotalLoginHours) AS TotalLoginHours,
        COUNT (A.ID) AS TotalApplication,
        SUM (TotalLoginHours) / COUNT (A.ID) AS ApplicationPerHour,
        (
            SUM (TotalLoginHours) / COUNT (A.ID)
        ) * 1.75 AS RPH
    FROM
        (
            SELECT
                A.agent_id,
                A.ID,
                A.created_at,
                SUM (b.loginhours) AS TotalLoginHours
            FROM
                forms A
            INNER JOIN loginhours b ON A.agent_id = b.user_id
            WHERE
                A.created_at >= '2015-07-10'
            GROUP BY
                A.agent_id,
                A.ID,
                A.created_at
        ) A
    INNER JOIN users ON users.user_id = A.agent_id
    GROUP BY
        A.agent_id,
        users.user_id,
        A.created_at
    • 0

relate perguntas