Regrouper et résumer le résultat dans postgreSQL

Question

J'ai une table appelée DETAILS qui a 5 colonnes numériques DETAILS (id, key2, key3, num1, num2, num3, num4, num5). La combinaison de id, fk1, fk2, fk3, key2 et key3 est la clé primaire. Chaque id peut avoir plusieurs lignes car la clé primaire est la combinaison de (id, fk1, fk2, fk3)

Mon exigence est d'obtenir les 10 premières valeurs SUM de chaque colonne regroupées par identifiant comme ci-dessous.

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;

J'ai besoin que les résultats ci-dessus soient combinés en fonction de l'identifiant ci-dessous

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

Disons que la première requête retourne

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

la deuxième requête renvoie

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

Le résultat devrait être

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

Est-ce possible avec une seule requête utilisant la jointure ou quelque chose? Si oui, comment puis-je y parvenir avec une requête optimisée?

C'est juste un type de requête avec fk1 dans la clause WHERE. Je peux avoir à interroger fréquemment avec les conditions 'WHERE fk2 =$3' OR 'WHERE fk3 = $4 '. Dans de rares cas, je peux avoir à interroger avec les combinaisons de plusieurs conditions sur fk1, fk2 et fk3 ensemble;

Je pense à trois approches

Approche n ° 1:

• Créer des tables récapitulatives smry_id_fk1, smry_id_fk2, smry_id_fk3
• A chaque insertion, mise à jour et suppression de la table DETAILS, additionner les valeurs et insérer / mettre à jour / supprimer les nouvelles tables respectives

Approche n ° 2:

• Créer une table récapitulative smry_id_fk1_fk2_fk3 avec la clé primaire (id, fk1, fk2, fk3)

• À chaque insertion, mise à jour et suppression de la table DETAILS, SOMMEZ les valeurs et insérez / mettez à jour / supprimez la table smry_id_fk1_fk2_fk3 les valeurs possibles pour smry_id_fk1_fk2_fk3 pourraient être

  (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)

Approche n ° 3:

• Ne créez pas de tableaux résumés. Utilisez une requête optimisée pour obtenir les résultats de la table DETAILS elle-même.

Des questions:

Quelle est la meilleure approche? Si l'approche n ° 3 est meilleure, comment obtenir le résultat souhaité sans compromettre les performances?

Answer 1

Vous semblez vouloir des identifiants et des sommes où les sommes sont dans le top 10 général.

Cela ressemble à une agrégation avec des fonctions de fenêtre:

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;