Agrupe e resuma o resultado no postgreSQL

Question

Eu tenho uma tabela chamada DETAILS que tem 5 colunas numéricas DETAILS (id, chave2, chave3, num1, num2, num3, num4, num5). A combinação de id, fk1, fk2, fk3, key2 e key3 é a chave primária. Cada id pode ter várias linhas, pois a chave primária é a combinação de (id, fk1, fk2, fk3)

Meu requisito é obter os 10 principais valores SUM de cada coluna agrupados por id conforme abaixo.

select   id
        ,sum(num1) val1
 from details   
group by id
order by sum(num1) desc nulls last
limit 10;

select   id, sum(num2) val2 from details where fk1=$1 group by id order by sum(num2) desc nulls last limit 10; select id, sum(num3) val3 from details where fk1=$1            
group by id
order by sum(num3) desc nulls last
limit 10;

select   id, sum(num4) val4 from details where fk1=$1 group by id order by sum(num4) desc nulls last limit 10; select id,sum(num5) val5 from details where fk1=$1            
group by id
order by sum(num5) desc nulls last
limit 10;

Preciso que os resultados acima sejam combinados com base na id conforme abaixo

id, sum(num1), sum(num2), sum(num3), sum(num4), sum(num5)

Digamos que a primeira consulta retorne

[{id: 1, val1: 70}, {id: 2, val1: 60}, {id: 3, val1: 50}]

a segunda consulta retorna

[{id: 3, val2: 170}, {id: 4, val2: 160}, {id: 3, val2: 150}]

O resultado deve ser

[
{id: 1, val1: 50, val2: null}, 
{id: 2, val1: 60, val2: null}, 
{id: 3, val1: 70, val2: 150},
{id: 4, val1: null, val2: 160},
{id: 5, val1: null, val2: 170},
]

Isso é possível com uma única consulta usando junção ou algo assim? Em caso afirmativo, como faço para fazer isso com uma consulta otimizada?

Este é apenas um tipo de consulta com fk1 na cláusula WHERE. Posso ter que consultar frequentemente com as condições 'WHERE fk2 =$3' OR 'WHERE fk3 = $4 '. Em casos raros, posso ter que consultar as combinações de várias condições em fk1, fk2 e fk3 juntas;

Estou pensando em três abordagens

Abordagem # 1:

• Criar tabelas de resumo smry_id_fk1, smry_id_fk2, smry_id_fk3
• Em cada inserção, atualização e exclusão da tabela DETAILS, somar os valores e inserir / atualizar / excluir as respectivas novas tabelas

Abordagem # 2:

• Crie uma tabela de resumo smry_id_fk1_fk2_fk3 com chave primária (id, fk1, fk2, fk3)

• Em cada inserção, atualização e exclusão da tabela DETAILS, SOMA os valores e insira / atualize / exclua a tabela smry_id_fk1_fk2_fk3, os valores possíveis para smry_id_fk1_fk2_fk3 podem ser

  (1, fk1value, 'N ​​/ A', 'N / A', 50, 60, 0, 0, 80)

  (2, 'N / A, valor fk2,' N / A ', 150, 0, 160, 0, 170)

  (3, 'N / A,' N / A ', fk3value, 0, 0, 200, 210, 220)

Abordagem # 3:

• Não crie nenhuma tabela resumida. Use a consulta otimizada para obter os resultados da própria tabela DETAILS.

Questões:

Qual abordagem é melhor usar? Se a abordagem nº 3 for melhor, como posso alcançar o resultado desejado sem comprometer o desempenho?

Answer 1

Você parece querer ids e somas onde as somas estejam entre as 10 primeiras no geral.

Isso parece uma agregação com funções de janela:

select id,
       (case when seqnum_1 <= 10 then num1 end),
       (case when seqnum_2 <= 10 then num2 end),
       (case when seqnum_3 <= 10 then num3 end),
       (case when seqnum_4 <= 10 then num4 end),
       (case when seqnum_5 <= 10 then num5 end)
from (select id,
             sum(num1) as num1, sum(num2) as num2, sum(num3) as num3, sum(num4) as num4, sum(num5) as num5,
             row_number() over (order by sum(num1) nulls last) as seqnum_1,
             row_number() over (order by sum(num2) nulls last) as seqnum_2,
             row_number() over (order by sum(num3) nulls last) as seqnum_3,
             row_number() over (order by sum(num4) nulls last) as seqnum_4,
             row_number() over (order by sum(num5) nulls last) as seqnum_5
      from details d
      group by id
     ) d
where seqnum_1 <= 10 or seqnum_2 <= 10 or seqnum_3 <= 10 or seqnum_4 <= 10 or seqnum_5 <= 10;