Discussion :

[mathieu_BO]

Hello,
Je re-découvre ce forum et j'espère bien y amener ma contribution quand dispo.
Aujourd'hui voici ma question : quelle est la meilleure des deux requêtes ci-dessous ou tout ou à refaire ?

Objectif : avoir 2 group by sur donc 2 niveaux différents et ainsi faire un pourcentage (genre le mois de Janvier 2016 rapporte que 5% du chiffre d'affaire total de 2016 etc.

R1 : INNER JOIN SELECT

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SELECT a.col1, a.col2, a.col3, sum(a.amt), b.amt, sum(a.amt)/b.amt as PERCENTAGE
FROM MyTable a
INNER JOIN (
    SELECT b.col1, b.col2, sum(b.amt)
    FROM MyTable b
    GROUP BY b.col1, b.col2
    ) b ON a.col1 = b.col1 AND a.col2 = b.col2
GROUP BY a.col1,a.col2, a.col3, b.amt

R2: SELECT (SELECT (..))

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT a.col1, a.col2, a.col3, sum(a.amt), 
    (SELECT  sum(b.amt)
    FROM MyTable b
    WHERE a.col1 = b.col1 AND a.col2 = b.col2
    ) as 
FROM MyTable a
GROUP BY a.col1,a.col2, a.col3

Pour info, je suis sur db2, mais la réponse est intéressante/différente pour tout type de base je pense.

Merci

Cdlt

[CinePhil]

La requête corrélée (R2) est en principe moins performante que la jointure (R1) parce que la requête corrélée doit être exécutée pour chaque ligne alors que la jointure opère sur l'ensemble.

Ceci dit, ne manquerait-il pas quelque chose dans vos requêtes ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT b.col1, b.col2, sum(b.amt) AS b.amt

et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT  sum(b.amt) AS b.amt

Parce que sinon, je ne vois pas à quoi sert ce calcul dans la sous-requête vu qu'il n'est pas réutilisé dans la requête principale !

[escartefigue]

Bonsoir,

Pour comparer les performances de plusieurs requêtes utilisez un EXPLAIN

[mathieu_BO]

Bonjour @CinePhil,
Merci pour la réponse, c'est effectivement ce que je pensais, d'un point de vue perso je trouve çà logique qu'écrit de cette façon le moteur se "retape" un GROUP BY pour chaque ligne pour y appliquer sa condition. Il me semble qu'un Expert MSSQL m'avait conseiller la R1 aussi.
Pour ta question j'ai effectivement oublié des AS ou autres erreurs de column naming en écrivant ma requête, oups. Mais je réutilise bien les élements des sous group by plus haut, notamment pour le pourcentage ou visualisation tout simplement.

@escartefigue, je n'arrive pas utiliser la clause Explain apparemment il me faut des tables en plus, j'ai pas trop envie de toucher à la DB, je vais voir ce que je peux faire pour analyser les plan d’exécution autrement.
Pour le moment mes tests sont inexploitables vu le peu de données et rapidité de temps de traitement.

thanks both of you, je reviendrais si j'ai les plans d’exécution

[CinePhil]

Quel SGBD utilisez-vous ?

[aieeeuuuuu]

Bonjour,

A mon avis, ceci devrait être plus performant, en évitant la sous requete et donc un scan de table.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT 
        col1
    ,   col2
    ,   col3
    ,   SUM(amt) AS montantMensuel
    ,   100 * SUM(amt) / SUM(SUM(amt)) OVER() AS Pourcentage
FROM LaTable
GROUP BY 
        col1
    ,   col2
    ,   col3

[mathieu_BO]

Quel SGBD utilisez-vous ?

Je suis sur db2 (je cherche la version exacte là, pas accès en ligne de commande seulement via DBeaver(sql)) sur un AS400.

@aieeeuuuuu,
j'ai regardé sur le net le OVER, OVER (Partition By) ca semble en effet correspondre aux pourcentages, à voir comment manipuler en détail avec plus dimensions.
Par contre j'ai pas trouvé cette fonction sur db2, mais cette méthode semble intéressante pour d'autres systèmes !

[escartefigue]

@escartefigue, je n'arrive pas utiliser la clause Explain apparemment il me faut des tables en plus, j'ai pas trop envie de toucher à la DB, je vais voir ce que je peux faire pour analyser les plan d’exécution autrement.

Sous DB2, un EXPLAINrequiert de façon obligatoire la table PLAN_TABLE et de façon facultative la DSN_STATEMNT_TABLE

La première sert à recueillir la stratégie de l'optimiseur (index utilisés, colonne d'index, méthode, ordre de résolution, tris éventuels...)
La deuxième permet d'estimer le coût CPU et MSU

Créer l'une et/ou l'autre table ne prend que quelques secondes ce n'est pas ça qui doit vous retenir, d'autant que l'EXPLAINest un outil indispensable que tout développeur devrait maitriser

Demandez à un tiers de créer les tables pour vous (et de vous octroyer les droits bien sur ) si vous ne maitrisez pas le DDL