Discussion :

[jelmouss]

Bonjour,

Pourriez-vous m'apporter un peu de votre aide car la je ne comprends pas forcément tout je ce que lis et faits.
J'explique, en se moment je me rafraichis un peu la tête sur du sql, par contre je rencontre quelques difficultés sur les fonctions arithmétiques.(+,-,*,/).

Je suis le tuto actuellement présent sur le site
http://sqlpro.developpez.com/cours/sqlaz/ensembles/

J'en suis à la clause GROUP BY (exemple 5) et aimerais reproduire, mais surtout comprendre d'une façon simple et arithmétique comment nous arrivons à obtenir le résultat (TAUX_OCCUPATION_POURCENT)

PS : Les requêtes ce trouvent dans le lien ci-dessus.

Merci de votre aide et retour.

[tbc92]

La requête dont tu parles est :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT (CAST(SUM(CHB_PLN_CLI_NB_PERS) AS FLOAT) / 
        CAST(SUM(CHB_COUCHAGE) AS FLOAT)) * 100 
        	AS TAUX_OCCUPATION_POURCENT,
       C.CHB_ID
FROM   T_CHAMBRE C
       JOIN TJ_CHB_PLN_CLI CPC
             ON C.CHB_ID = CPC.CHB_ID
       CROSS JOIN T_PLANNING P
WHERE  P.PLN_JOUR BETWEEN '1999-01-01' AND '1999-12-31'
GROUP  BY C.CHB_ID

On va la simplifier un peu, les CAST() me paraissent superflus. La requête qui te pose problème devient donc :

Code :

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

1
2
3
4
5
6
7
8
SELECT SUM(CHB_PLN_CLI_NB_PERS)  / SUM(CHB_COUCHAGE) * 100   	AS TAUX_OCCUPATION_POURCENT,
       C.CHB_ID
FROM   T_CHAMBRE C
       JOIN TJ_CHB_PLN_CLI CPC
             ON C.CHB_ID = CPC.CHB_ID
       CROSS JOIN T_PLANNING P
WHERE  P.PLN_JOUR BETWEEN '1999-01-01' AND '1999-12-31'
GROUP  BY C.CHB_ID

Je vais encore la modifier un peu :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT 
SUM(CHB_PLN_CLI_NB_PERS)  as COL01,
SUM(CHB_COUCHAGE)    aS COL02,
SUM(CHB_PLN_CLI_NB_PERS)  / SUM(CHB_COUCHAGE) * 100   	AS TAUX_OCCUPATION_POURCENT,
       C.CHB_ID
FROM   T_CHAMBRE C
       JOIN TJ_CHB_PLN_CLI CPC
             ON C.CHB_ID = CPC.CHB_ID
       CROSS JOIN T_PLANNING P
WHERE  P.PLN_JOUR BETWEEN '1999-01-01' AND '1999-12-31'
GROUP  BY C.CHB_ID

J'ai ajouté 2 colonnes :
Que représente mon COL01 ?
Que représente mon COL02 ?

[jelmouss]

Bonjour,

Comme représenté ci dessous, la colonne 1 représente la somme du champ 'CHB_PLN_CLI_NB_PERS' de chaque chambre pour l'ensemble des jours du champ 'PLN_JOUR' correspondant au prédicat passé en paramètre.

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT SUM(CHB_PLN_CLI_NB_PERS), C.CHB_ID
FROM   T_CHAMBRE C
       JOIN TJ_CHB_PLN_CLI CPC
            ON C.CHB_ID = CPC.CHB_ID
           CROSS JOIN T_PLANNING P
WHERE  P.PLN_JOUR BETWEEN  TO_DATE('01/01/1999','DD/MM/YYYY') 
AND TO_DATE('31/12/1999','DD/MM/YYYY')
GROUP  BY C.CHB_ID;

Comme représenté ci dessous, la colonne 2 représente la somme du champ 'CHB_COUCHAGE' de chaque chambre pour l'ensemble des jours du champ 'PLN_JOUR' correspondant au prédicat passé en paramètre.

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT SUM(CHB_COUCHAGE) AS MAX_OCCUPATION, C.CHB_ID
FROM   T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI CPC
             ON C.CHB_ID = CPC.CHB_ID
       CROSS JOIN T_PLANNING P
WHERE  P.PLN_JOUR BETWEEN  TO_DATE('01/01/1999','DD/MM/YYYY') 
AND TO_DATE('31/12/1999','DD/MM/YYYY')
GROUP  BY C.CHB_ID;

Se qui au final donne le résultat ci-dessous:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT 
SUM(CHB_PLN_CLI_NB_PERS)  as COL01,
SUM(CHB_COUCHAGE)    aS COL02,
SUM(CHB_PLN_CLI_NB_PERS)  / SUM(CHB_COUCHAGE) * 100   	AS TAUX_OCCUPATION_POURCENT,
       C.CHB_ID
FROM   T_CHAMBRE C
       JOIN TJ_CHB_PLN_CLI CPC
             ON C.CHB_ID = CPC.CHB_ID
       CROSS JOIN T_PLANNING P
WHERE  P.PLN_JOUR BETWEEN TO_DATE('01/01/1999','DD/MM/YYYY') 
AND TO_DATE('31/12/1999','DD/MM/YYYY')
GROUP  BY C.CHB_ID;

Ps : en faite, pour résumé un peu mon manque de compréhension arithmétique sur cette exemple. Lors de mon calcul, je n'arriver pas à récupérer le même pourcentage car je ne croisé pas avec la colonne PLN_JOUR de la table T_Planning, or la le résultat final me semble beaucoup plus compréhensible. A noté quand même que je dois combler mon ptit manque (comprehension sql) sur ce type de cas.

Merci pour ton aide.

[jelmouss]

Salut tbc92,

Pourrais-tu à nouveau m'apporter ton aide en décomposant la requête ci-dessous car je ne comprend pas comment le résultat 'CA' est obtenu.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT F.CLI_ID,
-- le calcul est le suivant : 
-- (quantité * montant * remise en %) - remise en francs
-- les colonnes "remises" pouvant avoir des valeurs NULL
-- il faut les remplacer par un zéro à l'aide de la fonction COALESCE
       SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
                                       - COALESCE(LIF_REMISE_MONTANT, 0)) AS CA
FROM   T_FACTURE F
       INNER JOIN T_LIGNE_FACTURE L
             ON F.FAC_ID = L.FAC_ID
WHERE  EXTRACT(YEAR FROM F.FAC_DATE) = 2000
GROUP  BY F.CLI_ID
HAVING SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
                                       - COALESCE(LIF_REMISE_MONTANT, 0)) < 15000;

J'ai bien essayé de décomposer la requête mais sans succès.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
-- (quantité * montant * remise en %) - remise en francs
SELECT F.CLI_ID,
SUM(LIF_QTE) AS COL1,
SUM(LIF_MONTANT) AS COL2,
SUM(LIF_REMISE_POURCENT) AS COL3,
SUM(LIF_REMISE_MONTANT) AS COL4,
SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
                               - COALESCE(LIF_REMISE_MONTANT, 0)) AS CA
FROM T_FACTURE F
INNER JOIN T_LIGNE_FACTURE L
ON F.FAC_ID = L.FAC_ID
WHERE EXTRACT(YEAR FROM F.FAC_DATE) = 2000
GROUP BY F.CLI_ID
HAVING SUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
                                       - COALESCE(LIF_REMISE_MONTANT, 0)) < 15000;

[tbc92]

Dans la 2ème requête que tu donnes, les colonnes COL1 COL2 COL3 et COL4 n'ont aucun sens.

Si dans ma base de données j'ai :
Vente 1 : 4 chaises à 50 € avec une remise de 3%
Vente 2 : 1 table à 200 € avec une remise de 5%
La colonne COL2 va donner 50 + 200 = 250€ ... ce qui ne représente rien du tout.
Idem COL3 va donner 3%+5% , soit 8% ... qui n'a aucun sens non plus.
Idem COL1 a un sens, mais pas beaucoup d'intérêt, COL1 va donner 4 + 1 : Nombre total d'articles vendus.

Pour comprendre une requête avec un group-By, la démarche est souvent d'exécuter une requête un peu similaire, mais sans la clause GROUP-BY.

Donc , au lieu d'afficher sum(un_certain_calcul), on va afficher uniquement un_certain_calcul.
Ca devient :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT F.CLI_ID,
      (LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
                                       - COALESCE(LIF_REMISE_MONTANT, 0)) AS CA
FROM   T_FACTURE F
       INNER JOIN T_LIGNE_FACTURE L
             ON F.FAC_ID = L.FAC_ID
WHERE  EXTRACT(YEAR FROM F.FAC_DATE) = 2000;

Et pour mieux comprendre le calcul, on peut aussi exécuter cette requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT F.CLI_ID,
      F.FAC_ID, 
      LIF_QTE, 
      LIF_MONTANT ,
      LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))  as MONTANT_MOINS_REMISE_POURCENT ,
      LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100, 0))) - COALESCE(LIF_REMISE_MONTANT, 0)  as PRIX_VENTE_FINAL ,
      (LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
                                       - COALESCE(LIF_REMISE_MONTANT, 0)) AS CA
FROM   T_FACTURE F
       INNER JOIN T_LIGNE_FACTURE L
             ON F.FAC_ID = L.FAC_ID
WHERE  EXTRACT(YEAR FROM F.FAC_DATE) = 2000;

Cette démarche est utile si tu peux exécuter cette requête, et regarder de près les résultats obtenus. Si tu es juste dans la théorie, pas sûr que ça aide.
Difficile de t'aider plus ...

Et dernier point, l'utilisation de COALESCE ici ne me convainc pas du tout ; j'aurais préféré NVL.

[Waldar]

Et dernier point, l'utilisation de COALESCE ici ne me convainc pas du tout ; j'aurais préféré NVL.

Pourquoi ?