Unificar coluna Mês

[oracle_student]

Olá.
Preciso de uma ajuda (oracle)

Eu tenho uma consulta SQL que pega a soma de um campo float em determinado mês. Mas não estou conseguindo unificar esses meses quando eu mapeio
o campo mês para a consulta principal e com isso está duplicando meu resultado.

O resultado que preciso seria isso:
month | client_id | value_one | value_two | value_three | value_four

Essa é minha consulta SQL

Selecionar tudo

SELECT
    client.client_id,
    value_one.value_one,
    value_one.month_one
    value_two.value_two,
    value_two.month_two,
    value_three.value_three,
    value_three.month_three,
    value_four.value_four,
    value_four.month_four

FROM
    client,
    (SELECT to_char(to_timestamp(t1.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') as month_one,
            client_id,
            sum(value) as value_one
       FROM value_one,
            dim_time t1
      WHERE value_one.dt_timestamp_id = t1.time_id
   GROUP BY to_char(to_timestamp(t1.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm'), client_id
    ) value_one,
    (SELECT to_char(to_timestamp(t2.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') as month_two,
            client_id,
            sum(value) as value_two
       FROM value_two,
            dim_time t2
      WHERE value_two.dt_timestamp_id = t2.time_id
   GROUP BY to_char(to_timestamp(t2.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm'), client_id
    ) value_two,
    (SELECT to_char(to_timestamp(t3.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') as month_three,
            client_id,
            sum(value) as value_three
       FROM value_three,
            dim_time t3
      WHERE value_three.dt_timestamp_id = t3.time_id
   GROUP BY to_char(to_timestamp(t3.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm'), client_id
    ) value_three,
    (SELECT to_char(to_timestamp(t4.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') as month_four,
            client_id,
            sum(value) as value_four
       FROM value_four,
            dim_time t4
      WHERE value_three.dt_timestamp_id = t4.time_id
   GROUP BY to_char(to_timestamp(t4.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm'), client_id
    ) value_four
WHERE client.client_id = value_one.client_id(+)
  AND client.client_id = value_two.client_id(+)
  AND client.client_id = value_three.client_id(+)
  AND client.client_id = value_four.client_id(+)

Alguém sabe como resolve isso?

[dr_gori]

Isso está ocorrendo porque as suas tabelas tem um group by por CLIENTE e POR mês.
Veja:

Selecionar tudo

GROUP BY to_char(to_timestamp(t1.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm'), client_id

Então isso está retornando multiplas linhas pra cada cliente.
Eu acho que faria isso de forma diferente. Algo assim:

Selecionar tudo

select month, client_id, sum(value_one), sum(value_two), sum(value_three), sum(value_four)
from 
  (

    SELECT to_char(to_timestamp(t1.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') as month,
            client_id
            , sum(value) as value_one
            , NULL value_two
            , NULL value_three
            , NULL value_four
        FROM value_one,
            dim_time t1
        WHERE value_one.dt_timestamp_id = t1.time_id
    GROUP BY to_char(to_timestamp(t1.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm'), client_id

    UNION ALL

    SELECT to_char(to_timestamp(t2.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') as month,
            client_id
            , NULL value_one
            , sum(value) as value_two
            , NULL value_three
            , NULL value_four
        FROM value_two,
            dim_time t2
        WHERE value_two.dt_timestamp_id = t2.time_id
    GROUP BY to_char(to_timestamp(t2.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm'), client_id

    UNION ALL

    SELECT to_char(to_timestamp(t3.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') as month,
            client_id
            , NULL value_one
            , NULL value_two
            , sum(value) as value_three
            , NULL value_four
        FROM value_three,
            dim_time t3
        WHERE value_three.dt_timestamp_id = t3.time_id
    GROUP BY to_char(to_timestamp(t3.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm'), client_id

    UNION ALL

    SELECT to_char(to_timestamp(t4.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') as month,
            client_id
            , NULL value_one
            , NULL value_two
            , NULL value_three
            , sum(value) as value_four
        FROM value_four,
            dim_time t4
        WHERE value_three.dt_timestamp_id = t4.time_id
    GROUP BY to_char(to_timestamp(t4.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm'), client_id
  )
group by month, client_id
order by 1,2

OU ainda melhor: Eu tiraria cada GROUP BY original, pra não ter vários group by...

Selecionar tudo

select month, client_id, sum(value_one), sum(value_two), sum(value_three), sum(value_four)
from 
  (

    SELECT to_char(to_timestamp(t1.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') as month,
            client_id
            , value as value_one
            , NULL value_two
            , NULL value_three
            , NULL value_four
        FROM value_one,
            dim_time t1
        WHERE value_one.dt_timestamp_id = t1.time_id
    
    UNION ALL

    SELECT to_char(to_timestamp(t2.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') as month,
            client_id
            , NULL value_one
            , value as value_two
            , NULL value_three
            , NULL value_four
        FROM value_two,
            dim_time t2
        WHERE value_two.dt_timestamp_id = t2.time_id

    UNION ALL

    SELECT to_char(to_timestamp(t3.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') as month,
            client_id
            , NULL value_one
            , NULL value_two
            , value as value_three
            , NULL value_four
        FROM value_three,
            dim_time t3
        WHERE value_three.dt_timestamp_id = t3.time_id

    UNION ALL

    SELECT to_char(to_timestamp(t4.date_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'mm') as month,
            client_id
            , NULL value_one
            , NULL value_two
            , NULL value_three
            , value as value_four
        FROM value_four,
            dim_time t4
        WHERE value_three.dt_timestamp_id = t4.time_id
  )
group by month, client_id
order by 1,2