Raggruppa e riassumi il risultato in postgreSQL

Question

Ho una tabella chiamata DETTAGLI che ha 5 colonne numeriche DETTAGLI (id, key2, key3, num1, num2, num3, num4, num5). La combinazione di id, fk1, fk2, fk3, key2 e key3 è la chiave primaria. Ogni id può avere più righe poiché la chiave primaria è la combinazione di (id, fk1, fk2, fk3)

Il mio requisito è ottenere i primi 10 valori SUM di ogni colonna raggruppati per ID come di seguito.

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;

Ho bisogno che i risultati di cui sopra siano combinati in base all'ID come di seguito

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

Diciamo che la prima query restituisce

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

la seconda query restituisce

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

Il risultato dovrebbe essere

[
{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},
]

È possibile con una singola query utilizzando join o qualcosa del genere? In caso affermativo, come posso ottenere questo risultato con una query ottimizzata?

Questo è solo un tipo di query con fk1 nella clausola WHERE. Potrei dover interrogare frequentemente con le condizioni 'WHERE fk2 =$3' OR 'WHERE fk3 = $4 '. In rari casi potrei dover interrogare con le combinazioni di più condizioni su fk1, fk2 e fk3 insieme;

Sto pensando a tre approcci

Approccio n. 1:

• Crea tabelle di riepilogo smry_id_fk1, smry_id_fk2, smry_id_fk3
• Ad ogni inserimento, aggiornamento e cancellazione della tabella DETTAGLI, SOMMA i valori e inserisci / aggiorna / cancella le rispettive nuove tabelle

Approccio n. 2:

• Crea una tabella di riepilogo smry_id_fk1_fk2_fk3 con chiave primaria (id, fk1, fk2, fk3)

• Su ogni inserimento, aggiornamento ed eliminazione della tabella DETTAGLI, SOMMA i valori e inserisci / aggiorna / elimina la tabella smry_id_fk1_fk2_fk3 i valori possibili per smry_id_fk1_fk2_fk3 potrebbero essere

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

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

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

Approccio n. 3:

• Non creare tabelle riepilogative. Utilizza la query ottimizzata per ottenere i risultati dalla tabella DETAILS stessa.

Domande:

Quale approccio è meglio seguire? Se l'approccio n. 3 è migliore, come posso ottenere il risultato desiderato senza compromettere le prestazioni?

Answer 1

Sembra che tu voglia ID e somme in cui le somme sono tra i primi 10 in generale.

Sembra un'aggregazione con le funzioni della finestra:

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;